Re: Optimizing Query

On 22 Oct 2001 05:01:57 -0700, Colin
<colinwinning_at_yahoo.com> a écrit:
>Apologies for the fairly long post,

>select /*+FIRST_ROWS*/ *
>from clients c, orders o
>where c.id = o.client_id
>and c.processed = 0 --one of about 60 different values, 0 unprocessed
>and o.ordertype = 1 --either 1, 2, or null
>and rownum <= 1000
>
>I have tried many combinations of indexes on fields on each of the two tables.
>The optimizer is currently using the primary key on clients (id) and a bitmap
>index on orders (ordertype) - as seen by the explain plan.
>There is also an index on the orders (client_id) field.

Did you try partitions ? It would definitively be good for the orders table. Create a partition for each value of ordertype (as you do no update ordertype) Don't forget the clause to allow rows to be moved between partitions (if updates are allowed)
In your select, you juste have to say which partition you're looking in :-)

It was a real gain in our case ( 3 values, badly balanced : about 99/100 on the value most often searched , so the index was completely counter productive)

-- 
Damien