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@xxxxxxxxxxxxx=0D Date: 06/24/04 11:31:10=0D To: oracle-l@xxxxxxxxxxxxx=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@xxxxxxxxxxxxx=0D [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling=0D Sent: June 22, 2004 7:10 PM=0D To: oracle-l@xxxxxxxxxxxxx=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@xxxxxxxxxxxxx=0D put 'unsubscribe' in the subject line.=0D --=0D Archives are at //www.freelists.org/archives/oracle-l/=0D FAQ is at //www.freelists.org/help/fom-serve/cache/1.html=0D -----------------------------------------------------------------=0D =0D =0D <FONT SIZE =3D 1>***********************************************************************= **** =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.</FONT>=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@xxxxxxxxxxxxx=0D put 'unsubscribe' in the subject line.=0D --=0D Archives are at //www.freelists.org/archives/oracle-l/=0D FAQ is at //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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------