|
|
| 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   |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
