Re: Optimizing Query

Colin,

Try one more thing. Drop the bitmapped index and instead create a composite index on client_id and ordertype. Create Index on MyTable(Client_id ,ordertype)

I assume that and ordinary index only on ordertype would be bad because of low cardinality. but a composite index **could** be better

Regards
Mark

damien.salvador_at_via.ecp.fr (Damien Salvador) wrote in message news:<slrn9tbib2.use.damien.salvador_at_zen.via.ecp.fr>...
> 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)
Received on Wed Oct 24 2001 - 00:09:57 CDT