| 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
Thanks Stephane. Is there some place (some article) other than the Oracle
Manual which deals with this(latest) features on the composite index
Thanks and Regards
Novice
No more Oracle Certifiable DBA
From: Stephane Faroult <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: Re: concatenated index
Date: Mon, 07 Jul 2003 21:39:25 -0800
MIME-Version: 1.0
Received: from ns3.fatcity.com ([66.27.56.210]) by mc8-f10.law1.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Mon, 7 Jul 2003 21:51:34 -0700 Received: from ns3.fatcity.com (localhost.localdomain [127.0.0.1])byns3.fatcity.com (8.12.5/8.12.5) with ESMTP id h684e8eA008979for <[EMAIL PROTECTED]>; Mon, 7 Jul 2003 21:40:08 -0700 Received: (from [EMAIL PROTECTED])by ns3.fatcity.com (8.12.5/8.12.5/Submit) id h684e8V7008977for [EMAIL PROTECTED]; Mon, 7 Jul 2003 21:40:08 -0700 Received: by fatcity.com (05-Jun-2003/v1.0g-b73/bab) via fatcity.com id 005C3BD6; Mon, 07 Jul 2003 21:39:25 -0800 X-Message-Info: JGTYoYF78jEHjJx36Oi8+Q1OJDRSDidP Message-ID: <[EMAIL PROTECTED]>
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). _________________________________________________________________ Reconnect with old pals. Relive the happy times. http://www.batchmates.com/msn.asp With just one click. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Novice DBA 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 - 01:36:15 CDT
 |
 |