I want to take advantage of the subquery result because it return just a few rows to filter the TABLE_A and remove the INDEX FULL SCAN
SELECT /*+ index(A IDX_TABLE_A) */ *
FROM TABLE_A A
WHERE EXISTS (SELECT /*+ merge_sj */ 'X' FROM VIEW_B_B S WHERE A.INDV_ID = S.INDV_ID);
The plan is:
Rows Row Source Operation
------- ---------------------------------------------------
10 MERGE JOIN SEMI
19183 TABLE ACCESS BY INDEX ROWID TABLE_A
19183 INDEX FULL SCAN (object id 36600)
10 SORT UNIQUE
1 VIEW VIEW_B
1 UNION-ALL
0 FILTER
0 INDEX FULL SCAN (object id 37701)
0 FILTER
0 NESTED LOOPS
0 INDEX RANGE SCAN (object id 36602)
0 INDEX UNIQUE SCAN (object id 37701)
1 FILTER
1 INDEX UNIQUE SCAN (object id 37701)
Thanks!