RE: Wrong index being used ... Basic SQL Qs

From: Alvaro Jose Fernandez <alvaro.fernandez_at_sivsa.com>
Date: Tue, 18 Sep 2007 11:30:50 +0200
Message-ID: <607D6181D9919041BE792D70EF2AEC48242E6E@LIMENS.sivsa.int>

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***

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 18 2007 - 04:30:50 CDT