Home » RDBMS Server » Performance Tuning » Need to perform INDEX RANGE SCAN instead of FTS (oracle 9.2.0.6.0 ,sunsolaris 9.5)
Need to perform INDEX RANGE SCAN instead of FTS [message #280680] Wed, 14 November 2007 08:59 Go to next message
kesavansundaram
Messages: 181
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]
Re: Need to perform INDEX RANGE SCAN instead of FTS [message #280685 is a reply to message #280680] Wed, 14 November 2007 09:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the kind of question I don't answer (nothing personal) because there is so much to say that it can't be explain in a forum (in my opinion).
Read How to Identify Performance Problem and Bottleneck .

Once again this is not AskMichel, please ask the whole community.

Regards
Michel

Re: Need to perform INDEX RANGE SCAN instead of FTS [message #280687 is a reply to message #280685] Wed, 14 November 2007 09:14 Go to previous message
kesavansundaram
Messages: 181
Registered: October 2007
Location: MUMBAI
Senior Member

Dear Sir,
Sorry for the incon(.). 
Hereafter I will create topic after referring the necessary documents for subject issues. 
Thank you,
kesavan.[CODE][/CODE]
Previous Topic: Query problem
Next Topic: Why such a difference in explain plans
Goto Forum:
  


Current Time: Sat Dec 10 05:01:17 CST 2016

Total time taken to generate the page: 0.23529 seconds