Re: query not picking up index
- From: DA Morgan <damorgan@xxxxxxxxx>
- Date: Sun, 29 Apr 2007 19:10:24 -0700
vanbastenardo@xxxxxxxxx wrote:
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...
My guess is you have 100 values scattered evenly in 6M records meaning
you are trying to select 60,000 records scattered in tens or hundreds
of thousands of blocks. Seems like Oracle is making an good decision.
What is the clustering factor?
SELECT leaf_blocks, distinct_keys, clustering_factor, num_rows, last_analyzed
FROM user_indexes
WHERE index_name = <your_index_on_batch_program_ids>;
Try the query in Explain Plan both with and without the INDEX hint
and I suspect Oracle will tell you it is smarter than you are. <g>
Be sure to do so using DBMS_XPLAN not your script.
--
Daniel A. Morgan
University of Washington
damorgan@xxxxxxxxxxxxxxxx
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.
- Follow-Ups:
- Re: query not picking up index
- From: vanbastenardo
- Re: query not picking up index
- References:
- query not picking up index
- From: vanbastenardo
- query not picking up index
- Prev by Date: No Family Patchset for Linux Intel 64 Bit 10.2.0.3?
- Next by Date: Re: 10gR2 install on Kubuntu Stuck at 4%
- Previous by thread: query not picking up index
- Next by thread: Re: query not picking up index
- Index(es):
Relevant Pages
|
