Invisible Index기능은 Optimizer가 Execution Plan을 생성 시에 Invisible Index인 Index들을 무시 하게 되는 기능입니다.
Session이나 System별로 OPTIMIZER_USE_INVISIBLE_INDEXES Parameter를 True로 설정하게 되면 Optimizer가 Invisible Index라고 할지라도 무시하지 않고 Execution Plan을 작성하게 됩니다.
Unusable Index와는 다르게 DML 작업을 하면 Invisible Index들은 계속 유지가 됩니다.
Invisible Index의 기능을 이용하면 다음과 같은 장점을 이용할 수 있습니다.
1) Index를 Drop하기 전에 Execution Plan의 변화를 미리 Test해 보실 수 있습니다.
2) 전체 Application의 영향을 주지 않고 특정 Application에서만 Temporary하게 Index를 사용하게 하실 수 있습니다.
2. Syntax
1) Index를 Invisible하게 Create
CREATE INDEX emp_deptno ON emp(deptno)
TABLESPACE users
STORAGE (INITIAL 20K
NEXT 20k
PCTINCREASE 75)
INVISIBLE; < ———————— Invisible Option을 사용하여 Create 한다.
2) Index를 Invisible 혹은 Visible하게 만들기
ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;
3. Test
1) Invisible Index 생성 후에 확인
SQL> CREATE INDEX emp_deptno ON emp(deptno)
TABLESPACE users
STORAGE (INITIAL 20K
NEXT 20k
PCTINCREASE 75)
INVISIBLE;
Index created.
SQL> select index_name, visibility from user_indexes;
INDEX_NAME VISIBILIT
—————————— ———
PK_EMP VISIBLE
EMP_DEPTNO INVISIBLE
PK_DEPT VISIBLE
2) PK_EMP를 Invisible로 바꾼 후의 변화
SQL> ALTER INDEX PK_EMP INVISIBLE;
Index altered.
SQL> set autotrace on
SQL> select * from scott.emp where empno=7369;
EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
———- ——— ———- ——— ———- ———- ———-
7369 CLERK 7902 17-DEC-80 800 20
Execution Plan
———————————————————-
Plan hash value: 3956160932
————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 33 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 33 | 3 (0)| 00:00:01 |
————————————————————————–
3) PK_EMP를 다시 Visible로 바꾼 후의 변화
SQL> ALTER INDEX PK_EMP VISIBLE;
Index altered.
SQL> set autot on
SQL> select * from scott.emp where empno=7369;
EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
———- ——— ———- ——— ———- ———- ———-
7369 CLERK 7902 17-DEC-80 800 20
Execution Plan
———————————————————-
Plan hash value: 2949544139
————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 33 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 33 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
————————————————————————————–
4) 전체 Application에 영향을 미치지 않고 특정 Query에서만 Invisible Index 사용하기
– Session A
* Invisible Index를 사용하도록 OPTIMIZER_USE_INVISIBLE_INDEXES Parameter를 True로 설정합니다.
SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES = true;
Session altered.
* Invisible Index가 있는지 확인 합니다.
SQL> select index_name, visibility from user_indexes;
INDEX_NAME VISIBILIT
—————————— ———
PK_EMP VISIBLE
EMP_DEPTNO INVISIBLE
PK_DEPT VISIBLE
* Index를 사용하는 부분을 확인 할 수 있습니다.
SQL> set autot on
SQL> select * from scott.emp where deptno=20;
EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
———- ——— ———- ——— ———- ———- ———-
7369 CLERK 7902 17-DEC-80 800 20
7566 MANAGER 7839 02-APR-81 2975 20
7788 ANALYST 7566 19-APR-87 3000 20
7876 CLERK 7788 23-MAY-87 1100 20
7902 ANALYST 7566 03-DEC-81 3000 20
Execution Plan
———————————————————-
Plan hash value: 1182541070
——————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
——————————————————————————————
| 0 | SELECT STATEMENT | | 5 | 165 | 2 (0)|00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 165 | 2 (0)|00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPTNO | 5 | | 1 (0)|00:00:01 |
——————————————————————————————
– Session B
SQL> connect / as sysdba
Connected.
* OPTIMIZER_USE_INVISIBLE_INDEXES가 Default로 False임을 확인 합니다.
SQL> show parameter OPTIMIZER_USE_INVISIBLE_INDEXES
NAME TYPE VALUE
———————————— ———– ——————————
optimizer_use_invisible_indexes boolean FALSE
SQL> connect scott/tiger
Connected.
SQL> set autot on
* OPTIMIZER_USE_INVISIBLE_INDEXES가 False일 때 Invisible Index를 사용하지 않음을 확인 할 수 있습니다.
SQL> select * from scott.emp where deptno=20;
EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
———- ——— ———- ——— ———- ———- ———-
7369 CLERK 7902 17-DEC-80 800 20
7566 MANAGER 7839 02-APR-81 2975 20
7788 ANALYST 7566 19-APR-87 3000 20
7876 CLERK 7788 23-MAY-87 1100 20
7902 ANALYST 7566 03-DEC-81 3000 20
Execution Plan
———————————————————-
Plan hash value: 3956160932
————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 5 | 165 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 5 | 165 | 3 (0)| 00:00:01 |
————————————————————————–