query not picking up index
- From: vanbastenardo@xxxxxxxxx
- Date: 29 Apr 2007 17:23:40 -0700
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...
.
- Follow-Ups:
- Re: query not picking up index
- From: Mladen Gogala
- Re: query not picking up index
- From: Jonathan Lewis
- Re: query not picking up index
- From: DA Morgan
- Re: query not picking up index
- Prev by Date: Re: 10gR2 install on Kubuntu Stuck at 4%
- Next by Date: No Family Patchset for Linux Intel 64 Bit 10.2.0.3?
- Previous by thread: 10gR2 install on Kubuntu Stuck at 4%
- Next by thread: Re: query not picking up index
- Index(es):
Relevant Pages
|
