RE: Slow running Query.

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 21 Jun 2004 18:18:26 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKIELCEOAA.mwf@rsiz.com>

Two thoughts:

Take your first fast query and modify the where clause using the second fast query as the IN list.

where as_clcode in (select cl_clcode from .....your second fast query)

If you're on a release that supports order by, ordering the IN list query by cl_clcode *might* make the view's group by operation faster and probably costs little.

Second thought:

make the entire part of the query that delivers you the cl_clcode list you need a virtual table in the from clause. If this doesn't "fix" the plan selected, consider adding a dummy rownum column in the query part of the virtual table so it forces projection.

Now if your list of cl_clcodes actually delivered is very long, the sub-seconds for each part may add up to the few minutes you mentioned.

The existence of the referenced features varies on release, and your mileage may vary.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Kumar, Dharminder Sent: Monday, June 21, 2004 5:06 PM
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 - 17:21:21 CDT