Re: howto force use of indices on one table

A copy of this was sent to "Sven G." <gis_at_informatik.tu-chemnitz.de> (if that email address didn't require changing) On Fri, 28 May 1999 11:41:10 +0200, you 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.
>

you have a table T ( a0 number, a1 number, ... a9 number );

you have indexes a0_idx(a0), ... a9_idx(a9).

Is the query you issue something like:

select *
 from T
where a0 between :x and :y

   OR a1 = :z

or is it like:

select *
  from t
 where a0 between :x and :y
   AND a1 = :z

the first query, with the OR is logically equivalent to:

select * from t where a0 between :x and :y union
select * from t where a1 = :z

and could use 2 indexes. the second query however can only use 1 index, followed by a table access by rowid to perform the 2'cnd part of the where clause. The and in this case makes using 2 indexes suboptimal. You could rewrite the AND query as:

select * from t where a0 between :x and :y INTERSECT
select * from t where a1 = :z

the problem with that is that you might end up scanning by index lookup 50% of your table and getting 0 rows returned. Or, you might get lucky and find that the results overlap perfectly (but in order to do that you scanned the 25% segment 2 times!!)

If you are using the AND, using 1 index is the shortest execution path. If you use 2 indexes you will ALWAYS index access more data then if you just used 1 index.

Have you looked at COMPOUND indexes? for example, the AND query will go very fast if you have an index on (a1,a0)

>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

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri May 28 1999 - 07:26:43 CDT