RE: Shadow Indexes
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <kennaim@xxxxxxxxx>, <jkstill@xxxxxxxxx>, "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 29 Sep 2006 10:28:03 -0400
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:
