Stephane,
The queries will almost always include data from the data blocks in
addition to the index information. The only query I can think of where
that would not be true would be a simple count of orders by date.
I'm beginning to think, based on this discussion and others we've had
here where it's been proven that a full table scan is more efficient
than an index lookup plus data block read, that perhaps NO index would
be best.
We are talking row counts in the (at most) 10's of thousands, not
millions. I wish it were millions of rows, that would mean the store
site was making money :)
Rachel
- Stephane Faroult <sfaroult_at_oriolecorp.com> wrote:
> 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).
>
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).
Received on Wed May 28 2003 - 08:29:42 CDT
