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'; EXPLAIN PLAN (for CHOOSE session)=20 ------------ 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 and following is the EXPLAIN PLAN ( same for both CHOOSE, RULE). SQL> @expsql view OPERATION OPTIONS OBJECT_NAME = POSITION ------------------------- ------------------------- -------------------- --= -------- SELECT STATEMENT 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling Sent: June 25, 2004 1:00 PM To: oracle-l@xxxxxxxxxxxxx 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@xxxxxxxxx>: >=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 --=20 regards Wolfgang Breitling Oracle 7,8,8i,9i OCP DBA Centrex Consulting Corporation www.centrexcc.com ---------------------------------------------------------------- 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 ----------------------------------------------------------------- <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 -----------------------------------------------------------------