The issue:
Query:
select
* from t
where not exists (select
1 from c
where ban = t.ban
and subscriber_no = t.subscriber_no
and actv_bill_seq_no = t.bill_seq_no;
The execution plan will always be a FILTER.
By adding the UNNEST hint, we can actually force the optimizer to join the 2 tables, so we can hint for hash or nl joins.
select
* from t
where not exists (select /*+ unnest */
1 from c
where ban = t.ban
and subscriber_no = t.subscriber_no
and actv_bill_seq_no = t.bill_seq_no
