Re: Shadow Indexes

Ah, I'm not sure I ever knew about that bit.

Thanks all for the replies.

Jared

On 9/29/06, Pande, Rajendra <rajendra.pande_at_ubs.com> wrote:
>
> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Bobak, Mark
> *Sent:* Friday, September 29, 2006 10:28 AM
> *To:* kennaim_at_gmail.com; jkstill_at_gmail.com; 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_at_gmail.com]
> *Sent:* Thursday, September 28, 2006 7:36 PM
> *To:* Bobak, Mark; jkstill_at_gmail.com; '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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Bobak, Mark
> *Sent:* Thursday, September 28, 2006 5:22 PM
> *To:* jkstill_at_gmail.com; 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *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.*
>
>

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

--
http://www.freelists.org/webpage/oracle-l