Simple (and artificial) example how index-access and table access based on its row-id can be de-coupled.

index_decoupled.sql

create index DEPT_DNAME_DEPTNO on DEPT(DNAME,DEPTNO);

With DEPT_DNAME_NO as (
SELECT /*+ qb_name(DEPT_DNAME_NO) no_merge */ 
       d1.dname, d1.deptno, d1.rowid as drid
FROM DEPT d1
WHERE d1.DNAME='RESEARCH'),
     EMP_DEP_DNAME_ENAME as (
SELECT /*+ qb_name(EMP_DEP_DNAME_ENAME) no_merge*/ 
     dw.dname, dw.deptno, dw.drid,
     e1.ename, e1.rowid as erid
FROM EMP e1, DEPT_DNAME_NO dw
WHERE e1.deptno = dw.deptno ),
     DEPT_LOC_DNAME_EMPENAME as (
SELECT /*+ qb_name(DEPT_LOC_DNAME_EMPENAME) no_Merge
USE_NL(@"SEL$1" "EDW"@"SEL$1")
USE_NL(@"SEL$1" "D2"@"SEL$1")
USE_NL(@"EMP_DEP_DNAME_ENAME" "E1"@"EMP_DEP_DNAME_ENAME")

BX3
*/ 
     d2.LOC, edw.dname, edw.ename
FROM DEPT d2,
     EMP_DEP_DNAME_ENAME edw     
WHERE d2.rowid = edw.drid)
SELECT /*+ sb_name(MAIN) NO_MERGE */ dlde.* 
FROM DEPT_LOC_DNAME_EMPENAME dlde
/

LOC           DNAME          ENAME     
------------- -------------- ----------
DALLAS        RESEARCH       SMITH     
DALLAS        RESEARCH       JONES     
DALLAS        RESEARCH       SCOTT     
DALLAS        RESEARCH       ADAMS     
DALLAS        RESEARCH       FORD      


select t.* 
from (select sql_id from v$sql where sql_text like '%BX3% dlde' and rownum = 1) st,
table(dbms_xplan.display_cursor(sql_id=>st.sql_id, format=>'ALLSTATS LAST +outline')) t;


------------------------------------------
SQL_ID  bhuuuc8scxpf9, child number 0
-------------------------------------
With DEPT_DNAME_NO as ( SELECT /*+ qb_name(DEPT_DNAME_NO) no_merge */   
      d1.dname, d1.deptno, d1.rowid as drid FROM DEPT d1 WHERE 
d1.DNAME=:"SYS_B_0"),      EMP_DEP_DNAME_ENAME as ( SELECT /*+ 
qb_name(EMP_DEP_DNAME_ENAME) no_merge*/       dw.dname, dw.deptno, 
dw.drid,      e1.ename, e1.rowid as erid FROM EMP e1, DEPT_DNAME_NO dw 
WHERE e1.deptno = dw.deptno ),      DEPT_LOC_DNAME_EMPENAME as ( SELECT 
/*+ qb_name(DEPT_LOC_DNAME_EMPENAME) no_Merge USE_NL(@"SEL$1" 
"EDW"@"SEL$1") USE_NL(@"SEL$1" "D2"@"SEL$1") 
USE_NL(@"EMP_DEP_DNAME_ENAME" "E1"@"EMP_DEP_DNAME_ENAME")  BX3 */       
d2.LOC, edw.dname, edw.ename FROM DEPT d2,      EMP_DEP_DNAME_ENAME edw 
     WHERE d2.rowid = edw.drid) SELECT /*+ sb_name(MAIN) NO_MERGE */ 
dlde.*  FROM DEPT_LOC_DNAME_EMPENAME dlde
 
Plan hash value: 1536945150
 
-------------------------------------------------------------------
| Id  | Operation                    | Name              | E-Rows |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |        |
|   1 |  VIEW                        |                   |      5 |
|   2 |   NESTED LOOPS               |                   |      5 |
|   3 |    VIEW                      |                   |      5 |
|   4 |     NESTED LOOPS             |                   |      5 |
|   5 |      VIEW                    |                   |      1 |
|*  6 |       INDEX RANGE SCAN       | DEPT_DNAME_DEPTNO |      1 |
|*  7 |      TABLE ACCESS FULL       | EMP               |      5 |
|   8 |    TABLE ACCESS BY USER ROWID| DEPT              |      1 |
-------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"DEPT_DNAME_NO")
      OUTLINE_LEAF(@"EMP_DEP_DNAME_ENAME")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$2")
      NO_ACCESS(@"SEL$2" "DLDE"@"SEL$2")
      NO_ACCESS(@"SEL$1" "EDW"@"SEL$1")
      ROWID(@"SEL$1" "D2"@"SEL$1")
      LEADING(@"SEL$1" "EDW"@"SEL$1" "D2"@"SEL$1")
      USE_NL(@"SEL$1" "D2"@"SEL$1")
      NO_ACCESS(@"EMP_DEP_DNAME_ENAME" "DW"@"EMP_DEP_DNAME_ENAME")
      FULL(@"EMP_DEP_DNAME_ENAME" "E1"@"EMP_DEP_DNAME_ENAME")
      LEADING(@"EMP_DEP_DNAME_ENAME" "DW"@"EMP_DEP_DNAME_ENAME" 
              "E1"@"EMP_DEP_DNAME_ENAME")
      USE_NL(@"EMP_DEP_DNAME_ENAME" "E1"@"EMP_DEP_DNAME_ENAME")
      INDEX(@"DEPT_DNAME_NO" "D1"@"DEPT_DNAME_NO" ("DEPT"."DNAME" 
              "DEPT"."DEPTNO"))
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("D1"."DNAME"=:SYS_B_0)
       filter("D1"."DNAME"=:SYS_B_0)
   7 - filter("E1"."DEPTNO"="DW"."DEPTNO")
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level