Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: query not picking up index
<vanbastenardo_at_gmail.com> wrote in message
news:1177892620.425581.16310_at_o5g2000hsb.googlegroups.com...
>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...
>
It seems a little unlikely that the query could return 60,000 rows instantaneously. Are you giving this a fair test, or are you simply timing how long it takes to get the first few rows.
For a fairer test, try:
set timing on
set autotrace traceonly statistics
{run query}
To factor in the time it takes to get all the data.
There are two probable issues:
First issue - making the default plan faster - it looks like your hash join has to dump to disc. If you are using manual workareas, then an increase in the hash_area_size may reduce the query time - if you are running with automatic workareas, then check if your pga_aggregate_target is sensible (for exactly the same reason). You can also check v$sql_workarea to see what's been going on with this query in the past - you should also (given that you're using 9.2) be using explain plan with dbms_xplan, not relying on autotrace to give you execution plans.
Second issue - fixing the statistics. If the query really is much faster when using the nested loop, then it's because (a) the target data is all cached or (b) the target data is packed in a relatively small number of blocks. If (a) is the case then you need to decide if this would be realistically the case when you run on production; if (b) is the case, then you need to correct the clustering_factor (reported in your second post), because the optimizer thinks the data is scattered all over the table. You can do this by calling
dbms_stats.get_index_stats()
and
dbms_stats.set_index_stats()
after gathering stats.
Final thought - if your timings are about the time to get (say) the first 10 rows of data, and if that's all that you're really trying to do with this query, then maybe you should be running with your optimizer_mode set to something like
first_rows_10
as this would probably get you the desired execution plan
automatically.
Final, final thought - if the tables only have a couple more column than those shown, and this query is the really important thing, then recreating BATCH as an index organized table might be appropriate.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.htmlReceived on Mon Apr 30 2007 - 02:46:41 CDT
![]() |
![]() |