OCP Blog
Super DBA
Invisible index in oracle 11g, parameter OPTIMIZER_USE_INVISIBLE_INDEXES
by Vazha Mantua Thursday, December 29, 2011 12:37 PM

Good Day All, Today we will discuss about very interesting feature which is added in oracle 11g.

This is invisible indexes. Invisible indexes like any other indexes, but they ignored by the optimizer unless explicitly specified.
If the value of parameter optimizer_use_invisible_indexes is true on session level,
for this session optimizer ignored status of visibility index and use it in SQL_PLAN.

Let See Example:


First of all we create table:

create table NY_TABLE (person varchar2(50), department varchar2(20),salary number )
--------

Than we insert into table 1 row.

Insert into NY_TABLE values ('JONH SMITH','IT', 20000)
Commit;

--------
After this we insert 10000 test rows:

BEGIN
FOR i IN 1.. 10000 LOOP
  INSERT INTO NY_TABLE VALUES ('TEST','TEST',1);
END LOOP;
COMMIT;
END;
--------
Now we are gathering table statistic:

BEGIN
dbms_stats.gather_table_stats(SCOTT,'NY_TABLE',cascade => true,degree => 4);
END;

--------
We create index with invisible clause with command

create index NY_TABLE_I1 on NY_TABLE(DEPARTMENT) invisible

--------
Let see SQL_PLAN:

select * from NY_TABLE where department='IT'

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| NY_TABLE | 1 | 3 | 6 (0)| 00:00:01 |

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

As you see optimizer ignored index. Now we change status of index from invisible to visible

alter index NY_TABLE_I1 visible;
select * from NY_TABLE where department='IT'

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| NY_TABLE_I1 | 1 | 3 | 2 (0)| 00:00:01 |

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


And we see, that in this case index is used in SQL_PLAN
Let see example when OPTIMIZER_USE_INVISIBLE_INDEXES is set true.
---------

alter index NY_TABLE_I1 invisible;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
select * from NY_TABLE where department='IT'

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| NY_TABLE_I1 | 1 | 3 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------

In result we see that index is invisible, but it used by optimizer, reason of this is parameter SET OPTIMIZER_USE_INVISIBLE_INDEXES.

---------
Status of visibility of index you can find with command :select visibility from dba_indexes

Tags: , , , ,

Filter by APML

Calendar

<<  October 2014  >>
MoTuWeThFrSaSu
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789

View posts in large calendar

TextBox