Introduction to Fake / Virtual / NOSEGMENT Indexes January 11, 2008
Posted by Richard Foote in Fake Indexes, Index Access Path, NOSEGMENT Option, Oracle Cost Based Optimizer, Oracle Indexes, Virtual Indexes.trackback
OK, as promised, answer to index fact #5 you may not have known:
“It’s possible to make the CBO reference and use within an execution plan indexes that don’t in actual fact exist”.
Before I start, please note this feature is not officially documented other than the odd Metalink note and requires the setting of an undocumented parameter to work, so please exercise caution.
Fake Indexes (also known as Virtual or Nosegment Indexes) have been around for a long time, since 8i days. They’re used primarily by Oracle Enterprise Manager and its Tuning Pack which has various wizards that can do “what if” type analysis. One of these is the Index Wizard which can kinda “pretend” to create an index and see what the Cost Based Optimizer might do if such an index really existed.
It’s possible to create these Fake indexes manually by using the NOSEGMENT clause when creating an index:
CREATE INDEX Bowie_idx ON Bowie_Table(Ziggy) NOSEGMENT;
This will populate some (but not many) DD related tables but will not actually create an index segment or consume any actual storage. It’s not maintained in any way by DML operations on the parent table and it can’t be altered or rebuilt as can a conventional, “real” index (it will generate an ORA-08114 error if you try to do so). You can analyze or run dbms_stats over the index but the index is not treated as analyzed as such (as can be seen via a 10053 trace).
It’s also only visible to the CBO, if and only if a session has the following parameter set:
ALTER SESSION SET “_use_nosegment_indexes” = true;
The CBO will now consider the index and potentially include it within an execution plan. However, at execution time Oracle can of course not use the index and will revert to the next best thing.
A Fake index is basically an index you have when you don’t really have an index in order to see if it could be useful if it really existed.
This Fake Indexes Demo shows how they work and can be used.
1 down, 7 to go … 😉
Richard Foote's Oracle Blog 
Why did you collect statistics if the INDEX won’t be affected by them?
—
Wow, that is an actual error
ORA-08114 can not alter a fake index
Cause: An attempt was made to alter a fake index.
Action: Drop the fake index.
The Action seems a bit off though.
LikeLike
Hi Brian
Basically to collect statistics for the table and to show that using cascade won’t hurt even if you have a fake index.
Yes, it’s an actual error. However, thinking about it, perhaps for a fake index it should display a fake error message, something like “Hey, I have you all worked out, you good for nothing index, you’re nothing but a FAKE !!”.
Hummm, might raise a SR and see what Oracle can do …
LikeLike
But how can a nosegment index without statistics associated be reliably used to check whether or not the “real” index is going to be used (chosen) by the CBO – so aren’t nosegment indexes just a legacy from the RBO days, where the rule was simply “if the index can be used – use it” ?
At most, one can check whether the index is a candidate for access by forcing the index via an hint and check that the index is indeed referenced in the plan … if I have understood correctly you description, of course.
LikeLike
Hi Alberto
Exactly.
However, the stats it uses are those that Oracle derives on the fly from the table and column stats when an index has no statistics so it can often be in the correct ballpark if data is not too skewed. However if you look at a 10053 trace from my demo and look at the stats the fake index has vs. what the stats are for real, they do differ quite considerably (eg. fake lf_blks is 25, real is 7).
So you’re right in that it only shows what “might” happen if they get created for real assuming one would want to collect statistics ordinarily.
But it’s not dumb. For example, creating a fake index on the DROPPED column in my demo (which in my demo only contains NO) is NOT used by the CBO with a DROPPED = ‘NO’ query.
Note btw, fake indexes only work with the CBO, they’re ignored with the RBO.
I don’t find fake indexes particularly useful. If I want to see what would happen, I’ll create an index for real in our QA (Prod Copy) environments.
However if you don’t have a Prod Copy environment or the index that you want to test out is particularly large and resource hungry to create, it’s a quick method to see what “might” happen, without using too many resources.
LikeLike
Could it be that the statistics for nosegment indexes are the same assumed (defaulted) for regular, non-analyzed indexes ?
LikeLike
Hi Alberto
When I said
“However, the stats it uses are those that Oracle derives on the fly from the table and column stats when an index has no statistics”
I was trying to say
“statistics for nosegment indexes are the same assumed (defaulted) for regular, non-analyzed indexes” 😉
LikeLike
Ok, thanks 🙂
LikeLike
Hi Richard,
If you check the consistent gets b/n fake and regular indexes,it is 157 CR in case of fake index and 2 CRs incase of regular index
Thanks
LikeLike
Hi Anthony
That’s correct. It’s 2 CRs when it actually uses the index and 157 when it looks like the CBO is using the fake index as it’s really performing a FTS behind the scenes.
LikeLike
[…] has occurred to me that if you have a “nosegment” index that you’ve been using to test whether or not the optimizer would use it IF you created it, […]
LikeLike