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.
Home »
11g New Features
» Schema Management – Virtual Indexes - Invisible indexes
Schema Management – Virtual Indexes - Invisible indexes
Written By askMLabs on Tuesday, August 25, 2009 | 8:46 AM
Related Articles By Category
Labels:
11g New Features
Posts
Post a Comment
Thank you for visiting our site and leaving your valuable comment.