| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: skip scan index
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
> Okay, I have a developer here who has been reading the docs (this can
> be dangerous!)
>
>
>
>
>
>
>
>> Do you Yahoo!?
> __________________________________
>> To REMOVE yourself from this mailing list, send an E-Mail message
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote INET: richard.foote_at_bigpond.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: VIVEK_SHARMA INET: VIVEK_SHARMA_at_infosys.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 Thu May 29 2003 - 00:59:40 CDT
 |
 |