| 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: concatenated index
> Interestingly, the 'Novice DBA' mistake seems to be derived from a more
> grounded (because I _believe_ that at one point in the past it has been
> correct) urban legend, which is that the order of columns in an index
> matters a lot, the most significant columns having to come first.
This statement is true only if the word "significant" means "mentioned in WHERE clauses of more SQL statements." The best way to determine column order for composite index creation has long included the following guidelines:
Data selectivity is not a significant factor in determining column location within a composite index. This is true at least as long ago as Oracle version 6.
Why? Simple example. Consider a common everyday telephone directory. What's the most selective column? Last name, right? Does it depends on the country you live in (ever count the Wongs in Hong Kong)? No, and no. The most selective column is the telephone number column (almost unique). But a common telephone directory indexed (i.e., sorted) by telephone number would be almost completely worthless. To fulfill the common query you use with it (below), you'd have to full-scan it every time:
SELECT PHONE_NUMBER
FROM DIRECTORY
WHERE LAST_NAME=:v1
AND FIRST_NAME=:v2
AND ADDRESS LIKE :v3
You don't want selective columns at the head of the index. You want frequently specified query input columns at the head.
If you want further proof, take out your printed phone book, and find the phone number of a girl named Ursula.
> This is today totally false; in fact, if indexes are compressed, there may
> be some justification for doing the reverse and getting smaller indexes.
> Because of the point mentioned below, the only criterion should be 'what
> condition am I more likely to provide ?'. If A is always provided, B
> rarely and C sometimes, the order (assuming that A is still significant
> enough to justify using the index at all) should be A, C, B.
Yes.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details...
-----Original Message-----
Stephane Faroult
Sent: Tuesday, July 08, 2003 12:39 AM
To: Multiple recipients of list ORACLE-L
Mark,
You are wrong about the RBO. It takes conditions in the order it finds them in the WHERE clause, but it has always been more subtle than that - there is some weighting of conditions (column = constant better than column = other_column, unique_index_column = other_column better than non_unique_index_column = other_column, etc.) and the order only matters when all other things are equal. Regarding indexes more specifically, the RBO will favour an index for which a higher proportion of columns are referenced in the where clause, whatever the order of these columns.
Interestingly, the 'Novice DBA' mistake seems to be derived from a more grounded (because I _believe_ that at one point in the past it has been correct) urban legend, which is that the order of columns in an index matters a lot, the most significant columns having to come first. This is today totally false; in fact, if indexes are compressed, there may be some justification for doing the reverse and getting smaller indexes. Because of the point mentioned below, the only criterion should be 'what condition am I more likely to provide ?'. If A is always provided, B rarely and C sometimes, the order (assuming that A is still significant enough to justify using the index at all) should be A, C, B.
Note also that now the CBO can use an index even if the leading column(s) are not referenced in the WHERE clause - it may find more cost-effective to do a full index scan or 'skip scan' than a full table scan.
HTH S Faroult
Mark Richard wrote:
>
> The order of the where clause is not important. Including the leading
> (first) columns in the index is. If you remove the "a = ?" element from
> any of the queries then it may stop using the index. Oracle is smart
> enough to look at the entire where clause and work out what it can do to
> achieve the result quickest.
>
> You may be thinking on the rule based optimisor where the ordering of
where
> clauses is significant - for cost based optimisor the order is essentially
> irrelevant.
>
> Regards,
> Mark.
>
>
> "Novice DBA"
> <[EMAIL PROTECTED] To: Multiple
recipients of list ORACLE-L <[EMAIL PROTECTED]>
> l.com> cc:
> Sent by: Subject: concatenated
index
> [EMAIL PROTECTED]
> .com
>
>
> 08/07/2003 13:44
> Please respond to
> ORACLE-L
>
>
>
> Dear all,
> I have a basic doubt. I grew up (in Oracle) believing that for the
> concatenated indexes to be used by a query the ordering of the columns in
> the where clause was very important. But now I have doubts
> I have a table test and a composite index on it.
>
> CREATE TABLE TEST (
> A NUMBER,
> B NUMBER,
> C NUMBER,
> D VARCHAR2 (30) ) ;
>
> CREATE INDEX TEST_COMPOSITE ON
> TEST(A, B, C)
> ;
>
> I inserted some test data into it(376833 rows)
>
> Then tried some queries with explain plan. There is only one row which
> matches this criteria.
>
> SQL> explain plan for
> 2 select * from test
> 3 where a= 112
> 4 and b=113
> 5 and c=114;
>
> Explained.
>
> SQL> @plan
>
> Plan Table
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: Cary Millsap 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).Received on Tue Jul 08 2003 - 22:05:57 CDT
 |
 |