| SELECT doubt [message #161159] |
Thu, 02 March 2006 05:42  |
frank.svs
Messages: 162
Registered: February 2006
|
Senior Member |
|
|
Hi all,
People always the choose the table with less no of rows as driving table while join. Can i know the reason why.
Can anyone give one example.
Thank Q .
|
|
|
|
| Re: SELECT doubt [message #161206 is a reply to message #161159] |
Thu, 02 March 2006 09:58  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Actually, it is the table with fewer rows after the filter conditions have been applied, that should be used as the driving table. The reason is that the smaller the number of rows that you are working with, the faster it can process them. The earlier the point at which you narrow down that number of rows, the quicker it will process.
If table1 has 1,000 rows and table2 has 100,000 rows, but only 10 of the rows in table2 satisfy the condition "where filter_column = some_value" and you are running the following query with appropriate indexes on the id column:
select table1.some_column, table2.another_column
from table1, table2
where table1.id = table2.id
and table2.filter_column = some_value;
then table2 will be your driving table, because the filter condition can limit it to 10 rows before joining to table1.
However, this is a moot issue when using the cost-based optimizer (CBO), since it will select the driving table for you.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
