| ORDER BY causing Performance problems [message #20921] |
Mon, 01 July 2002 18:44  |
Mack Sundar
Messages: 20
Registered: July 2002
|
Junior Member |
|
|
I have a scenario with an SQL as follows:
SELECT A.columnA1, A.columnA2,
B.columnB1, B.columnB2
FROM A, B
WHERE A.columnA1 = B.columnB1
ORDER BY A.columnA2
Each of these tables has about 50K records and there are indexes defined on A.columnA1, B.columnB1 and A.columnA2.
When executing this query, the response time of the query without the ORDER BY is about 10ms. But with the ORDER BY, it is about 12secs.
SORT_AREA_SIZE = 100 MB
OPTIMIZER_MODE = FIRST_ROWS
Explain Plan shows me that Oracle 8i is doing a full table scan of A and an index scan of B.
How do I fix the response time of this query?
-Mahesh
|
|
|
|
| Re: ORDER BY causing Performance problems [message #20937 is a reply to message #20921] |
Tue, 02 July 2002 07:26  |
vipin
Messages: 40
Registered: May 2002
|
Member |
|
|
Hi,
I doubt if you can improve. An orderby basically does a sort in the temporary tablespace after selecting the records based on the where clause. So, the best way is to try to avoid it.
As for as the optimizer mode - try to set it to rule and see the explain plan. It should use the index. But in this case the optimizer feels that it is much faster with a full scan than with an index.
vipin.
|
|
|
|
| Re: ORDER BY causing Performance problems [message #20944 is a reply to message #20921] |
Tue, 02 July 2002 09:14 |
Mack Sundar
Messages: 20
Registered: July 2002
|
Junior Member |
|
|
The main problem that I am facing is the following:
The query is returning is 4000 records. When I sort another table with the same number of tables, it takes just about 15ms extra.
But when the joins happen with another table, the sort seems to be running for about 15 secs and the CPU utilization is also extremely high.
It sounds ridiculous but it almost seems to me that Oracle is trying to sort each table first before applying the WHERE clause!!!
Any help would be appreciated.
-Mahesh
|
|
|
|
| Re: ORDER BY causing Performance problems [message #20946 is a reply to message #20921] |
Tue, 02 July 2002 10:32 |
Mack Sundar
Messages: 20
Registered: July 2002
|
Junior Member |
|
|
The main problem that I am facing is the following:
The query is returning is 4000 records. When I sort another table with the same number of tables, it takes just about 15ms extra.
But when the joins happen with another table, the sort seems to be running for about 15 secs and the CPU utilization is also extremely high.
It sounds ridiculous but it almost seems to me that Oracle is trying to sort each table first before applying the WHERE clause!!!
Any help would be appreciated.
-Mahesh
|
|
|
|
| Re: ORDER BY causing Performance problems [message #20948 is a reply to message #20921] |
Tue, 02 July 2002 11:06 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Chances are that you have an index on A.columnA2 -try removing that if that's acceptible. I've seen the "order by" cause that index to be chosen and maybe the join indexes not to be used.
You should do an explain plan (or use autotrace) with and without the order by.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
