Re: Optimizing Query

Mark,

Thanks. I'll give it a try whenever I get some time on the database. I'm also going to remove as many of the other spurious indexes that exist on the tables (inherited database).

Cheers
Colin

mark_brehmen_at_yahoo.com (mark) wrote in message news:<fa4781e4.0110232109.48ae54b5_at_posting.google.com>...
> 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 - 07:43:29 CDT