Using Oracle 11g Invisible Indexes
By John Adolph Palinski
As a DBA, I am often called upon to tune some SQL. After analyzing the statement and explain plans I may decide to create an index. I sometimes get concerned about putting an index on a production system table in order to test its effectiveness since it might change the access plan for other SQL statement causing overall problems. Oracle has given us a new tool called an Invisible Index that can prevent the unforeseen impact of a new index. This new tool prevents the index from being used until the DBA is ready. In 11g the DBA can now create an index that is invisible to the optimizer. Invisible indexes can also be useful with canned systems where the DBA is not allowed to put an index in place in order to tune poorly running SQL. Let's take a look at the invisible index.
Our first task is to create some test data. Listing 1 contains the data definition language (DDL) scripts for a sequence and a table called Invisible_index_test. This table contains 68,918 records as the listing indicates.
Listing 1 - DDL scripts used to create and populate the Invisible_index_test table
SQL> create sequence test;
Sequence created.
SQL> create table invisible_index_test
2 as
3 select test.nextval rec_id, 'John' name
4 from dba_objects;
Table created.
SQL> select count(*) from invisible_index_test;
COUNT(*)
----------
68918
The next step is to look at the Explain plan for a Select statement that retrieves a single row from the example table table. The commands to create and display the plan are shown in Listing 2. The first statement populates the Plan_table with the explain plan. Following this statement is a DDL statement creating a view called Show_plan. This view simply reads the Plan_table and presents the plan in a formatted manner. This view will be used throughout this article. Notice that the last statement in the listing displays the generated plan. Review the plan and notice that the select statement must do a table scan to retrieve the desired record. It is evident that an index is needed on the Rec_id column.
Listing 2 - Creating and displaying an Explain plan for a Select statement
SQL> explain plan for
2 select *
3 from invisible_index_test
4 where rec_id = 54323;
Explained.
SQL> create view show_plan as
2 select
3 substr (lpad(' ', level-1) ||
4 operation || '(' || options || ')', 1, 30 ) "Operation",
5 object_name "Object"
6 from plan_table
7 start with id = 0
8 connect by prior id = parent_id;
View created.
SQL> select *
2 from show_plan;
Operation Object
------------------------------ ------------------------------
SELECT STATEMENT()
TABLE ACCESS(FULL) INVISIBLE_INDEX_TEST
Now we are able to look at the invisible index. You can create an invisible index by placing the Invisible option at the end of the Create Index statement. This is illustrated in Listing 3. In this listing an invisible index, Invisible_index_i01, is placed on the Rec_id column.
Listing 3 - Creating an invisible index on the Rec_id column
SQL> create index invisible_index_i01 on
2 invisible_index_test (rec_id) invisible;
Index created.
In Listing 4, an Explain plan is generated for our example Select statement. Notice the Explain plan shows that a full table scan is still performed. This proves that the optimizer ignores the invisible index placed on the Rec_id column.
Listing 4 - Explain plan showing that the invisible index is ignored by the optimizer
SQL> delete from plan_table;
2 rows deleted.
SQL> explain plan for
2 select *
3 from invisible_index_test
4 where rec_id = 54323;
Explained.
SQL> select *
2 from show_plan;
Operation Object
------------------------------ ------------------------------
SELECT STATEMENT()
TABLE ACCESS(FULL) INVISIBLE_INDEX_TEST
The optimizer cannot see the index even if a hint is placed in the statement. This is shown in Listing 5. A hint is added to the example Select statement. An explain plan is then generated. The Select statement still performs a full table scan.
Listing 5 - Hint is added to the Select statement
SQL> explain plan for
2 select /*+ index(invisible_index_i01) */ *
3 from invisible_index_test
4 where rec_id = 54323;
Explained.
SQL> select *
2 from show_plan;
Operation Object
------------------------------ ------------------------------
SELECT STATEMENT()
TABLE ACCESS(FULL) INVISIBLE_INDEX_TEST
To cause the optimizer to begin using invisibile indexes you must change the Optimizer_use_invisible_indexes parameter. This parameter can be changed at the session or the instance level. If it is changed at the session level it can be tested without affecting other sessions. It can also be turned on and off as needed. If the Optimizer_use_invisible_indexes parameter is turned on for the instance then the optimizer can potentially use the index for all sessions. In Listing 6 the session was changed and the Optimizer_use_invisible_indexes parameter is set to True. An Explain plan was generated for our example Select statement. The plan is then shown at the end of the listing. Notice that the explain now indicates that an Index(range scan) of the Invisible_index_i01 index will be performed rather than the tablespace scan. This proves that changing the parameter enables the use of hidden indexes.
Listing 7 - Optimizer_use_invisible_indexes session parameter is changed
SQL> alter session set optimizer_use_invisible_indexes = true;
Session altered.
SQL> delete from plan_table;
2 rows deleted.
SQL> explain plan for
2 select *
3 from invisible_index_test
4 where rec_id = 54323;
Explained.
SQL> select *
2 from show_plan;
Operation Object
------------------------------ ------------------------------
SELECT STATEMENT()
TABLE ACCESS(BY INDEX ROWID) INVISIBLE_INDEX_TEST
INDEX(RANGE SCAN) INVISIBLE_INDEX_I01
You can determine whether an index is visible by viewing the DBA_indexes data dictionary table. Oracle has added a Visibility column to the table. This is shown in Listing 7. The listing shows a Select statement against the Dba_indexes table showing the Invisible_index_i01 Visibility property.
SQL> select index_name, visibility
2 from dba_indexes
3 where index_name like 'INVISIBLE%';
INDEX_NAME VISIBILIT
------------------------------ ---------
INVISIBLE_INDEX_I01 INVISIBLE
Finally, you can make an existing index visible or invisible using an Alter Index statement. Listing 8 depicts this. The Alter Index command is used to make the Invisible_index_i01 index available for use by the optimizer.
SQL> alter index invisible_index_i01 visible;
Index altered.
SQL> select index_name, visibility
2 from user_indexes
3 where index_name like 'INVISIBLE%';
INDEX_NAME VISIBILIT
------------------------------ ---------
INVISIBLE_INDEX_I01 VISIBLE
SQL>
As you can see, the new Visibility index option can be a easy and effective tool for testing indexes without affecting production systems. They can also be used selectively by using Optimizer_use_invisible_indexes parameter.
John Adolph Palinski is a former adjunct faculty member at the University of Nebraska at Omaha and Iowa Western Community College. He is the author of the "Oracle Database Construction Kit", Que, 1997, the "Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts", Addison Wesley Longman, 2002, and "Oracle 9i Developer: Developing Web Applications With Forms Builder", Course Technology, 2003. He has also written numerous articles for periodicals such as Oracle Internals and Oracle Professional. He provides Oracle training and consulting services. One of his passions is to help customers set up logical business models to improve information efficiency and effectiveness. He may be contacted at www.oracle-trainer.com.
