기타 쿼리 변환 (by niceboyous) [2013.01.29]
12. 기타 쿼리 변환
(1) 조인 컬럼에 IS NOT NULL 조건 추가
select count(e.empno), count(d.dname)
from emp e, dept d
where d.deptno = e.deptno
and sal <= 2900
- 조인 컬럼 deptno가 null인 데이터는 조인 액세스가 불필요하다.
- 불필요한 테이블 액세스 및 조인 시도를 줄일 수 있으면 쿼리 성능 향상에 도움이 된다.
select count(e.empno), count(d.dname)
from emp e, dept d
where d.deptno = e.deptno
and sal <= 2900
and e.edptno is not null
and d.deptno is not null
SQL> create table t_emp as select * from emp , (select rownum no from dual connect by level <=1000);
테이블이 생성되었습니다.
SQL> update t_emp set deptno = null;
14000 행이 갱신되었습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> create index t_emp_idx on t_emp(sal);
인덱스가 생성되었습니다.
통계생성전
4.조회
select /*+ ordered use_nl(d) index(e t_emp_idx) index(d dept_pk) */
count(e.empno), count(d.dname)
from t_emp e, dept d
where d.deptno = e.deptno
and e.sal <= 2900;
Execution Plan
----------------------------------------------------------
Plan hash value: 3232964574
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 10359 (1)| 00:02:05 |
| 1 | SORT AGGREGATE | | 1 | 52 | | |
| 2 | NESTED LOOPS | | 1 | 52 | 10359 (1)| 00:02:05 |
| 3 | TABLE ACCESS BY INDEX ROWID| T_EMP | 9535 | 363K| 810 (1)| 00:00:10 |
|* 4 | INDEX RANGE SCAN | T_EMP_IDX | 9535 | | 27 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."SAL"<=2900)
6 - access("D"."DEPTNO"="E"."DEPTNO")
Rows Row Source Operation
------- -----------------------------------------------------------------------
1 SORT AGGREGATE (cr=841 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=841 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=841 pr=0 pw=0 time=0 us cost=11050 size=52 card=1)
10000 TABLE ACCESS BY INDEX ROWID T_EMP (cr=841 pr=0 pw=0 time=12783 us cost=809 size=399243 card=10237)
10000 INDEX RANGE SCAN T_EMP_IDX (cr=22 pr=0 pw=0 time=3417 us cost=27 size=0 card=10237)
0 INDEX UNIQUE SCAN PK_DEPT (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)
0 TABLE ACCESS BY INDEX ROWID DEPT (cr=0 pr=0 pw=0 time=0 us cost=1 size=13 card=1)
- 위에 정보를 볼때 아직 옵티마이저에 의해 추가된 필터 조건은 없다.
- 원본 emp 테이블에서 sal<=2900 사원 레코드가 10개인데 이것을 1,000번 복사하여 10,000개 레코드가 존재
- 여기서 t_emp테이블에서 10,000 레코드를 읽었지만 dept 테이블과의 조인 액세스가 발생되지 않은것을 확인
- is null 조건을 따로 기술 하지 않더라도 읽은 값이 null일때는 조인 액세스를 하지 않는다
- Inner 테이블을 Full Table Scan으로 액세스할 때는 아래처럼 조인 액세스가 발생
Select /*+ ordered use_nl(d) index(e t_emp_idx) full(d) */
count(e.empno), count(d.dname)
from t_emp e, dept d
where d.deptno = e.deptno
and e.sal <= 2900
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.016 0.012 0 72 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.078 0.079 0 70841 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.094 0.092 0 70913 0 1
Rows Row Source Operation
------- -----------------------------------------------------------------------
1 SORT AGGREGATE (cr=70841 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=70841 pr=0 pw=0 time=0 us cost=14691 size=52 card=1)
10000 TABLE ACCESS BY INDEX ROWID T_EMP (cr=841 pr=0 pw=0 time=16200 us cost=809 size=399243 card=10237)
10000 INDEX RANGE SCAN T_EMP_IDX (cr=22 pr=0 pw=0 time=5442 us cost=27 size=0 card=10237)
0 TABLE ACCESS FULL DEPT (cr=70000 pr=0 pw=0 time=0 us cost=1 size=13 card=1)
- 드라이빙 테이블에서 읽은 값이 null일 때도 상황에 따라 조인 액세스가 일어날 수 있는데 e.deptno is not null 조건을 명시적으로 추가해 준다면 염려할 필요가 없다.
- 컬럼 통계를 수집하고 나면 옵티마이저가 자동으로 추가해 주지만 null 5% 이상일 때만 이 기능이 작동한다.
begin
dbms_stats.gather_table_stats(user, 't_emp'
, method_opt=>'for all columns', no_invalidate=>false);
end;
/
select /*+ ordered use_nl(d) index(e t_emp_idx) full(d) */
count(e.empno), count(d.dname)
from t_emp e, dept d
where d.deptno = e.deptno
and e.sal <= 2900
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.016 0.006 0 841 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.016 0.007 0 841 0 1
Misses in library cache during parse : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)
Rows Row Source Operation
------- -----------------------------------------------------------------------
1 SORT AGGREGATE (cr=841 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=841 pr=0 pw=0 time=0 us cost=807 size=34 card=1)
0 TABLE ACCESS BY INDEX ROWID T_EMP (cr=841 pr=0 pw=0 time=0 us cost=804 size=21 card=1)
10000 INDEX RANGE SCAN T_EMP_IDX (cr=22 pr=0 pw=0 time=3417 us cost=22 size=0 card=10001)
0 TABLE ACCESS FULL DEPT (cr=0 pr=0 pw=0 time=0 us cost=3 size=13 card=1)
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 807 (1)| 00:00:10 |
| 1 | SORT AGGREGATE | | 1 | 34 | | |
| 2 | NESTED LOOPS | | 1 | 34 | 807 (1)| 00:00:10 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T_EMP | 1 | 21 | 804 (1)| 00:00:10 |
|* 4 | INDEX RANGE SCAN | T_EMP_IDX | 10001 | | 22 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | DEPT | 1 | 13 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."DEPTNO" IS NOT NULL)
4 - access("E"."SAL"<=2900)
5 - filter("D"."DEPTNO"="E"."DEPTNO")
- dept 테이블을 10,000번 Full Scan하면서 발생하던 70,000개의 블록 I/O가 사라졌다.
- 옵티마이저에 의해 e.deptno is not null 조건이 추가되었음을 알 수 있다.
- t_emp테이블을 액세스하면서 발생한 블록 I/O 통계정보를 수집하기 전과 똑같이 841개이다.
- t_emp테이블을 엑세스 하면서 발생한 블록 i/o는 통계정보를 수집하기 전과 똑같은 841개이다.
- 추가된 is not null 조건을 필터링하면서 어차피 테이블을 방문하기 때문이다.
- t_emp_idx 인덱스에 deptno 컬럼을 추가하고 다시 수행하여 I/O가 841에서 23으로 준다.
select /*+ ordered use_nl(d) index(e t_emp_idx) */
count(e.empno), count(d.dname)
from t_emp e, dept d
where d.deptno = e.deptno
and e.sal <= 2900
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.002 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.069 27 23 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.071 27 23 0 1
Misses in library cache during parse : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)
Rows Row Source Operation
------- -----------------------------------------------------------------------
1 SORT AGGREGATE (cr=23 pr=27 pw=0 time=0 us)
0 NESTED LOOPS (cr=23 pr=27 pw=0 time=0 us)
0 NESTED LOOPS (cr=23 pr=27 pw=0 time=0 us cost=24 size=34 card=1)
0 TABLE ACCESS BY INDEX ROWID T_EMP (cr=23 pr=27 pw=0 time=0 us cost=24 size=21 card=1)
0 INDEX RANGE SCAN T_EMP_IDX (cr=23 pr=27 pw=0 time=0 us cost=24 size=0 card=1)
0 INDEX UNIQUE SCAN PK_DEPT (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)
0 TABLE ACCESS BY INDEX ROWID DEPT (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
- 조인 컬럼에 is not null 조건을 추가해 주면 NL 조인뿐만 아니라 해시 조인, 소트 머인조인 시에도 효과를 발휘한다.
- 해시 조인시 Build Input을 읽어 해시 맵을 만들 때 적은 메모리를 사용한다.
- Probe Input을 읽을 때도 null 값인 레코드를 제외함으로서 탐색 횟수를 줄일 수 있다.
- 소트 머지 조인시 양쪽 테이블에서 조인 컬럼 null인 레코드를 제외한다면 연산 횟수를 줄일 수 있다.
- 이러한 null을 체크하는 옵티마이저의 성능은 null 값이 5%을 넘을 때 변환을 시도하기 때문에 쿼리 작성시 직접 조건에 추가해 주는것이 불필요한 액세스를 줄일 수 있다.
(2) 필터 조건 추가
- 아래와 같이 바이드 변수로 between 검색하는 쿼리가 있다고 하자
- 쿼리를 수행할 때 사용자가 :mx보다 :mm변수에 더 큰 값을 입력한다면 결과는 공집합
select * from emp
where sal between :mn and :mx
- 사전에 두 값을 비교해알 수 있음에도 쿼리를 수행하고서야 공집합을 출력한다면 매우 비합리적이다.
- 잦은 일은 아니겠지만 최대용량 테이블을 조회하면서 사용자가 값을 거꾸로 입력하는 경우를 상상해 보라.
- 그럴 경우 8i까지는 사용자가 한참을 기다려야만 했다. 9i부터는 이를 방지하기 위해 옵티마이저가 임의로 필터 조건식을 추가한다.
- 아래 실행계획에서 1번 오퍼레이션 단계에 사용된 Filter Predicate 정보를 확인하기 바란다.
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:MN)<=TO_NUMBER(:MX))
2 - filter("SAL">=TO_NUMBER(:MN) AND "SAL"<=TO_NUMBER(:MX))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets --블록 I/O전혀없음
0 physical reads
0 redo size
669 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
- 위의 Filter Predicate 정보를 확인
- 실행계획 상으로는 Table Full Scan을 수행하고나서 필터 처리가 일어나는것 같지만, 실제는 Table Full Scan 자체를 생략한 것이다.
- 바인드변수대신 상수값으로 조회할 때도 filter 조건이 추가되는데 9i와 10g는 조금 다르게 처리된다.
- 9i : filter(5000 <=100)
- 10g이상 : filter(null is not null)
- 9i에서 통계쩡보가 없으면 RBO 모드로 작동해서 위와 같은 쿼리 변환이 일어나지 않는다.
- 10g는 통계정보가 없어도 항상 CBO 모드로 작동하므로 쿼리변환이 잘 일어나지만 optimizer_features_enable 파라미터를 8.1.7로 바꾸고 테스트 해보면 아래와 같이 불필요한 I/O를 수행한다.
SQL> alter session set optimizer_features_enable='8.1.7';
세션이 변경되었습니다.
SQL> select * from emp
2 where sal between :mn and :mx;
선택된 레코드가 없습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 37 | 1 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL">=TO_NUMBER(:MN) AND "SAL"<=TO_NUMBER(:MX))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets --불필요한 I/O 발생
0 physical reads
0 redo size
669 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
(3) 조건절 비교 순서
- 위 데이터를 아래의 SQL문으로 검색하면 B컬럼에 대한 조건식을 먼저 평가하는 것이 유리하다.
- 대부분의 레코드가 B=1000 조건을 만족하지 않아 A 컬럼에 대한 비교 연산을 수행하지 않아도 되기 때문이다.
SELECT * FROM T
WHERE A = 1
AND B = 1000
- 반대로 A = 1 조건식을 먼저 평가한다면, A컬럼에 1인 값이 많기 때문에 그만큼 수행해야 하므로 CPU 사용량이 늘어날 것이다.
- 조건절을 처리할 때도 부등호 조건을 먼저 평가하느냐 LIKE 조건을 먼저 평가하느냐에 따라 일량에 차이가 생긴다
SELECT /*+ FULL(도서) */ 도서번호, 도서명, 가격, 저자, 출판사, isbn
FROM 도서
WHERE 도서명 > :last_book_nm
ADN 도서명 LIKE :book_nm||'%'
- 옵티마이저는 테이블 전체를 스캔하거나 인덱스를 수평적으로 스캔할 때의 Filter 조건식을 평가할 때 선택도가 낮은 컬럼을 먼저 처리하도록 순서를 조정한다.
- 이런한 쿼리 변환이 작동하려면 9i, 10g 를 불문하고 옵티마이저에게 시스템 통계를 제공함으로써 CPU Costing 모델을 활성화 해야한다.
SQL> set autotrace traceonly exp;
SQL> select * from t
2 where a = 1
3 and b = 1000 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 445 (10)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 445 (10)| 00:00:06 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=1000 AND "A"=1)
- a와 b 컬럼에 대한 조건식을 서로 바꿔가며 테스트해도 선택도가 낮은 b 컬럼이 항상 먼저 처리되는것을 확인할 수 있다
- ordered_predicates 힌트를 사용하여 CPU Consting 모드에서의 조건절 비교순서 제어
- 옵티마이저의 판단을 무시하고 아래의 힌트를 썼더니 예상비용이 늘어난것을 확인할 수 있다.
- I/O 뿐만 아니라 CPU 연산 시간까지 비용 계산식에 포함하고 있음을 알수 있다
SQL> select /*+ ORDERED_PREDICATES */ * from t
2 where a = 1
3 and b = 1000 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 453 (12)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 453 (12)| 00:00:06 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=1 AND "B"=1000)
- 9i에서 시스템 통계를 지우거나 10g에서 I/O 비용 모델로 전환한 상태에서 수행하면 where 절에 기술된 순서대로 조건 비교가 일어난다.
exec dbms_stats .delete_system_stats; -- 9i 일 때
alter session set " optimizer_cost_model" = io; -- 10g일 때
- RBO 로 바꾼 상태에서 테스트하면 where 절에 기술된 반대 순서로 조건 비교가 일어난다.
alter session set optimizer_mode = rule;
- ordered_predicates 힌트의 또 다른 용도
- 10g에서 OR 또는 IN-List조건에 대한 OR-Expansion이 일어날 때 실행순서를 제어할 목적으로 ordered_predicates힌트를 사용할수 있다.
- 예를 들어 9i까지는 I/O비용모델, CPU 비용모델을 불문하고 IN-List를 OR-Expansion(=Concatenation) 방식으로 처리할 때 뒤쪽에 있는 값을 먼저 실행한다. 하지만 10g CPU비용 모델 하에서는 계산된 카디널리티가 낮은 쪽을 먼저 실행한다.
- 실제 그런지 10g에서 테스트해보자.
- 7절에서 설명한 것처럼 10g에서 같은 컬럼에 대한 OR 또는 IN-List 조건에 OR-Expansion이 작동하도록 하려면 use_concat 힌트에 아래와 같은 인자를 사용해야 한다.
select /*+ use_concat(@subq 1) qb_name(subq) index(e) */ *
from emp e
where deptno in (10, 30);
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 333 | 4 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 111 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 3 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 222 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 6 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=10)
5 - access("DEPTNO"=30)
- 30을 IN-List 뒤쪽에 기술했음에도, Predicate정보를 보면 통계정보 상 카디널리티가 낮은 10이 위쪽으로 올라가는 것을 볼수 있다.
- 실제 수행해 봐도 10이 먼저 출력된다.
- 아래와 같이 ordered_predicates 힌트를 사용하면 9i이전 버전처럼 IN-List 뒤쪽에 있는 값을 먼저 실행한다.
select /*+ use_concat(@subq 1) qb_name(subq) index(e) ordered_predicates */ *
from emp e
where deptno in (10, 30) ;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 333 | 4 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 222 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 6 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 111 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 3 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=30)
5 - access("DEPTNO"=10)
_optimizer_cost_model 파라미터를 'IO'로 설정하거나 아래와 같이 no_cpu_costing 힌트를 사용해 IO 비용 모델로 변경해도 IN-List 뒤쪽부터 실행한다.