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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Juan Carlos Reyes
Pacheco
Sent: June 22, 2004 12:15 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------