All Forums Database
Akshay.Shaha 4 posts Joined 02/13
24 Sep 2013
Correlated SubQuery needs Tuning.. INDEX and LENGTH functions causing problem

Hi All,
I have scenario like below:

Requirement - Need to select ID values those are present ( hidden)  in DESCR column. But no joining condition in both the tables.
Query I have used -

 
Problem Faced - Query is working correctly as per requirement. But in Production environment it getting rejected bcoz of some Product Join Rejection rule.
                            I tried running explain plan,  though with minimal of data it is showing 1 hours and 30 minutes for subquery only.
 
Can anyone please suggest how should subquery be tuned ?
 
PS : I can not use Cross join !! If I remove INDEX and Length function is Substr , query is working fine !!
 
Many Thanks in advance..!!
 

Akshay.Shaha 4 posts Joined 02/13
24 Sep 2013

Hi All,
I have scenario like below:

Requirement - Need to select ID values those are present ( hidden)  in DESCR column. But no joining condition in both the tables.
Query I have used -

 
Problem Faced - Query is working correctly as per requirement. But in Production environment it getting rejected bcoz of some Product Join Rejection rule.
                            I tried running explain plan,  though with minimal of data it is showing 1 hours and 30 minutes for subquery only.
 
Can anyone please suggest how should subquery be tuned ? I can not use Cross join !!
 
Many Thanks in advance..!!
 

M.Saeed Khurram 544 posts Joined 09/12
24 Sep 2013

Hi,
INDEX and SUBSTR functions impact the query performance, I suggest you should use a derived table instead of Subquery.
Perform all the INDEX and SUBSTR functionality inside the derived table and then use those derived columns in main query. 
 

Khurram

You must sign in to leave a comment.