오라클이 힌트를 무시하는 것 처럼 보일 때

힌트를 사용할 수 없는 환경일 때

drop table t1 purge;

create table t1(c1 int, c2 varchar2(10), c3 varchar2(10) );

create index t1_n1 on t1(c1);


insert into t1
select level, 'A','a'
  from dual
connect by level <= 10000;


select /*+ index_ffs(t1 t1(c1) */ c1
  from t1;


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 |   126K|     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   | 10000 |   126K|     7   (0)| 00:00:01 |
--------------------------------------------------------------------------
=> 힌트를 타지 않았다.(null은 인덱스에 존재하지 않기 때문에)

select /*+ index_ffs(t1 t1(c1) */ c1
  from t1
 where c1 is not null  ;

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       | 10000 |   126K|     7   (0)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| T1_N1 | 10000 |   126K|     7   (0)| 00:00:01 |
------------------------------------------------------------------------------
=> not null조건 추가로 인해 null인 데이터를 볼 필요가 없으므로 힌트를 탔다



ALTER TABLE t1 MODIFY (c1 int NOT NULL); 

select /*+ index_ffs(t1 t1(c1) */ c1
  from t1
;

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       | 10000 |   126K|     7   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| T1_N1 | 10000 |   126K|     7   (0)| 00:00:01 |
------------------------------------------------------------------------------
=> c1컬럼에 not null 제약조건 추가로 인해 null인 데이터가 없는 것이 확실하므로 인덱스를 탔다.

sub query unnesting 이 발생할 때 ordered 힌트를 줄 때

drop table t2 purge;

create table t2(c1 int, c2 int );

create index t1_n2 on t1(c2);
create index t2_n1 on t2(c1);

insert into t2
select level, level
  from dual
connect by level <= 10000;

create table t3(c1 varchar2(10),c2 varchar2(10) );

insert into t3 values ('A','a');
insert into t3 values ('B','b');
commit;


=> ORDERED 힌트를 이용해서 t1->t2로 조인순서를 변경하고자 할 때
select /*+ ordered */ *
  from t1,t2
 where t1.c2 in (select c1 from t3 where c2 > 'A')
   and t1.c1 = t2.c1;

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 10000 |   654K|    20  (15)| 00:00:01 |
|*  1 |  HASH JOIN           |      | 10000 |   654K|    20  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | T2   | 10000 |   253K|     7   (0)| 00:00:01 |
|*  3 |   HASH JOIN          |      | 10000 |   400K|    12  (17)| 00:00:01 |
|   4 |    SORT UNIQUE       |      |     2 |    28 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| T3   |     2 |    28 |     3   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL | T1   | 10000 |   263K|     7   (0)| 00:00:01 |
----------------------------------------------------------------------------- 
=> t3->t1->t2 순으로 실행되었다.(Subquery Unnesting 발생 함)

select /*+ ordered */ *
  from t1,t2
 where t1.c2 in (select /*+ no_unnest */ c1 from t3 where c2 > 'A')
   and t1.c1 = t2.c1;

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    32 |  1696 |   954   (1)| 00:00:05 |
|*  1 |  FILTER             |      |       |       |            |          |
|*  2 |   HASH JOIN         |      | 10000 |   517K|    15   (7)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   | 10000 |   263K|     7   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   | 10000 |   253K|     7   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL | T3   |     1 |    14 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
=> 제대로 힌트가 적용됨

Non Unique Primary Key가 존재하는 경우의 Direct Path Insert

• 기존의 Non Unique Index에 PK constraint만 추가 할 경우
• 복잡한 Data처리를 위해 Deferrable Constraint를 사용하는 경우

 
alter table t1
add constraint pk_t1 primary key (c1)
using index
;

insert /*+ append */ into t1
select level+10000000, 'A','a'
  from dual
connect by level <= 10000;

-----------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | INSERT STATEMENT             |      |     1 |     2   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
=> 오라클은 Non Unique Primary Key가 존재하는 경우에는 Direct Path Insert를 사용하지 못한다.


alter table t1 drop constraint pk_t1;
drop index t1_n1;
alter table t1 add constraint pk_t1 primary key(c1);

insert /*+ append */ into t1
select level, 'A','a'
  from dual
connect by level <= 10000;