| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: query not picking up index
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 | ----------------------------------------------------------------------------------------Received on Sun Apr 29 2007 - 23:03:43 CDT
 |
 |