I have a query structure where I've got 3 nested queries (e.g. select * from (select * from (select * from table ) a ) b
There's an index in trying to avoid, because it was causing long run times. It appeared twice in the plan. I used a noindex hint on the innermost query to remove one instance of it, but I can't seem to get rid of the second instance.
Is there a way to use noindex on the outer queries of a nested query?
Edit: More detailed query example. Note, in the real query there are GROUP BY statements on every level, but a WHERE statement only in the innermost query.
Select /* parallel(8) no_index(b,bad_index) */
b.col1
b.col2
b.col3
FROM
(Select /* parallel(8) no_index(a,bad_index) */
a.col1
a.col2
a.col3
FROM
(SELECT /* parallel(8) no_index(t,bad_index) */
t.col1
t.col2
t.col3
FROM table_1 t
INNER JOIN table_2 t2
ON t.col1=t2.col1
WHERE
t.col2='value' ) a
) b
Plan is below. Note, this is the actual execution plan that I changed the names of the tables for anonymity. There are joins I didn't include in the example for simplicity's sake. Lines 72/73 include the index that I'm trying to avoid. I added the predicate information.
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84613730 | 20138067740 | 5534656 | 00:03:37 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10014 | 84613730 | 20138067740 | 5534656 | 00:03:37 |
| 3 | SORT GROUP BY | | 84613730 | 20138067740 | 5534656 | 00:03:37 |
| 4 | PX RECEIVE | | 84613730 | 20138067740 | 5534656 | 00:03:37 |
| 5 | PX SEND HASH | :TQ10013 | 84613730 | 20138067740 | 5534656 | 00:03:37 |
| 6 | SORT GROUP BY | | 84613730 | 20138067740 | 5534656 | 00:03:37 |
| 7 | PX RECEIVE | | 84613730 | 20138067740 | 5534656 | 00:03:37 |
| 8 | PX SEND HASH | :TQ10012 | 84613730 | 20138067740 | 5534656 | 00:03:37 |
| 9 | SORT GROUP BY | | 84613730 | 20138067740 | 5534656 | 00:03:37 |
| 10 | VIEW | | 84613730 | 20138067740 | 5135062 | 00:03:21 |
| 11 | WINDOW SORT | | 84613730 | 22338024720 | 5135062 | 00:03:21 |
| 12 | PX RECEIVE | | 84613730 | 22338024720 | 5135062 | 00:03:21 |
| 13 | PX SEND HASH | :TQ10011 | 84613730 | 22338024720 | 5135062 | 00:03:21 |
| 14 | WINDOW BUFFER | | 84613730 | 22338024720 | 5135062 | 00:03:21 |
| 15 | SORT GROUP BY | | 84613730 | 22338024720 | 5135062 | 00:03:21 |
| 16 | PX RECEIVE | | 84613730 | 22338024720 | 5135062 | 00:03:21 |
| 17 | PX SEND HASH | :TQ10010 | 84613730 | 22338024720 | 5135062 | 00:03:21 |
| 18 | HASH GROUP BY | | 84613730 | 22338024720 | 5135062 | 00:03:21 |
| 19 | VIEW | | 84613730 | 22338024720 | 4201665 | 00:02:45 |
| 20 | WINDOW SORT | | 84613730 | 33760878270 | 4201665 | 00:02:45 |
| 21 | PX RECEIVE | | 84613730 | 33760878270 | 4201665 | 00:02:45 |
| 22 | PX SEND HASH | :TQ10009 | 84613730 | 33760878270 | 4201665 | 00:02:45 |
| 23 | WINDOW BUFFER | | 84613730 | 33760878270 | 4201665 | 00:02:45 |
| 24 | SORT GROUP BY | | 84613730 | 33760878270 | 4201665 | 00:02:45 |
| 25 | PX RECEIVE | | 84613730 | 33760878270 | 4201665 | 00:02:45 |
| 26 | PX SEND HASH | :TQ10008 | 84613730 | 33760878270 | 4201665 | 00:02:45 |
| 27 | HASH GROUP BY | | 84613730 | 33760878270 | 4201665 | 00:02:45 |
| * 28 | HASH JOIN | | 84613730 | 33760878270 | 2809746 | 00:01:50 |
| 29 | PX RECEIVE | | 48812 | 1366736 | 2 | 00:00:01 |
| 30 | PX SEND BROADCAST | :TQ10003 | 48812 | 1366736 | 2 | 00:00:01 |
| 31 | PX BLOCK ITERATOR | | 48812 | 1366736 | 2 | 00:00:01 |
| 32 | TABLE ACCESS STORAGE FULL | Table5 | 48812 | 1366736 | 2 | 00:00:01 |
| * 33 | HASH JOIN RIGHT OUTER | | 84350244 | 31293940524 | 2809707 | 00:01:50 |
| 34 | PX RECEIVE | | 48812 | 1025052 | 2 | 00:00:01 |
| 35 | PX SEND BROADCAST | :TQ10004 | 48812 | 1025052 | 2 | 00:00:01 |
| 36 | PX BLOCK ITERATOR | | 48812 | 1025052 | 2 | 00:00:01 |
| 37 | TABLE ACCESS STORAGE FULL | Table5 | 48812 | 1025052 | 2 | 00:00:01 |
| * 38 | HASH JOIN RIGHT OUTER | | 84087578 | 29430652300 | 2809669 | 00:01:50 |
| 39 | PX RECEIVE | | 37177 | 669186 | 3 | 00:00:01 |
| 40 | PX SEND BROADCAST | :TQ10005 | 37177 | 669186 | 3 | 00:00:01 |
| 41 | PX BLOCK ITERATOR | | 37177 | 669186 | 3 | 00:00:01 |
| 42 | TABLE ACCESS STORAGE FULL | Table4 | 37177 | 669186 | 3 | 00:00:01 |
| * 43 | HASH JOIN | | 84087578 | 27917075896 | 2809629 | 00:01:50 |
| 44 | TABLE ACCESS STORAGE FULL | Table4 | 37177 | 669186 | 3 | 00:00:01 |
| * 45 | HASH JOIN OUTER | | 84087578 | 26403499492 | 2809590 | 00:01:50 |
| 46 | JOIN FILTER CREATE | :BF0000 | 84087578 | 17490216224 | 2134146 | 00:01:24 |
| 47 | PX RECEIVE | | 84087578 | 17490216224 | 2134146 | 00:01:24 |
| 48 | PX SEND HASH (NULL RANDOM) | :TQ10006 | 84087578 | 17490216224 | 2134146 | 00:01:24 |
| * 49 | HASH JOIN BUFFERED | | 84087578 | 17490216224 | 2134146 | 00:01:24 |
| 50 | PART JOIN FILTER CREATE | :BF0001 | 345508495 | 11401780335 | 589579 | 00:00:24 |
| 51 | PX RECEIVE | | 345508495 | 11401780335 | 589579 | 00:00:24 |
| 52 | PX SEND HASH | :TQ10000 | 345508495 | 11401780335 | 589579 | 00:00:24 |
| 53 | PX PARTITION LIST INLIST | | 345508495 | 11401780335 | 589579 | 00:00:24 |
| * 54 | TABLE ACCESS STORAGE FULL | Table3 | 345508495 | 11401780335 | 589579 | 00:00:24 |
| * 55 | HASH JOIN | | 84087578 | 14715326150 | 1532914 | 00:01:00 |
| 56 | JOIN FILTER CREATE | :BF0003 | 84087578 | 12276786388 | 1201043 | 00:00:47 |
| 57 | PART JOIN FILTER CREATE | :BF0002 | 84087578 | 12276786388 | 1201043 | 00:00:47 |
| 58 | PX RECEIVE | | 84087578 | 12276786388 | 1201043 | 00:00:47 |
| 59 | PX SEND HASH | :TQ10001 | 84087578 | 12276786388 | 1201043 | 00:00:47 |
| 60 | PX PARTITION LIST INLIST | | 84087578 | 12276786388 | 1201043 | 00:00:47 |
| * 61 | TABLE ACCESS STORAGE FULL | Table1 | 84087578 | 12276786388 | 1201043 | 00:00:47 |
| 62 | PX RECEIVE | | 388151817 | 11256402693 | 320877 | 00:00:13 |
| 63 | PX SEND HASH | :TQ10002 | 388151817 | 11256402693 | 320877 | 00:00:13 |
| 64 | JOIN FILTER USE | :BF0003 | 388151817 | 11256402693 | 320877 | 00:00:13 |
| 65 | PX PARTITION LIST INLIST | | 388151817 | 11256402693 | 320877 | 00:00:13 |
| * 66 | TABLE ACCESS STORAGE FULL |Table2 | 388151817 | 11256402693 | 320877 | 00:00:13 |
| 67 | PX RECEIVE | | 191371730 | 20285403380 | 660306 | 00:00:26 |
| 68 | PX SEND HASH | :TQ10007 | 191371730 | 20285403380 | 660306 | 00:00:26 |
| 69 | JOIN FILTER USE | :BF0000 | 191371730 | 20285403380 | 660306 | 00:00:26 |
| 70 | PX PARTITION LIST INLIST | | 191371730 | 20285403380 | 660306 | 00:00:26 |
| * 71 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | Table1 | 191371730 | 20285403380 | 660306 | 00:00:26 |
| 72 | BITMAP CONVERSION TO ROWIDS | | | | | |
| * 73 | BITMAP INDEX RANGE SCAN | BAD_INDEX | | | | |
* 73 - access("B"."date_field"( )>=TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE@!-INTERVAL' 00-06' YEAR(2) TO MONTH),'YYYYMMDD')))
* 73 - filter(NVL("B"."date_field"( ),99990101)>=TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE@!-INTERVAL' 00-06' YEAR(2) TO MONTH),'YYYYMMDD')) AND
"B"."date_field"( )>=TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE@!-INTERVAL' 00-06' YEAR(2) TO MONTH),'YYYYMMDD')))
CodePudding user response：
First of all, you need to look into 
no_index( [queryblock] tablespec [indexspec])
where 
So for example for this query:
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;
You can use no_index(b.a.t2), ie outer inline view b, then inner inline view a, and finally your table alias.
BUT(!) don't forget about official note:
Note: Specifying a global hint using the tablespec clause does not work for queries that use ANSI joins, because the optimizer generates additional views during parsing. Instead, specify @queryblock to indicate the query block to which the hint applies.
So for ANSI joins (queries with join), it's better to use @queryblock. And it's easier to use explain plan additional sections like alias, projection, note, report_hint. You can do it easily with format=>'all', but I would suggest also outline and hint_report, for example:
select * from table(dbms_xplan.display(format=>'all outline hint_report'));
From the doc:
format Controls the level of details for the plan. It accepts the following values:
- BASIC: Displays the minimum information in the plan—the operation ID, the operation name and its option.
- TYPICAL: This is the default. Displays the most relevant information in the plan (operation id, name and option, #rows, #bytes and optimizer cost). Pruning, parallel and predicate information are only displayed when applicable. Excludes only PROJECTION, ALIAS, and REMOTE SQL information (see below).
- SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.
- ALL: Maximum user level. Includes information displayed with the TYPICAL level with additional information (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).
In fact, 'ALL' is not a "Maximum level". There is also more detailed format - advanced, but it's not documented officially (though you can find it on MOS), so I wouldn't suggest it.
For example, we have these tables and indexes:
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;
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;
create index BAD_INDEX on table_2(col2,col1);
Let's get an execution plan for this query:
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'));
Execution plan:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2962753836
---------------------------------------------------------------------------------------------
| 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_C0010147 | 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('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.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) "T"."COL1"[NUMBER,22], "T"."COL2"[NUMBER,22],
"T"."COL3"[VARCHAR2,100]
2 - (#keys=0) "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]
As you can see, we have NESTED LOOPS with 2 index access here:
line #3 - IRS (Index Range Scan) by BAD_INDEX, and line #4 IUS (Index Unique Scan).
and in the OUTLINE section we see 4 most interesting hints for us:
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"))
So for example, if we want to disable index access to T2, we can look at the outline hint
INDEX(@"SEL$535BA485" "T2"@"SEL$3" ("TABLE_2"."COL2" "TABLE_2"."COL1"))
take the query block (QBName for short) from it - @"SEL$535BA485" and table alias - "T2"@"SEL$3" and add use them in our hint as NO_INDEX(@"SEL$535BA485" "T2"@"SEL$3" BAD_INDEX)
Example #2:
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'));
Execution plan #2:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 247834218
---------------------------------------------------------------------------------------------
| 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_C0010147 | 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('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.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) "T"."COL1"[NUMBER,22], "T"."COL2"[NUMBER,22],
"T"."COL3"[VARCHAR2,100]
2 - (#keys=0) "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")
As you can see, IRS was replaced to FTS (Full Table Scan - TABLE ACCESS FULL in the plan) and hint_report section shows that our hint was used successfully.
In fact, this example query is too simple and CBO can understand even standard no_index(b.a.t2) here (example below), but don't forget that it may not work for more complex ANSI syntax as I mentioned above.
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'));
Example #3:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 247834218
---------------------------------------------------------------------------------------------
| 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_C0010147 | 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
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.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) "T"."COL1"[NUMBER,22], "T"."COL2"[NUMBER,22],
"T"."COL3"[VARCHAR2,100]
2 - (#keys=0) "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)
Finally, all examples on DBFiddle: https://dbfiddle.uk/?rdbms=oracle_21&fiddle=5d4a44a13cb5ca3920794caedeaab44d