Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted July 25, 2016

Is Your Oracle Invisible Index REALLY Invisible?

By David Fitzjarrell

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.

See all articles by David Fitzjarrell



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM