Invisible Indexes

Oracle 11g allows indexes to be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level. Indexes can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command.


 1. Create a table t1 with 2 columns n1 and n2


Hint : Create table t1(n1 number,n2 number);


SQL> show parameter visible


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

optimizer_use_invisible_indexes      boolean     FALSE

SQL> Create table t1(n1 number,n2 number);


Table created.


2. Populate Records


    Begin

          For i in 1..1000 loop

           Insert into t1 values(i,i);

          end loop;

   end;

/


SQL>   Begin

          For i in 1..1000 loop

           Insert into t1 values(i,i);

          end loop;

   end;

/  2    3    4    5    6


PL/SQL procedure successfully completed.


SQL>


3.Create a Invisible index on column n1


Hint :-


SQL> create index t1_n1 on t1(n1) invisible;


create index t1_n1 on t1(n1) invisible;


4

SQL> explain plan for select count(*) from t1 where n1=:b1;




SQL> select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3724264953


---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    13 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |

|*  2 |   TABLE ACCESS FULL| T1   |    10 |   130 |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


   2 - filter("N1"=TO_NUMBER(:B1))


Note

-----

   - dynamic sampling used for this statement (level=2)


18 rows selected.

If you see above it's not using index as we have give it to be in invisible mode.


5 alter index t1_n1 visible;


6 Define a bind variable b1


sql> variable b1 number


sql>begin

        :b1:=5;

     end;



7 SQL> explain plan for select count(*) from t1 where n1=:b1;


8 SQL> select * from table(dbms_xplan.display);

---------------------------------------------------------------------------

| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |       |      1|     5|      1   (0)| 00:00:01 |

|   1|   SORT AGGREGATE    |       |      1|     5|             |          |

|*  2|     INDEX RANGE SCAN| T1_N1 |      1|     5|      1   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

   2 - access("N1"=TO_NUMBER(:B1))


9 alter index t1_n1 visible;


10 SQL> explain plan for select count(*) from t1 where n1=:b1;



SQL> explain plan for select count(*) from t1 where n1=:b1;


Explained.


SQL> select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 73337487


---------------------------------------------------------------------------

| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |       |     1 |    13 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |       |     1 |    13 |            |          |

|*  2 |   INDEX RANGE SCAN| T1_N1 |    10 |   130 |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


   2 - access("N1"=TO_NUMBER(:B1))


Note

-----

   - dynamic sampling used for this statement (level=2)


18 rows selected.


SQL>


Benefits:Invisible indexes can be useful for processes with specific indexing needs, where the presence of the indexes may adversely affect other functional areas. They are also useful for testing the impact of dropping an index.


View:The current visibility status of an index is indicated by the VISIBILITY column of the [DBA|ALL|USER]_INDEXES views.


Bottleneck: Invisble index are just not visible to select statement, rather it will operate normally in case of DML's.


Comments

Post a Comment

Popular posts from this blog

How to Solve - "WAIT FOR EMON PROCESS NTFNS"

Event Monitor Process in short called as EMON is generally used by oracle to send notification. Often applications uses Database Change Notification process also called as DBCN to be aware of changes in the database, based on these notifications they take further actions. Often clients subscribe to the database and notifications are send as callback to them. In some cases you will notice database sessions are showing wait event "WAIT FOR EMON PROCESS NTFNS" and it can even make your database un-responsive. One observation can be these processes are taking more CPU (may not in be in all cases). As stated EMON process deals with database notification and it's spawn and gets down itself and Administrators have no control on theses processes. Anyone can go through oracle documents to read more EMON functioning process.  EMON uses oracle advanced queues and database queue notifications into them and EMON process these notifications to clients based on their subscriptions. In s
Read more

Query Regression - "OR" Transformation Oracle 19c

Recently while upgrading Oracle Database from version 12.1 to 19.10 found there were couple of queries which was running slow post upgrade. Usually these type of behavior is seen while upgrading to newer version and oracle provide various ways to fix them. The difference between good and bad plan was an introduction of  "MERGE JOIN CARTESIAN" (aka MJC) and that's in where regressed query in 19c taking most of the time. MJC sometimes works for queries but in this case it was clearly not helping.  We had following options here:     1. Copy the profile from 12c to 19c database and it worked, but the problem was not just to specific query but with many similar query. This means profile option is not very attractive in this case.     2. We also tried running query with OFE 12.1 to use the same optimizer features as previous version and query behaves well. Though this option can't be placed as we need to use 19c OFE and can be placed only for testing purpose.           alte
Read more

ORA - 12537: TNS: connection closed

Often when active clone to be done to create a new environment, we land up into couple of issues. I will discuss about one issues during this process. Action : Create a new database on different machine from a source. Error : ORA - 12537: TNS: connection closed In order to duplicate the environment was using below rman log /tmp/duplicate.log connect target sys/<password>@<source tns alias> connect auxiliary sys/<password>@<target tns alias> run{ allocate channel ch1 device type disk; allocate channel ch2 device type disk; allocate channel ch3 device type disk; allocate channel ch4 device type disk; allocate auxiliary channel ax1 device type disk; allocate auxiliary channel ax2 device type disk; allocate auxiliary channel ax3 device type disk; allocate auxiliary channel ax4 device type disk; DUPLICATE TARGET DATABASE TO <TARGET_DBNAME> FROM ACTIVE DATABASE NOFILENAMECHECK; } While making connection to Auxiliary database received mentioned error ORA - 12537:
Read more