> 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@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 -----------------------------------------------------------------