Re: O9i: How to create a matching index to a given query

> Hth

Yes, I think it helped (at least in general) to understand a bit more in the oracle- dshungle.

Completely clear was before, that I have to get Oracle to take as few rows as possible at first.
Now I know that the table is called drive table and it is useless (or bad) to take a lookup table as a drive table. I also know that it is only important to Oracle to find all columns first, that minimize the number of rows. Does this mean that an index with only these columns is better than that one that includes the IDs for lookup tables?

example:
SELECT <any fields> FROM MasterTable MT
JOIN DetailTable DT ON DT.IDMASTER = MT.ID JOIN LookupTable1 L1 ON DT.IDLOOKUP1 = L1.ID JOIN LookupTable2 L2 ON MT.IDLOOKUP2 = L2.ID WHERE MT.F1='A' AND DT.F1='B'

1. CREATE INDEX ON MASTERTABLE (ID, F1) and CREATE INDEX ON DETAILTABLE (ID, F1)
2. CREATE INDEX ON MASTERTABLE (ID, F1, IDLOOKUP2) -- correct sequence? CREATE INDEX ON DETAILTABLE (ID, F1, IDLOOKUP1) -- correct sequence?

I now know how to analyze the decisions of the CBO (TRACE- File). This will take some time.
I ordered a book that will help me understand.

My problem still exists, but I think at first I have to learn about CBO.

> --
> Sybrand Bakker
> Senior Oracle DBA

Many thanks
Andreas Mosmann

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de