Re: query not picking up index

On Apr 29, 7:10 pm, DA Morgan <damor...@xxxxxxxxx> wrote:
vanbastena...@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
damor...@xxxxxxxxxxxxxxxx
(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 |
----------------------------------------------------------------------------------------




.

Relevant Pages

  • Re: query not picking up index
    ... The query below is taking over 1 min. ... Execution Plan ... Seems like Oracle is making an good decision. ... Try the query in Explain Plan both with and without the INDEX hint ...
    (comp.databases.oracle.server)
  • Re: Forcing index usage...
    ... I use the index hint. ... But as soon as I add more predicates or nest the query into another one, ... The Co-operative Oracle Users' FAQ ...
    (comp.databases.oracle.server)
  • Re: query not picking up index
    ... it's doing full scan on BATCH. ... Is there any way (reconstruct query, ... Seems like Oracle is making an good decision. ... With index hint, the query returns instantaneously. ...
    (comp.databases.oracle.server)
  • Re: Sybase and materialized views
    ... >>> Hi I'm new to Sybase from Oracle. ... >> For those not familiar with Oracle materialized views ... ... > And static tables can be created/updated in batch DSS environments. ... You can then, either query the view directly, or use them to ...
    (comp.databases.sybase)
  • Re: How ">=" Work on Varchar field?
    ... Because my original Query was bit slow when used with order by. ... using that with Index hint, ... > Columnist, SQL Server Professional ... > Hi Tom, ...
    (microsoft.public.sqlserver.programming)