explain plan for
select * from acct_ly.OP_BILL_RENT_DETAIL_ITEM_T a /*set a.vip_flag=9 */
where exists (select 1 from acct_ly.target_cust_level b where a.acct_id=b.acct_id )
select * from table(dbms_xplan.display())

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL | OP_BILL_RENT_DETAIL_ITEM_T | | | |
|* 3 | INDEX RANGE SCAN | IX_TARGET_CUST_LEVEL | | | |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( EXISTS (SELECT 0 FROM "ACCT_LY"."TARGET_CUST_LEVEL" "B" WHERE
"B"."ACCT_ID"=:B1))
3 - access("B"."ACCT_ID"=:B1)

Note: rule based optimization

acct_ly.target_cust_level index(IX_TARGET_CUST_LEVEL)
-----------------------------------------------------------------------------
create index idx_op_acct_id on acct_ly.OP_BILL_RENT_DETAIL_ITEM_T(acct_id) parallel(degree 3) nologging
tablespace ys_owe_dat_2_1;
drop index acct_ly.IX_TARGET_CUST_LEVEL
------------------------------------------------------------------------------

explain plan for
select * from acct_ly.OP_BILL_RENT_DETAIL_ITEM_T /*set vip_flag=9*/ where rowid in
(select a.rowid from acct_ly.OP_BILL_RENT_DETAIL_ITEM_T a, acct_cc.target_cust_level b
where a.acct_id = b.acct_id)
select * from table(dbms_xplan.display()) ;

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | NESTED LOOPS | | | | |
| 2 | VIEW | VW_NSO_1 | | | |
| 3 | SORT UNIQUE | | | | |
| 4 | NESTED LOOPS | | | | |
| 5 | TABLE ACCESS FULL | TARGET_CUST_LEVEL | | | |
|* 6 | INDEX RANGE SCAN | IDX_OP_ACCT_ID | | | |
| 7 | TABLE ACCESS BY USER ROWID| OP_BILL_RENT_DETAIL_ITEM_T | | | |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("A"."ACCT_ID"="B"."ACCT_ID")

Note: rule based optimization