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: , , , ,

Comments (7) -

1/1/2012 10:27:50 PM #

Saurabh

Nice demo on use of Invisible Indexes...

Saurabh India

1/7/2012 5:03:15 PM #

Orya Jan

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

Orya Jan United States

1/14/2012 2:27:36 PM #

Villas for Rent in Italy

I have already read this but this is great for me and I am surprised. Thanks for sharing again!

Villas for Rent in Italy United States

6/12/2012 9:33:36 AM #

3D Scanner

Oracle 11g allows indexes to be noticeable as unseen. Invisible indexes are maintained like any other index, but they are unnoticed by the optimizer except the OPTIMIZER_USE_INVISIBLE_INDEXES stricture is set to TRUE at the illustration or session level. Indexes can be shaped as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command.

3D Scanner United States

6/13/2012 7:52:32 AM #

Mcx Tips

The topics of your blog is very modern and interesting issues, I really like your blog.

Mcx Tips India

6/20/2012 2:34:27 PM #

Diminished Value

You lost me, buddy. I mean, I assume I get what youre indicating. I have an understanding of what you’re saying, but you just appear to have forgotten that you can find some other individuals within the world who see this matter for what it actually is and may perhaps not agree with you. You might be turning away a lot of persons who may have been lovers of your website.

Diminished Value United Kingdom

7/15/2012 3:14:05 PM #

Fiberglass Column

While the Oracle optimizer is not prone to select a sub-optimal plan when a new index is added, it's feasible, and when testing a new index for immigration to construction, total workload testing is de-rigueur.

Fiberglass Column United States

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Filter by APML

Calendar

<<  November 2017  >>
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

View posts in large calendar

TextBox