Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 20857 invoked from network); 18 Sep 2007 05:16:04 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 18 Sep 2007 05:16:04 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E742975B0EC; Tue, 18 Sep 2007 06:16:03 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 10351-05; Tue, 18 Sep 2007 06:16:03 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 52F5775AD91; Tue, 18 Sep 2007 06:16:03 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 18 Sep 2007 05:31:03 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 30A8675B64C for ; Tue, 18 Sep 2007 05:31:03 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 04890-06 for ; Tue, 18 Sep 2007 05:31:03 -0400 (EDT) Received: from castafiore.sivsa.com (correo.sivsa.com [212.145.138.162]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3007675B487 for ; Tue, 18 Sep 2007 05:31:00 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by castafiore.sivsa.com (Postfix) with ESMTP id 3F96129C084; Tue, 18 Sep 2007 11:30:59 +0200 (CEST) Received: from castafiore.sivsa.com ([127.0.0.1]) by localhost (castafiore.sivsa.com [127.0.0.1]) (amavisd-new, port 10024) with LMTP id Kq66b2I3YAAH; Tue, 18 Sep 2007 11:30:51 +0200 (CEST) Received: from LIMENS.sivsa.int (limens [10.174.123.154]) by castafiore.sivsa.com (Postfix) with ESMTP id 8ED6129C05B; Tue, 18 Sep 2007 11:30:51 +0200 (CEST) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C7F9D6.9A945373" Subject: RE: Wrong index being used ... Basic SQL Qs Date: Tue, 18 Sep 2007 11:30:50 +0200 Message-ID: <607D6181D9919041BE792D70EF2AEC48242E6E@LIMENS.sivsa.int> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Wrong index being used ... Basic SQL Qs From: "Alvaro Jose Fernandez" To: , X-archive-position: 1643 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: alvaro.fernandez@sivsa.com Precedence: normal Reply-to: alvaro.fernandez@sivsa.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------_=_NextPart_001_01C7F9D6.9A945373 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Hi Vivek =20 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 =3D '89651' (1 Constant Value). =20 Have you evaluated dropping this index? =20 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) =20 NOTE - ICI Table is partitioned on SOL_ID field & has only 81636 Rows. =20 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. =20 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) =20 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?=20 maybe the clustering factor or other stats of idx_inst_num is beating the other index, etc.=20 =20 Thanks indeed =20 P.S. SQL Query =20 SELECT COUNT(*) FROM ICI WHERE ICI.BANK_ID =3D '01' AND SOL_ID =3D '0049' AND ZONE_CODE =3D 'PECINW0008' AND ZONE_DATE =3D TO_DATE( '08-05-2003' ,'DD-MM-YYYY HH24:MI:SS') AND INST_NUM =3D '89651' =20 Misses in library cache during parse: 0 Optimizer mode: CHOOSE Parsing user id: 35 (TBAADM) =20 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=3D3 pr=3D0 pw=3D0 time=3D87 us) 0 TABLE ACCESS BY GLOBAL INDEX ROWID INW_CLG_INST_TABLE PARTITION: 2 2 (cr=3D3 pr=3D0 p w=3D0 time=3D70 us) 0 INDEX RANGE SCAN IDX_INST_NUM (cr=3D3 pr=3D0 pw=3D0 = time=3D65 us)(object id 28758) =20 the above performance is bad ? (87us, cr=3D3) **************** 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*** =09 ------_=_NextPart_001_01C7F9D6.9A945373 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

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 =3D = '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 =3D  '01'

AND  SOL_ID =3D '0049'

AND ZONE_CODE =3D  'PECINW0008'

AND ZONE_DATE =3D TO_DATE( '08-05-2003'  ,'DD-MM-YYYY = HH24:MI:SS')

AND INST_NUM =3D  '89651'

 

Misses in library cache during parse: 0

Optimizer mode: CHOOSE

Parsing user id: 35  (TBAADM)

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  SORT AGGREGATE (cr=3D3 pr=3D0 pw=3D0 time=3D87 = us)

      0   TABLE ACCESS BY GLOBAL INDEX ROWID INW_CLG_INST_TABLE = PARTITION: 2 2 (cr=3D3 pr=3D0 p

w=3D0 time=3D70 us)

0           = ;     INDEX RANGE SCAN IDX_INST_NUM (cr=3D3 pr=3D0 pw=3D0 time=3D65 us)(object id = 28758)

 

the above = performance is bad ? (87us, cr=3D3)

**************** = 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***
------_=_NextPart_001_01C7F9D6.9A945373-- -- http://www.freelists.org/webpage/oracle-l