[SQL학원,오라클학원☞탑크리에듀](동영상강의)오라클힌트강좌#9,힌트를통한SQL튜닝사례,멀티테이블조인시조인방법선정을통한SQL튜닝사례(ORDERED, USE_HASH, USE_NL)

(동영상강의)오라클힌트강좌#9,힌트를통한SQL튜닝사례,멀티테이블조인시조인방법선정을통한SQL튜닝사례(ORDERED, USE_HASH, USE_NL)_오라클SQL튜닝교육

-- 멀티테이블 조인시 ORDERED, 조인방법을 선정하는 힌트 

-- USE_HASH, USE_NL을 이용한 튜닝

select * from myemp1;    

select * from mylecture1;

select * from mysugang1;

-- 통계정보 생성

execute dbms_stats.gather_table_stats(USER,'myemp1');

execute dbms_stats.gather_table_stats(USER,'mylecture1');

execute dbms_stats.gather_table_stats(USER,'mysugang1');

-- MYEMP1테이블, empno 칼럼이 PK, 1000만건

SELECT a.index_name, a.column_name, b.visibility 

  FROM  user_ind_columns a, user_indexes b 

 WHERE  a.table_name = 'MYEMP1' 

 AND a.index_name = b.index_name ;

 -- MYLECTURE1 테이블, lecture_id 칼럼이 PK, 7건

 SELECT a.index_name, a.column_name, b.visibility 

  FROM  user_ind_columns a, user_indexes b 

 WHERE  a.table_name = 'MYLECTURE1' 

 AND a.index_name = b.index_name ;

 -- MYSUGANG1 테이블, empno, lecture_id, seq 3개 칼럼 복합 인덱스, 550만건

  SELECT a.index_name, a.column_name, b.visibility 

  FROM  user_ind_columns a, user_indexes b 

 WHERE  a.table_name = 'MYSUGANG1' 

 AND a.index_name = b.index_name ;

 show parameter optimizer_mode;  -- CBO, ALL_ROWS

-- "SQL기초실무과정" 과정을 수강한 수강생들의 급여의 평균은?

-- 힌트를 사용하지 않았을 때 CBO에서 l, s를 해시조인

-- 그 결과와 e를 해시조인하며 myemp1을 스캐닝시 empno  인덱스를 

-- 경우하지않고 FULLSCAN 했다. select되는 칼럼이 sal 이므로 인덱스를 

-- 경유하더라도 rowid로 원본을 접근해야 하므로... 

--. 1.9초

SELECT AVG(sal)

FROM mylecture1 l, mysugang1 s, myemp1 e

WHERE l.lecture_nm = 'SQL기초실무과정'

AND    l.lecture_id = s.lecture_id

AND    s.empno = e.empno;

-- 이번에는 힌트를 사용하여 myemp1을 Access할때 PK인덱스를 이용하라고..

-- 이 경우엔 인덱스를 경유하더라도 rowid로 원본을 접근해야 하므로... 더느림

-- 4.3초,  FULLSCAN이 더 효율적

SELECT /*+ ordered use_hash(s e) index(e pk_myemp1)  */ AVG(sal)d

FROM mylecture1 l, mysugang1 s, myemp1 e

WHERE l.lecture_nm = 'SQL기초실무과정'

AND    l.lecture_id = s.lecture_id

AND    s.empno = e.empno;

-- mylecture1, mysugang1을 중첩루프조인이 걸리도록

--  use_nl(l s) : 건수 적은 mylecture1이 드라이빙

--. 1.6초

SELECT /*+ ordered use_nl(l s) */ AVG(sal)

FROM mylecture1 l, mysugang1 s, myemp1 e

WHERE l.lecture_nm = 'SQL기초실무과정'

AND    l.lecture_id = s.lecture_id

AND    s.empno = e.empno;

-- 이번에는 mylecture1, mysugang1의 조인결과와 myemp1도 

-- 중첩루프가 걸리도록, 마지막에 중첩루프한번 더 돈것은

-- 인덱스영역에는 sal 칼럼이 없으므로 원본 테이블 접근해서 sal 추출위헤

--. 0.5초

SELECT /*+ ordered use_nl(s) use_nl(e) */ AVG(sal)

FROM mylecture1 l, mysugang1 s, myemp1 e

WHERE l.lecture_nm = 'SQL기초실무과정'

AND    l.lecture_id = s.lecture_id

AND    s.empno = e.empno;