RE: skip scan index

Rachel,

   You are right about 'skip scan' being a way to scan the index rather than something else. In fact, it's an improvement on a full index scan.    IMHO, since you say that the volume is not that big, there is no such thing as giving it a try, and possibly comparing it to a full scan. An index scan (skip or not skip) makes sense if the index is much smaller than the table. If there is no enormous difference, and if your query requires other columns than the ones in the index, the cost of scanning the index plus fetching data blocks (especially if the clustering factor is low) may well be higher that the cost of scanning the table in the first place - when I say 'cost', read 'elapsed time' more than any obscure CBO cooking recipe.    I would naturally tend to consider either two indices or none at all.

SF

>----- ------- Original Message ------- -----
>From: Rachel Carmichael <wisernet100_at_yahoo.com>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Wed, 28 May 2003 02:59:39
>
>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
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.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).