 |
|
The _use_nosegment_indexes
parameter and nosegment keyword
Oracle Tips by Burleson Consulting |
Oracle has a special hidden "nosegment" clause to the create index
syntax that allows you to create an index definition without actually
creating the index.A virtual index is a "fake" index
whose definition exists in the data dictionary, but has no index tress
association. It is used by Oracle developers to test whether a
specific index is going to use useful without having to use the disk
space associated with the "real" index. The hidden
parameter _use_nosegment_indexes is used by Quest tools and is also use
in the Oracle Tuning pack.
Creating a virtual index is easy:
create index
virtual_idx
on
emp(emp_id)
nosegment;
If you own the extra cost Oracle
Tuning Pack, you will note the "virtual index wizard" area.
According to Oracle, virtual indexes will help you determine how the
Oracle cost-based SQL optimizer (CBO) will evaluate and use the
potential index.
Because the virtual index is a fake
index, Oracle will never be able to use it, but you can use the hidden
_use_nosegment_indexes parameter to evaluate execution plans
for virtual indexes:
set autotrace on explain;
alter session set
"_use_nosegment_indexes" = true;
Session altered.
SQL> select ename from emp where ename = 'KING';
ENAME
-----
KING
1 rows selected.
OBJECT OPERATION
---------- ----------------------------------------
SELECT STATEMENT()
NESTED LOOPS()
EMP TABLE ACCESS(FULL)
EMP TABLE ACCESS(BY INDEX ROWID)
VIRTUAL_INDEX INDEX(UNIQUE SCAN)
I cannot really see a use for this
undocumented Oracle features except in cases where you wanted to
evaluate how the CBO will change execution plans on very large tables.
