Content-Type: Text/Plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Why don't you try to use for your session =0D
ALTER SESSION SET OPTIMIZER_MODE =3Dchoose=0D
=0D
=0D
-------Original Message-------=0D
=0D
From: oracle-l_at_freelists.org=0D
Date: 06/24/04 11:31:10=0D
To: oracle-l_at_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_at_freelists.org=0D
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling=0D
Sent: June 22, 2004 7:10 PM=0D
To: oracle-l_at_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_at_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
<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_at_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_at_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
-----------------------------------------------------------------
Received on Thu Jun 24 2004 - 16:23:53 CDT
