OraFAQ Forum: Performance Tuning » FROM and WHERE clause

Home » RDBMS Server » Performance Tuning » FROM and WHERE clause

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

FROM and WHERE clause [message #65774]

Wed, 22 December 2004 05:58

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.

Report message to a moderator

Re: FROM and WHERE clause [message #65776 is a reply to message #65774]

Wed, 22 December 2004 07:38

Mahesh Rajendran
Messages: 10708
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

Report message to a moderator

Re: FROM and WHERE clause - TYPO -ignore the previous [message #65777 is a reply to message #65774]

Wed, 22 December 2004 07:52

Mahesh Rajendran
Messages: 10708
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

Report message to a moderator

Re: FROM and WHERE clause [message #65780 is a reply to message #65776]

Thu, 23 December 2004 00:52

Milind Deshpande
Messages: 93
Registered: May 2004

Member

Hi,

Thanks Mahesh for your response. My doubt is clear now.

Report message to a moderator

Previous Topic:	What influences cost?
Next Topic:	Do Remote Table use Remote indexes ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 11:24:04 CST 2024