Home » » Schema Management – Virtual Indexes - Invisible indexes

Schema Management – Virtual Indexes - Invisible indexes

Written By Srikrishna Murthy Annam on Tuesday, August 25, 2009 | 5:46 AM

Virtual Indexes - Invisible Indexes

Virtual Indexes :-
To simulate the existence of an index and test its impact without actually building actual index. This saves lot of time and most important I/O from and to Oracle resources (Disk, CPU, etc).

Once the Virtual Index is created, we can run an explain plan as if the index is present. Once we have decided to choose what indexes to create, we can proceed to creation of actual indexes.

Invisible Indexes :-
The invisible indexes are just opposite to virtual indexes. The index already exist on the table, but we have a doubt that it might be having a negative impact on few queries. It might be helping one query but hurting 10 others.

In earlier releases ( prior to 11g) we have two options
a) Drop the index and test the other queries for performance
b) Make the index unusable

-- If we drop it and then later we have to recreate it after testing has been done, this will result in unnecessary wastage of time and oracle resources.
-- If we make it UNUSABLE , it will result in all DML failing which is totally un expectable in 99% of the situation.

Then how do we go ....

Oracle 11g gives us "Invisible Indexes"

SQL> ALTER INDEX emp_idx INVISIBLE;
SQL> ALTER INDEX emp_idx VISIBLE;

Once the index is invisible it will not be used in any query unless explicitly mention as hint in the query, but will not have any impact on DML operations i.e DML operation will continue to update and delete from index as usual.Technically it will only become invisible to optimizer unless SQL statement explicitly specify the index with a hint.

So, to "what" exactly is this index invisible? Well, it's not invisible to the user. It's invisible to the optimizer only. Regular database operations such as inserts, updates, and deletes will continue to update the index. Be aware of that when you create invisible indexes; you will not see the performance gain due to the index while at the same time you may pay a price during DML operations.

NOTE : If we rebuild the index it will be visible again.

=====================================================================
DEMO               DEMO                DEMO              DEMO              DEMO                  DEMO                      DEMO
=====================================================================


Virtual Index Demo
=============
=
SQL> create table test ( id number ) ;

Table created.

SQL> BEGIN
FOR i IN 1 .. 10000 LOOP
INSERT INTO test VALUES (i);
END LOOP;
COMMIT;
END;

/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> select count(1) from test;

COUNT(1)
----------
10000

SQL> exec dbms_stats.GATHER_TABLE_STATS('SYS', 'TEST', CASCADE=> true);

PL/SQL procedure successfully completed.

SQL> explain plan for select * from test where id = 9999;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020

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

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=9999)

13 rows selected.
--- Full table scan, Because there are no indexes.
SQL> sho parameter _use_nosegment_indexes

-- It is invisible init parameter, So following query is used to check the value
SQL> col no format 99999
col name format a30
col typ format 999
col value format a25
col dflt format a5
col flg format 999999
set pagesize 2000
SELECT KSPFTCTXPN "no", KSPPINM "name", KSPPITY "typ", KSPFTCTXVL "value",
KSPFTCTXDF "dflt"
FROM X$KSPPI A, X$KSPPCV2 B
WHERE A.INDX + 1 = KSPFTCTXPN
and KSPPINM like '%&1%';SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5
Enter value for 1: use_nosegment_indexes
old   5: and KSPPINM like '%&1%'
new   5: and KSPPINM like '%use_nosegment_indexes%'

no name                            typ value                     dflt
---- ------------------------------ ---- ------------------------- -----
1391 _use_nosegment_indexes            1 FALSE                     TRUE

SQL> alter session set "_use_nosegment_indexes" = true;

Session altered.

SQL> col no format 99999
col name format a30
col typ format 999
col value format a25
col dflt format a5
col flg format 999999
set pagesize 2000
SELECT KSPFTCTXPN "no", KSPPINM "name", KSPPITY "typ", KSPFTCTXVL "value",
KSPFTCTXDF "dflt"
FROM X$KSPPI A, X$KSPPCV2 B
WHERE A.INDX + 1 = KSPFTCTXPN
and KSPPINM like '%&1%';SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5
Enter value for 1: use_nosegment_indexes
old   5: and KSPPINM like '%&1%'
new   5: and KSPPINM like '%use_nosegment_indexes%'

no name                            typ value                     dflt
------ ------------------------------ ---- ------------------------- -----
1391 _use_nosegment_indexes            1 TRUE                      TRUE

