Invisible Index ( 11g ~ )
: 사전에 Index 영향도 파악 한다는 의미로는 Invisible Index 이상적이나,
DML 이 많이 발생 하고, Query 에서 자주 사용되는 테이블의 경우,
운영 중, 실제 Index 를 visible, Invisible 시키기는 불가능하다.
추가로, Index Invisible 옵션은 opt_param 설정불가능함
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 | -- DB Version SCOTT>select * from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production -- DB Paramter Check SCOTT>col name for a50 SCOTT>col value for a30 SCOTT>SELECT NAME, VALUE, ISDEFAULT FROM V$PARAMETER WHERE NAME ='optimizer_use_invisible_indexes'; NAME VALUE ISDEFAULT -------------------------------------------------- ------------------------------ --------- optimizer_use_invisible_indexes FALSE TRUE -- Create Teset Table SCOTT>drop table t1 purge ; Table dropped. SCOTT>create table T1 ( a1 number, a2 varchar2(100)) ; Table created. -- Create Invisible Index SCOTT>create index xak01_t1 on T1 ( a1 ) invisible ; Index created. SCOTT>select /*+ TG01 GATHER_PLAN_STATISTICS */ count(*) from t1 where a1 = 1 ; COUNT(*) ---------- 0 -- Full Scan - Index is Invisible SCOTT>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS LAST -OUTLINE -PREDICATE')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ SQL_ID c4nud85cs2gvh, child number 0 ------------------------------------- select /*+ TG01 GATHER_PLAN_STATISTICS */ count(*) from t1 where a1 = 1 Plan hash value: 3724264953 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | | 1 | SORT AGGREGATE | | 1 | 1 | 13 | | | 1 |00:00:00.01 | | 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 13 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | ----------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] Note ----- - dynamic sampling used for this statement (level=2) 29 rows selected. -- optimizer_use_invisible_indexes --> true SCOTT>ALTER SESSION SET optimizer_use_invisible_indexes = true; Session altered. SCOTT>select /*+ TG01 GATHER_PLAN_STATISTICS */ count(*) from t1 where a1 = 1 ; COUNT(*) ---------- 0 -- Index Range Scan SCOTT>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS LAST -OUTLINE -PREDICATE')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ SQL_ID c4nud85cs2gvh, child number 1 ------------------------------------- select /*+ TG01 GATHER_PLAN_STATISTICS */ count(*) from t1 where a1 = 1 Plan hash value: 2398699050 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 1 (100)| | 1 |00:00:00.01 | | 1 | SORT AGGREGATE | | 1 | 1 | 13 | | | 1 |00:00:00.01 | | 2 | INDEX RANGE SCAN| XAK01_T1 | 1 | 1 | 13 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | -------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] Note ----- - dynamic sampling used for this statement (level=2) 29 rows selected. SCOTT> -- Set Index Visible SCOTT>alter index xak01_t1 visible ; Index altered. SCOTT>select index_name , VISIBILITY from user_indexes where index_name = upper('xak01_t1'); INDEX_NAME VISIBILIT ------------------------------ --------- XAK01_T1 VISIBLE SCOTT> | cs |