create table t1(c1 int, c2 int);
create index t1_n1 on t1(c1);
create index t1_n2 on t1(c2);
insert into t1
select level, level
from dual
connect by level <= 10000;
exec dbms_stats.gather_table_stats ('HYUN','t1',CASCADE=>TRUE,method_opt =>'FOR ALL COLUMNS SIZE REPEAT');
select *
from t1
where c1 = 1 or c2 = 5;
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 2 (0)|00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 2 | 14 | 2 (0)|00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | T1_N1 | | | 1 (0)|00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | T1_N2 | | | 1 (0)|00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("C1"=1)
7 - access("C2"=5)
=> or Expansion(혹은 Concatenation으로 변경하려면 use_concat 힌트 사용)
select /*+ use_concat */ *
from t1
where c1 = 1 or c2 = 5;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 4 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_N2 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C2"=5)
4 - filter(LNNVL("C2"=5))
5 - access("C1"=1)
=> Column C1에 대한 predicate는 Table Full Scan으로,
Column C2에 대한 predicate는 Index Range Scan으로 처리하고 싶다면?
* UNION ALL로 변경
select /*+ full(t1) */ *
from t1
where c1 = 1
union all
select /*+ index(t1) */ *
from t1
where c2 = 5
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 9 (23)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | TABLE ACCESS FULL | T1 | 1 | 7 | 7 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T1_N2 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C1"=1)
4 - access("C2"=5)
* Full Hint Naming 사용
explain plan for
select /*+ use_concat full(t1) */ *
from t1
where c1 = 1 or c2 = 5;
select * from table(dbms_xplan.display(null,null,'outline'));
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 15 (7)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 7 | 7 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T1 | 1 | 7 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1_2" "T1"@"SEL$1_2") -- Full 힌트를 사용한 부분
FULL(@"SEL$1_1" "T1"@"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE_LEAF(@"SEL$1_2")
USE_CONCAT(@"SEL$1" 8)
OUTLINE_LEAF(@"SEL$1_1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
3 - filter("C1"=1 AND LNNVL("C2"=5))
=> 특정 Query Block의 특정 Object에 대해서만 힌트 부여
explain plan for
select /*+ use_concat FULL(@"SEL$1_1" "T1"@"SEL$1") index(@"SEL$1_2" "T1"@"SEL$1_2") */ *
from t1
where c1 = 1 or c2 = 5;
select * from table(dbms_xplan.display(null,null,'outline'));
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 9 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
|* 2 | TABLE ACCESS FULL | T1 | 1 | 7 | 7 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
* union all을 사용하지 않고도 첫번째 Query Block에 대해서는 Table Full Scan을,
두번째 Query Block에 대해서는 Index Range Scan을 수행하도록 할 수 있다.