SQL> explain plan for select * from test where id = 9999;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020

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

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=9999)

13 rows selected.

SQL> create index test_ind on test(id) nosegment;

Index created.
-- Virtual Index created , key word used is nosegment.
SQL> select segment_name,segment_type from dba_segments where segment_name='TEST_IND';

no rows selected
--  No segments for the created virtual index.
SQL> explain plan for select * from test where id = 9999;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3357096749

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

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("ID"=9999)

13 rows selected.

-- The query now uses the index. If we have the expected performance gain , we can drop the virtual index and create the actual index.

Invisible Index Demo
===============


Case 1 :
optimizer_use_invisible_indexes=FALSE  and  index=INVISIBLE

Case 2 :
optimizer_use_invisible_indexes=FALSE  and  index=VISIBLE

Case 3 :
optimizer_use_invisible_indexes=TRUE  and  index=INVISIBLE

Case 4 :
optimizer_use_invisible_indexes=TRUE  and  index=VISIBLE

SQL> create table test (id  number);

Table created.

SQL> begin
for i in 1 .. 10000 loop
insert into test values (i);
end loop;
commit;
end;

/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> select count(1) from test;

COUNT(1)
----------
10000

SQL> create index test_ind on test(id) invisible;

Index created.

SQL> select index_name,visibility from user_indexes where index_name = 'TEST_IND';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
TEST_IND                       INVISIBLE

SQL> exec dbms_stats.gather_table_stats('SYS', 'TEST', cascade=> true);
BEGIN dbms_stats.gather_table_stats('SYS', 'TEST', cascade=> true); END;

*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 17806
ORA-06512: at "SYS.DBMS_STATS", line 17827
ORA-06512: at line 1

SQL> exec dbms_stats.gather_index_stats('SYS','TEST_IND');
BEGIN dbms_stats.gather_index_stats('SYS','TEST_IND'); END;

*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 14037
ORA-06512: at "SYS.DBMS_STATS", line 14060
ORA-06512: at line 1
--- It is an intended behaviour that we cant collect statistics for an invisible index. The only way to collect  the statistics for invisible indexes is by setting the parameter OPTIMIZER_USE_INVISIBLE_INDEXES.

SQL> explain plan for select * from test where id = 9999;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020

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

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter("ID"=9999)

13 rows selected.

CASE 1 :

SQL> sho parameter invisible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE
SQL> select index_name,visibility from user_indexes where index_name = 'TEST_IND';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
TEST_IND                       INVISIBLE

SQL> explain plan for select * from test where id = 9999;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020

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

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter("ID"=9999)

13 rows selected.
-- Full table scan. Optimizer will not use the index.
SQL> explain plan for select /*+ INDEX (test test_ind) */ * from test WHERE id = 9999;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020

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

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter("ID"=9999)

13 rows selected.
-- Optimizer should use index with hint. But it is not using it. I need to further test on it.

CASE 2

SQL> sho parameter invisible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE
SQL> alter index test_ind visible;

Index altered.

SQL> exec dbms_stats.gather_table_stats('SYS', 'TEST', cascade=> true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_index_stats('SYS','TEST_IND');

PL/SQL procedure successfully completed.

-- We are able to gather statistics , because the index is now visible.
SQL> explain plan for select * from test where id = 9999;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3357096749

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

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - access("ID"=9999)

13 rows selected.
-- Optimizer is now using the index

CASE 3

SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;

Session altered.

SQL> sho parameter invisible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     TRUE
SQL> alter index test_ind invisible;

Index altered.

SQL> select index_name,visibility from user_indexes where index_name = 'TEST_IND';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
TEST_IND                       INVISIBLE

SQL> explain plan for select * from test where id = 9999;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3357096749

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

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - access("ID"=9999)

13 rows selected.
--- Optimizer used the invisible index by setting the init parameter TRUE

CASE 4

SQL> sho parameter invisible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     TRUE
SQL> alter index test_ind visible;

Index altered.

SQL> select index_name,visibility from user_indexes where index_name = 'TEST_IND';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
TEST_IND                       VISIBLE

SQL> explain plan for select * from test where id = 9999;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3357096749

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

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - access("ID"=9999)

13 rows selected.

-- Optimizer uses the invisible index.
Share this article :

Related Articles By Category



Post a Comment

Thank you for visiting our site and leaving your valuable comment.

 
Support :
Copyright © 2013. askMLabs - All Rights Reserved
Proudly powered by Blogger