Hi.
Is it possible for you to try following:
1. Drop bitmap index on ORDERS table ( by ORDERTYPE ).
2. CREATE BITMAP INDEX clients$processed ON clients ( processed ).
-- I assume that most of rows have processed <> 0.
- It would helpful to know the result of
- SELECT COUNT(*) FROM clients WHERE processed = 0;
3. CREATE INDEX orders$id_type ON orders ( client_id, ordertype );
4. Change the query:
select /*+ ORDERED index(c clients$processed ) index( o orders$id_type)*/ *
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
5. The EXPLAIN plan is supposed to show something like that:
SELECT STATEMENT
STOPCOUNT
NESTED LOOP
TABLE BY INDEX ROWID CLIENTS
INDEX CLIENTS$PROCESSED
TABLE BY INDEX ROWID ORDERS
INDEX RANGE SCAN ORDERS$ID_TYPE
HTH. Michael
Received on Wed Oct 24 2001 - 03:02:27 CDT
