| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Slow running Query.
Wolfgang and Juan,
First of all sorry for reply after so many days as I was too busy at workp=
lace.
Here is my findings.
After doing compute statistics on the tables. The following query=20
select * from me_client_assets=20
where ca_clcode in=20
( select cl_clcode from client where cl_decmkr=3D'2929292')
and substr(ca_date,1,4)||substr(ca_date,6,2)=3D'20012'
used the required INDEX in both the cases ( 'CHOOSE', 'RULE).
But still on my original query, I was not able to=20 get INDEX being used (in both RULE, CHOSSE sessions).=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,perfclient, client
WHERE
as_clcode=3Dpc_clcode
and as_date=3D '200312'
AND pc_clcode=3Dcl_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'
and cl_decmkr=3D'645010103';
OPERATION OPTIONS OBJECT_NAME =POSITION
------------------------- ------------------------- -------------------- --=
--------
SELECT STATEMENT =
2923
SORT AGGREGATE =
1
HASH JOIN =
1
NESTED LOOPS =
1
TABLE ACCESS BY INDEX ROWID CLIENT =
1
INDEX RANGE SCAN CL_DECMKR_IDX =
1
TABLE ACCESS BY INDEX ROWID PERFCLIENT =
2
INDEX UNIQUE SCAN PC_CLCODE_IDX =
1
VIEW ASSETS_VIEW =
2
SORT GROUP BY =
1
TABLE ACCESS FULL ME_CLIENT_ASSETS =
1
So I decided to use table name instead of view in the query as follows and now finally it uses the required INDEX on table ME_CLIENT_ASSETS table.
select
sum(decode(ca_status, 'O', decode(ca_class, 'C', nvl(ca_mktval, 0), 0), 0))=
,
sum(decode(ca_status, 'O', decode(ca_class, 'F', nvl(ca_mktval, 0), 0), 0))=
,
sum(decode(ca_status, 'O', decode(ca_class, 'E', nvl(ca_mktval, 0), 0), 0))=
,
sum(decode(ca_status, 'O', nvl(ca_mktval_ai,0),0))
from me_client_assets,client,perfclient
where
ca_clcode=3Dpc_clcode
and substr(ca_date,1,4)||substr(ca_date,6,2)=3D '200312'
AND pc_clcode=3Dcl_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'
and cl_decmkr=3D'32002590'
group by ca_clcode,ca_date
SQL> @expsql view
OPERATION OPTIONS OBJECT_NAME =POSITION
------------------------- ------------------------- -------------------- --=
SORT GROUP BY =
1
NESTED LOOPS =
1
NESTED LOOPS =
1
TABLE ACCESS BY INDEX ROWID CLIENT =
1
INDEX RANGE SCAN CL_DECMKR_IDX =
1
TABLE ACCESS BY INDEX ROWID PERFCLIENT =
2
INDEX UNIQUE SCAN PC_CLCODE_IDX =
1
TABLE ACCESS BY INDEX ROWID ME_CLIENT_ASSETS =
2
INDEX RANGE SCAN PK_ME_CLIENT_ASSETS =
1
Once again thanks for all your inputs that helped me make this query work e= fficiently.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling
Sent: June 25, 2004 1:00 PM
To: oracle-l_at_freelists.org
Subject: RE: Slow running Query.
In a case like this, the statistics on the tables, columns and indexes are=
vitally important and nobody could accurately reproduce those in a test=20
environment.
Can you export your statistics to a stattab table and post the export of th=
at=20
table to the same website?
Quoting "Kumar, Dharminder" <Dharminder.Kumar_at_Bmonb.com>:
>=20
> As mail server does not allow for SQL files to be attached to the email, I > =3D > have put the scripts for tables, views, queries in question at the > followin=3D > g web adress, incase somebody is interestig in doing tests. > http://dharm-renu.tripod.com/sqltuning.htm
--=20
regards
Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com
-- 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 Wed Jul 07 2004 - 10:20:58 CDT
 |
 |