Need to perform INDEX RANGE SCAN instead of FTS [message #280680] |
Wed, 14 November 2007 08:59 |
kesavansundaram
Messages: 183 Registered: October 2007 Location: MUMBAI
|
Senior Member |
|
|
[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]
|
|
|
|
|