Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: HELP! Index Debate!
Thank you very much for the detailed info!
Ron
-----Original Message-----
Sent: Thursday, July 31, 2003 5:09 PM
To: Multiple recipients of list ORACLE-L
Provided we are talking cost based optimizer, then the order of the
predicates in the where clause does not matter - except under very rare
conditions to break a tie. Nor is the order of the predicates in the
where
clause related to the order of the columns in the index. The only thing
that matters is which columns of the index are present in the where
clause
and what is their relative position in the index. It's probably best
explained with an example:
index on c1, c2, c3, c4, ...
where clause:
c1 = ... and c2 = ... index can be used c2 = ... and c1 = ... index can be used c1 = ... and c3 = ... index can be used, but only c1 = portion ofindex
c2 = ... and ... index can not be used (not until Oracle9);
use all predicates on the index.
as long as the leading columns are present in the where clause with an
equal predicate, the index can be used. The first omission or non-equal
predicate breaks the chain and only the part of the index up to that
column
can be used.
Oracle is built around composite (or compound) indexes. Except for
bitmap
indexes it does not easily use more than one index for the same table
access. The optimizer is slowly learning to use more than one index, but
it's still rare.
And yes, you can determine how many and which predicates are used for
the
index access, but you need to run a CBO trace to find out.
At 12:34 PM 7/31/2003 -0800, you wrote:
>Please help resolve this dispute.
>We have a query that runs over 5 hours. Sections of the query are
>listed below.
>
>The table QOH_DAY_FACT table had only on index and that was on the
>TIME_ID column. I propose adding an index with PROD_ID, WHS_ID,
>LOT_ID, WHS_LOC, TIME_ID and QUALITY_ID. My test shows the runtime was
>reduced to about 1.5 hours.
>
>The developer said all the columns in the index except PROD_ID were
>being ignored. He says there should be six separate indexes, one for
>each column before Oracle will use them.
>
>Also, does the order of the columns in the index have to match the
>order of the columns in the WHERE CLAUS or is it more important to
>match the WHERE CLAUS to the data content (least number of rows first)?
>
>I will be running more tests, but I would like some input on this if
>anyone has an opinion.
>
>Thanks!
>Ron
>
>
>select T1."COUNTRY_NAME" "c1"
> , T2."PRODTN_PROC_NAME" "c2"
> , T2."PLANT_NAME" "c3"
> , T1."WHS_NAME" "c4"
> , T1."WHS_CMPLX_NAME" "c5"
> , T3."WHS_LOC_NAME" "c6"
> , T4."GRADE_DESC" "c7"
> , T4."PACK_DESC" "c8"
> , T5."FULL_DT" "c9"
> , T6."QOH_MT" "c10".......
>
>....from "DWMART"."DISTRIB_FCLTY_DIM" T1
> , "DWMART"."MFG_FCLTY_DIM" T2
> , "DWMART"."DISTRIB_LOC_DIM" T3
> , "DWMART"."TIME_DIM" T5
> , "DWMART"."QUALITY_DIM" T7
> , "DWMART"."QOH_DAY_FACT" T6
> , "DWMART"."PROD_DIM" T4
>where T6."PROD_ID"=T4."PROD_ID"(+)
> and T6."WHS_ID"=T1."WHS_ID"
> and T6."LOT_ID"=T2."LOT_ID"
> and T6."WHS_LOC_ID"=T3."WHS_LOC_ID"
> and T6."TIME_ID"=T5."TIME_ID"
> and T6."QUALITY_ID"=T7."QUALITY_ID"
>order by "c9" asc
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Smith, Ron L.
> INET: rlsmith_at_kmg.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).
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: breitliw_at_centrexcc.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: Smith, Ron L. INET: rlsmith_at_kmg.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 Jul 31 2003 - 17:19:24 CDT