SQL> create index emp_idx01 on emp(empno) invisible;
Index created.
+ 일반 세션에서 SQL 수행
SQL> set autot on exp
SQL>
SQL> select empno from emp where empno = 7839;
EMPNO
----------
7839
Execution Plan
----------------------------------------------------------
Plan hash value: 2872589290
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 12 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 4 | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7839)
+ INVISIBLE INDEX 사용 정의
SQL> alter session set optimizer_use_invisible_indexes=true;
Session altered.
SQL> select empno from emp where empno = 7839;
EMPNO
----------
7839
Execution Plan
----------------------------------------------------------
Plan hash value: 3956893595
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| EMP_IDX01 | 1 | 4 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPNO"=7839)
+ INVISIBLE INDEX 사용 해제
SQL> alter session set optimizer_use_invisible_indexes=false;
Session altered.
SQL> select empno from emp where empno = 7839;
EMPNO
----------
7839
Execution Plan
----------------------------------------------------------
Plan hash value: 2872589290
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 12 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 4 | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7839)
+ OPT_PARAM hint를 통한 INVISIBLE index 사용 권한 부여 -> 실패 ?
SQL> select /*+ opt_param('optimizer_use_invisible_indexes','true') */ empno from emp where empno = 7839;
EMPNO
----------
7839
Execution Plan
----------------------------------------------------------
Plan hash value: 2872589290
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 12 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 4 | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7839)
'Oracle Database' 카테고리의 다른 글
| Oracle Table의 통계정보를 옮기는 방법. (1) | 2010.08.03 |
|---|---|
| Oracle datafile 생성 이후 모든 archive log는 있으나 datafile의 backup이 없는 경우 recovery (0) | 2010.07.30 |
| Database에 savepoint를 찍어보자 !! (0) | 2010.07.13 |
| Oracle Join 종류: CROSS, INNER, OUTER, NATURAL,EQUI, SELF join (0) | 2010.07.13 |
| 오라클 옵티마이져의 비용 산정 모듈의 세가지 예상치 (Selectivity, cardinality, cost) (0) | 2010.07.07 |