| Tune a sql statement with 5 joint tables [message #20293] |
Sat, 11 May 2002 09:04  |
Loo Bin Soon
Messages: 8
Registered: April 2002
|
Junior Member |
|
|
Hi anyone can help.
Currently I had a sql statemet retrieving records from 5 different tables. Statement look like this:
select a.a1, b.b1, c.c1, d.d1, e.e1 from
table a, table b, table c, table d, table e
where a.a2=b.b2 and
b.b2=c.c2 and
c.c2=d.d2 and
d.d2=e.e2 and
group by a.a1, b.b1, c.c1, d.d1, e.e1
The query took a very long time to comlete, so is there any way to tune this statement.
Thank you.
|
|
|
|
| Re: Tune a sql statement with 5 joint tables [message #20304 is a reply to message #20293] |
Sun, 12 May 2002 21:12  |
Satish Shrikhande
Messages: 167
Registered: October 2001
|
Senior Member |
|
|
Now look at your SQL statement , you are using
GROUP BY - it ask to sort the data , check your sort_area_size parameter and see wheather the sorting is in memory or is it on disk , check your temporary table also which you have assigned to the user .
Keep
The smallest table at last in the FROM clause
The largest table column at first in the WHERE clause
|
|
|
|
| Re: Tune a sql statement with 5 joint tables [message #20307 is a reply to message #20293] |
Mon, 13 May 2002 00:52 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
The first thing to do is to ensure that columns a2,b2,c2... are indexed and that the tables have been analyzed.
If you are using 8i, you can create indexes on columns (a2,a1), columns (b2,b1) etc and the query will never need to look at the tables as it can get all the data from the index.
Other than that, try looking at an explain plan for the query.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
