Re: query not picking up index

On 30 abr, 06:03, vanbastena..._at_gmail.com wrote:
> On Apr 29, 7:10 pm, DA Morgan <damor..._at_psoug.org> wrote:
>
>
>
> > vanbastena..._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
> > damor..._at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org
>
> Hi,
>
> Doesn't seem like oracle is making a good decision. The query takes
> close to a minute to execute. It's doing a full scan on the BATCH
> table. With index hint, the query returns instantaneously.
>
> Here's the cluster factor for INDEX IND_BP_PROGRAM_ID:
>
> LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS LAST_ANAL
> ----------- ------------- ----------------- ---------- ---------
> 11113 100 28442 5486663 29-APR-07
>
> EXPLAIN PLAN w/o hint:
>
> ----------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows
> | Bytes | Cost |
> ----------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 54867
> | 1768K| 11900 |
> |* 1 | HASH JOIN | | 54867
> | 1768K| 11900 |
> | 2 | TABLE ACCESS BY INDEX ROWID| BATCH_PROGRAM | 54867
> | 535K| 399 |
> |* 3 | INDEX RANGE SCAN | IND_BP_PROGRAM_ID | 54867
> | | 114 |
> | 4 | TABLE ACCESS FULL | BATCH |
> 5486K| 120M| 7001 |
> ----------------------------------------------------------------------------------------
>
> EXPLAIN PLAN with index hint:
>
> ----------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows
> | Bytes | Cost |
> ----------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 54867
> | 1768K| 110K|
> | 1 | NESTED LOOPS | | 54867
> | 1768K| 110K|
> | 2 | TABLE ACCESS BY INDEX ROWID| BATCH_PROGRAM | 54867
> | 535K| 399 |
> |* 3 | INDEX RANGE SCAN | IND_BP_PROGRAM_ID | 54867
> | | 114 |
> | 4 | TABLE ACCESS BY INDEX ROWID| BATCH | 1
> | 23 | 2 |
> |* 5 | INDEX UNIQUE SCAN | PK_BATCH | 1
> | | 1 |
> ----------------------------------------------------------------------------------------

The optimizer is estimating 54867 rows ( NUM_ROWS=5486663 / DISTINCT_KEYS=100). You should check:

DB_FILE_MULTIBLOCK_READ_COUNT and

optimizer_index_caching
optimizer_index_cost_adj

HTH. Cheers.

Carlos. Received on Mon Apr 30 2007 - 03:02:50 CDT