Can you try removing the where condition and add it as a AND in the join.
FROM XYZ_TBL A
INNER JOIN PQR_TBL B
What's the Explain and the actual resource usage from QryLogStepsV?
The Tables are having millions of records. If we simply select and execute the subquery it will give some billions of records
If you got millions before the join, but billions after it, you do something wrong. From which table is S_COL?
Which statistics exist?