At my work got few questions about Virtual index from fellow
DBA, how about creating virtual index on a live production system and having
them tested against the production workloads.
Before getting into those response in detail, thought it
worth a blog post to help explain about virtual index.
It is possible to create Virtual index (aka fake index)
manually using NOSEGMENT clause, kind of “pretend” to see what the CBO might do
if such an index really exists.
Creating a virtual index doesn’t consume any storage and
doesn’t maintained any way during the DML operations on the table, and it can’t
be altered or rebuild like the real index. You can do stats gather (using
dbms_stats) or analyze validate structure but no details will be available in
user_indexes and index_stats data dictionary.
It is only visible to the CBO if and only if the session has
set this hidden parameter (_use_nosegment_indexes = true)
The CBO will now consider to cost the index and potentially
include them in the explain plan, however at runtime Oracle can of course not
use that index and revert to the next best thing.
Let’s start with a demo. With a table in which the column OBJECT_ID
values are distributed throughout the whole table.
demo@ORA12C> create table t( id int, object_id int, created
date );
Table created.
demo@ORA12C> insert into t (id, object_id, created)
2 select id, mod(id,100)+1 , created
3 from big_table;
1000000 rows created.
demo@ORA12C> commit;
Commit complete.
demo@ORA12C> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
demo@ORA12C>
We can now create a virtual index based on the OBJECT_ID
column.
demo@ORA12C> create index t_idx on t(object_id) NOSEGMENT;
Index created.
demo@ORA12C>
Creating a virtual index makes no entry in USER_INDEXES and
USER_SEGMENTS dictionary, but mark an entry into USER_OBJECTS.
demo@ORA12C> select * from user_segments where segment_name
='T_IDX';
no rows selected
demo@ORA12C> select * from user_indexes where index_name
='T_IDX';
no rows selected
demo@ORA12C> select object_name,status from user_objects
where object_name ='T_IDX';
OBJECT_NAME
STATUS
------------------------------ -------
T_IDX VALID
demo@ORA12C>
Now to verify if the optimizer make use of the virtual index, let’s do
an explain plan.
demo@ORA12C> alter session set
"_use_nosegment_indexes"=true;
Session altered.
demo@ORA12C> set autotrace traceonly explain
demo@ORA12C> select * from t where object_id = 42;
Execution Plan
----------------------------------------------------------
Plan hash value: 767293772
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 10000 | 156K|
9 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 10000 |
156K| 9 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX |
10000 | | 1
(0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("OBJECT_ID"=42)
demo@ORA12C> set autotrace off
demo@ORA12C> alter session set "_use_nosegment_indexes"=false;
Session altered.
demo@ORA12C>
Indeed the virtual index is costed and used in the explain plan. If we
now physically create this index for real then.
demo@ORA12C> drop index t_idx;
Index dropped.
demo@ORA12C> create index t_idx on t(object_id);
Index created.
demo@ORA12C> column index_name format a10
demo@ORA12C> select
i.index_name,i.clustering_factor,t.blocks,t.num_rows
2 from user_indexes i,
3 user_tables t
4 where t.table_name = i.table_name
5 and i.index_name ='T_IDX';
INDEX_NAME CLUSTERING_FACTOR
BLOCKS NUM_ROWS
---------- ----------------- ---------- ----------
T_IDX
288917 2890 1000000
demo@ORA12C>
demo@ORA12C> set autotrace traceonly explain
demo@ORA12C> select * from t where object_id = 42;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 10000 | 156K|
796 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL|
T | 10000 | 156K|
796 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter("OBJECT_ID"=42)
demo@ORA12C> set autotrace off
we see the CBO has decided to perform the now cheaper full table scan,
Although the Virtual Index on this column was used, once the actual
characteristics of the index are determined via the index statistics, the CBO
has decided the actual physical index was just too expensive to use to retrieve
the 1% of rows.
Virtual index is one among many heuristics built into the CBO. Virtual
index can be useful to quickly determine whether an index is a viable option if
it were to be actually created, however caution need to be exercised.
The big advantage of a Virtual Index of course is that it doesn’t
really exist and so consumes no storage and can be created extremely
quickly/cheaply.
The disadvantage of a Virtual index is that it doesn’t
really exist and so Oracle cannot collect segment level statistics. Without
statistics however, the CBO has a very tough time of doing its job properly …
So don’t use virtual index in live production system, since
it is an undocumented feature and also requires an undocumented parameter set
at the session level.
But it is safe to play with virtual index in Pre-prod/SIT/UAT/Test
database and see if the real index benefit the workload.
Always have the real index in the live production system but not the
virtual index.
