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