Re: howto force use of indices on one table

In article <374E64B6.9BD01784_at_informatik.tu-chemnitz.de>,   "Sven G." <gis_at_informatik.tu-chemnitz.de> wrote:
> Moin,
>
> I got a table with 10 float columns (a0 .. a9) and 600000 rows. I
> created indices for each column. But when I do a select (exact or
range
> match) on 2 or more columns Oracle uses only one index.
>
> At first all values per column were distinct. Than I tried only 4
> diffrent values per column. So querying a concrete value in one column
> selects 1/4 of all. I expect Oracle using the index of the next column
> to get (1/4)/4 of all and so on, instead it does a full scan of 1/4.
>
> My settings:
> optimizer goal : first_rows
> analyzed the table and each index
>
> Can anyone tell me what I did wrong?
>
> thx
> Sven
>

It would help if you actually post the sql since you could have written this as a select from a single table with multiple where conditions or as a join of the table to itself. You also provided no sizing information and a full table scan would be more efficient for a small table than indexed access.

I would try comparing the explain plan output for the statement using a all_rows, first_rows, and rule hint. Then I would think about the distribution of the data and determine how I think the data should be accessed and compare this to the plans. If you really want to use multiple single column indexes then you may want to consider using the 'and_equal' hint to cause Oracle to merge the results of index scans.

I hope this helps you solve your problem.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri May 28 1999 - 08:34:00 CDT