12. 기타 쿼리 변환
2011.06.06 08:47
(1) 조인 컬럼에 IS NOT NULL 조건 추가
select count(e.empno),count (d.dname)
from emp e, dept d
where d.deptno=e.deptno
and sal<=2900 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1036783580
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 7 (15)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 20 | | |
| 2 | MERGE JOIN | | 8 | 160 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 8 | 56 | 5 (20)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMP | 8 | 56 | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
6 - filter("SAL"<=2900)
- 조인컬럼 deptno 가 null 인 데이터는 조인 액세스가 불필요
- deptno is not null 조건을 추가 하면 불필요한 테이블 액세스및 조인 시도 감소 가능
select count(e.empno),count (d.dname)
from emp e, dept d
where d.deptno=e.deptno
and sal<=2900
and e.deptno is not null
and d.deptno is not null ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1036783580
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 7 (15)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 20 | | |
| 2 | MERGE JOIN | | 8 | 160 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 8 | 56 | 5 (20)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMP | 8 | 56 | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
6 - filter("SAL"<=2900 AND "E"."DEPTNO" IS NOT NULL)
SQL>
- 실험
SQL> create table hwi_temp
as
select *
from emp,(select rownum no from dual connect by level<=1000);
Table created.
SQL> update hwi_temp set deptno=null;
14000 rows updated.
SQL> commit;
Commit complete.
select /*+ ordered use_nl(d) index(e hwi_temp_idx) index(d dept_pk) */
count(e.empno),count (d.dname)
from hwi_temp e,scott.dept d
where d.deptno=e.deptno
and e.sal <= 2900
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.02 0 95 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.02 0 95 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=95 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=95 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=95 pr=0 pw=0 time=0 us cost=10617 size=52 card=1)
10000 TABLE ACCESS FULL HWI_TEMP (cr=95 pr=0 pw=0 time=15821 us cost=26 size=412932 card=10588)
0 INDEX UNIQUE SCAN PK_DEPT (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 73180)
0 TABLE ACCESS BY INDEX ROWID DEPT (cr=0 pr=0 pw=0 time=0 us cost=1 size=13 card=1)
※ hwi_temp table에서 10000번읽었지만 dept테이블과는 조인이 일어나지 않음
Predicate 정보 에도 별도로 추가된 필터 없음
* 실수로 hwi_temp_idx 생성 하지 않았음 ㅡㅡ;;
SQL> alter session set optimizer_features_enable='9.2.0.8';
Session altered.
SQL>
SQL> select /*+ ordered use_nl(d) index(e hwi_temp_idx) index(d dept_pk) full(d) */
count(e.empno),count (d.dname)
from hwi_temp e,scott.dept d
where d.deptno=e.deptno
and e.sal <= 2900;
COUNT(E.EMPNO) COUNT(D.DNAME)
-------------- --------------
0 0
SQL>
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.07 0.08 6 70095 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.07 0.09 6 70097 0 1
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=70095 pr=6 pw=0 time=0 us)
0 NESTED LOOPS (cr=70095 pr=6 pw=0 time=0 us cost=9444 size=52 card=1)
10000 TABLE ACCESS FULL HWI_TEMP (cr=95 pr=0 pw=0 time=7087 us cost=15 size=484497 card=12423)
0 TABLE ACCESS FULL DEPT (cr=70000 pr=6 pw=0 time=0 us cost=1 size=13 card=1)
※ inner table을 full table scan으로 액세스 할때는 조인 액세스가 발생
11.2.0.0
select /*+ ordered use_nl(d) index(e hwi_temp_idx) index(d dept_pk) full(d) */
count(e.empno),count (d.dname)
from hwi_temp e,scott.dept d
where d.deptno=e.deptno
and e.sal <= 2900;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 86 72 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.02 6 95 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.03 92 167 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=95 pr=6 pw=0 time=0 us)
0 NESTED LOOPS (cr=95 pr=6 pw=0 time=0 us)
0 NESTED LOOPS (cr=95 pr=6 pw=0 time=0 us cost=10617 size=52 card=1)
10000 TABLE ACCESS FULL HWI_TEMP (cr=95 pr=6 pw=0 time=8100 us cost=26 size=412932 card=10588)
0 INDEX UNIQUE SCAN PK_DEPT (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 73180)
0 TABLE ACCESS BY INDEX ROWID DEPT (cr=0 pr=0 pw=0 time=0 us cost=1 size=13 card=1)
- 통계정보 존재유무
없을때
SQL> select /*+ ordered use_nl(d) index(e hwi_temp_idx) index(d dept_pk) full(d) */
count(e.empno),count (d.dname)
from hwi_temp e,scott.dept d
where d.deptno=e.deptno
and e.sal <= 2900;
COUNT(E.EMPNO) COUNT(D.DNAME)
-------------- --------------
0 0
Execution Plan
----------------------------------------------------------
Plan hash value: 1599336290
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 10617 (1)| 00:02:08 |
| 1 | SORT AGGREGATE | | 1 | 52 | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 52 | 10617 (1)| 00:02:08 |
|* 4 | TABLE ACCESS FULL | HWI_TEMP | 10588 | 403K| 26 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("E"."SAL"<=2900)
5 - access("D"."DEPTNO"="E"."DEPTNO")
begin
dbms_stats.gather_table_stats(user,'hwi_temp',method_opt=>'for all columns', no_invalidate=>false);
end;
/
PL/SQL procedure successfully completed.
SQL> select /*+ ordered use_nl(d) index(e hwi_temp_idx) index(d dept_pk) full(d) */
count(e.empno),count (d.dname)
from hwi_temp e,scott.dept d
where d.deptno=e.deptno
and e.sal <= 2900;
COUNT(E.EMPNO) COUNT(D.DNAME)
-------------- --------------
0 0
Execution Plan
----------------------------------------------------------
Plan hash value: 1599336290
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 26 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 34 | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 34 | 26 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | HWI_TEMP | 1 | 21 | 26 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("E"."DEPTNO" IS NOT NULL AND "E"."SAL"<=2900)
5 - access("D"."DEPTNO"="E"."DEPTNO")
※ 통계정보 수집시 deptno is not null 조건이 추가됨
- 조건
- 조인 컬럼의 null 값 비중이 5% 이상일때
- is not null 조건 추가시 이점
- 해시조인
- Build Input을 읽어 해시 맵을 만들때 더 적은 메모리 사용
- Probe Input를 읽을때 null값인 레코드를 제외함으로 해시 맵 탐색 횟수를 줄임
- 양쪽 모두 null값 비중이 클수록 효과가 큼
- 소트머지 조인
- 양쪽 테이블에서 조인 컬럼이 null 인 레코드를 제외한다면 소트 및 비교 연산 횟수가 줄어듬
- 결론
- is not null조건은 조인시 이점이 많음.
- 옵티마이져는 null값 비중이 5%이상일때만 쿼리 변환을 수행하므로 사용자가 직접추가
- 조인 컬럼에 null이 많을경우 default값으로 (0,’X’)등을 사용시 조인 성능이 떨어짐
(2) 필터 조건 추가
select * from emp
where sal between :mn and :mx
- 사용자가 :mx보다 :mn변수에 더 큰 값을 입력한다면 쿼리 결과는 공집합
- 8i 까지는 공집합임을 일수 있는데도 불구하고 실제 수행이 이루어짐
- 9i 부터 수행 방지를 위해 임의의 필터 조건식을 추가
스크립트 ch4_28.txt
변수조건 테스트
SQL> create table emp as select * from scott.emp;
Table created.
SQL>variable mn number;
variable mx number;
SQL>
SQL> begin
:mn := 5000;
:mx := 100;
end; /
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly;
11g
SQL> select * from emp
where sal between :mn and :mx;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3896240783
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 14 | 1218 | 2 (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))
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> alter session set optimizer_features_enable = '9.0.1';
Session altered.
통계정보없음
SQL> select * from emp
where sal between :mn and :mx;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| EMP |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL"<=TO_NUMBER(:MX) AND "SAL">=TO_NUMBER(:MN))
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> analyze table emp compute statistics;
Table analyzed.
통계정보 생성후
SQL> select * from emp
where sal between :mn and :mx;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3896240783
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 1 |
|* 1 | FILTER | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 32 | 1 |
-----------------------------------------------------------
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))
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> alter session set optimizer_features_enable = '8.1.7';
Session altered.
SQL> select * from emp
where sal between :mn and :mx;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 1 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 32 | 1 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL">=TO_NUMBER(:MN) AND "SAL"<=TO_NUMBER(:MX))
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
상수값으로 조회시
SQL> alter session set optimizer_features_enable = '11.2.0.1';
Session altered.
SQL> select * from emp
where sal between &mn and &mx;
Enter value for mn: 5000
Enter value for mx: 100
old 2: where sal between &mn and &mx
new 2: where sal between 5000 and 100
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3896240783
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 0 (0)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 32 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - filter("SAL"<=100 AND "SAL">=5000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> alter session set optimizer_features_enable = '9.0.1';
Session altered.
SQL> select * from emp
where sal between &mn and &mx; 2
Enter value for mn: 5000
Enter value for mx: 100
old 2: where sal between &mn and &mx
new 2: where sal between 5000 and 100
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3896240783
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 1 |
|* 1 | FILTER | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 32 | 1 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(5000<=100)
2 - filter("SAL">=5000 AND "SAL"<=100)
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
(3) 조건절 비교 순서
A | ... | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ... |
B | ... | 990 | 991 | 992 | 993 | 994 | 995 | 996 | 997 | 998 | 999 | 1000 | 1001 | ... |
select * from t
where a=1
and b= 1000;
- 검색시 B 조건식을 먼저 평가하는것이 유리
- 대부분의 레코드가 B=1000조건을 만족하지 않아 A컬럼 비교 연산을 수행할 필요가 없음
- A=1 을 먼저 평가하면 B컬럼에 대한 비교연산까지 수행해야하므로 CPU사용량 증가
- 조건절 처리시 부등호(>) 조건을 먼저 평가하느냐 like조건을 먼저하느냐에 따라 일량 차이생김
- 옵티마이져는 테이블 전체를 스캔하거나
인덱스를 수평적으로 스캔할때의 filter조건식을 평가할때
선택도가 낮은 컬럼을 먼저 처리하도록 순서를 조정
- 옵티마이져 모드별 처리
옵티마이저 모드 | 조건절 비교 순서 |
RBO | where 절에 기술된 반대 순서로 |
CBO (I/O Costing 모드) | where 절에 기술된 순서대로 |
CBO (CPU Costing 모드) | 비교 연산해야 할 일량을 고려해 옵티마이저가 결정 선택도가 낮은 조건식부터 평가 |
create table t
nologging
as
select '1' a, level b from dual connect by level <=1000000;
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'t',no_invalidate=>false);
PL/SQL procedure successfully completed.
SQL> set autot 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 | 458 (2)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 458 (2)| 00:00:06 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=1000 AND TO_NUMBER("A")=1)
SQL> select * from t
2 where b=1000
3 and a=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 458 (2)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 458 (2)| 00:00:06 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=1000 AND TO_NUMBER("A")=1)
SQL>
- cpu costing 모드에서 조건절 비교 순서 제어
select /*+ ORDERED_PREDICATES */ * from t
where a=1
and b=1000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 463 (3)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 463 (3)| 00:00:06 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("A")=1 AND "B"=1000)
SQL>
- 9i
- 시스템 통계 삭제
- exec dbms_stats.delete_system_stats;
- 10g
- i/o 비용 모델로 전환
- alter session set “_optimizer_cost_model”=io;
- rbo 모드로 변환
- alter session set optimizer_mode=rule;
ordered_predicates 힌트의 또 다른 용도
- 10g
- or또는 in-list조건에 대한 or-expansion이 일어날대 실행 순서를 제어할 목적으로 사용
- cpu모델 하에서 계산된카디널리티가 낮은 쪽을 먼저 실행
- 9i
- i/o,cpu비용모델 불문하고 in-list,or-expansion방식 처리시에는 뒤쪽값을 우선실행
10g 에서 실험
SQL> select /*+ use_concat(@subq 1) qb_name(subq) index(e) */ *
from emp e
where deptno in (10,30);
Execution Plan
----------------------------------------------------------
Plan hash value: 2959818684
-----------------------------------------------------------------------------------------
| 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_IDX1 | 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_IDX1 | 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이 위쪽에 올라감
SQL> select /*+ use_concat(@subq 1) qb_name(subq) index(e) ordered_predicates */ *
from emp e
where deptno in (10,30);
Execution Plan
----------------------------------------------------------
Plan hash value: 2959818684
-----------------------------------------------------------------------------------------
| 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_IDX1 | 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_IDX1 | 3 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=30)
5 - access("DEPTNO"=10)∴ ordered_predicates 힌트를 사용하면 9i처럼 inlist 뒤쪽에 있는 값을 먼저 수행
SQL> select /*+ use_concat(@subq 1) qb_name(subq) index(e) no_cpu_costing */ *
from emp e
where deptno in (10,30);
Execution Plan
----------------------------------------------------------
Plan hash value: 2959818684
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 333 | 4 |
| 1 | CONCATENATION | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 222 | 2 |
|* 3 | INDEX RANGE SCAN | EMP_IDX1 | 6 | | 1 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 111 | 2 |
|* 5 | INDEX RANGE SCAN | EMP_IDX1 | 3 | | 1 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=30)
5 - access("DEPTNO"=10)
Note
-----
- cpu costing is off (consider enabling it)
SQL>
∴ no_cpu_costing 힌트를 사용해 IO비용모델로 변경해도 in-list 뒤쪽부터 실행※ _optimizer_cost_model 파라미터를 ‘IO’ 로 설정
- 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
- 작성자: 남송휘 (tofriend)
- 최초작성일: 2011년 6월 7일
- 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
- 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
55 | 4장._쿼리_변환 | 휘휘 | 2011.09.07 | 2394 |
54 | 진행기록 | 운영자 | 2011.08.23 | 2383 |
53 | 2. 병렬 Order By와 Group By | 휘휘 | 2011.06.30 | 10127 |
52 | 3. 병렬 조인 | 오예스 | 2011.06.29 | 4969 |
51 | 4. PQ_DISTRIBUTE 힌트 | 휘휘 | 2011.06.27 | 15620 |
50 | 5. 병렬 처리에 관한 기타 상식 [1] | balto | 2011.06.26 | 28162 |
49 | 1. 테이블 파티셔닝 | 오라클잭 | 2011.06.21 | 28274 |
48 | 2. 파티션 Pruning | 휘휘 | 2011.06.19 | 11868 |
47 | 7. Sort Area 크기 조정 | 오예스 | 2011.06.16 | 5987 |
46 | 5. 인덱스를 이용한 소트 연산 대체 | AskZZang | 2011.06.16 | 4955 |
45 | 4. 소트가 발생하지 않도록 SQL 작성 | AskZZang | 2011.06.16 | 3109 |
44 | 3. 데이터 모델 측면에서의 검토 | AskZZang | 2011.06.16 | 2724 |
43 | 5장. 소트 튜닝 | balto | 2011.06.16 | 4283 |
42 | 6. Sort Area를 적게 사용하도록 SQL 작성 | 오예스 | 2011.06.14 | 4209 |
41 | 1. 소트 수행 원리 | balto | 2011.06.12 | 8025 |
40 | 2. 소트를 발생시키는 오퍼레이션 | balto | 2011.06.12 | 4847 |
39 | 11. 집합 연산을 조인으로 변환 | 오라클잭 | 2011.06.08 | 4956 |
38 | 10. 실체화 뷰 쿼리로 재작성 | 오라클잭 | 2011.06.08 | 9953 |
» | 12. 기타 쿼리 변환 | 휘휘 | 2011.06.06 | 3122 |
36 | 7. OR-Expansion | AskZZang | 2011.06.01 | 8334 |