From oracle-l-bounce@freelists.org Thu Jun 24 11:47:12 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i5OGklD05569 for ; Thu, 24 Jun 2004 11:46:57 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i5OGkX605522 for ; Thu, 24 Jun 2004 11:46:44 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 587F472DCC7; Thu, 24 Jun 2004 11:29:00 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 02237-64; Thu, 24 Jun 2004 11:29:00 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8485972DCFC; Thu, 24 Jun 2004 11:28:35 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 24 Jun 2004 11:27:00 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3905C72C84A for ; Thu, 24 Jun 2004 11:26:59 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 00506-98 for ; Thu, 24 Jun 2004 11:26:58 -0500 (EST) Received: from mail.acelerate.com (mail.acelerate.com [200.105.128.132]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id B64E772DBEA for ; Thu, 24 Jun 2004 11:26:43 -0500 (EST) Received: (qmail 17603 invoked from network); 24 Jun 2004 16:48:07 -0000 Received: from dazasoftware.com (HELO cachitoss) (200.105.151.94) by 0 with SMTP; 24 Jun 2004 16:48:07 -0000 MIME-Version: 1.0 Message-Id: <40DB04C8.000001.00848@CACHITOSS> Date: Thu, 24 Jun 2004 12:43:52 -0400 Content-type: text/plain X-Mailer: IncrediMail (3001524) From: "Juan Carlos Reyes Pacheco" References: <00135E0349FB494EBB1D6B582EFCFAF2C5EFF2@NBNOTOCEXCH3.nesbittburns.ca> To: Subject: RE: Slow running Query. X-FID: PLAINTXT-NONE-0000-0000-000000000000 X-Priority: 3 X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 3572 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jreyes@dazasoftware.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Content-Type: Text/Plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I never used rule mode =0D =0D -------Original Message-------=0D =0D From: oracle-l@freelists.org=0D Date: 06/24/04 11:31:10=0D To: oracle-l@freelists.org=0D Subject: RE: Slow running Query.=0D =0D First of all it is not type mismatch as clcode column in all the tables = is defined as VARCHAR.=0D Based on the data the sub query for cl_clcode will return a maximum of 5 rows.=0D =0D The new addition is that I have found from the DBA that we run the databa= se in RULE optimizer MODE. So I tried to run the explain for following query= =2E=0D select * from me_client_assets -- on the table itself not VIEW assets_v= iew=0D where ca_clcode in=0D (select cl_clcode from client where cl_decmkr=3D'299292')=0D and substr(ca_date,1,4)||substr(ca_date,6,2)=3D'20012'=0D and got the following explain plan.=0D =0D OPERATION OPTIONS OBJECT_NAME = =20 POSITION=0D ------------------------- ------------------------- -------------------- ----------=0D SELECT STATEMENT=0D NESTED LOOPS = =20 1=0D TABLE ACCESS BY INDEX ROWID CLIENT = =20 1=0D INDEX RANGE SCAN CL_DECMKR_IDX = =20 1=0D TABLE ACCESS BY INDEX ROWID ME_CLIENT_ASSETS = =20 2=0D INDEX RANGE SCAN PK_ME_CLIENT_ASSETS = =20 1=0D =0D Now as you can see that this query uses the index PK_ME_CLIENT_ASSETS despited the query clause=0D substr(ca_date,1,4)||substr(ca_date,6,2)=3D'20012=0D =0D So it seems to me as if original query does use the required index becaus= e of the definition of View assets_view.=0D =0D I am attaching with following file, if someone is interested in doing som= e test.=0D 1. create_schema.sql ( will create required tables and view).=0D 2. drop_schema.sql ( will drop all the objects created by above script).=0D 3. q1.sql ( My original query based on view, which does not use required Index)=0D 4. q2.sql (The above query where I use substr(column) in the where clause= , but still used Index).=0D =0D Anyway thanks for all of you who provided your valuable inputs.=0D =0D =0D =0D -----Original Message-----=0D From: oracle-l-bounce@freelists.org=0D [mailto:oracle-l-bounce@freelists.org]On Behalf Of Wolfgang Breitling=0D Sent: June 22, 2004 7:10 PM=0D To: oracle-l@freelists.org=0D Subject: RE: Slow running Query.=0D =0D =0D How many cl_clcodes does the subselect return and how does that number=0D compare to the cardinality of the VW_NSO_1 view in the explain plan?=0D =0D At 11:03 AM 6/22/2004, you wrote:=0D >Mark,=0D >I tried both the options suggested by you. Following is the result.=0D >=0D >Thought 1.=0D >Query=0D >=0D >SELECT sum(nvl(as_ofcsh,0)),sum(nvl(as_offxd,0)),=0D > sum(nvl(as_ofeqty,0)),sum(nvl(as_ofai,0))=0D > FROM assets_view=0D > WHERE as_clcode in ( select cl_clcode from perfclient,client=0D > WHERE cl_decmkr=3D3D'64501013'=0D > AND cl_clcode=3D3Dpc_clcode=0D > AND pc_prfcomb in ('B', 'Y')=0D > AND pc_grpdte !=3D '0000-00-00' -- dkum= ar added=0D > AND=0D > substr(pc_grpdte,1,4)||substr(pc_grpdte,6,2) <=3D '200312')=0D > and as_date =3D'200312'=0D =0D regards=0D =0D Wolfgang Breitling=0D Centrex Consulting Corporation=0D www.centrexcc.com=0D =0D ----------------------------------------------------------------=0D Please see the official ORACLE-L FAQ: http://www.orafaq.com=0D ----------------------------------------------------------------=0D To unsubscribe send email to: oracle-l-request@freelists.org=0D put 'unsubscribe' in the subject line.=0D --=0D Archives are at http://www.freelists.org/archives/oracle-l/=0D FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html=0D -----------------------------------------------------------------=0D =0D =0D ***********************************************************************= **** =0D This e-mail and any attachments may contain confidential and privileged information. If you are not the intended recipient, please notify the sen= der immediately by return e-mail, delete this e-mail and destroy any copies. = Any dissemination or use of this information by a person other than the inten= ded recipient is unauthorized and may be illegal. Unless otherwise stated, opinions expressed in this e-mail are those of the author and are not endorsed by the author's employer.=0D =0D =0D -- Binary/unsupported file stripped by Ecartis --=0D -- Type: application/octet-stream=0D -- File: create_schema.sql=0D -- Desc: create_schema.sql=0D =0D =0D -- Binary/unsupported file stripped by Ecartis --=0D -- Type: application/octet-stream=0D -- File: drop_schema.sql=0D -- Desc: drop_schema.sql=0D =0D =0D -- Binary/unsupported file stripped by Ecartis --=0D -- Type: application/octet-stream=0D -- File: q1.sql=0D -- Desc: q1.sql=0D =0D =0D -- Binary/unsupported file stripped by Ecartis --=0D -- Type: application/octet-stream=0D -- File: q2.sql=0D -- Desc: q2.sql=0D =0D =0D ----------------------------------------------------------------=0D Please see the official ORACLE-L FAQ: http://www.orafaq.com=0D ----------------------------------------------------------------=0D To unsubscribe send email to: oracle-l-request@freelists.org=0D put 'unsubscribe' in the subject line.=0D --=0D Archives are at http://www.freelists.org/archives/oracle-l/=0D FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html=0D ----------------------------------------------------------------- -- Binary/unsupported file stripped by Ecartis -- -- Type: image/gif -- File: IMSTP.gif ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------