Re: query not picking up index

vanbastenardo_at_gmail.com 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_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org