Order of tables in joins

[Sri]

I am writing a download process in which i have a condition where i
need to join four tables. Each table have lot of data say around
300000 recs.
my question is when i am doing the joins on the columns is there any
specific order i need to follow.

for example
My original query looks like this

select a.col1,a.col2
from a
inner join b
on a.id1=b.id1
and a.id2=b.id2
inner join c
on c.id1=b.id1
and c.id2=b.id2
inner join d
on d.id1=c.id1
and d.id2=c.id2

If i change the query like below... does it make any difference

select a.col1,a.col2
from a
inner join b
on b.id1=a.id1
and b.id2=a.id2

inner join c
on c.id1=a.id1
and c.id2=a.id2

inner join d
on d.id1=a.id1
and d.id2=a.id2
Any help is appreciated.
Thanks

Sri

[Dave Hau]

If your statistics are up to date, then it doesn't matter. The query
optimizer will determine the best join order for you. Ideally you want the
earlier joins to be more selective so that the later joins can have an
easier time.

If your statistics are out of date, then you can either update your
statistics ("update statistics mytable with fullscan"), or determine the
best join order yourself and include a "force order" hint in your select
statement.

Regards,
Dave

"Sri" <sr*******@hotmail.com> wrote in message
news:9b**************************@posting.google.c om...

I am writing a download process in which i have a condition where i
need to join four tables. Each table have lot of data say around
300000 recs.
my question is when i am doing the joins on the columns is there any
specific order i need to follow.

for example
My original query looks like this

select a.col1,a.col2
from a
inner join b
on a.id1=b.id1
and a.id2=b.id2
inner join c
on c.id1=b.id1
and c.id2=b.id2
inner join d
on d.id1=c.id1
and d.id2=c.id2

If i change the query like below... does it make any difference

select a.col1,a.col2
from a
inner join b
on b.id1=a.id1
and b.id2=a.id2

inner join c
on c.id1=a.id1
and c.id2=a.id2

inner join d
on d.id1=a.id1
and d.id2=a.id2
Any help is appreciated.
Thanks

Sri

[--CELKO--]

>> my question is when i am doing the joins on the columns is there
any specific order i need to follow. <<

You don't understand the basic idea of a non-procedural language like
SQL. You tell the compiler what your want and it figures out how to
find it.

In rare cases, if you have a large number of tables of nearly the same
size and indexing, then the optimizer can have so many permutations to
consider that it will make a sub-optimal decision about the order of
using the tables.

[Greg D. Moore \(Strider\)]

"--CELKO--" <jo*******@northface.edu> wrote in message
news:a2**************************@posting.google.c om...

my question is when i am doing the joins on the columns is there

any specific order i need to follow. <<

You don't understand the basic idea of a non-procedural language like
SQL. You tell the compiler what your want and it figures out how to
find it.

In rare cases, if you have a large number of tables of nearly the same
size and indexing, then the optimizer can have so many permutations to
consider that it will make a sub-optimal decision about the order of
using the tables.

Note in SQL 6.5 I believe join order did matter (mostly because the
optimizer wasn't as smart as now.)

These days, I haven't seen any difference in the join order in terms of
performance.

[Joe Celko]

>> Note in SQL 6.5 I believe join order did matter (mostly because the
optimizer wasn't as smart as now.) <<

The magic number in early SQL product was 5 tables. This is also a
human processing limit, too. The reason is that the permutations jump
at five:

tables permutations
====================
1 1
2 2
3 6
4 24
5 120

Oracle used to process tables in the reverse order they appeared in the
FROM clause before they got an optimizer; you could get some really
weird performance from that design flaw. People tended to put the
"minor" tables at the end of the list, so main storage would fill up
with a larger table and the smaller tables would be paged in and out.
Doing it the other way, the smaller tables went into main storage and
stayed there while the larger tables were scanned and paged in.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!