RE: Slow running Query.

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

Juan,
Your PL/SQL suggestion are valid.
But for this query ca_date columns does not seems to be a problem because s= ee if I run the following query.

SELECT sum(nvl(as_ofcsh,0)),sum(nvl(as_offxd,0)),

            sum(nvl(as_ofeqty,0)),sum(nvl(as_ofai,0))    FROM assets_view ,client
   WHERE
   as_clcode in ( select cl_clcode from client where cl_decmkr=3D'9939393')

Following the the explain Plan, which still does not use INDEX on ME_CLIENT= _ASSETS tables on which the VIEW assets_view is based.

PERATION                 OPTIONS                   OBJECT_NAME            P=

OSITION
------------------------- ------------------------- -------------------- --=

SELECT STATEMENT

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

So the real problem is that somehow we need to make sure that the query use= s INDEX in question, whichs is not happening.

Thanks.

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

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Juan Carlos Reyes Pacheco
Sent: June 22, 2004 12:15 PM
To: oracle-l_at_freelists.org
Subject: RE: Slow running Query.

Hi, I will do t he following

Try creating function indexes on composed columns you are queryin and tellme if this improves something
For example you are using as_date in the where column, but you don't have a index on substr(ca_date,1,4,....

Only to avoid unncesary plsql work
1. instead sum(nvl(column,0)) I'll use nvl(sum(column),0 2. instead of decode use case

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

Received on Tue Jun 22 2004 - 11:53:23 CDT