Home » RDBMS Server » Performance Tuning » FROM and WHERE clause
FROM and WHERE clause [message #65774] Wed, 22 December 2004 05:58 Go to next message
Milind Deshpande
Messages: 93
Registered: May 2004
Member
Hi Experts,

I got this tip from this forum only that SQL should be written in this way. I just want to know if this is correct and if wrong what is the correct way of writing the FROM and WHERE clause for an SQL in Oracle to get better performance. My version is 9.2.

Is this correct :- In the SQL
Order of tables in the FROM clause should be:
larger table
smaller table
Order of conditions should be:
Less selective
More selective

Please help.

Thanks in Advance.

Milind.
Re: FROM and WHERE clause [message #65776 is a reply to message #65774] Wed, 22 December 2004 07:38 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Order of tables in the FROM clause should be:
>>larger table
>>smaller table
>>Order of conditions should be:
>>Less selective
>>More selective

All above doesnt matter when you use CBO.
The order of tables in from DOES matter only when you use RBO
Re: FROM and WHERE clause - TYPO -ignore the previous [message #65777 is a reply to message #65774] Wed, 22 December 2004 07:52 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Seems i missed portions of the thread.

>>Order of tables in the FROM clause should be:
>>larger table
>>smaller table

actually the ORDER OF TABLES in FROM clause
doesnt matter when you use CBO.
The order of tables in from DOES matter only when you use RBO
Re: FROM and WHERE clause [message #65780 is a reply to message #65776] Thu, 23 December 2004 00:52 Go to previous message
Milind Deshpande
Messages: 93
Registered: May 2004
Member
Hi,

Thanks Mahesh for your response. My doubt is clear now.
Previous Topic: What influences cost?
Next Topic: Do Remote Table use Remote indexes ?
Goto Forum:
  


Current Time: Thu Mar 28 13:33:26 CDT 2024