03. Invisible Index
말 그대로 보여지지 않는 인덱스를 의미합니다. 즉, 인덱스는 존재하지만 옵티마이저가 인덱스를 볼 수 없는 상태로 만들어서 플랜을 타지 않도록 합니다.
사용 이유
인덱스가 많은 경우 DML 문장에 나쁜 영향을 주기 때문에 사용하지 않는 인덱스는 삭제해 주어야 합니다.
문제는 해당 인덱스를 삭제하려고 했을 때, 정말 사용하는지 사용하지 않는 것인지를 정확하게 알아야 합니다.
인덱스를 삭제했는데 나중에 생각지도 못했던 부분에서 문제가 발생할 수 있습니다.
인덱스가 정말 사용되고 있는 것인 지, 인덱스가 드롭되면 쿼리 성능에 어떤 변화가 있을 지 알고 싶을 때 인덱스를 드롭하지 않고 Invisiable 상태로 바꿔놓고 테스트 해보면 됩니다.
인덱스를 옵티마이저에서 보이지 않게 설정함으로써 쿼리가 사용할 수 없도록 하는 방법입니다. 쿼리에서 INVISIBLE INDEX를 사용하려면 명시적으로 힌트를 적용해야 합니다.
실습
INDEX의 VISIBILITY 조회
IDX_EMP01_ENAME 인덱스를 생성하고, USER_INDEXES 뷰에서 해당 인덱스에 대한 VISIBILITY 상태를 확인하는 경우
SQL> CREATE INDEX IDX_EMP01_ENAME ON EMP01(ENAME);
SQL> SELECT TABLE_NAME, INDEX_NAME, VISIBILITY FROM USER_INDEXES WHERE TABLE_NAME = 'EMP01'; 
현재 visible로 활성화 되어 있는 것으로 확인할 수 있습니다.
INDEX의 VISIBILITY : VISIBLE -> INVISIBLE
IDX_EMP01_ENAME 인덱스를 INVISIBLE("사용 안 함") 상태로 설정하는 경우
SQL> ALTER INDEX IDX_EMP01_ENAME INVISIBLE;
SQL> SELECT TABLE_NAME, INDEX_NAME, VISIBILITY FROM USER_INDEXES WHERE TABLE_NAME = 'EMP01'; 
실습에서 인덱스를 INVISIBLE 했어도 인덱스가 지워진 것은 아니므로 DML 작업 시 인덱스 내용은 계속 반영됩니다. 따라서 INVISIBLE로 설정한 후 점검하여 다른 SQL 문장에 영향을 주는것이 없는 것으로 확인되면 해당 인덱스를 지우면 됩니다.
플랜 테스트
아래 명령을 실행하고 실행 계획을 확인합니다.
SQL> select * from res where guest_id = 101;인덱스가 사용되고 있음을 확인할 수 있습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 1519600902
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| RES | 1 | 28 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IN_RES_GUEST | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------이 인덱스를 INVISIBLE INDEX로 설정해 봅시다
SQL> alter index in_res_guest invisible;다시 쿼리를 실행한 후 플랜을 확인해봅니다.
SQL> select * from res where guest_id = 101
2 /인덱스가 사용되지 않고 있음을 확인할 수 있습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 3824022422
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 140 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| RES | 1 | 28 | 140 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------INVISIBLE 상태인 인덱스를 옵티마이저가 사용하도록 하고 싶다면 힌트에 인덱스 이름을 명시하면 됩니다.
SQL> select /*+ INDEX (res IN_RES_GUEST) */ res_id from res where guest_id = 101;다시 플랜을 타는 것을 볼 수 있습니다.
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| RES | 1 | 28 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IN_RES_GUEST | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------또는 세션 레벨 매개변수에서 INVISIBLE INDEX를 사용하도록 설정할 수도 있습니다:
SQL> alter session set optimizer_use_invisible_indexes = true;이 기능은 코드를 변경할 수 없는 써드 파티 애플리케이션 환경에서 매우 유용하게 활용됩니다.
인덱스를 생성하는 시점에 INVISIBLE 조건을 사용해서 인비저블 인덱스로 생성할 수도 있습니다.
이 인덱스의 리빌드 작업을 수행하면, 인덱스가 VISIBLE 상태로 전환된다는 점에 주의하시기 바랍니다.
리빌드를 완료한 후 다시 한 번 명시적으로 INVISIBLE로 설정해 주어야 합니다.
주의사항
기존 DML의 성능에 영향을 줄 수 있음에 따라 운영 중인 DB에는 사용해서는 안됩니다.
참고
