Performance issue with WHERE clause [message #524339] |
Fri, 23 September 2011 03:06 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Derek N
Messages: 80 Registered: September 2002
|
Member |
|
|
I have the following code with a sub query which takes 54.313 seconds to run in PL/SQL Developer:
SELECT *
FROM transfers a
WHERE a.location_code_from = 'ABC'
AND a.season = 2011
AND EXISTS (SELECT 'Y'
FROM available_transactions b
WHERE b.available_qty > 0
AND a.season = b.season
AND a.transfer_release_no = b.release_no);
When I change the code from a subquery to a standard join it takes .0234 seconds.
SELECT *
FROM transfers a, available_transactions b
WHERE a.location_code_from = 'ABC'
AND a.season = 2011
AND b.available_qty > 0
AND a.fin_year = b.fin_year
AND a.transfer_release_no = b.release_no;
My problem is that I have to use the code with the sub query as I need to use this code in the where clause of a block in forms. All indexes are fine. Are there any other options available?
|
|
|
|
|
|
|
Re: Performance issue with WHERE clause [message #524366 is a reply to message #524360] |
Fri, 23 September 2011 05:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Derek N
Messages: 80 Registered: September 2002
|
Member |
|
|
Changed my query to :
SELECT *
FROM transfers a
WHERE a.location_code_from = 'ABC'
AND a.season = 2011
AND (a.season, a.transfer_release_no) IN
(SELECT b.season, a.transfer_release_no
FROM available_transactions b
WHERE b.available_qty > 0)
It now works much faster. Now takes .828 seconds compared to 54.313 seconds. I used the asterisk as I was testing the performance via pl/sql developer. By the way can a standard join be used in the where clause of a form?
|
|
|
|