|
View previous topic :: View next topic
|
| Author |
Message |
Sowmya Ramachandra
New User
Joined: 11 May 2007
Posts: 20
Location: Canada
|
|
|
|
Hi,
I have the below cursor which needs to fine tuned. Will it help if I split the Query. i.e Having the Subquery as a different cursor and CUR1 as simple Sql. Any other suggestions plz let me know
DECLARE CUR1 CURSOR FOR
SELECT F1
,F2
,F3
,F4
FROM TAB1
WHERE F1 IN
(SELECT DISTINCT D2
FROM TAB2
WHERE D1 = :WS-D1
AND D2 > '' -
AND D3 = :WS-D3
AND D4 = :WS-D4
AND D5 > 0
AND D6 > 0
)
AND F5 = :WS-F5
AND F6 <> '10' |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
How does the subquery perform when it is not a subquery?
Depending on the volume of data needed to be examined to satisfy the subquery, the performance of the outer select may be dramatically different.
Suggest you work with your dba to look at the requirement and make sure your queries and your table/index definitions will properly support this requirement. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Try joining TAB1 and TAB2 using F1 and D2 instead of a subquery... but this depends on the respective index definitions and vol of data ... if you could post all data we might be able to help ... |
|
| Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
|
|
| One quick observation. You can remove the Distinct from your subquery. |
|
| Back to top |
|
|
|
|
