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

Hi All
I have two tables like below with sample data:
 
ACCNT_TABLE    |   REF_TABLE
   ID_COL            |     REF_COL
    123                  |     Desc_123
    100                  |     200_payout
    200                  |     300-desc%
    300               
 
Requirement - Need to select ID_COL column values (from ACCNT_TABLE) those are present ( hidden)  in REF_COL column (in REF_TABLE). But no joining condition in both the tables.
 
Query I have used –
   SELECT * FROM ACCNT_TABLE A WHERE ID_COL IN
    ( SELECT SUBSTR(R.REF_COL , INDEX(R.REF_COL , A.ID_COL),LENGTH(A.ID_COL))
              FROM REF_TABLE R   WHERE INDEX(R.REF_COL , A.ID_COL) > 0) ;
 
Problem Faced - Query is fetching data 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’t use Cross join !! If I remove INDEX and LENGTH function in Substr, Query works fine!!
 
Many Thanks,
 
 
 

dnoeth 4628 posts Joined 11/04
25 Sep 2013

PS: I can’t use Cross join !!

Well, you don't call it a Cross Join, but it is a cross join, when you explain it you'll see a PRODUCT join.
No matter how you rewrite it using a Correlated Subquery or a Cross Join or LIKE instead of INDEX/LENGTH.
 
The only tuning will be a properly normalized REF_TABLE.
 
Dieter

Dieter

You must sign in to leave a comment.