RE: Shadow Indexes

Ken,
 
"Will this change an explain plan as well?"
 
As well as...?
 
A nosegment index will (to my knowledge) *only* change an execution
plan.
 
The whole point of it is to be able to quickly do "what-if" scenarios.
 
Remember, nosegment literally means there is no segment.  There is no
index structure.  The optimizer sees the data dictionary entry for the
index, but doesn't realize the index isn't really there.  It allows you
to see how adding a specific index would impact the execution plan,
without having to take the time to actually create the index.
 
Hope that helps,
 
-Mark
 
-- 
Mark J. Bobak 
Senior Oracle Architect 
ProQuest Information & Learning 
For a successful technology, reality must take precedence over public
relations, for Nature cannot be fooled.  --Richard P. Feynman, 1918-1988
 

________________________________

From: Ken Naim [mailto:kennaim@xxxxxxxxx] 
Sent: Thursday, September 28, 2006 7:36 PM
To: Bobak, Mark; jkstill@xxxxxxxxx; 'Oracle-L Freelists'
Subject: RE: Shadow Indexes


Will this change an explain plan as well? I'd test it but don't have
access to oracle right now. 
 
________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Bobak, Mark
Sent: Thursday, September 28, 2006 5:22 PM
To: jkstill@xxxxxxxxx; Oracle-L Freelists
Subject: RE: Shadow Indexes
 
The magic phrase is 'NOSEGMENT'.
 
But, if you're on 9.2.0.x and x < 8, don't create a bitmap nosegment
index, or you won't be able to drop it. ;-)
 
(Just hit that bug, and applied the 9.2.0.8 patch yesterday to fix the
problem.)
 
-Mark
 
-- 
Mark J. Bobak 
Senior Oracle Architect 
ProQuest Information & Learning 
For a successful technology, reality must take precedence over public
relations, for Nature cannot be fooled.  --Richard P. Feynman, 1918-1988
 
 
________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jared Still
Sent: Thursday, September 28, 2006 6:20 PM
To: Oracle-L Freelists
Subject: Shadow Indexes

Does someone here recall the syntax for creating metadata only indexes?

This for use by the SQL tuning advisors, etc.

I can't recall the syntax, and cannot find it.

Thanks,

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

Other related posts: