Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table_1(col1 primary key,col2,col3)
  as select 
        level,
        trunc(level/10) as col2,
        rpad('x',100) as col3
     from dual
     connect by level<=10000;
10000 rows affected

create table table_2(col1,col2,col3)
  as select 
        level,
        trunc(level/10) as col2,
        rpad('x',100) as col3
     from dual
     connect by level<=10000;
10000 rows affected

create index BAD_INDEX on table_2(col2,col1);
explain plan for
Select
 b.col1,
 b.col2,
 b.col3 
FROM
  (Select
    a.col1,
    a.col2,
    a.col3
  FROM 
   (SELECT
       t.col1,
       t.col2,
       t.col3
    FROM table_1 t
    JOIN table_2 t2
         on t.col1 = t2.col1
    WHERE
        t2.col2=:bind
   ) a
) b;
select * from table(dbms_xplan.display('','','all +outline +hint_report'));

PLAN_TABLE_OUTPUT

Plan hash value: 32162746

---------------------------------------------------------------------------------------------

| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |              |    10 |  1170 |    12   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                |              |    10 |  1170 |    12   (0)| 00:00:01 |

|   2 |   NESTED LOOPS               |              |    10 |  1170 |    12   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | BAD_INDEX    |    10 |    80 |     2   (0)| 00:00:01 |

|*  4 |    INDEX UNIQUE SCAN         | SYS_C0040454 |     1 |       |     0   (0)| 00:00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| TABLE_1      |     1 |   109 |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$535BA485

   3 - SEL$535BA485 / "T2"@"SEL$3"

   4 - SEL$535BA485 / "T"@"SEL$3"

   5 - SEL$535BA485 / "T"@"SEL$3"

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      NLJ_BATCHING(@"SEL$535BA485" "T"@"SEL$3")

      USE_NL(@"SEL$535BA485" "T"@"SEL$3")

      LEADING(@"SEL$535BA485" "T2"@"SEL$3" "T"@"SEL$3")

      INDEX(@"SEL$535BA485" "T"@"SEL$3" ("TABLE_1"."COL1"))

      INDEX(@"SEL$535BA485" "T2"@"SEL$3" ("TABLE_2"."COL2" "TABLE_2"."COL1"))

      OUTLINE(@"SEL$3")

      OUTLINE(@"SEL$4")

      MERGE(@"SEL$3" >"SEL$4")

      OUTLINE(@"SEL$37633EB5")

      OUTLINE(@"SEL$2")

      MERGE(@"SEL$37633EB5" >"SEL$2")

      OUTLINE(@"SEL$BB1798A6")

      OUTLINE(@"SEL$1")

      MERGE(@"SEL$BB1798A6" >"SEL$1")

      OUTLINE_LEAF(@"SEL$535BA485")

      ALL_ROWS

      DB_VERSION('21.1.0')

      OPTIMIZER_FEATURES_ENABLE('21.1.0')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("T2"."COL2"=TO_NUMBER(:BIND))

   4 - access("T"."COL1"="T2"."COL1")

Column Projection Information (identified by operation id):

-----------------------------------------------------------

   1 - (#keys=0) "T2"."COL1"[NUMBER,22], "T"."COL1"[NUMBER,22],

       "T"."COL2"[NUMBER,22], "T"."COL3"[VARCHAR2,100]

   2 - (#keys=0) "T2"."COL1"[NUMBER,22], "T".ROWID[ROWID,10], "T"."COL1"[NUMBER,22]

   3 - "T2"."COL1"[NUMBER,22]

   4 - "T".ROWID[ROWID,10], "T"."COL1"[NUMBER,22]

   5 - "T"."COL2"[NUMBER,22], "T"."COL3"[VARCHAR2,100]

Note

-----

   - this is an adaptive plan

explain plan for
Select--+ NO_INDEX(@"SEL$535BA485" "T2"@"SEL$3")
 b.col1,
 b.col2,
 b.col3 
FROM
  (Select
    a.col1,
    a.col2,
    a.col3
  FROM 
   (SELECT
       t.col1,
       t.col2,
       t.col3
    FROM table_1 t
    JOIN table_2 t2
         on t.col1 = t2.col1
    WHERE
        t2.col2=:bind
   ) a
) b;
select * from table(dbms_xplan.display('','','all +outline +hint_report'));

PLAN_TABLE_OUTPUT

Plan hash value: 2872227011

---------------------------------------------------------------------------------------------

| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |              |    10 |  1170 |    58   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                |              |    10 |  1170 |    58   (0)| 00:00:01 |

|   2 |   NESTED LOOPS               |              |    10 |  1170 |    58   (0)| 00:00:01 |

|*  3 |    TABLE ACCESS FULL         | TABLE_2      |    10 |    80 |    48   (0)| 00:00:01 |

|*  4 |    INDEX UNIQUE SCAN         | SYS_C0040454 |     1 |       |     0   (0)| 00:00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| TABLE_1      |     1 |   109 |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$535BA485

   3 - SEL$535BA485 / "T2"@"SEL$3"

   4 - SEL$535BA485 / "T"@"SEL$3"

   5 - SEL$535BA485 / "T"@"SEL$3"

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      NLJ_BATCHING(@"SEL$535BA485" "T"@"SEL$3")

      USE_NL(@"SEL$535BA485" "T"@"SEL$3")

      LEADING(@"SEL$535BA485" "T2"@"SEL$3" "T"@"SEL$3")

      INDEX(@"SEL$535BA485" "T"@"SEL$3" ("TABLE_1"."COL1"))

      FULL(@"SEL$535BA485" "T2"@"SEL$3")

      OUTLINE(@"SEL$3")

      OUTLINE(@"SEL$4")

      MERGE(@"SEL$3" >"SEL$4")

      OUTLINE(@"SEL$37633EB5")

      OUTLINE(@"SEL$2")

      MERGE(@"SEL$37633EB5" >"SEL$2")

      OUTLINE(@"SEL$BB1798A6")

      OUTLINE(@"SEL$1")

      MERGE(@"SEL$BB1798A6" >"SEL$1")

      OUTLINE_LEAF(@"SEL$535BA485")

      ALL_ROWS

      DB_VERSION('21.1.0')

      OPTIMIZER_FEATURES_ENABLE('21.1.0')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("T2"."COL2"=TO_NUMBER(:BIND))

   4 - access("T"."COL1"="T2"."COL1")

Column Projection Information (identified by operation id):

-----------------------------------------------------------

   1 - (#keys=0) "T2"."COL1"[NUMBER,22], "T"."COL1"[NUMBER,22],

       "T"."COL2"[NUMBER,22], "T"."COL3"[VARCHAR2,100]

   2 - (#keys=0) "T2"."COL1"[NUMBER,22], "T".ROWID[ROWID,10], "T"."COL1"[NUMBER,22]

   3 - "T2"."COL1"[NUMBER,22]

   4 - "T".ROWID[ROWID,10], "T"."COL1"[NUMBER,22]

   5 - "T"."COL2"[NUMBER,22], "T"."COL3"[VARCHAR2,100]

Hint Report (identified by operation id / Query Block Name / Object Alias):

Total hints for statement: 1

---------------------------------------------------------------------------

   3 -  SEL$535BA485 / "T2"@"SEL$3"

           -  NO_INDEX(@"SEL$535BA485" "T2"@"SEL$3")

Note

-----

   - this is an adaptive plan

explain plan for
Select--+ NO_INDEX(b.a.t2)
 b.col1,
 b.col2,
 b.col3 
FROM
  (Select
    a.col1,
    a.col2,
    a.col3
  FROM 
   (SELECT
       t.col1,
       t.col2,
       t.col3
    FROM table_1 t
    JOIN table_2 t2
         on t.col1 = t2.col1
    WHERE
        t2.col2=:bind
   ) a
) b;
select * from table(dbms_xplan.display('','','all +outline +hint_report'));

PLAN_TABLE_OUTPUT

Plan hash value: 2872227011

---------------------------------------------------------------------------------------------

| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |              |    10 |  1170 |    58   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                |              |    10 |  1170 |    58   (0)| 00:00:01 |

|   2 |   NESTED LOOPS               |              |    10 |  1170 |    58   (0)| 00:00:01 |

|*  3 |    TABLE ACCESS FULL         | TABLE_2      |    10 |    80 |    48   (0)| 00:00:01 |

|*  4 |    INDEX UNIQUE SCAN         | SYS_C0040454 |     1 |       |     0   (0)| 00:00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| TABLE_1      |     1 |   109 |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$535BA485

   3 - SEL$535BA485 / "T2"@"SEL$3"

   4 - SEL$535BA485 / "T"@"SEL$3"

   5 - SEL$535BA485 / "T"@"SEL$3"

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      NLJ_BATCHING(@"SEL$535BA485" "T"@"SEL$3")

      USE_NL(@"SEL$535BA485" "T"@"SEL$3")

      LEADING(@"SEL$535BA485" "T2"@"SEL$3" "T"@"SEL$3")

      INDEX(@"SEL$535BA485" "T"@"SEL$3" ("TABLE_1"."COL1"))

      FULL(@"SEL$535BA485" "T2"@"SEL$3")

      OUTLINE(@"SEL$3")

      OUTLINE(@"SEL$4")

      MERGE(@"SEL$3" >"SEL$4")

      OUTLINE(@"SEL$37633EB5")

      OUTLINE(@"SEL$2")

      MERGE(@"SEL$37633EB5" >"SEL$2")

      OUTLINE(@"SEL$BB1798A6")

      OUTLINE(@"SEL$1")

      MERGE(@"SEL$BB1798A6" >"SEL$1")

      OUTLINE_LEAF(@"SEL$535BA485")

      ALL_ROWS

      DB_VERSION('21.1.0')

      OPTIMIZER_FEATURES_ENABLE('21.1.0')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("T2"."COL2"=TO_NUMBER(:BIND))

   4 - access("T"."COL1"="T2"."COL1")

Column Projection Information (identified by operation id):

-----------------------------------------------------------

   1 - (#keys=0) "T2"."COL1"[NUMBER,22], "T"."COL1"[NUMBER,22],

       "T"."COL2"[NUMBER,22], "T"."COL3"[VARCHAR2,100]

   2 - (#keys=0) "T2"."COL1"[NUMBER,22], "T".ROWID[ROWID,10], "T"."COL1"[NUMBER,22]

   3 - "T2"."COL1"[NUMBER,22]

   4 - "T".ROWID[ROWID,10], "T"."COL1"[NUMBER,22]

   5 - "T"."COL2"[NUMBER,22], "T"."COL3"[VARCHAR2,100]

Hint Report (identified by operation id / Query Block Name / Object Alias):

Total hints for statement: 1

---------------------------------------------------------------------------

   3 -  SEL$535BA485 / "T2"@"SEL$3"

           -  NO_INDEX(b.a.t2)

Note

-----

   - this is an adaptive plan