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_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
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_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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Sep 29 2006 - 09:28:03 CDT