Wrong index being used ... Basic SQL Qs

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Tue, 18 Sep 2007 14:42:04 +0530
Message-ID: <69E1360E54B50C4A828A136C158E474201DC2C9487@BLRKECMBX02.ad.infosys.com>

Folks

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).

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)

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)


**************** 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:12:04 CDT