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 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,

1 REPLY
Junior Contributor

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

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