RE: Wrong index being used ... Basic SQL Qs
- From: "Alvaro Jose Fernandez" <alvaro.fernandez@xxxxxxxxx>
- To: <VIVEK_SHARMA@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 18 Sep 2007 11:30:50 +0200
Hi Vivek
During an Internal Benchmark Run, following SQL Query is using the Wrong
index idx_inst_num which has very Low Cardinality.ALL Rows of the Table
have inst_num value = '89651' (1 Constant Value).
Have you evaluated dropping this index?
Usage of Unique index IDX_INW_CLG_INST_TABLE would be preferred over
idx_inst_num index as Cardinality of (sol_id, zone_code, zone_date,
zone_srl_num, bank_id ) Combination is much better than (inst_num)
NOTE - ICI Table is partitioned on SOL_ID field & has only 81636 Rows.
Qs Will Creating Histogram on inst_num field make the optimizer avoid
usage of the respective idx_inst_num index choose the Correct index i.e.
IDX_INW_CLG_INST_TABLE ? Any Other Ideas?
NOTE - SQL Code Change can Not be made.
Indexes on ICI Table:-
Unique IDX_INW_CLG_INST_TABLE index - Locally Prefixed Partitioned Index
- (sol_id, zone_code, zone_date, zone_srl_num, bank_id )
idx_inst_num index - (inst_num)
would you mind to send an 10053 excerpt for the "table stats" and
"SINGLE TABLE ACCESS PLAN" for this query, so we can look further?
maybe the clustering factor or other stats of idx_inst_num is beating
the other index, etc.
Thanks indeed
P.S. SQL Query
SELECT COUNT(*) FROM ICI
WHERE ICI.BANK_ID = '01'
AND SOL_ID = '0049'
AND ZONE_CODE = 'PECINW0008'
AND ZONE_DATE = TO_DATE( '08-05-2003' ,'DD-MM-YYYY HH24:MI:SS')
AND INST_NUM = '89651'
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 35 (TBAADM)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=87 us)
0 TABLE ACCESS BY GLOBAL INDEX ROWID INW_CLG_INST_TABLE
PARTITION: 2 2 (cr=3 pr=0 p
w=0 time=70 us)
0 INDEX RANGE SCAN IDX_INST_NUM (cr=3 pr=0 pw=0 time=65
us)(object id 28758)
the above performance is bad ? (87us, cr=3)
**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended
solely for the use of the addressee(s). If you are not the intended
recipient, please notify the sender by e-mail and delete the original
message. Further, you are not to copy, disclose, or distribute this
e-mail or its contents to any other person and any such actions are
unlawful. This e-mail may contain viruses. Infosys has taken every
reasonable precaution to minimize this risk, but is not liable for any
damage you may sustain as a result of any virus in this e-mail. You
should carry out your own virus checks before opening the e-mail or
attachment. Infosys reserves the right to monitor and review the content
of all messages sent to or from this e-mail address. Messages sent to or
from this e-mail address may be stored on the Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***
Other related posts:
