Home » RDBMS Server » Performance Tuning » join conditions for smaller /larger tables
join conditions for smaller /larger tables [message #65890] Mon, 24 January 2005 01:56 Go to next message
Neeraj Ranjan Rath
Messages: 1
Registered: January 2005
Junior Member
This is Neeraj from India.I have a query .Can anybody please help me in this regards?
Could anybody please explain when joining multiple tables the smallest table should be specified last

• Before Optimization
Select count(*)
From TShirt t, Shirt s, Clothing c,
Products p
Where
t.barcode=s.barcode and s.barcode = c.barcode
and c.barcode = p.barcode; Time Taken: 10.0 Sec

• After Optimization
Select count(*) From Products p, Clothing c, Shirt s, TShirt t Where t.barcode = s.barcode and s.barcode = c.barcode
and c.barcode = p.barcode; Time Taken : 2.1 Sec
Re: join conditions for smaller /larger tables [message #65891 is a reply to message #65890] Mon, 24 January 2005 02:25 Go to previous message
Tony Andrews
Messages: 29
Registered: January 2005
Junior Member
When using the ancient Rule Based Optimizer (RBO), the order of tables in the FROM clause is significant. The advice is "driving table last" rather than "smallest table last".

In 2005 it is to be hoped that you are in a position to use the Cost Based Optimizer (CBO), which couldn't care less what order your FROM clause is defined in!
Previous Topic: What if index using index is not sufficient
Next Topic: Indexes
Goto Forum:
  


Current Time: Thu Mar 28 17:38:48 CDT 2024