Developers often come to me with the same question: “I want to build the following index, do you think it is correct?”
I will assume that the developer analyzed the functional use of the index correctly, so I can explain how to test the impact of the index in your production environment.
First of all, we need to generate a specific scenario. We will create and populate a table, and then we will create an invisible index and gather statistics for all these objects.
SQL> CREATE TABLE TEST.INDEX_TEST_TABLE 2 AS 3 (SELECT * FROM DBA_OBJECTS); Table created. SQL> CREATE INDEX TEST.IDX_TEST_TABLE 2 ON TEST.INDEX_TEST_TABLE (OBJECT_NAME, OBJECT_TYPE) 3 INVISIBLE 4 ONLINE; Index created. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'TEST', TABNAME => 'INDEX_TEST_TABLE', CASCADE => TRUE); PL/SQL procedure successfully completed. SQL>
After you’ve done that you can see that, although you have gathered statistics, the execution plan does not use the index; that is because invisible indexes cannot be ‘seen’ by the CBO (Cost-Based Optimizer):
SQL> SET AUTOTRACE ON EXPLAIN
SQL> COL OBJECT_ID FORMAT 999999999
SQL> COL OBJECT_NAME FORMAT A12
SQL> COL OBJECT_TYPE FORMAT A12
SQL> SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE
2 FROM TEST.INDEX_TEST_TABLE
3 WHERE OBJECT_NAME = 'DBA_TABLES' AND OBJECT_TYPE = 'VIEW';
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ------------ ------------
3415 DBA_TABLES VIEW
Execution Plan
----------------------------------------------------------
Plan hash value: 2221628123
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 235 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| INDEX_TEST_TABLE | 1 | 39 | 235 (3)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DBA_TABLES' AND "OBJECT_TYPE"='VIEW')
SQL>
Now, we modify the parameter OPTIMIZER_USE_INVISIBLE_INDEXES at session level to understand the efficiency of the new index.
SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
Session altered.
SQL> COL OBJECT_ID FORMAT 999999999
SQL> COL OBJECT_NAME FORMAT A12
SQL> COL OBJECT_TYPE FORMAT A12
SQL> SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE
2 FROM TEST.INDEX_TEST_TABLE
3 WHERE OBJECT_NAME = 'DBA_TABLES' AND OBJECT_TYPE = 'VIEW';
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ------------ ------------
3415 DBA_TABLES VIEW
Execution Plan
----------------------------------------------------------
Plan hash value: 3916810155
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| INDEX_TEST_TABLE | 1 | 39 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_TABLE | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='DBA_TABLES' AND "OBJECT_TYPE"='VIEW')
SQL>
As you can see, now that the CBO can use the new index and generate a new execution plan, it optimizes the execution plan cost.
And what about the execution time? Well, if you are improving your execution plan, you will obviously have a better execution time.
Running the query with the parameter OPTIMIZER_USE_INVISIBLE_INDEXES by default (FALSE), we get:
SQL> SET AUTOTRACE OFF
SQL> SET TIMING ON
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
Elapsed: 00:00:00.04
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
Elapsed: 00:00:00.05
SQL> COL OBJECT_ID FORMAT 999999999
SQL> COL OBJECT_NAME FORMAT A12
SQL> COL OBJECT_TYPE FORMAT A12
SQL> SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE
2 FROM TEST.INDEX_TEST_TABLE
3 WHERE OBJECT_NAME = 'DBA_TABLES' AND OBJECT_TYPE = 'VIEW';
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ------------ ------------
3415 DBA_TABLES VIEW
Elapsed: 00:00:00.36
SQL>
Now, we execute the query once again with the parameter OPTIMIZER_USE_INVISIBLE_INDEXES set as ‘TRUE’.
SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
Session altered.
Elapsed: 00:00:00.01
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
Elapsed: 00:00:00.02
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
Elapsed: 00:00:00.21
SQL> COL OBJECT_ID FORMAT 999999999
SQL> COL OBJECT_NAME FORMAT A12
SQL> COL OBJECT_TYPE FORMAT A12
SQL> SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE
2 FROM TEST.INDEX_TEST_TABLE
3 WHERE OBJECT_NAME = 'DBA_TABLES' AND OBJECT_TYPE = 'VIEW';
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ------------ ------------
3415 DBA_TABLES VIEW
Elapsed: 00:00:00.05
SQL>
The benefit is clearly evident, so you can decide if you want to make it effective or not.
Currently, your index is still invisible to the CBO; the previous examples just show how to evaluate and make an index visible or invisible at session level.
You can verify that by executing the following query:
SQL> SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME = 'IDX_TEST_TABLE'; INDEX_NAME VISIBILIT ------------------------------ --------- IDX_TEST_TABLE INVISIBLE Elapsed: 00:00:00.03 SQL>
You can change your global index visibility by executing:
SQL> ALTER INDEX TEST.IDX_TEST_TABLE VISIBLE;
Index altered. Elapsed: 00:00:00.36 SQL> SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME = 'IDX_TEST_TABLE'; INDEX_NAME VISIBILIT ------------------------------ --------- IDX_TEST_TABLE VISIBLE Elapsed: 00:00:00.00 SQL>
It’s important to note that when you add invisible indexes, the “cloak of invisibility” only applies for search phases, i.e. when your indexed column is present inside the predicate or, for example, when you use INDEX FULL SCAN as a data-access method, to search for the maximum or minimum value of your indexed column. The I/O and CPU resources derived from modifying the indexed column on the table will still be consumed. If you insert or delete a row, or if you update the indexed column, the change must be done inside the index so that you have an overhead associated with it.
Additionally, you can also use this feature for other purposes – you can read many articles by database administrators recommending the use of this feature for analyzing indexes you are about to delete, thereby avoiding the need to rebuild it again if something goes wrong after deleting it. Another common usage could be creating invisible indexes for specific queries that are very seldom executed.
Whatever the case may be, you can always use this feature to fit your particular needs.
