Invisible Index


  1. Invisible Index

  • Invisible Index는 11g에서 새로 나온 것으로 Virtual Index와는 달리 Optimizer에게 보이지 않을 뿐 실제로 세그먼트
    형태로 존재하는 인덱스입니다.
  • Invisible Index는 실제로 존재하는 인덱스  이고 따라서 통계 정보가 수집된다. 따라서 보다 정확한 테스트를 수행할 수 있습니다.
  • 테스트 후 반영여부가 결정되면 손쉽게 Visible Index로 전환할 수 있습니다.

     

  1. Invisible Index Test

  • Test 환경 구성
• Tablespace 생성 및 User 생성

SQL> CREATE TABLESPACE TEST DATAFILE ‘/oracle11/app/oracle/oradata/ORA11/test01.dbf’ SIZE 10M;

SQL> CREATE USER TEST IDENTIFIED BY TEST DEFAULT TABLESPACE TEST;

SQL> GRANT CONNECT, RESOURCE TO TEST;

• Test Table 생성 및 통계정보 수집

SQL> CREATE TABLE Invisible_index (id NUMBER);

SQL> BEGIN

FOR i IN 1 .. 10000 LOOP

INSERT INTO invisible_index VALUES (i);

END LOOP;

COMMIT;

END;

/

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘Invisible_index’, CASCADE=>TRUE); 

  • Test Table 의 PLAN 정보 확인

• PLAN 확인

SQL> SET SERVEROUTPUT ON;

SQL> SET AUTOTRACE TRACEONLY EXP;

SQL> SELECT * FROM Invisible_index WHERE id = 9999;

  • 컬럼에 Invisible Index 추가 후 PLAN 확인

• ID에 Invisible Index 추가

SQL> CREATE INDEX invisible_id ON invisible_index(id) INVISIBLE;

• PLAN 확인

SQL> SET SERVEROUTPUT ON;

SQL> SET AUTOTRACE TRACEONLY EXP;

SQL> SELECT * FROM Invisible_index WHERE id = 9999;


è Invisible 상태이기 때문에 옵티마이저가 Index를 볼 수가 없어서 Full Table Scan을 함.

  • Invisible Index 사용 하기 위해서 Parameter 설정 후 PLAN확인
• Invisible Index를 사용하도록 Parameter 설정

SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;

• Parameter 설정 후 PLAN확인

SQL> SET SERVEROUTPUT ON;

SQL> SET AUTOTRACE TRACEONLY EXP;

SQL> SELECT * FROM Invisible_index WHERE id = 9999;


è Parameter 설정 후 PLAN을 확인해 보면 Index range scan을 한 것을 확인할 수 있음.

  • Index 확인

• user_indexes View 조회

SQL> SET AUTOTRACE OFF;

SQL> SELECT index_name, visibility FROM user_indexes;

è
Virtual Index와는 달리 Invisible Index는 실제 Segment가 할당이 되기 때문에 user_indexes View에서 확인이 가능하고 Invisible 상태인지 visible 상태인지 확인도 가능하다.

  • Invisible Index를 visible 상태로 변경
• Invisible Index를 Optimizer가 볼 수 있도록 visible 상태로 변경

SQL> ALTER INDEX Invisible_index visible;

• Index 상태 확인

SQL> SELECT index_name, visibility FROM user_indexes;


è Index의 상태가 VISIBLE인 것을 확인할 수 있음.


Comments

comments

haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

Invisible Index”의 1개의 댓글

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다