[CODE]

Dear Michel, 

This is regarding query tuning for the below one : 

SELECT dfm.SOURCE, NVL (fld.last_user_id, 9999), source_name,
       period_ending_date, load_date, dfm.file_id            
FROM data_file_master dfm, file_load_detail fld
WHERE  fld.file_id =dfm.file_id 
  AND dfm.filename = 'x'


the above query, when we run in one database DB01 ( oracle ver: 9.2.0.6.0 ) , 
the explain plan shows INDEX RANGE SCAN and optimizer mode is FIRST_ROWS_1000.

the same query when we run in another database DB02 ( oracle ver: 9.2.0.6.0 ),
explain plan shows FULL TABLE SCAN and optmizer mode is RULE.

in both databases, index have been set properly for subject columns used in this query.

Further we forced the index by putting /* +index_name */ in the abv query...
but explain plan shows FULL TABLE SCAN ONLY IN DB02.

Kindly adv, is there any alternate to perform index range scan instead of full table scan...

if we changed optimizer mode to first_rows_1000, will it be ok otherwise ?[/CODE]

Dear Sir,
Sorry for the incon(.). 
Hereafter I will create topic after referring the necessary documents for subject issues. 
Thank you,
kesavan.[CODE][/CODE]