You will also need to have the statement below for the explain plan to be
able to "see" the index
ALTER SESSION SET "_use_nosegment_indexes" = TRUE;
------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Bobak, Mark
*Sent:* Friday, September 29, 2006 10:28 AM
*To:* kennaim@xxxxxxxxx; jkstill@xxxxxxxxx; Oracle-L Freelists
*Subject:* 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*
*P**roQuest 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*
*P**roQuest 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
*Please do not transmit orders or instructions regarding a UBS account by
e-mail. The information provided in this e-mail or any attachments is not an
official transaction confirmation or account statement. For your protection,
do not include account numbers, Social Security numbers, credit card
numbers, passwords or other non-public information in your e-mail. Because
the information contained in this message may be privileged, confidential,
proprietary or otherwise protected from disclosure, please notify us
immediately by replying to this message and deleting it from your computer
if you have received this communication in error. Thank you.*
*UBS Financial Services Inc.*
*UBS International Inc.*
