Re: Wrong index being used ... Basic SQL Qs
- From: "Ghassan Salem" <salem.ghassan@xxxxxxxxx>
- To: VIVEK_SHARMA@xxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 18 Sep 2007 13:22:43 +0200
It seems that either the query is wrong, or your email (1st paragraphe,
about int_num values) is wrong, otherwise how come the rowsource says that
you have 0 rows returned from the index range scan?
On 9/18/07, Alvaro Jose Fernandez <alvaro.fernandez@xxxxxxxxx> wrote:
>
> 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:
