Rachel,
For skip scan to work you'll need statistics, namely CBO, which if that's the case, create the one index if needed due to a unique constraint and forget about it. Chances are that the CBO will decide on a FTS anyway.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
Sent: Wednesday, May 28, 2003 10:15 AM
To: Multiple recipients of list ORACLE-L
Got it.... this is going to buy me exactly nothing given my app and
database design and database size......
- Mladen Gogala <mgogala_at_adelphia.net> wrote:
> I tried it and what it does is, essentially, a fast full index scan
> on the
> remaining columns of the index. To resolve the query, oracle does a
> full
> sequential scan on the index instead on the table. If your index is
> one third
> size of the table, you saved quite a few IOs but don't expect
> anything like
> search on unique key performance.
>
> On 2003.05.28 07:54 Mark Leith wrote:
> > Rachel,
> >
> >
>
http://technet.oracle.com/oramag/webcolumns/2003/techarticles/schumacher_ski
> > pscan.html
> > http://www.oracle-base.com/Articles/9i/IndexSkipScanning.asp
> >
> > I don't have any personal experience with them myself :( The first
> link
> > gives a pretty good overview though..
> >
> > Mark
> >
> > -----Original Message-----
> > Carmichael
> > Sent: 28 May 2003 12:00
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Okay, I have a developer here who has been reading the docs (this
> can
> > be dangerous!)
> >
> > we are adding functionality to one of our applications, this will
> > involve using multiple fulfillment houses, so we'll be adding the
> > fulfillment vendor id to the order table. Easy, this is not a
> problem.
> > We want to be able to search by order date and by fulfillment
> vendor
> > id/order date
> >
> > Traditional design would be to add two indexes: one on order date,
> and
> > a concatenated one on fulfillment vendor id/order date.
> >
> > The developer is telling me to create a "skip scan index" instead
> of
> > two different ones. MY reading in the FM tells me that skip scan
> index
> > is not a type of index, but rather a way Oracle uses to use an
> index
> > even if the leftmost column is not in the query.
> >
> > Is there any benefit in my building only the one index? Our order
> > volume is not so high (and never will be) that there is a visible
> > performance impact if I have the two indices.
> >
> > This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near
> future.
> > Solaris
> >
> > Any suggestions/comments/war stories would be appreciated. I know
> I've
> > seen Jonathan post on skip scan indexes before but I can't find the
> > specific reference at the moment.
> >
> > Rachel
> >
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> > http://calendar.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Rachel Carmichael
> > INET: wisernet100_at_yahoo.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting
> services
> >
> ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like
> subscribing).
> >
> > ---
> > Incoming mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Mark Leith
> > INET: mark_at_cool-tools.co.uk
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting
> services
> >
> ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like
> subscribing).
> >
>
> --
> Mladen Gogala
> Oracle DBA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
> INET: mgogala_at_adelphia.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Goulet, Dick
INET: DGoulet_at_vicr.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed May 28 2003 - 10:09:54 CDT
