Correlated SubQuery needs Tuning.. INDEX and LENGTH functions causing problem

Database
Enthusiast

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..!!

2 REPLIES
Enthusiast

Re: 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 ? I can not use Cross join !!

Many Thanks in advance..!!

Enthusiast

Re: Correlated SubQuery needs Tuning.. INDEX and LENGTH functions causing problem

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