Using Invisible Indexes
Table created.
SQL> create index ind_TEST.index_tables on TEST.tables_empty (a)
Index created.
OWNER OBJECT_NAME OBJECT_TYPE
—————————— —————————— ———–
TEST TABLES_EMPTY TABLE
IND_TEST INDEX_TABLES INDEX
SQL> exec dbms_stats.gather_table_stats
(ownname=>’TEST’,tabname=>’TABLES_EMPTY’,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_index_stats
(ownname=>’IND_TEST’,indname=>’INDEX_TABLES’);
PL/SQL procedure successfully completed.
SQL> conn ind_TEST
Enter password:
SQL> explain plan for
2 select * from TEST.TABLES_EMPTY;
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
———————————————————————–
Plan hash value: 932631555
———————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TABLES_EMPTY | 1 | 13 | 2 (0)| 00:00:01 |
———————————————————————————-
SQL> explain plan for
2 select /*+ index (a INDEX_TABLES)*/ * from TEST.TABLES_EMPTY a;
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
———————————————————–
Plan hash value: 932631555
———————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TABLES_EMPTY | 1 | 13 | 2 (0)| 00:00:01 |
———————————————————————————-
CBO does not use created index,neither using hint, with this method.
Invisible indexes are the solution to test different cases and best cbo access plan.
SQL> create index TEST.INDEX_TABLES on TEST.tables_empty (1) invisible;
Index created.
INDEX_NAME VISIBILIT LAST_ANAL
—————————— ——— ———
INDEX_TABLES INVISIBLE 23-JAN-12
OWNER OBJECT_NAME OBJECT_TYPE
—————————— ——————————
TEST TABLES_EMPTY TABLE
TEST INDEX_TABLES INDEX
SQL> sho user
USER is “IND_TEST”
SQL> explain plan for
2 select * from TEST.TABLES_EMPTY;
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
—————————————————————
Plan hash value: 932631555
——————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TABLES_EMPTY | 1 | 13 | 2 (0)| 00:00:01 |
————————————————————–
SQL> alter session set optimizer_use_invisible_indexes=true;
Session altered.
SQL> explain plan for
2 select /*+ index (a INDEX_TABLES)*/ * from TEST.TABLES_EMPTY a;
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
————————————————————————-
Plan hash value: 482654189
———————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TABLES_EMPTY | 1 | 13 | 1 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | INDEX_TABLES | 1 | | 1 (0)| 00:00:01 |
———————————————————————————————
Testing it whith different users
SQL> sho user
USER is “TEST”
SQL>
SQL> explain plan for
2 select /*+ index (a INDEX_TABLES)*/ * from TEST.TABLES_EMPTY a;
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
———————————————————————
Plan hash value: 932631555
———————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TABLES_EMPTY | 1 | 13 | 2 (0)| 00:00:01 |
———————————————————————————-
SQL> alter session set optimizer_use_invisible_indexes=true;
Session altered.
SQL> explain plan for
2 select /*+ index (a INDEX_TABLES)*/ * from TEST.TABLES_EMPTY a;
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
—————————————————————————————–
Plan hash value: 482654189
———————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TABLES_EMPTY | 1 | 13 | 1 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | INDEX_TABLES | 1 | | 1 (0)| 00:00:01 |
———————————————————————————————
