HI, If you are in 9i, only to try, to use the /*+ NO_MERGE */ hint and tell
me what happen, once this solved the problem
select /*+ NO_MERGE */ sum.........
If not get the execution plan, because thre is the answer.
-------Original Message-------
From: oracle-l_at_freelists.org
Date: 06/21/04 17:18:53
To: oracle-l_at_freelists.org
Subject: Slow running Query.
> Hi all,
> I have the following query which is running very slow as it takes few min=
utes to complete.
>=20
> SELECT sum(nvl(as_ofcsh,0)),sum(nvl(as_offxd,0)),
> sum(nvl(as_ofeqty,0)),sum(nvl(as_ofai,0))
> FROM perfclient, assets_view, client
> WHERE cl_decmkr=3D'64501013'
> AND cl_clcode=3Das_clcode
> AND as_date=3D '200312'
> AND cl_clcode=3Dpc_clcode
> AND pc_prfcomb in ('B', 'Y')
> AND pc_grpdte !=3D '0000-00-00'
> AND substr(pc_grpdte,1,4)||substr(pc_grpdte,6,2) <=3D '200312'
>=20
> But if I run the following queries the response time is less than a seco=
nd.
>=20
> SELECT sum(nvl(as_ofcsh,0)),sum(nvl(as_offxd,0)),
> sum(nvl(as_ofeqty,0)),sum(nvl(as_ofai,0))
> FROM assets_view
> WHERE as_clcode in '64501013'=20
> and as_date=3D'200312'
>=20
> select cl_clcode from=20
> perfclient,client
> WHERE cl_decmkr=3D'64501013'
> AND cl_clcode=3Dpc_clcode
> AND pc_prfcomb in ('B', 'Y')
> AND pc_grpdte !=3D '0000-00-00'
> AND substr(pc_grpdte,1,4)||substr(pc_grpdte,6,2) <=3D '200312'
>=20
> Following is the definiton of VIEW assets_view
>=20
> CREATE OR REPLACE VIEW retail.assets_view
> ( as_clcode
> , as_date
> , as_ofcsh
> , as_offxd
> , as_ofeqty
> , as_delcsh
> , as_delfxd
> , as_deleqty
> , as_uncsh
> , as_unfxd
> , as_uneqty
> , as_ofai
> , as_delai
> , as_unai
> )
> AS
> select ca_clcode as_clcode,
> substr(ca_date,1,4)||substr(ca_date,6,2) as_date,
> sum(decode(ca_status, 'O', decode(ca_class, 'C', nvl(ca_mktval, 0), 0), 0=
)) as_ofcsh,
> sum(decode(ca_status, 'O', decode(ca_class, 'F', nvl(ca_mktval, 0), 0), 0=
)) as_offxd,
> sum(decode(ca_status, 'O', decode(ca_class, 'E', nvl(ca_mktval, 0), 0), 0=
)) as_ofeqty,
> sum(decode(ca_status, 'D', decode(ca_class, 'C', nvl(ca_mktval, 0), 0), 0=
)) as_delcsh,
> sum(decode(ca_status, 'D', decode(ca_class, 'F', nvl(ca_mktval, 0), 0), 0=
)) as_delfxd,
> sum(decode(ca_status, 'D', decode(ca_class, 'E', nvl(ca_mktval, 0), 0), 0=
)) as_deleqty,
> sum(decode(ca_status, 'U', decode(ca_class, 'C', nvl(ca_mktval, 0), 0), 0=
)) as_uncsh,
> sum(decode(ca_status, 'U', decode(ca_class, 'F', nvl(ca_mktval, 0), 0), 0=
)) as_unfxd,
> sum(decode(ca_status, 'U', decode(ca_class, 'E', nvl(ca_mktval, 0), 0), 0=
)) as_uneqty,
> sum(decode(ca_status, 'O', nvl(ca_mktval_ai,0),0)) as_ofai,
> sum(decode(ca_status, 'D', nvl(ca_mktval_ai,0),0)) as_delai,
> sum(decode(ca_status, 'U', nvl(ca_mktval_ai,0),0)) as_unai
> from me_client_assets
> group by ca_clcode, ca_date
>=20
> (ME_CLIENT_ASSETS table has UNIQUE INDEX pk_me_client_assets
> ON me_client_assets
> ( ca_clcode,
> ca_date,
> ca_status,
> ca_class,
> ca_risk,
> ca_rspelig,
> ca_short,
> ca_foreign )
> CLIENT table has index on column "cl_clcode" and another index on "cl_dec=
mkr".
> PERFCLIENT table has index on column "pc_clcode"
>=20
> So can you suggest some ways to improve this query.
> Thanks in advance.=20
>=20
>=20
>=20
>=20
> Dharminder Kumar
>=20
<FONT SIZE =3D 1>**********************************************************=
******************
This e-mail and any attachments may contain confidential and privileged inf=
ormation. If you are not the intended recipient, please notify the sender i=
mmediately 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 intend=
ed recipient is unauthorized and may be illegal. Unless otherwise stated, o=
pinions expressed in this e-mail are those of the author and are not endors=
ed by the author's employer.</FONT>
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
-----------------------------------------------------------------
----------------------------------------------------------------
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 Mon Jun 21 2004 - 16:30:21 CDT
