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

<<  September 2014  >>
MoTuWeThFrSaSu
25262728293031
1234567
891011121314
15161718192021
22232425262728
293012345

View posts in large calendar

TextBox