Re: Help !! Index creation

raj_at_cs.clemson.edu (Raj Rangarjan) wrote:
>Hi,
> I had two question regarding index creation in oracle and was
>hoping someone could answer them:
> 1. Does it matter what order the indexes are created , if two
> or more fields in a table are indexed?

Yes. (See below)

> 2. Does the order of the fields in the table(ie the order they
> are specified when the table is created) matter when indexes
> are created on this field?

No.

>Any help would be appreciated . Please post your replies here or send
>them to raj_at_cs.clemson.edu
>
>Thanks
>
>--
>*******************************************
>-Rajasekaran Rangarajan
>-Administrative Progamming Services
>-Clemson University
>-Ph:(864) 656 0731/ Fax:(864) 656-0122
>

Oracle uses the "leading edge" (the first x columns ) of "composite" indexes (indexes with more than one column).

E.g. CREATE INDEX JUNK_COMPOSITE_INDEX

     ON JUNK (COL1, COL2, COL3, COL4)....
     /

Oracle would use index JUNK_COMPOSITE_INDEX for WHERE clauses

SELECT ...
WHERE COL1 = 'somthing' AND COL2 = 'somethingelse' AND COL3 = 'more' ...

SELECT ...
WHERE COL1 = 'somthing' AND COL2 = 'somethingelse' ...

SELECT ...
WHERE COL1 = 'somthing' ...

All 3 of these are "leading edges"

Oracle will NOT use JUNK_COMPOSITE_INDEX for WHERE clauses

SELECT ...
WHERE COL2 = 'somethingelse' AND COL3 = 'more' ...

SELECT ...
WHERE COL4 = 'more' ...

SELECT ...
WHERE COL2 = 'somethingelse' AND COL4 = 'more' ...

HTH DL Kray Received on Fri Mar 22 1996 - 00:00:00 CET