Re: query not picking up index
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Mon, 30 Apr 2007 08:46:41 +0100
<vanbastenardo@xxxxxxxxx> wrote in message
news:1177892620.425581.16310@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.html
.
- References:
- query not picking up index
- From: vanbastenardo
- query not picking up index
- Prev by Date: Re: add field in PK
- Next by Date: Re: query not picking up index
- Previous by thread: Re: query not picking up index
- Next by thread: Re: query not picking up index
- Index(es):
Relevant Pages
|
