Hi Vivek,
In my discussion I was referring to a concatenated index as in multi
columns, not concatenated as in one column with 2 concatenated values,
although I admit the use of || didn't help.
Sorry for the confusion ;(
Richard
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, May 29, 2003 3:59 PM
> Hi Richard , List
>
> Your E-mail "order date or by order date and order id (and not
> necessarily by order id only), then you may find a single index order date
> || order id would meet all your requirements."
>
> Is there any advantage having the index defined as ( order date || order
id )
> over ( order date , order id ) ?
>
> SAMPLE TEST :-
>
> SQL> desc tmp1
> Name Null? Type
> ----------------------------------------- -------- ----------------------
------
> TRAN_DATE DATE
> TRAN_ID VARCHAR2(10)
>
> Index defined as ( TRAN_DATE || TRAN_ID ) is NOT used as shown in the
following query :-
>
> SQL> select * from tmp1 where tran_date=('01-01-2003');
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=RULE
> 1 0 TABLE ACCESS (FULL) OF 'TMP1'
>
>
> SQL> select * from tmp1 where tran_date=('01-01-2003') and tran_id='D1';
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=RULE
> 1 0 TABLE ACCESS (FULL) OF 'TMP1'
>
> Thanks
>
>
> -----Original Message-----
> Sent: Wednesday, May 28, 2003 7:50 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi Rachel,
>
> Correct, "Skip Scan Index" is not a type of index but a method whereby
> Oracle can eliminate the need to visit leaf nodes by determining whether
the
> leading column(s) have changed by sussing out only the branch nodes. It's
> possibly useful in situations where previously Oracle would not consider a
> concatenated index if the leading column of the index is unknown whereas
now
> the optimizer might determine that sufficient leaf nodes can be avoided
for
> the index to be of benefit. It's a kinda improved version of the full
index
> scan (or not so full if you know what I mean),
>
> However this requires the leading column to have *low* cardinality, low
> enough for the same repeated column from one leaf node to extent across
all
> values of it's neighbouring leaf node. If the leading column changes from
> one leaf node to the next, then that leaf node must be at least visited
> (although subsequent inspection of the index values may enable Oracle to
> "pull out early" from having to read all index values, if a subsequent
> change in the leading column rules out all remaining entries).
>
> A quick (and nasty) formula would be to consider the ratio of leaf nodes
to
> distinct values (LN/DV). The higher the ratio the better with any value
> somewhat greater than 1 giving a skip scan index path a chance with the
> number representing an approximate number of leaf nodes that could be
> "skipped" per leading index value. This obviously assumes evenish
> distribution of leading column(s) index values.
>
> However, getting back to your actual situation, if table access is only to
> be made via the order date or by order date and order id (and not
> necessarily by order id only), then you may find a single index order date
> || order id would meet all your requirements.
>
> Cheers
>
> Richard Foote
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Wednesday, May 28, 2003 8:59 PM
>
>
> > 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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: Richard Foote
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: VIVEK_SHARMA
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: Richard Foote
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
