Optimizing Query

From: Colin <colinwinning_at_yahoo.com>
Date: 22 Oct 2001 05:01:57 -0700
Message-ID: <8181cd1e.0110220401.21740d7a@posting.google.com>

Apologies for the fairly long post,

I am currently trying to optimize a query which is proving quite difficult. I have two tables clients and orders. The clients table has around 15M rows and the orders table around 25M rows.

I am trying to do a join between these two tables as follows :

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.

Forgot to mention, as I process each record, the processed field is updated to another value. Also, the optimizer mode is set to choose. Oracle version 8.1.5.

I have had different response times coming back from the server depending on when the tables and indexes were last analyzed. These are not always better when the statistics have been computed.

Can anyone suggest where I might be going wrong and if there is a better combination of indexes that should be used.

TIA
Colin Received on Mon Oct 22 2001 - 07:01:57 CDT