Home » RDBMS Server » Performance Tuning » Does the order of tables in the FROM clause affect the outcome of a query?
Does the order of tables in the FROM clause affect the outcome of a query? [message #365864] Sun, 27 February 2000 12:28 Go to next message
Mike Gruen
Messages: 2
Registered: February 2000
Junior Member
I have two queries which are exactly the same except for the order in which the tables are listed in the FROM clause. One works fine and the other hangs with no result. I am completely perplexed. Can anyone offer an explanation?
Thanks,
Mike Gruen
Re: Does the order of tables in the FROM clause affect the outcome of a query? [message #365867 is a reply to message #365864] Mon, 28 February 2000 05:14 Go to previous messageGo to next message
Thierry Van der Auwera
Messages: 44
Registered: January 2000
Member
Hallo Mike,

The result of the two queries will always be the same. But the time to accomplish can be different.
I will try to explain it by an example.
You have two tables : TAB_SMALL with 10 records, TAB_BIG with 10000000 records.
SELECT t1.*,t2.amount FROM tab_small t1, tab_big t2 WHERE t1.key = t2.key; (RESULT in 1 sec.)
OR
SELECT t1.*,t2.amount FROM tab_big t2,tab_small t1 WHERE t1.key = t2.key; (RESULT in 1 sec.)

Why : in the first Select, oracle reads 10 records from tab_small and try to find the 10 matching in tab_big.
so it does a full table on tab_small and an index search on tab_big.
In the second select, it will do the same but does now a full table on tab_big !!!!!

This example is just a stupid on, bud it give you an idea.

Oracle has two ways to calculate his best execution plan : RULE-based or COST-based.
To activate the COST-based, you must ANALYZE the tables and the indexes.
See also which parameter is in your INITdb.ora file. (RULE/COST/CHOOSE)
When some queries are not performing good, you can add HINTS to your sql.

Look in the documentation to find info on these topics.

Note: another example when a query is faster/slower than an other: Which fields you put in your SELECT clause!

It is not easy to explain everything in an E-Mail, but hope this gives you an idea. Look at the doc.

Greetings,

Thierry.
Re: Does the order of tables in the FROM clause affect the outcome of a query? [message #365870 is a reply to message #365867] Mon, 28 February 2000 14:59 Go to previous message
Mike Gruen
Messages: 2
Registered: February 2000
Junior Member
Thierry,

thanks very much for taking the time for the long answer. It was very good and based on the info I've already searched and found some relevant info.

BR,
Mike Gruen
Previous Topic: I/O configuaration
Next Topic: Order of tables in the query..
Goto Forum:
  


Current Time: Sat Jan 23 12:03:35 CST 2021