Invisible indexes are a nice addition to Oracle, since in general, the optimizer ignores them when generating execution plans. It appears though, that in 11.2.0.4 that isn’t necessarily the case since it can still ‘see’ the index statistic distinct_key and thus will consider the index at run time. Let’s look at a modifed example, provided by Jonathan Lewis that illustrates this. The same example will be run in both 11.2.0.4 and in 12.1.0.2 and there will be differences in the behavior.
The general concept of invisible indexes is that everything associated with them will be ‘invisible’ to the optimizer, just as though the index were dropped. This makes it so much easier for the DBA to evaluate whether an index is ‘used’ or not by examining the execution plans. Unfortunately in this case the existence of the invisible index DOES impact the execution plan. Running the example in 11.2.0.4, the version known to exhibit this behavior produces the following output:
SQL> execute dbms_random.seed(0)
PL/SQL procedure successfully completed.
SQL>
SQL> drop table t2;
Table dropped.
SQL> drop table t1;
Table dropped.
SQL>
SQL> create table t1
2 nologging
3 as
4 with generator as (
5 select --+ materialize
6 rownum id
7 from dual
8 connect by
9 level <= 1e4
10 )
11 select
12 cast(rownum as number(8,0)) id,
13 cast(mod(rownum,1000) as number(8,0)) n1,
14 cast(lpad(rownum,10,'0') as varchar2(10)) v1,
15 cast(lpad('x',100,'x') as varchar2(100)) padding
16 from
17 generator v1,
18 generator v2
19 where
20 rownum <= 1e6
21 ;
Table created.
SQL>
SQL> create table t2
2 as
3 select
4 rownum id,
5 trunc(dbms_random.value(0,10000)) n1
6 from
7 dual
8 connect by
9 level <= 100
10 ;
Table created.
SQL> begin
2 dbms_stats.gather_table_stats(
3 ownname => user,
4 tabname =>'T1',
5 method_opt => 'for all columns size 1'
6 );
7 dbms_stats.gather_table_stats(
8 ownname => user,
9 tabname =>'T2',
10 method_opt => 'for all columns size 1'
11 );
12 end;
13 /
PL/SQL procedure successfully completed.
SQL>
SQL> column n1 new_value m_n1
SQL> select n1 from t2 where id = 50;
N1
----------
5308
SQL> clear columns
SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> select
2 t1.*
3 from
4 t1, t2
5 where
6 t2.n1 = &m_n1
7 ;
old 6: t2.n1 = &m_n1
new 6: t2.n1 = 5308
Execution Plan
----------------------------------------------------------
Plan hash value: 1323614827
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 119M| 4764 (1)| 00:00:58 |
| 1 | MERGE JOIN CARTESIAN| | 1000K| 119M| 4764 (1)| 00:00:58 |
|* 2 | TABLE ACCESS FULL | T2 | 1 | 4 | 2 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 1000K| 115M| 4762 (1)| 00:00:58 |
| 4 | TABLE ACCESS FULL | T1 | 1000K| 115M| 4762 (1)| 00:00:58 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T2"."N1"=5308)
SQL>
This same plan should be generated with an invisible index present, but it isn’t. Creating the index and running the query again shows what should be expected, the index changes the execution path:
SQL> create unique index t2_i1 on t2(n1)
2 -- invisible
3 ;
Index created.
SQL>
SQL> select
2 t1.*
3 from
4 t1, t2
5 where
6 t2.n1 = &m_n1
7 ;
old 6: t2.n1 = &m_n1
new 6: t2.n1 = 5308
Execution Plan
----------------------------------------------------------
Plan hash value: 3718023653
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 119M| 4762 (1)| 00:00:58 |
| 1 | NESTED LOOPS | | 1000K| 119M| 4762 (1)| 00:00:58 |
|* 2 | INDEX UNIQUE SCAN| T2_I1 | 1 | 4 | 0 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1000K| 115M| 4762 (1)| 00:00:58 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T2"."N1"=5308)
SQL>
Making the index invisible should return us to the original plan, but it doesn’t:
SQL> alter index t2_i1 invisible;
Index altered.
SQL>
SQL> select
2 t1.*
3 from
4 t1, t2
5 where
6 t2.n1 = &m_n1
7 ;
old 6: t2.n1 = &m_n1
new 6: t2.n1 = 5308
Execution Plan
----------------------------------------------------------
Plan hash value: 4016936828
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 119M| 4764 (1)| 00:00:58 |
| 1 | NESTED LOOPS | | 1000K| 119M| 4764 (1)| 00:00:58 |
|* 2 | TABLE ACCESS FULL| T2 | 1 | 4 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1000K| 115M| 4762 (1)| 00:00:58 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T2"."N1"=5308)
SQL>
Even though no index is listed in the plan steps the index statistics have affected the plan, changing it from a merge join Cartesian with a one-row table to a nested loops join. A 10053 trace of the query shows the invisible index has been considered in the path generation. The trace without the index shows these base table statistics:
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T2 Alias: T2
#Rows: 100 #Blks: 1 AvgRowLen: 7.00 ChainCnt: 0.00
***********************
Table Stats::
Table: T1 Alias: T1
#Rows: 1000000 #Blks: 17544 AvgRowLen: 121.00 ChainCnt: 0.00
Access path analysis for T1
***************************************
The trace with the invisible index in place:
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T2 Alias: T2
#Rows: 100 #Blks: 1 AvgRowLen: 7.00 ChainCnt: 0.00
Index Stats::
Index: T2_I1 Col#: 2
LVLS: 0 #LB: 1 #DK: 100 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
INVISIBLE
***********************
Table Stats::
Table: T1 Alias: T1
#Rows: 1000000 #Blks: 17544 AvgRowLen: 121.00 ChainCnt: 0.00
***************************************
Moving to release 12.1.0.2 the story is different; without the invisible index created the same plan is generated as was in 11.2.0.4:
SQL> execute dbms_random.seed(0)
PL/SQL procedure successfully completed.
SQL>
SQL> drop table t2;
Table dropped.
SQL> drop table t1;
Table dropped.
SQL>
SQL> create table t1
2 nologging
3 as
4 with generator as (
5 select --+ materialize
6 rownum id
7 from dual
8 connect by
9 level <= 1e4
10 )
11 select
12 cast(rownum as number(8,0)) id,
13 cast(mod(rownum,1000) as number(8,0)) n1,
14 cast(lpad(rownum,10,'0') as varchar2(10)) v1,
15 cast(lpad('x',100,'x') as varchar2(100)) padding
16 from
17 generator v1,
18 generator v2
19 where
20 rownum <= 1e6
21 ;
Table created.
SQL>
SQL> create table t2
2 as
3 select
4 rownum id,
5 trunc(dbms_random.value(0,10000)) n1
6 from
7 dual
8 connect by
9 level <= 100
10 ;
Table created.
SQL> begin
2 dbms_stats.gather_table_stats(
3 ownname => user,
4 tabname =>'T1',
5 method_opt => 'for all columns size 1'
6 );
7 dbms_stats.gather_table_stats(
8 ownname => user,
9 tabname =>'T2',
10 method_opt => 'for all columns size 1'
11 );
12 end;
13 /
PL/SQL procedure successfully completed.
SQL>
SQL> column n1 new_value m_n1
SQL> select n1 from t2 where id = 50;
N1
----------
5308
SQL> clear columns
SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> select
2 t1.*
3 from
4 t1, t2
5 where
6 t2.n1 = &m_n1
7 ;
old 6: t2.n1 = &m_n1
new 6: t2.n1 = 5308
Execution Plan
----------------------------------------------------------
Plan hash value: 1323614827
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 119M| 4776 (1)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 1000K| 119M| 4776 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | T2 | 1 | 4 | 2 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 1000K| 115M| 4774 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 1000K| 115M| 4774 (1)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T2"."N1"=5308)
SQL>
Creating the index in a visible state, again alters the plan as we would expect:
SQL> create unique index t2_i1 on t2(n1)
2 -- invisible
3 ;
Index created.
SQL>
SQL> select
2 t1.*
3 from
4 t1, t2
5 where
6 t2.n1 = &m_n1
7 ;
old 6: t2.n1 = &m_n1
new 6: t2.n1 = 5308
Execution Plan
----------------------------------------------------------
Plan hash value: 3718023653
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 119M| 4774 (1)| 00:00:01 |
| 1 | NESTED LOOPS | | 1000K| 119M| 4774 (1)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN| T2_I1 | 1 | 4 | 0 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1000K| 115M| 4774 (1)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T2"."N1"=5308)
SQL>
Making the index invisible returns us to the original plan:
SQL> alter index t2_i1 invisible;
Index altered.
SQL>
SQL> select
2 t1.*
3 from
4 t1, t2
5 where
6 t2.n1 = &m_n1
7 ;
old 6: t2.n1 = &m_n1
new 6: t2.n1 = 5308
Execution Plan
----------------------------------------------------------
Plan hash value: 1323614827
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 119M| 4776 (1)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 1000K| 119M| 4776 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | T2 | 1 | 4 | 2 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 1000K| 115M| 4774 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 1000K| 115M| 4774 (1)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T2"."N1"=5308)
SQL>
This is as it should be. Oracle does consider this a bug, 16564891, and it is associated with bug 16544878, the ‘original’ invisible index bug that caused the distinct_key statistic to be used for invisible indexes even though it should be ignored. As noted above it is fixed in version 12.1.0.2; for those still running 11.2.0.4 this can be a problem especially if you’re trying to decide whether an index is ‘used’ or not and have decided that making it invisible will reveal the necessary information.
As mentioned before in other articles following the mantra ‘test, test, test’ is a must when deciding on changes to your database. Unfortunately, in 11.2.0.4, using what should be the ‘easy way out’ when testing how an index changes execution plans may adversely impact database performance since the plans with the invisible index are likely to differ from those generated when the index doesn’t exist.
