오라클11g INDEX INVISIBLE TIP [ORACLEJAVA커뮤니티자바오라클교육강좌오라클자바교육강좌,자바교육오라클교육]

QUERY 작업을 할 때 가끔은 인덱스가 없다면 실행계획이 어떻게 될까,고민 하면서 인덱스를 DROP 하고 실행 계획을 본 후 다시 INDEX를 생성하여 실행 계획을 보는 경우 들이 있을 것이다. 적은 량의 데이터라면 문제가 아닐텐데 많은 양의 데이터가 있는 테이블 이라면 조금은 고민 될 것이다.
이때 사용할 수 있는 것이 Oracle 11g에서 소개된 INVISIBLE INDEX 인데 인덱스를 invisible로 만들어서 옵티마이저에서 명시적으로 사용하지 말라고 지시를 할 수 있다.

 오라클자바커뮤니티에서 설립한  개발자실무교육6년차 오엔제이프로그래밍 실무교육센터

(신입사원채용무료교육, 오라클, SQL, 튜닝, 자바, 스프링, Ajax, jQuery, 안드로이드, 아이폰, 닷넷, C#, ASP.Net)   www.onjprogramming.co.kr 

[예]

conn / as sysdba

SQL>@e:\app\a\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql
SQL>@e:\app\a\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql
SQL>grant plustrace to scott

conn scott/tiger

SQL>set autotrace on

create index idx_emp_sal on emp(sal);

set autotrace on

select empno, ename from emp
where sal > 500;

Execution Plan
----------------------------------------------------------
Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

|   0 | SELECT STATEMENT            |             |    14 |   196 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP         |    14 |   196 |     2   (0)|
|*  2 |   INDEX RANGE SCAN          | IDX_EMP_SAL |    14 |       |     1   (0)|

IDX_EMP_SAL 인덱스를 이용했음을 알 수 있다.

이제 인덱스를 invisible 시켜보자.

SQL>ALTER INDEX IDX_EMP_SAL INVISIBLE;



SQL>select empno, ename from emp
where sal > 500;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   196 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |   196 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

실행 계획을 보면 IDX_EMP_SAL 인덱스를 사용하지 않았음을 알 수 있다.


SQL> SELECT INDEX_NAME, INDEX_TYPE, VISIBILITY
    FROM ALL_INDEXES
    WHERE INDEX_NAME LIKE '%EMP%';

INDEX_NAME                     INDEX_TYPE                  VISIBILITY          
------------------------------ --------------------------- --------------------
IDX_EMP_SAL                    NORMAL                      INVISIBLE          
PK_EMP                         NORMAL                      VISIBLE  


ALL_INDEXES 뷰를 보면 INVISIBLE 상태로 되어 있음을 알 수 있다.

다시 인덱스를 보이도록 하려면...

SQL>alter index IDX_EMP_SAL visible;

SQL> SELECT INDEX_NAME, INDEX_TYPE, VISIBILITY 
    FROM ALL_INDEXES 
    WHERE INDEX_NAME LIKE '%EMP%';

INDEX_NAME                     INDEX_TYPE                  VISIBILITY          
------------------------------ --------------------------- --------------------
IDX_EMP_SAL                    NORMAL                      VISIBLE            
PK_EMP                         NORMAL                      VISIBLE            

2 rows selected.

 

[출처] 오라클자바커뮤니티 - http://www.oraclejavanew.kr/bbs/board.php?bo_table=Oracle11&wr_id=1