query not picking up index

From: <vanbastenardo_at_gmail.com>
Date: 29 Apr 2007 17:23:40 -0700
Message-ID: <1177892620.425581.16310@o5g2000hsb.googlegroups.com>

I have 2 tables BATCH and BATCH_PROGRAM.

BATCH

---

BATCH_ID NUMBER(10)
BATCH_NAME VARCHAR2(10)
-- pk on BATCH_ID

BATCH_PROGRAM

---

BATCH_PROGRAM_BATCH_ID NUMBER(10)
BATCH_PROGRAM_ID NUMBER(10)
-- pk on (BATCH_PROGRAM_BATCH_ID, BATCH_PROGRAM_ID)

• index on BATCH_PROGRAM_ID

They both have exactly the same number of records (~6M). IDs between BATCH.BATCH_ID and BATCH_PROGRAM.BATCH_PROGRAM_BATCH_ID is exactly 1 to 1 matching and unique. BATCH_PROGRAM.BATCH_PROGRAM_ID has only 100 unique values.

The query below is taking over 1 min. From explain plan it shows that it's doing full scan on BATCH. The optimizer is not able to pick up the index(pk) on BATCH.BATCH_ID. An index hint will make it execute instantaneously. But we can't use a hint in the app for some practical reasons. Is there any way (reconstruct query, stats etc) to fix this? Thanks!

SELECT b.batch_name
FROM BATCH b, BATCH_PROGRAM bp
WHERE b.BATCH_ID=bp.BATCH_PROGRAM_BATCH_ID AND bp.BATCH_PROGRAM_PROGRAM_ID = 555;

Execution Plan

---

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11900 Card=54867 Bytes=1810611)
1 0 HASH JOIN (Cost=11900 Card=54867 Bytes=1810611) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BATCH_PROGRAM' (Cost=399 Card=54867 Bytes=548670)
3 2 INDEX (RANGE SCAN) OF 'IND_BP_PROGRAM_ID' (NON-UNIQUE) (Cost=114 Card=54867)
4 1 TABLE ACCESS (FULL) OF 'BATCH' (Cost=7001 Card=5486663 Bytes=126193249)

P.S. oracle 9.2.0.8.0 EE on Solaris 10. Schema statistics is collected fully by dbms_stats procedure. And please don't ask me why have 2 tables instead of 1. This is a 3rd party db... Received on Sun Apr 29 2007 - 19:23:40 CDT