> 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:
1. Put columns that are referenced in more statements' WHERE clauses at the
head, not the tail.
2. Put columns that are more likely to be range-scanned at the tail, not the
head.
3. If you can create an index with the columns in the same order as
frequently executed ORDER BY clauses, then do it; it obviates sorting.
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
>
----------------------------------------------------------------------------
----
>
> | Operation | Name | Rows | Bytes| Cost | Pstart|
> Pstop |
>
----------------------------------------------------------------------------
----
>
> | SELECT STATEMENT | | 5K| 74K| 88 | |
>
> |
> | TABLE ACCESS BY INDEX ROW|TEST | 5K| 74K| 88 | |
>
> |
> | INDEX RANGE SCAN |TEST_COMP | 5K| | 39 | |
>
> |
>
----------------------------------------------------------------------------
----
>
> 6 rows selected.
>
> SQL> explain plan for
> 2 select * from test
> 3 where a= 112
> 4 and c=114
> 5 and b=113;
>
> Explained.
>
> SQL> @plan
>
> Plan Table
>
----------------------------------------------------------------------------
----
>
> | Operation | Name | Rows | Bytes| Cost | Pstart|
> Pstop |
>
----------------------------------------------------------------------------
----
>
> | SELECT STATEMENT | | 5K| 74K| 88 | |
>
> |
> | TABLE ACCESS BY INDEX ROW|TEST | 5K| 74K| 88 | |
>
> |
> | INDEX RANGE SCAN |TEST_COMP | 5K| | 39 | |
>
> |
>
----------------------------------------------------------------------------
----
>
> 6 rows selected.
>
> SQL> explain plan for
> 2 select * from test
> 3 where b=113
> 4 and a= 112
> 5 and c=114;
>
> Explained.
>
> SQL> @plan
>
> Plan Table
>
----------------------------------------------------------------------------
----
>
> | Operation | Name | Rows | Bytes| Cost | Pstart|
> Pstop |
>
----------------------------------------------------------------------------
----
>
> | SELECT STATEMENT | | 5K| 74K| 88 | |
>
> |
> | TABLE ACCESS BY INDEX ROW|TEST | 5K| 74K| 88 | |
>
> |
> | INDEX RANGE SCAN |TEST_COMP | 5K| | 39 | |
>
> |
>
----------------------------------------------------------------------------
----
>
> 6 rows selected.
>
> SQL> explain plan for
> 2 select * from test
> 3 where b=113
> 4 and c=114
> 5 and a= 112;
>
> Explained.
>
> SQL> @plan
>
> Plan Table
>
----------------------------------------------------------------------------
----
>
> | Operation | Name | Rows | Bytes| Cost | Pstart|
> Pstop |
>
----------------------------------------------------------------------------
----
>
> | SELECT STATEMENT | | 5K| 74K| 88 | |
>
> |
> | TABLE ACCESS BY INDEX ROW|TEST | 5K| 74K| 88 | |
>
> |
> | INDEX RANGE SCAN |TEST_COMP | 5K| | 39 | |
>
> |
>
----------------------------------------------------------------------------
----
>
> 6 rows selected.
>
> SQL> explain plan for
> 2 select * from test
> 3 where b=113
> 4 and c=114;
>
> Explained.
>
> SQL> @plan
>
> Plan Table
>
----------------------------------------------------------------------------
----
>
> | Operation | Name | Rows | Bytes| Cost | Pstart|
> Pstop |
>
----------------------------------------------------------------------------
----
>
> | SELECT STATEMENT | | 23K| 299K| 158 | |
>
> |
> | TABLE ACCESS FULL |TEST | 23K| 299K| 158 | |
>
> |
>
----------------------------------------------------------------------------
----
>
> Now I am at loss. I know this is something very basic. But I am unable to
> understand why the index is being used even when the order of the columns
> in
> the where clause is changed.
>
> Oracle version 8.1.7.2.
>
> Please enlighten me
>
> Thanks in advance
>
> Novice
> No more Oracle Certifiable DBA
>
--
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).
