Slow running Query Again

From: Kumar, Dharminder <Dharminder.Kumar_at_Bmonb.com>
Date: Mon, 21 Jun 2004 17:31:45 -0400
Message-ID: <00135E0349FB494EBB1D6B582EFCFAF2C5EFE9@NBNOTOCEXCH3.nesbittburns.ca>

Hi all,
I have the following query which is running very slow as it takes few minut= es to complete.

 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'

But if I run the following queries the response time is less than a second.

 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'

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'

Following is the definiton of VIEW assets_view

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

(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_decmk= r".
PERFCLIENT table has index on column "pc_clcode"

So can you suggest some ways to improve this query. Thanks in advance.=20

Dharminder Kumar

Dharminder Kumar
Senior Consultant
AllStream IT=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
-----------------------------------------------------------------

Received on Mon Jun 21 2004 - 16:28:49 CDT