Re: Help !! Index creation

From: Gunnar Boee <gunnar_at_taskon.no>
Date: 1996/03/27
Message-ID: <315931FF.ABD322C_at_taskon.no>#1/1


deangup_at_admin.ci.seattle.wa.us wrote:
>
> > raj_at_cs.clemson.edu (Raj Rangarjan) writes:
> > 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?
> No, as I understand it. In DB2 the first index is the default clustering index,
> predisposing the DBMS to
> physically order rows per that index. But Oracle does not appear to care about physical
> row order as far
> as I've been able to tell.
> > 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, IMHP.
>
> - Paul de Anguera, City of Seattle HRIS
Please read ORACLE 7 Server Application Guide p 2-24. It says :

"Order Index Columns for Performance. The order in which columns are named in the CREATE INDEX command need not to corresond to the order in which they appear in the table. HOWEVER, the order of columns in the CREATE INDEX statement is SIGNIFICANT because query performance can be effected by the order choosen."

Also note that when doing indexed joins the query optimizer ranks predicate clauses based upon the type of index scan involved. If all criteria are equal the last table in the FROM clause become the drving table. Therefore you should list tables with the samllest number of qualified rows last in the FROM clause. (from SQL Tuning: New features in Oracle7)

-- 
Gunnar Boee	 	internet: gunnar_at_taskon.no
Taskon AS	 	www: http://www.sn.no/taskon/
Gaustadalleen 21	Tel: +47-22 95 86 24
N-0371 Oslo, Norway	Fax: +47-22 60 44 27
Received on Wed Mar 27 1996 - 00:00:00 CET

Original text of this message