RE: HELP! Index Debate!

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 31 Jul 2003 14:09:29 -0800
Message-ID: <F001.005C80B7.20030731140929@fatcity.com>

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 of index 

since c2 is not in a predicate, here an index on c1, c3, c2, ... would be better, then both c1= and c3= can be used.

c2 = ... and ...                index can not be used (not until Oracle 9); 
but index on c2, ... could be used
c1 = ... and c2 = ... and c3 > ... and c4 =     index can be used, but only 

the c1=, c2= and c3 > portion since the inequality breaks the chain; an index on c1, c2, c4, c3 (or c2, c1, c4, c3 or c4, c1, c2, c3 etc ) could 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).

Received on Thu Jul 31 2003 - 17:09:29 CDT