Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Slow running Query.

RE: Slow running Query.

From: Kumar, Dharminder <Dharminder.Kumar_at_Bmonb.com>
Date: Tue, 22 Jun 2004 13:03:43 -0400
Message-ID: <00135E0349FB494EBB1D6B582EFCFAF2C7822E@NBNOTOCEXCH3.nesbittburns.ca> 





Mark,


I tried both the options suggested by you. Following is the result.



Thought 1.


Query


=3D=3D=3D=3D=3D


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 ( select cl_clcode from 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' -- dkumar a=
dded
                                AND substr(pc_grpdte,1,4)||substr(pc_grpdte=
,6,2) <=3D '200312')
        and   as_date =3D'200312'





Explain Plan


=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

OPERATION                 OPTIONS                   OBJECT_NAME            =


POSITION


------------------------- ------------------------- -------------------- --=






SELECT STATEMENT


  SORT                    AGGREGATE                                        =
       1
    MERGE JOIN                                                             =
       1
      SORT                JOIN                                             =
       1
        VIEW                                        ASSETS_VIEW            =
       1
          SORT            GROUP BY                                         =
       1
            TABLE ACCESS  FULL                      ME_CLIENT_ASSETS       =
       1
      SORT                JOIN                                             =
       2
        VIEW                                        VW_NSO_1               =
       1
          SORT            UNIQUE                                           =
       1
            NESTED LOOPS                                                   =
       1
              TABLE ACCES BY INDEX ROWID            CLIENT                 =
       1
                INDEX     RANGE SCAN                CL_DECMKR_IDX          =
       1
              TABLE ACCES BY INDEX ROWID            PERFCLIENT             =
       2
                INDEX     RANGE SCAN                PC_CLCODE_IDX          =
       1






Thought 2 ( Hope I got it right)


=3D=3D=3D=3D=3D=3D=3D=3D=3D


Query 2


=3D=3D=3D=3D=3D=3D=3DSELECT  sum(nvl(as_ofcsh,0)),sum(nvl(as_offxd,0)),

                   sum(nvl(as_ofeqty,0)),sum(nvl(as_ofai,0))
        FROM assets_view ,(select cl_clcode,rownum from  client,perfclient
        WHERE cl_decmkr=3D'64501013'
        AND cl_clcode=3Dpc_clcode
        AND pc_prfcomb in ('B', 'Y')
        AND pc_grpdte !=3D '0000-00-00' -- dkumar added
        AND substr(pc_grpdte,1,4)||substr(pc_grpdte,6,2) <=3D '200312')
        where as_clcode =3D cl_clcode
        and as_date=3D'200312'






Explain Plan


=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

OPERATION                 OPTIONS                   OBJECT_NAME            =


POSITION


------------------------- ------------------------- -------------------- --=






SELECT STATEMENT


  SORT                    AGGREGATE                                        =
       1
    MERGE JOIN                                                             =
       1
      SORT                JOIN                                             =
       1
        VIEW                                                               =
       1
          COUNT                                                            =
       1
            NESTED LOOPS                                                   =
       1
              TABLE ACCES BY INDEX ROWID            CLIENT                 =
       1
                INDEX     RANGE SCAN                CL_DECMKR_IDX          =
       1
              TABLE ACCES BY INDEX ROWID            PERFCLIENT             =
       2
                INDEX     RANGE SCAN                PC_CLCODE_IDX          =
       1
      SORT                JOIN                                             =
       2
        VIEW                                        ASSETS_VIEW            =
       1
          SORT            GROUP BY                                         =
       1
            TABLE ACCESS  FULL                      ME_CLIENT_ASSETS       =
       1





So you can see that in both the cases, queries are not using the INDEX in q=
uestion.


Let me know if you have any other suggestion.
Thanks.



-----Original Message-----


From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mark W. Farnham
Sent: June 21, 2004 6:18 PM


To: oracle-l_at_freelists.org


Subject: RE: Slow running Query.




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=

=3D


utes to complete.

>=3D20


>  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=3D3D'64501013'

>         AND cl_clcode=3D3Das_clcode

>         AND as_date=3D3D '200312'

>         AND cl_clcode=3D3Dpc_clcode

>         AND pc_prfcomb in ('B', 'Y')

>         AND pc_grpdte !=3D3D '0000-00-00'

>         AND substr(pc_grpdte,1,4)||substr(pc_grpdte,6,2) <=3D3D '200312'

>=3D20




> But if I run the following  queries the response time is less than a seco=

=3D


nd.

>=3D20


>  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'=3D20

>         and         as_date=3D3D'200312'

>=3D20


> select cl_clcode from=3D20

>          perfclient,client

>         WHERE cl_decmkr=3D3D'64501013'

>         AND cl_clcode=3D3Dpc_clcode

>         AND pc_prfcomb in ('B', 'Y')

>         AND pc_grpdte !=3D3D '0000-00-00'

>         AND substr(pc_grpdte,1,4)||substr(pc_grpdte,6,2) <=3D3D '200312'

>=3D20




> Following is the definiton of VIEW assets_view


>=3D20




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

=3D


)) as_ofcsh,


> sum(decode(ca_status, 'O', decode(ca_class, 'F', nvl(ca_mktval, 0), 0), 0=

=3D


)) as_offxd,


> sum(decode(ca_status, 'O', decode(ca_class, 'E', nvl(ca_mktval, 0), 0), 0=

=3D


)) as_ofeqty,


> sum(decode(ca_status, 'D', decode(ca_class, 'C', nvl(ca_mktval, 0), 0), 0=

=3D


)) as_delcsh,


> sum(decode(ca_status, 'D', decode(ca_class, 'F', nvl(ca_mktval, 0), 0), 0=

=3D


)) as_delfxd,


> sum(decode(ca_status, 'D', decode(ca_class, 'E', nvl(ca_mktval, 0), 0), 0=

=3D


)) as_deleqty,


> sum(decode(ca_status, 'U', decode(ca_class, 'C', nvl(ca_mktval, 0), 0), 0=

=3D


)) as_uncsh,


> sum(decode(ca_status, 'U', decode(ca_class, 'F', nvl(ca_mktval, 0), 0), 0=

=3D


)) as_unfxd,


> sum(decode(ca_status, 'U', decode(ca_class, 'E', nvl(ca_mktval, 0), 0), 0=

=3D


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


>=3D20




> (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=

=3D


mkr".


> PERFCLIENT table has index on column "pc_clcode"


>=3D20




> So can you suggest some ways to improve this query.

> Thanks in advance.=3D20


>=3D20
>=3D20
>=3D20
>=3D20




> Dharminder Kumar



>=3D20



<FONT SIZE =3D3D 1>********************************************************=


**=3D




This e-mail and any attachments may contain confidential and privileged inf=
=3D


ormation. If you are not the intended recipient, please notify the sender i=
=3D


mmediately by return e-mail, delete this e-mail and destroy any copies. Any=
=3D


 dissemination or use of this information by a person other than the intend=
=3D


ed recipient is unauthorized and may be illegal. Unless otherwise stated, o=
=3D


pinions expressed in this e-mail are those of the author and are not endors=
=3D


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


<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 Tue Jun 22 2004 - 12:01:02 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US