조회 수 11355 추천 수 0 댓글 1
11g New Feature : Invisible Index
========================

1. Invisible Index란

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


Reference
Article-ID: Note 453295.1
Title: 11g New Feature : Invisible Index


List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
공지 Q&A Oracle관련 게시물만 Sean 2014.04.09 84902
40 Tip 오라클 업그레이드 메뉴얼 1 file perfstat 2012.02.06 11894
39 Tip LIBRARY CACHE PIN 고구마 2011.11.10 12949
38 Tip ORACLE 10G CONVERTING TABLESPACES WITH THE RMAN CONVERT 1 흑수건 2011.10.20 11949
37 Tip 11g alertlog 1 흑수건 2011.10.06 14417
36 Tip DATABASE에 CONNECT안될때 SYSTEMSTATE DUMP하는법 흑수건 2011.10.06 10655
35 Tip [10g]DATAPUMP IMPORT는 자동으로 USER 생성 흑수건 2011.10.06 16771
» Tip [11g] New Feature : Invisible Index 1 흑수건 2011.10.06 11355
33 Tip EXPORT의 Query Option 기능 흑수건 2011.10.01 11840
32 Tip 특정 DB USER의 SESSION수를 제한하는 방법 흑수건 2011.10.01 12385
31 Tip 오라클상세 버젼 확인하기 lsinventory 고구마 2011.09.14 13844
30 Tip Windows 32bit OS 메모리 제약과 Oracle 에서 추가메모리 사용하도록 흑수건 2011.09.04 15772
29 Tip SESSIONS: derived (1.1 * PROCESSES + 5) 흑수건 2011.08.30 11322
28 Tip cpu 사용이 높을때 흑수건 2011.08.30 11489
27 Tip hardparse 발생및 세션 검사 2 도로시 2011.08.17 12665
26 Tip ora-3113 에러 분석 접근관련... 고구마 2011.06.14 12788
25 Tip 오라클 제품 비교 file 송기성 2011.05.22 13415
24 자료 oracle 문제발생시 대처 가이드 2 file dbkill 2011.05.16 5646
23 자료 ORA-600 metalink notes for help file dbkill 2011.03.29 7640
22 자료 C컴파일시 makefile의 구조설명 file 김준호 2010.04.22 7227
21 자료 윈도우 tail 사용법 1 file 유주환 2010.04.19 7369
Board Pagination Prev 1 2 3 4 5 6 7 8 Next
/ 8