성능을 향상 쿼리 튜닝
[멀티캠퍼스] ★PDF 교재 파일 전달
PDF PW : FA00XK
https://classnow.webex.com
http://naver.me/FcgGEiOK
1111
25124970655
1234
CLOUD
NAME: clould_ora01
사용자 : ora01
비밀번호 : oracle_4U2023 v
접속유형 : 클라우드 전자지갑
구성파일 : wallet_edudb.zip
--Chapter 1 인덱스 활용
--Chapter 2 join 문장을 이용한 데이터 연결
--Chapter 3 Subquery를 이용한 데이터 연결
--Chapter 4 그룹 함수 활용
--Chapter 5 분석 함수 활용
--Chapter 6 계층형 쿼리
--Chapter 7 정렬 작업 속도 개선
Shared Pool
parse 컴파일
1) parse - Shared Pool (Library Cache)
SQL text
실행계획
p-code ==> Shared Pool 의 Library
soft parse
hard parse
2) bind
3) execute : DBC : data block의 복사본
db_block_size = 8k
logical reads :
physical reads :
cache hit ratio 중요 :
1000 1000
100 50
server tuning
sql tuning
4) fetch -- only select
실행계획
Chapter1
인덱스 활용
인덱스의 주된 목표
- select 를 빨리하기 위해서
B* Tree (발란스 트리구조)
select EMPNO, ROWIND from EMP order by EMPNO;
select * from TABLE (DBMS_XPLAN.DISPLAY_CURSOR); --실행계획
set linesize 200
set pagesize 100
--SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allSTATS LAST')) ;
SELECT * FROM table(dbms_xplan.display_cursor) ;
@c:\LABS\XPLAN
도구
-환경설정
-데이터베이스
@XPLAN -- 환경설정 도구-환경설정-데이터베이스-워크시트 -스크립트를 찾을기본 경로선택 ㅊ
리버스 키인덱스
실행계획
SELECT *
FROM EMP
WHERE DEPTNO = 20;
WHAT
HOW -- PROGRAM
EXE PLAN
PARSE : 실행계획 : OPTIMIZER : RULE, COST
FULL TABLE SCAN : 5
INDEX :
FTS
optimizer_mode = all_rows (default) : 전체처리를 가장 빠르기
first_rows : response time을 빠르기
full table scan : 5
cost :
EXECUTE
FETCH
;
show parameter optimizer;
/**/ BLOCK Comment 힌트
힌트
INDEX scan
인덱스 스캔
인덱스 access
index scan
range
table random access HWM
시퀀셔 엑세스
인덱스를 제대로 못탈 경우 풀테이블 스캔이 더 나음
6가지 인덱스 스캔
-1.인덱스 유니크 스캔
SELECT *
FROM EMP
WHERE ENAME ='SCOTT'
@XPLAN
SELECT ENAME, ROWID FROM EMP ORDER BY 1;
힌트 : index (테이블 명 인덱스명)
index_rs (테이블명 인덱스명)
index_rs (테이블명(컬럼명))
index_rs_asc ( 테이블명(컬럼명))
index_rs_desc (테이블명(컬럼명))
인덱스 풀스캔일 경우
/*+ index_ffs(e(employee_id)) */
select empno
from emp
order by 1
;
select EMPNO
from EMP;
;
@XPLAN
select empno, rowid from emp order by empno;
select * from employees;
select /*+ index_join (e EMPLOYEES_IDX02 EMPLOYEES_ID04 ) */
department_id, JOB_id
FROM employees e
where department_id = 80
and job_id ='SA_REP'
;
인덱스 스킵 스캔
select /*+ index_ss (e empl_name ix) */ employee_id, first_name, last_name, salary,
department_id
from employees e
where first_name ='Steven';
인덱스 활용
인덱스 스캔 불가 사례
1. <>, !=, ^= 등 부정형의 비교
2. 인덱스 컬럼의 변형이 발생한 경우
3. Null 인 데이터를 조회하는 경우
4.like '%문자열' 사용하는경우
NESTED LOOP JOIN
9I NLJ PREFETCH
11g, NLJ_BATCHING 물리적읽기로 인한 지연을 줄이기 위해서
DBC 데이터베이스 버퍼 캐쉬
인덱스 통해서 블럭단위를 읽을 때
https://www.multicampus.com/common/board/boardMstr/124387?p_menu=MzU3I01BSU4=&p_gubun=Qw==
내선번호 02-3429-5740
설문은 종료 1시간전에 한다고 합니다
-- USER SQL
--ALTER USER "TEST"
--DEFAULT TABLESPACE "SYSTEM"
--TEMPORARY TABLESPACE "TEMP"
--ACCOUNT UNLOCK ;
-- QUOTAS
-- ROLES
--ALTER USER "TEST" DEFAULT ROLE "DBA","CONNECT","RESOURCE";
-- SYSTEM PRIVILEGES
--;
select empno
from emp
order by 1
;
select EMPNO
from EMP;
--@XPLAN
;
select empno, rowid from emp order by empno;
select * from employees;
select /*+ index_join (e EMPLOYEES_IDX02 EMPLOYEES_ID04 ) */
department_id, JOB_id
FROM employees e
where department_id = 80
and job_id ='SA_REP'
;
@IDX
70
70
80 R1
80 R3
80 R7
80 R11
JOB_ID
....
SA_REP R3
SA_REP R4
SA_REP R6
SA_REP R11
SA_REP R20
;
--@XPLAN
select /*+ index_join (e EMPLOYEES_IDX02 EMPLOYEES_ID04 ) */
department_id, JOB_id
FROM employees e
where department_id = 80
and job_id ='SA_REP'
;
--@XPLAN
select /*+ index_join (e EMPLOYEES_IDX02 EMPLOYEES_ID04 ) */
department_id, JOB_id, salary
FROM employees e
where department_id = 80
and job_id ='SA_REP'
;
SELECT * FROM employees where age BETWEEN 20 and 29
;
create index empl_name_ix ON employees(last_name, first_name);
select /*+ index_ss (e empl_name ix) */ employee_id, first_name, last_name, salary,
department_id
from employees e
where first_name ='Steven'
;
select /*+ INDEX (EMP) */ *
from EMP
WHERE DEPTNO <> 20;
SELECT DEPTNO, ROWID FROM EMP ORDER BY 1;
;
SELECT *
FROM CUSTS
WHERE CUST_ID LIKE '7%'
;
--@XPLAN
;
select *
from employees
where 1 = 1
and first_name = 'Peter'
;
-- create inex
CREATE INDEX EMP_FNAME_IX ON EMPLOYEES ( FIRST_NAME) ;
CREATE INDEX EMP_FNAME_FBI ON EMPLOYEES ( UPPER(FIRST_NAME) );
CREATE INDEX EMP_FANME_FBI ON EMPLOYEES( UPPER(FIRST_NAME));
CREATE INDEX EMP_FNAME_IX ON EMPLOYEES ( FIRST_NAME) ;
;
select *
from employees
where upper(
;
update cust set cust_email =null
where cust_id in (100,200);
;
show parameter statistics level
;
alter SYSTEM SET STATISTICS_LEVEL =ALL;
;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allSTATS LAST +cost')) ;
create index cust_email_fbi on custs(nvl(cust_email,'NULL'));
select * from custs
where NBL(cust_email,'null') = 'NULL'
;
@IDX
;;
select *
from emp
;
select * from emp
where ename like 'S%'
;
--@XPLAN
select * from emp
where ename like '%S'
;
--@XPLAN
;
select reverse(ENAME), ROWID from emp order by 1
;
CREATE INDEX emp_ename_fbi ON emp(REVERSE(ename));
select *
from emp
where REVERSE(ename) LIKE 'TT%'
;
SELECT EMPNO, ENAME, HIREDATE, TO_CHAR(HIREDATE, 'YYYY/MM/DD HH24:MI:SS')
FROM EMP
;
SELECT EMPNO, ENAME, HIREDATE
FROM EMP
WHERE HIREDATE ='81/04/02'
;
SELECT EMPNO, ENAME, HIREDATE
FROM EMP
WHERE HIREDATE ='81/04/02'
;
NLS_DATE_FORMAT ='RR/MMDD';
SELECT EMPNO, ENAME, HIREDATE
FROM EMP
WHERE TO_CHAR(HIREDATE,'RR/MM/DD') = '80/12/17'
;
SELECT EMPNO, ENAME, HIREDATE
FROM EMP
WHERE HIREDATE BETWEEN '80/12/17' AND '80/12/18' -1/24/60/60 ;
-- WHERE TO_CHAR(HIREDATE,'RR/MM/DD') = '80/12/17'
;
;
DESC EMP;
DESC DEPT;
SELECT * FROM EMP;
SELECT * FROM DEPT;
;
SELECT EMPNO, ENAME, E.DEPTNO, D.DEPTNO, DNAME
FROM EMP E, DEPT D
;
--ANSI SYNTAX
--
SELECT EMPNO, ENAME, E.DEPTNO, D.DEPTNO, DNAME
FROM EMP E JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)
--WHERE E.DEPTNO = D.DEPTNO
;
--NATURAL JOIN :이름이 같은 모든 컬럼을 조인조건으로 생성
SELECT EMPNO, ENAME, E.DEPTNO, D.DEPTNO, DNAME
FROM EMP E NATURAL JOIN DEPT D
;
--JOIN USING : 이름이 같은 컬럼 중 USING에 사용된 컬럼만 조인조건으로 사용
SELECT EMPNO, ENAME, DEPTNO, DEPTNO
FROM EMP E JOIN DEPT D
USING (DEPTNO);
;
--NON EQUI JOIN
SELECT EMPNO, ENAME, SAL
FROM EMP;
SELECT EMPNO, ENAME, SAL, GRADE
FROM EMP, SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL;
SELECT * FROM SALGRADE;
;
SELECT employee_id, last_name,
d.department_id, department_name
from employees e join departments d
on e.department_id = d.department_id
;
--using
SELECT employee_id, last_name,
department_id, department_name
from employees e join departments d
using (department_id)
;
--
SELECT employee_id, last_name,
department_id, department_name
from employees e natural join departments d
--using (department_id)
;
SELECT employee_id, last_name,
d.department_id, department_name
FROM employees e, departments d, locations L
where e.department_id = d.department_id
and d.LOCATION_ID= L.LOCATION_ID
;
SELECT employee_id, last_name,
d.department_id, department_name
FROM employees e, departments d,
on e.department_id = d.department_id
join LOCATIONS L
on d.LOCATION_ID= L.LOCATION_ID
;
select *
FROM EMP CROSS JOIN DEPT
;
CREATE TABLE EMP2 AS SELECT * FROM EMP;
INSERT INTO EMP2 ( EMPNO, ENAME) VALUES ( 9000,'TEST');
COMMIT;
SELECT * FROM EMP2;
SELECT EMPNO, ENAME, D.DEPTNO ,DNAME
FROM EMP2 E, DEPT D
WHERE E.DEPTNO= D.DEPTNO;
;
CREATE TABLE EMP2 AS SELECT * FROM EMP;
INSERT INTO EMP2(EMPNO, ENAME) VALUES (9000,'TEST');
COMMIT;
SELECT * FROM EMP2;
SELECT employee_id, last_name,
department_id, department_name
from employees e join departments d
using (department_id);
;
--OUTER JOIN
SELECT EMPNO, ENAME, D.DEPTNO, DNAME
FROM EMP2 E, DEPT D
WHERE E.DEPTNO = D.DEPTNO(+)
;
--OUTER JOIN
SELECT EMPNO, ENAME, D.DEPTNO, DNAME
FROM EMP2 E LEFT OUTER JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)
;
SELECT EMPNO, ENAME, D.DEPTNO, DNAME
FROM EMP2 E RIGHT OUTER JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)
;
SELECT EMPNO, ENAME, D.DEPTNO, DNAME
FROM EMP2 E RIGHT OUTER JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)
;
--옵티마이저가 조인을 만나면 DRIVING
--Nested Loop join
;
outer table leading, driving , driven
;
dept -> EMP
4 4
풀스캔 4
;
--Nested Loop join
--기본적인 조인 방법
driving 조건으로 몇 개의 행을 실행
;
SELECT /*+ USE_NL ( E, D) */
e.ename, e.deptno, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno;
--@XPLAN
;
SELECT /*+ USE_MERGE ( E, D) */
e.ename, e.deptno, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno;
--@XPLAN
;
PGA EMP
PGA
SORT MARGE
;
--
SORT EMP
SORT MERGE JOIN
--정렬작업이 완료되기전까지 조인 결과 도출 불가능
NESTED LOOP
--- 처음 몇개행이 빨리 튀어나오는 방식
hash join
SP
PGA
olap
--조인 관련 힌트
USE_NL (t1 t2)
;
SELECT /*+ USE_HASH (D E) LEADING(E) */
EMPNO, ENAME, D.DEPTNO, DNAME
FROM DEPT D, EMP E
WHERE E.DEPTNO = D.DEPTNO;
;
SELECT /*+ USE_HASH (D E) LEADING(E) */
EMPNO, ENAME, D.DEPTNO, DNAME
FROM DEPT D, EMP E
WHERE E.DEPTNO = D.DEPTNO;
;
-- 조인 처리 방식의 이해
CREATE TABLE CP_EMP AS SELECT * FROM EMP;
CREATE TABLE CP_DEPT AS SELECT * FROM DEPT;
;
SELECT EMPNO, ENAME, D.DEPTNO, DNAME
FROM CP_EMP E, CP_DEPT D
WHERE E.DEPTNO = D.DEPTNO
;
-- HASH 조인
;
;
SELECT /*+ USE_NL(E D) LEADING(E) */
EMPNO, ENAME, D.DEPTNO, DNAME
FROM CP_EMP E, CP_DEPT D
WHERE E.DEPTNO = D.DEPTNO
;
SELECT /*+ USE_NL(E D) LEADING(E) */
EMPNO, ENAME, D.DEPTNO, DNAME
FROM CP_EMP E, CP_DEPT D
WHERE E.DEPTNO = D.DEPTNO
;
--@XPLAN
--조인 키 컬럼의 인덱스는 필수
;
CREATE INDEX cp_emp_deptno on cp_emp(deptno);
CREATE INDEX cp_dept_deptno on cp_dept(deptno);
;
-- Shared Pool
-- 논리적, 물리적
--
--인덱스 쿼리 튜닝기법
--퍼포먼스
--인덱스 스캔 방법
--index full, fase full scan,
--유일하게 멀티블럭 하는 인덱스 index fast full scan multiblock io
--조인 유형
--nested loop 조인
--hash join
--해쉬 조인의 경우 리딩 테이블의 중요하다
;
;
SELECT /*+ leading(c) use_hash(s) */ count(*)
from customers c, sales s
where c.cust_id= s.cust_id
;
;
--join 튜닝 사례
---
select /*+ swap_join_inputs(d) leading(d) use_hash(e) */ d.department_id
,department_name
,e.department_id
,last_name
,salary
from departments d, employees e
where D.department_id(+) = e.department_id
and e.salary(+) > 6000
;
--NL JOIN TUNING
--Nested Loops join 의 최적화
--Looping을 최소화시킬 수 있도록 Driving table을 선택한다
select c.cust_id. c.cust_las_name, c.cust_city
from custs c, sales s
create index sales_custid_timeid_ix on sales(cust_id, time_id);
SELECT /*+ use_nl(c s) index_rs_asc (s sales_custid_timeid_ix) */
c.cust_id, c.cust_last_name, c.cust_ciry, c.cust_credit
from custs c, sales s
where c.cust_id = s.cust_id
and c.cust_city = 'Los Angeles'
;
Nested SubQuert : where 절에 사용되는 subquery
--
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME='SCOTT') ;
--메인 쿼리와 서브쿼리
;
--단일행 서브쿼리 다중행 서브쿼리 실행계획 차이점
--쿼리 트랜스포메이션
select EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME ='SCOTT');
;
--OPTIMIZER 가실행계획을 작성할때
QUERY TRANSFORMER : SUBQUERY -> JOIN 서브쿼리 UNNEST
COST ESTIMATOR
;
Nested Subquety : where 절에 사용되는 Subquery
;
상관 서브 쿼리
-- 회사 평균보다 급여를 많이 받는 사원 정보를 조회하세요.
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP T1
WHERE 1 = 1
AND SAL >= (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = T1.DEPTNO)
;
SELECT AVG(SAL) FROM EMP
;
--Subquery Null
--In과 Not in 비교
create table emp2 as select * from emp;
insert into emp2(empno, ename) values (9000,'SCOTT');
select *
from dept
where deptno in (select deptno from emp2);
;
select *
from dept
where deptno not in ( select deptno from emp2);
--not in의 경우 서브쿼리에 null값이 포함되면 결과가 나오지 않는다
select *
from dept
where deptno not in (select deptno from emp2 where deptno is not null)
;
-- exists
select *
from dept
where exists (select deptno from emp2 where deptno = dept.deptno)
;
-- not exists
select *
from dept
where not exists (select 'X' from emp2 where deptno = dept.deptno)
;
select *
from emp
where deptno in( select deptno from dept)
;
select e.*
from emp e, dept d
where e.deptno = d.deptno
;
--alter table emp disable constraint emp_deptno_fk; --제약조건 제외
;
select *
from dept
where deptno in (select deptno from emp);
--실행 결과 다름
select d.*
from emp e, dept d
where e.deptno = d.deptno;
;
select *
from dept
where deptno in (select deptno from emp);
--INLINE View의 특징
--Simple View
--조건절과 조인문만을 포함
--언제든 Merging이 일어난다
--힌트 : MERGE, No_MERGE
--Complex view
--Group by, distinct 를 포함하는 view
--10g 이후 Merging이
Simple view
SELECT *
FROM (SELECT * FROM emp WHERE job ='SALESMAN') e
,(SELECT * FROM dept WHERE loc ='CHICAGO') d
where e.deptno = d.deptno;
select *
from emp e, dept d
where e.deptno = d.deptno
where e.job = 'SALESMAN'
;
--join 후에 distinct 가 수행되었다.
--먼저 distinct 를 서브쿼리에서 수행후에 join 을 할경우 cost 가 줄어든다
-- hash unique 인라인 뷰에서 distinct 이후에 처리 하는방법이 효율적
--스칼라 서브쿼리
--
select empno, ename, sal, (SELECT DNAME FROM DEPT WHERE DEPTNO = EMP.DEPTNO) DNAME
from EMP
;
--NVL이 적용이안됨
select empno, ename, sal, (SELECT NVL(DNAME, '부서없음') FROM DEPT WHERE DEPTNO = E.DEPTNO) DNAME
from EMP2 E
;
--NVL이 적용됨
select empno, ename, sal, NVL((SELECT DNAME FROM DEPT WHERE DEPTNO = E.DEPTNO), '부서없음') DNAME
from EMP2 E
;
SELECT CUST_ID
,NVL((SELECT /*+ NO_INDEX(S (CUST_ID)) */ SUM(AMOUNT_SOLD)
FROM SALES S
WHERE CUST_ID = C.CUST_ID),0) AMT_SOLD
FROM CUSTOMERS C
;
--COMPLEX view
SUBQUERRY FACTORING (WITH)
--WITH 쓰는 목적 가독성, 성능향상
;
--힌트사용 nf : materialize
-- inline
with sum_sal as ( select deptno, sum(sal) as sum
from emp
group by deptno)
select *
from sum_sal
where sum > (SELECT AVG(sum) from sum_sal);
with sum_sal as ( select /*+ INLINE */ deptno, sum(sal) as sum
from emp
group by deptno)
select *
from sum_sal
where sum > (SELECT AVG(sum) from sum_sal);
;
--MATERIALIZE
;
--그룹 함수 활용
--단원 그룹함수
--행집합
--AVG
--COUNT
--MAX
--MIN
--SUM
--
;
SELECT SUM(SAL), AVG(SAL), COUNT(*)
FROM EMP;
SELECT DEPTNO, SUM(SAL), AVG(SAL), COUNT(*)
FROM EMP2
GROUP BY DEPTNO;
--SELECT SAL
SELECT EMPNO, ENAME, SAL , (SELECT DNAME FROM DEPT WHERE DEPTNO = E.DEPTNO) DNAME
FROM EMP2 E;
;
--데이터 그룹
SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO;
SELECT DEPTNO, SUM(SAL), AVG(SAL), COUNT(*)
FROM EMP2
GROUP BY DEPTNO;
;
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY DEPTNO,JOB
ORDER BY 1,2
;
SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
ORDER BY 1,2
;
SELECT SUM(SAL)
FROM EMP
--GROUP BY DEPTNO
--ORDER BY 1,2
;
--ROLLUP, CUBE
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY DEPTNO,JOB
ORDER BY 1,2;
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP (DEPTNO,JOB) --N +1
ORDER BY 1,2;
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY CUBE (DEPTNO,JOB) --2의 N승
ORDER BY 1,2;
--GROUPING 함수
--SELECT
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
ORDER BY 1,2;
SELECT DEPTNO,GROUPING(DEPTNO), JOB, GROUPING(JOB), SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
ORDER BY 1,2;
SELECT DECODE (GROUPING(DEPTNO),1, '모든부서', DEPTNO) G_DEPT,
DECODE(GROUPING(JOB),1,'모든직무',JOB) G_JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
ORDER BY 1,2;
;
GROUPING_ID
;
--HAVING 절
;
SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE SUM(SAL) > 9000
GROUP BY DEPTNO;
ORDER BY DEPTNO;
;
--1번이 더 속도가 빠르다
--1
SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE DEPTNO > 20
GROUP BY DEPTNO
HAVING SUM(SAL) > 9000
AND DEPTNO > 20
ORDER BY DEPTNO
;
--2
SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING SUM(SAL) > 9000
AND DEPTNO > 20
ORDER BY DEPTNO
;
@XPLAN
;
SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO)
;
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY DEPTNO, ROLLUP(JOB);
SELECT DEPTNO, EMPNO,ENAME, SUM(SAL) AS SAL
FROM EMP
GROUP BY DEPTNO, ROLLUP((EMPNO, ENAME));
SELECT DEPTNO, EMPNO, ENAME, SUM(SAL) AS SAL
FROM EMP
GROUP BY ROLLUP(DEPTNO,(EMPNO,ENAME));
--Chapter 5 분석함수
;
--분석함수
select Analytic_function (arguments)
-- Over(
;
--select empno, ename, sal, avg(sal) over() avg_sal
--from emp
--group by ROLLUP(DEPTNO, JOB)
;
--select d.deptno, d.dname, e.ename, e.sal
;
SELECT EMPNO, ENAME, DEPTN, SAL, SUM(SAL) OVER ()
FROM EMP;
;
SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(PARTITION BY DEPTNO)
FROM EMP;
SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(ORDER BY SAL) SUM_SAL
FROM EMP;
;
SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(ORDER BY SAL) SUM_SAL
FROM EMP;
SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(ORDER BY SAL
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) SUM_SAL
FROM EMP
;
--WINDOW SORT
SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(ORDER BY SAL
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) SUM_SAL
FROM EMP;
SELECT EMPNO, ENAME, DEPTNO, SAL, ROUND( AVG(SAL) OVER (),2) AS AVG_SAL
FROM EMP
;
--WINDOW SORT
SELECT EMPNO, ENAME, DEPTNO, SAL, AVG(SAL) OVER(ORDER BY SAL
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) SUM_SAL
FROM EMP;
SELECT EMPNO, ENAME, DEPTNO, SAL, ROUND( AVG(SAL) OVER (PARTITION BY DEPTNO),2) AS AVG_SAL
FROM EMP
;
--ROWS(물리적 윈도우 )
SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(ORDER BY SAL
RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING ) SUM_SAL
FROM EMP;
SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(ORDER BY SAL
RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING ) SUM_SAL
FROM EMP;
-- 7788-100 = 7688
-- 7788 + 100 =7888
;
SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER() SUM_SAL,
MAX(SAL) OVER() MAX_SAL,
MIN(SAL) OVER() MIN_SAL,
AVG(SAL) OVER() AVG_SAL,
COUNT(SAL) OVER() CNT_SAL
FROM EMP;
SELECT EMPNO, ENAME, DEPTNO,SAL,
SUM(SAL) OVER(PARTITION BY DEPTNO) DEPT_SUM,
TRUNC(AVG(SAL) OVER(PARTITION BY DEPTNO)) DEPT_AVG,
MIN(SAL) OVER(PARTITION BY DEPTNO) DEPT_MIN,
MAX(SAL) OVER(PARTITION BY DEPTNO) DEPT_MAX
FROM EMP;
--SELECT ENAME, TO_CHAR(HIREDATE,'YYYY/MM/DD') HIREDATE, SAL,
-- SUM(SAL) OVER(ORDER BY HIREDATE)
-- RANGE BET
;
SELECT EMPNO,ENAME, SAL, DEPTNO,
ROUND(AVG(SAL) OVER(PARTITION BY DEPTNO),2) AS AVG_SAL
FROM EMP
ORDER BY DEPTNO;
;
--Co
--WINDOW 함수
--분석함수에서 조건절 사용시 주의 사항
SELECT EMPNO, ENAME, SAL, DEPTNO, JOB,
ROUND(AVG(SAL) OVER(PARTITION BY DEPTNO),2) AS AVG_SAL
FROM EMP
WHERE JOB = 'CLERK'
ORDER BY DEPTNO
;
SELECT EMPNO, ENAME, SAL, DEPTNO, JOB,
ROUND(AVG(SAL) OVER(PARTITION BY DEPTNO),2) AS AVG_SAL
FROM EMP
WHERE JOB = 'CLERK'
ORDER BY DEPTNO
;
SELECT EMPNO, ENAME, SAL, DEPTNO, JOB,
ROUND(AVG(SAL) OVER(PARTITION BY DEPTNO),2) AS AVG_SAL
FROM (SELECT EMPNO, ENAME, JOB, SAL, HIREDATE, DEPTNO,
ROUND(AVG(SAL) OVER(PARTITION BY DEPTNO),2) AS AVG_SAL
FROM EMP)
WHERE JOB = 'CLERK'
ORDER BY DEPTNO
;
--분석함수 순위관련
--SELECT empno, ename, sal, deptno,
-- RANK() over(ORDER
;
-- TOP-N 쿼리
;
SELECT *
FROM (SELECT * FROM EMP ORDER BY SAL DESC)
WHERE ROWNUM <= 3
;
--RANK
SELECT EMPNO, ENAME, DEPTNO, SAL
FROM EMP;
SELECT EMPNO, ENAME, DEPTNO, SAL, RANK() OVER(ORDER BY SAL DESC) RANK
,DENSE_RANK() OVER(ORDER BY SAL DESC) DRANK
,ROW_NUMBER() OVER(ORDER BY SAL DESC) RNUM
-- ,
FROM EMP;
SELECT EMPNO, ENAME, DEPTNO, SAL, RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RANK
FROM EMP;
--12 부터 가능함수
SELECT EMPNO, ENAME, SAL
FROM EMP
ORDER BY SAL DESC
FETCH FIRST 5 ROW ONLY;
SELECT * FROM V$VERSION;
;
--12 부터 가능함수
SELECT EMPNO, ENAME, SAL
FROM EMP
ORDER BY SAL DESC
FETCH FIRST 2 rows with ties;
;
show user
---순서 관련 함수
--LAG, LEAD
SELECT EMPNO, ENAME, DEPTNO, SAL, LAG(SAL) OVER(ORDER BY SAL) LAG1
-- LAG(SAL,1,0) OVER()
FROM EMP;
SELECT EMPNO, ENAME, DEPTNO, SAL, LAG(SAL,2,0) OVER(ORDER BY SAL) LAG1
-- LAG(SAL,1,0) OVER()
FROM EMP;
SELECT EMPNO, ENAME, DEPTNO, SAL, LEAD(SAL,2,0) OVER(ORDER BY SAL) LAG1
,LAG(SAL,1,0) OVER(ORDER BY SAL ) LAG2
FROM EMP;
;
--비율관련 함수
--CUME_DIST
--PERCENT_RANK
--NTILE
--RATIO_TO_REPORT
--비율 관련 분석함수
--NTILE : WINDOW 그룹의 행을 정렬 후 지정한 개수의 범위(등급)으로 나눈 후 각 값이 가지고 있는 등급 값을 보여준다.
;
SELECT EMPNO, ENAME, SAL, NTILE(5) OVER(ORDER BY SAL DESC) GRADE
FROM EMP;
--RATIO_TO_REPORT : WINDOW 영역의 합계 내에서 현재 값이 차지하는 백분율, 별도의 WINDOWING 절의 설정하는것은 불가능
;
--백분율 급여분석
SELECT DEPTNO,ENAME,SAL, ROUND(100 * RATIO_TO_REPORT(SAL) OVER(),2) AS PCT_ALL
FROM EMP;
;
--RATIO_TO_REPORT
SELECT DEPTNO,ENAME,SAL, ROUND(100 * RATIO_TO_REPORT(SAL) OVER(),2) AS PCT_ALL
, ROUND(100 * RATIO_TO_REPORT(SAL) OVER(PARTITION BY DEPTNO),2) AS PCT_DEPT --부서별 급여액
FROM EMP;
--
@IDX
;
SELECT DEPTNO, ENAME
FROM EMP
ORDER BY DEPTNO;
SELECT DEPTNO, LISTAGG(ENAME) WITHIN GROUP (ORDER BY ENAME)
FROM EMP
GROUP BY DEPTNO;
SELECT DEPTNO, LISTAGG(ENAME||'(' || SAL || ')' ,', ') WITHIN GROUP (ORDER BY ENAME)
FROM EMP
GROUP BY DEPTNO;
SELECT DEPTNO, LISTAGG(ENAME||'(' || SAL || ')' ,', ') WITHIN GROUP (ORDER BY SAL DESC0)
FROM EMP
GROUP BY DEPTNO
;
SELECT DEPTNO, LISTAGG(ENAME ||'(' || SAL || ')', ', ') WITHIN GROUP(ORDER BY SAL DESC)
FROM EMP
GROUP BY DEPTNO;
;
--@XPLAN
--6 계층형 쿼리
--셀프 조인
--부하직원(W) 상사(M)
--EMP EMP
SELECT W.EMPNO, W.ENAME, M.EMPNO, M.ENAME
FROM EMP W, EMP M
WHERE w.mgr = m.empno
;
--계층쿼리
--START WITH
--CONNECT BY
;
SELECT LPAD(' ',2*LEVEL) || ENAME, LEVEL
FROM EMP
CONNECT BY PRIOR EMPNO = MGR
START WITH ENAME ='KING'
;
SELECT LPAD('#',2*(LEVEL -1), '＊') || ENAME, LEVEL
FROM EMP
CONNECT BY PRIOR EMPNO = MGR
START WITH ENAME ='KING'
;
SELECT LPAD(' ',2*(LEVEL -1)) || ENAME, LEVEL
FROM EMP
CONNECT BY PRIOR EMPNO = MGR
START WITH ENAME ='KING'
;
SELECT LPAD(' ',2*(LEVEL-1)) || ENAME AS NAME, LEVEL, EMPNO, MGR
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
;
--WHERE 절줄때
SELECT LPAD(' ',2*(LEVEL-1)) || ENAME AS NAME, LEVEL, EMPNO, MGR
FROM EMP
WHERE ENAME <> 'SCOTT'
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
;
--
SELECT LPAD(' ',2*(LEVEL*1)) || ENAME AS NAME, LEVEL, EMPNO, MGR
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
;
SELECT LPAD(' ',2*(LEVEL*1)) || ENAME AS NAME, LEVEL, EMPNO WORKER, SAL W_SAL, PRIOR ENAME MANAGER, PRIOR SAL MANAGER_SAL
FROM EMP
CONNECT BY PRIOR EMPNO = MGR
AND ENAME <> 'SCOTT'
START WITH ENAME ='KING'
;
--최상위 급여와 아래 급여의 금액차이
SELECT LPAD(' ', LEVEL*2-2)|| ENAME AS NAME,
LEVEL,EMPNO, MGR, SAL,
CONNECT_BY_ROOT SAL - SAL AS DIFF
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
;
--ORDER 기준 X
SELECT LPAD( ' ',2*(LEVEL-1)) || ENAME, LEVEL
FROM EMP
CONNECT BY PRIOR EMPNO = MGR
START WITH ENAME = 'KING'
ORDER BY ENAME;
--ORDER 기준 O
SELECT LPAD( ' ',2*(LEVEL-1)) || ENAME, LEVEL
FROM EMP
CONNECT BY PRIOR EMPNO = MGR
START WITH ENAME = 'KING'
ORDER SIBLINGS BY ENAME;
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 10;
;
SELECT SYSDATE + LEVEL-1, LEVEL
FROM DUAL
CONNECT BY LEVEL <= 1000;
-- Chapter 7 정렬 작업 속도 개선
--SORT UNIQUE
--SORT AGGREGATE
--SORT GROUP BY
--PGA UNIQ
SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES --HASH UNIQUE 91
;
SELECT DISTINCT DEPTNO
FROM EMP --HASH UNIQUE 91
ORDER BY 1
;
;
SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
;
SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
ORDER BY 2
;
@XPLAN
ALTER SESSION SET "_GBY_HASH_AGGREGATION_ENABLED" = FALSE;
;
SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES
;
ALTER SESSION SET "_GBY_HASH_AGGREGATION_ENABLED" = TRUE;
SELECT /*+ NU_USE_HASH_AGGREGATION */ DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO;
SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
ORDER BY 2
;
SELECT EMPNO
FROM EMP;
;
@XPLAN
;
@IDX
;
SELECT EMPNO
FROM EMP
ORDER BY EMPNO;
;
SELECT CUST_ID, CUST_LAST_NAME, CUST_CREDIT_LIMIT
FROM CUSTS
ORDER BY CUST_CREDIT_LIMIT;
ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS
;
SHOW PARAMETER OPTIMIZER_MOD
;
--OLTP 옵티마이저 제어
ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS;
;
SELECT DEPARTMENT_ID
FROM EMPLOYEES;
;
SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
ORDER BY 2;
CREATE INDEX CUST_CLIMIT_IX ON CUSTS (CUST_CREDIT_LIMIT) NOLOGGING; --리커버리 불가능 하지만 속도 향상
--CTLR+ END 맨아래 데이터보기
--리두로그 발생 줄이기
SELECT CUST_ID, CUST_LAST_NAME, CUST_CREDIT_LIMIT
FROM CUSTS
ORDER BY CUST_ID;
DESC CUSTS
;
SELECT CUST_ID, CUST_LAST_NAME, CUST_CREDIT_LIMIT
FROM CUSTS
WHERE CUST_CREDIT_LIMIT IS NOT NULL
ORDER BY CUST_CREDIT_LIMIT;
@XPLAN
--READS 피지컬 니즈
;
SELECT cust_id, cust_last_name, cust_credit_limit
from custs
where cust_city ='Los Angeles'
order by cust_credit_limit;
create index cust_city_limit_idx on custs(cust_city, cust_credit_limit);
SELECT cust_id, cust_last_name, cust_credit_limit
from custs
where cust_city ='Los Angeles'
order by cust_credit_limit;
;
---------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 90 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTS | 90 | 90 (0)|
|* 2 | INDEX RANGE SCAN | CUST_CITY_LIMIT_IDX | 90 | 2 (0)|
---------------------------------------------------------------------------------
;
SELECT CUST_CITY, CUST_CREDIT_LIMIT, ROWID FROM CUSTS WHERE CUST_CITY ='Los Angeles' order by 1,2;
create index CUST_CITY_LIMIT_IX ON CUSTS(cust_city, cust_credit_limit) NOLOGGING;
;
SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID =50
ORDER BY SALARY;
;
CREATE INDEX EMP_NEW ON EMPLOYEES( DEPARTMENT_ID, SALARY);
;
;
SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID =50
ORDER BY SALARY;
;
@XPLAN
;
CREATE INDEX EMP_NEW2 ON EMPLOYEES( DEPARTMENT_ID, SALARY, EMPLOYEE_ID);
;
SELECT /*+ INDEX (E EMP_NEW2) */ EMPLOYEE_ID, DEPARTMENT_ID, SALARY
FROM EMPLOYEES E
WHERE DEPARTMENT_ID =50
ORDER BY SALARY;
;
@XPLAN
;
SELECT DEPARTMENT_ID, SALARY, EMPLOYEE_ID, ROWID FROM EMPLOYEES ORDER BY 1,2,3;
--성능을 고려한 튜닝
OPTIMIZER
QUERY TRANSFORMER : SUBQUERY -> JOIN UNNEST
COST ESTIMATOR
OLTP : INDEX 튜닝
JOIN - NL 메모리 사용 X
MERGE
HASH 큰 메모리 사용
SORT 튜닝
성능상 NL 사용하는것이 더 좋음
OLAP : FULL SCAN 을 하기때문에 병렬, 파티션, BITMAP 인덱스
조유미 ymchokr@naver.com