Oracle Live SQL - Script: 19c_VIEW_PUSHED_PREDICATE_table_or

Statement 1

drop table emp

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

create table emp  as select * from scott.emp

Table created.

Statement 3

create index idx_mgr on emp(mgr)

Index created.

Statement 4

with tf as 
 (select--+ MATERIALIZE 
   * 
    from (select column_value c from table (select collect(2) from dual))) 
select --+ push_pred(x2) no_merge(x2) opt_param('_optimizer_use_feedback' 'false') 
 * 
  from (select empno, count(*) 
          from emp 
         where MGR in (select c from tf) 
         group by empno) x1, 
       (select mgr, count(*) from emp group by mgr) x2 
 where x1.empno = x2.mgr(+)

no data found

Statement 5

SELECT * FROM TABLE(dbms_xplan.display_cursor('4uaskydrxp7nh', 0, format => '+outline +HINT_REPORT'))


PLAN_TABLE_OUTPUT
SQL_ID  4uaskydrxp7nh, child number 0
-------------------------------------
with tf as   (select--+ MATERIALIZE     *      from (select 
column_value c from table (select collect(2) from dual)))  select --+ 
push_pred(x2) no_merge(x2) opt_param('_optimizer_use_feedback' 'false') 
  *    from (select empno, count(*)            from emp           where 
MGR in (select c from tf)           group by empno) x1,         (select 
mgr, count(*) from emp group by mgr) x2   where x1.empno = x2.mgr(+)
 
Plan hash value: 2402073064
 
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                             |       |       |    48 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION                |                             |       |       |            |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D7681_66CE9569 |       |       |            |          |
|   3 |    VIEW                                   |                             |  8168 |   103K|    34   (0)| 00:00:01 |
|   4 |     COLLECTION ITERATOR PICKLER FETCH     |                             |  8168 | 16336 |    34   (0)| 00:00:01 |
|   5 |      SORT AGGREGATE                       |                             |     1 |       |            |          |
|   6 |       FAST DUAL                           |                             |     1 |       |     2   (0)| 00:00:01 |
|*  7 |   HASH JOIN OUTER                         |                             |     1 |    52 |    14  (15)| 00:00:01 |
|   8 |    JOIN FILTER CREATE                     | :BF0000                     |     1 |    26 |    10  (10)| 00:00:01 |
|   9 |     VIEW                                  |                             |     1 |    26 |    10  (10)| 00:00:01 |
|  10 |      HASH GROUP BY                        |                             |     1 |    21 |    10  (10)| 00:00:01 |
|* 11 |       HASH JOIN SEMI                      |                             |     1 |    21 |     9   (0)| 00:00:01 |
|  12 |        TABLE ACCESS BY INDEX ROWID BATCHED| EMP                         |    13 |   104 |     2   (0)| 00:00:01 |
|* 13 |         INDEX FULL SCAN                   | IDX_MGR                     |    13 |       |     1   (0)| 00:00:01 |
|  14 |        VIEW                               | VW_NSO_1                    |  8168 |   103K|     7   (0)| 00:00:01 |
|  15 |         VIEW                              |                             |  8168 |   103K|     7   (0)| 00:00:01 |
|  16 |          TABLE ACCESS FULL                | SYS_TEMP_0FD9D7681_66CE9569 |  8168 |   103K|     7   (0)| 00:00:01 |
|  17 |    VIEW                                   |                             |     6 |   156 |     4  (25)| 00:00:01 |
|  18 |     HASH GROUP BY                         |                             |     6 |    24 |     4  (25)| 00:00:01 |
|  19 |      JOIN FILTER USE                      | :BF0000                     |    14 |    56 |     3   (0)| 00:00:01 |
|* 20 |       TABLE ACCESS FULL                   | EMP                         |    14 |    56 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SEL$335DD26A")
      MERGE(@"SEL$3" >"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$D67CB2D2")
      MATERIALIZE(@"SEL$1")
      OUTLINE_LEAF(@"SEL$3C459230")
      OUTLINE_LEAF(@"SEL$0C7B6CF6")
      UNNEST(@"SEL$7" UNNEST_SEMIJ_VIEW)
      OUTLINE_LEAF(@"SEL$8")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$7")
      OUTLINE(@"SEL$6")
      NO_ACCESS(@"SEL$5" "X1"@"SEL$5")
      NO_ACCESS(@"SEL$5" "X2"@"SEL$5")
      LEADING(@"SEL$5" "X1"@"SEL$5" "X2"@"SEL$5")
      USE_HASH(@"SEL$5" "X2"@"SEL$5")
      PX_JOIN_FILTER(@"SEL$5" "X2"@"SEL$5")
      INDEX(@"SEL$0C7B6CF6" "EMP"@"SEL$6" ("EMP"."MGR"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$0C7B6CF6" "EMP"@"SEL$6")
      NO_ACCESS(@"SEL$0C7B6CF6" "VW_NSO_1"@"SEL$0C7B6CF6")
      LEADING(@"SEL$0C7B6CF6" "EMP"@"SEL$6" "VW_NSO_1"@"SEL$0C7B6CF6")
      USE_HASH(@"SEL$0C7B6CF6" "VW_NSO_1"@"SEL$0C7B6CF6")
      USE_HASH_AGGREGATION(@"SEL$0C7B6CF6")
      FULL(@"SEL$8" "EMP"@"SEL$8")
      USE_HASH_AGGREGATION(@"SEL$8")
      NO_ACCESS(@"SEL$3C459230" "TF"@"SEL$7")
      FULL(@"SEL$D67CB2D2" "T1"@"SEL$D67CB2D2")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      FULL(@"SEL$335DD26A" "KOKBF$0"@"SEL$3")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - access("X1"."EMPNO"="X2"."MGR")
  11 - access("MGR"="C")
  13 - filter("MGR" IS NOT NULL)
  20 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"MGR"))
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4 (U - Unused (1))
---------------------------------------------------------------------------
 
   0 -  STATEMENT
           -  opt_param('_optimizer_use_feedback' 'false')
 
   1 -  SEL$5 / X2@SEL$5
         U -  push_pred(x2)
           -  no_merge(x2)
 
   2 -  SEL$1
           -  MATERIALIZE
 

104 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 4uaskydrxp7nh, child number 0
-------------------------------------
with tf as (select--+ MATERIALIZE * from (select
column_value c from table (select collect(2) from dual))) select --+
push_pred(x2) no_merge(x2) opt_param('_optimizer_use_feedback' 'false')
* from (select empno, count(*) from emp where
MGR in (select c from tf) group by empno) x1, (select
mgr, count(*) from emp group by mgr) x2 where x1.empno = x2.mgr(+)

Plan hash value: 2402073064

-------------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
-------------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| \| \| 48 (100)\| \|
\| 1 \| TEMP TABLE TRANSFORMATION \| \| \| \| \| \|
\| 2 \| LOAD AS SELECT (CURSOR DURATION MEMORY) \| SYS_TEMP_0FD9D7681_66CE9569 \| \| \| \| \|
\| 3 \| VIEW \| \| 8168 \| 103K\| 34 (0)\| 00:00:01 \|
\| 4 \| COLLECTION ITERATOR PICKLER FETCH \| \| 8168 \| 16336 \| 34 (0)\| 00:00:01 \|
\| 5 \| SORT AGGREGATE \| \| 1 \| \| \| \|
\| 6 \| FAST DUAL \| \| 1 \| \| 2 (0)\| 00:00:01 \|
\|* 7 \| HASH JOIN OUTER \| \| 1 \| 52 \| 14 (15)\| 00:00:01 \|
\| 8 \| JOIN FILTER CREATE \| :BF0000 \| 1 \| 26 \| 10 (10)\| 00:00:01 \|
\| 9 \| VIEW \| \| 1 \| 26 \| 10 (10)\| 00:00:01 \|
\| 10 \| HASH GROUP BY \| \| 1 \| 21 \| 10 (10)\| 00:00:01 \|
\|* 11 \| HASH JOIN SEMI \| \| 1 \| 21 \| 9 (0)\| 00:00:01 \|
\| 12 \| TABLE ACCESS BY INDEX ROWID BATCHED\| EMP \| 13 \| 104 \| 2 (0)\| 00:00:01 \|
\|* 13 \| INDEX FULL SCAN \| IDX_MGR \| 13 \| \| 1 (0)\| 00:00:01 \|
\| 14 \| VIEW \| VW_NSO_1 \| 8168 \| 103K\| 7 (0)\| 00:00:01 \|
\| 15 \| VIEW \| \| 8168 \| 103K\| 7 (0)\| 00:00:01 \|
\| 16 \| TABLE ACCESS FULL \| SYS_TEMP_0FD9D7681_66CE9569 \| 8168 \| 103K\| 7 (0)\| 00:00:01 \|
\| 17 \| VIEW \| \| 6 \| 156 \| 4 (25)\| 00:00:01 \|
\| 18 \| HASH GROUP BY \| \| 6 \| 24 \| 4 (25)\| 00:00:01 \|
\| 19 \| JOIN FILTER USE \| :BF0000 \| 14 \| 56 \| 3 (0)\| 00:00:01 \|
\|* 20 \| TABLE ACCESS FULL \| EMP \| 14 \| 56 \| 3 (0)\| 00:00:01 \|
-------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_optimizer_use_feedback' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SEL$335DD26A")
MERGE(@"SEL$3" >"SEL$2")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$D67CB2D2")
MATERIALIZE(@"SEL$1")
OUTLINE_LEAF(@"SEL$3C459230")
OUTLINE_LEAF(@"SEL$0C7B6CF6")
UNNEST(@"SEL$7" UNNEST_SEMIJ_VIEW)
OUTLINE_LEAF(@"SEL$8")
OUTLINE_LEAF(@"SEL$5")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$7")
OUTLINE(@"SEL$6")
NO_ACCESS(@"SEL$5" "X1"@"SEL$5")
NO_ACCESS(@"SEL$5" "X2"@"SEL$5")
LEADING(@"SEL$5" "X1"@"SEL$5" "X2"@"SEL$5")
USE_HASH(@"SEL$5" "X2"@"SEL$5")
PX_JOIN_FILTER(@"SEL$5" "X2"@"SEL$5")
INDEX(@"SEL$0C7B6CF6" "EMP"@"SEL$6" ("EMP"."MGR"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$0C7B6CF6" "EMP"@"SEL$6")
NO_ACCESS(@"SEL$0C7B6CF6" "VW_NSO_1"@"SEL$0C7B6CF6")
LEADING(@"SEL$0C7B6CF6" "EMP"@"SEL$6" "VW_NSO_1"@"SEL$0C7B6CF6")
USE_HASH(@"SEL$0C7B6CF6" "VW_NSO_1"@"SEL$0C7B6CF6")
USE_HASH_AGGREGATION(@"SEL$0C7B6CF6")
FULL(@"SEL$8" "EMP"@"SEL$8")
USE_HASH_AGGREGATION(@"SEL$8")
NO_ACCESS(@"SEL$3C459230" "TF"@"SEL$7")
FULL(@"SEL$D67CB2D2" "T1"@"SEL$D67CB2D2")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
FULL(@"SEL$335DD26A" "KOKBF$0"@"SEL$3")
END_OUTLINE_DATA
*/

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

7 - access("X1"."EMPNO"="X2"."MGR")
11 - access("MGR"="C")
13 - filter("MGR" IS NOT NULL)
20 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"MGR"))

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4 (U - Unused (1))
---------------------------------------------------------------------------

0 - STATEMENT
- opt_param('_optimizer_use_feedback' 'false')

1 - SEL$5 / X2@SEL$5
U - push_pred(x2)
- no_merge(x2)

2 - SEL$1
- MATERIALIZE

Statement 6

with tf as 
 (select--+ MATERIALIZE 
                        2 c from dual) 
select --+ push_pred(x2) no_merge(x2) opt_param('_optimizer_use_feedback' 'false') 
 * 
  from (select empno, count(*) 
          from emp 
         where MGR in (select c from tf) 
         group by empno) x1, 
       (select mgr, count(*) 
        from emp group by mgr 
        ) x2 
 where x1.empno = x2.mgr(+)

no data found

Statement 7

SELECT * FROM TABLE(dbms_xplan.display_cursor('69nv02zdmfc2p', 0, format => '+outline +HINT_REPORT'))


PLAN_TABLE_OUTPUT
SQL_ID  69nv02zdmfc2p, child number 0
-------------------------------------
with tf as   (select--+ MATERIALIZE                          2 c from 
dual)  select --+ push_pred(x2) no_merge(x2) 
opt_param('_optimizer_use_feedback' 'false')   *    from (select empno, 
count(*)            from emp           where MGR in (select c from tf)  
         group by empno) x1,         (select mgr, count(*)          
from emp group by mgr          ) x2   where x1.empno = x2.mgr(+)
 
Plan hash value: 1905312462
 
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                             |       |       |     9 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION               |                             |       |       |            |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D7682_66CE9569 |       |       |            |          |
|   3 |    FAST DUAL                             |                             |     1 |       |     2   (0)| 00:00:01 |
|   4 |   NESTED LOOPS OUTER                     |                             |     2 |   104 |     7  (29)| 00:00:01 |
|   5 |    VIEW                                  |                             |     2 |    52 |     5  (40)| 00:00:01 |
|   6 |     HASH GROUP BY                        |                             |     2 |    22 |     5  (40)| 00:00:01 |
|   7 |      NESTED LOOPS                        |                             |     2 |    22 |     4  (25)| 00:00:01 |
|   8 |       NESTED LOOPS                       |                             |     2 |    22 |     4  (25)| 00:00:01 |
|   9 |        VIEW                              | VW_NSO_1                    |     1 |     3 |     2   (0)| 00:00:01 |
|  10 |         HASH UNIQUE                      |                             |     1 |     3 |            |          |
|  11 |          VIEW                            |                             |     1 |     3 |     2   (0)| 00:00:01 |
|  12 |           TABLE ACCESS FULL              | SYS_TEMP_0FD9D7682_66CE9569 |     1 |    13 |     2   (0)| 00:00:01 |
|* 13 |        INDEX RANGE SCAN                  | IDX_MGR                     |     2 |       |     0   (0)|          |
|  14 |       TABLE ACCESS BY INDEX ROWID        | EMP                         |     2 |    16 |     1   (0)| 00:00:01 |
|  15 |    VIEW PUSHED PREDICATE                 |                             |     1 |    26 |     1   (0)| 00:00:01 |
|  16 |     SORT GROUP BY                        |                             |     1 |     4 |     1   (0)| 00:00:01 |
|* 17 |      INDEX RANGE SCAN                    | IDX_MGR                     |     2 |     8 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$D67CB2D2")
      MATERIALIZE(@"SEL$1")
      OUTLINE_LEAF(@"SEL$8F9407EC")
      OUTLINE_LEAF(@"SEL$F26D6FDE")
      UNNEST(@"SEL$4" UNNEST_SEMIJ_VIEW)
      OUTLINE_LEAF(@"SEL$9384AC1D")
      PUSH_PRED(@"SEL$2" "X2"@"SEL$2" 1)
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$5")
      NO_ACCESS(@"SEL$2" "X1"@"SEL$2")
      NO_ACCESS(@"SEL$2" "X2"@"SEL$2")
      LEADING(@"SEL$2" "X1"@"SEL$2" "X2"@"SEL$2")
      USE_NL(@"SEL$2" "X2"@"SEL$2")
      NO_ACCESS(@"SEL$F26D6FDE" "VW_NSO_1"@"SEL$F26D6FDE")
      INDEX(@"SEL$F26D6FDE" "EMP"@"SEL$3" ("EMP"."MGR"))
      LEADING(@"SEL$F26D6FDE" "VW_NSO_1"@"SEL$F26D6FDE" "EMP"@"SEL$3")
      USE_NL(@"SEL$F26D6FDE" "EMP"@"SEL$3")
      NLJ_BATCHING(@"SEL$F26D6FDE" "EMP"@"SEL$3")
      USE_HASH_AGGREGATION(@"SEL$F26D6FDE")
      SEMI_TO_INNER(@"SEL$F26D6FDE" "VW_NSO_1"@"SEL$F26D6FDE")
      INDEX(@"SEL$9384AC1D" "EMP"@"SEL$5" ("EMP"."MGR"))
      NO_ACCESS(@"SEL$8F9407EC" "TF"@"SEL$4")
      USE_HASH_AGGREGATION(@"SEL$8F9407EC")
      FULL(@"SEL$D67CB2D2" "T1"@"SEL$D67CB2D2")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  13 - access("MGR"="C")
       filter("MGR" IS NOT NULL)
  17 - access("MGR"="X1"."EMPNO")
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4
---------------------------------------------------------------------------
 
   0 -  STATEMENT
           -  opt_param('_optimizer_use_feedback' 'false')
 
   1 -  SEL$2 / X2@SEL$2
           -  no_merge(x2)
           -  push_pred(x2)
 
   2 -  SEL$1
           -  MATERIALIZE
 

95 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 69nv02zdmfc2p, child number 0
-------------------------------------
with tf as (select--+ MATERIALIZE 2 c from
dual) select --+ push_pred(x2) no_merge(x2)
opt_param('_optimizer_use_feedback' 'false') * from (select empno,
count(*) from emp where MGR in (select c from tf)
group by empno) x1, (select mgr, count(*)
from emp group by mgr ) x2 where x1.empno = x2.mgr(+)

Plan hash value: 1905312462

------------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
------------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| \| \| 9 (100)\| \|
\| 1 \| TEMP TABLE TRANSFORMATION \| \| \| \| \| \|
\| 2 \| LOAD AS SELECT (CURSOR DURATION MEMORY)\| SYS_TEMP_0FD9D7682_66CE9569 \| \| \| \| \|
\| 3 \| FAST DUAL \| \| 1 \| \| 2 (0)\| 00:00:01 \|
\| 4 \| NESTED LOOPS OUTER \| \| 2 \| 104 \| 7 (29)\| 00:00:01 \|
\| 5 \| VIEW \| \| 2 \| 52 \| 5 (40)\| 00:00:01 \|
\| 6 \| HASH GROUP BY \| \| 2 \| 22 \| 5 (40)\| 00:00:01 \|
\| 7 \| NESTED LOOPS \| \| 2 \| 22 \| 4 (25)\| 00:00:01 \|
\| 8 \| NESTED LOOPS \| \| 2 \| 22 \| 4 (25)\| 00:00:01 \|
\| 9 \| VIEW \| VW_NSO_1 \| 1 \| 3 \| 2 (0)\| 00:00:01 \|
\| 10 \| HASH UNIQUE \| \| 1 \| 3 \| \| \|
\| 11 \| VIEW \| \| 1 \| 3 \| 2 (0)\| 00:00:01 \|
\| 12 \| TABLE ACCESS FULL \| SYS_TEMP_0FD9D7682_66CE9569 \| 1 \| 13 \| 2 (0)\| 00:00:01 \|
\|* 13 \| INDEX RANGE SCAN \| IDX_MGR \| 2 \| \| 0 (0)\| \|
\| 14 \| TABLE ACCESS BY INDEX ROWID \| EMP \| 2 \| 16 \| 1 (0)\| 00:00:01 \|
\| 15 \| VIEW PUSHED PREDICATE \| \| 1 \| 26 \| 1 (0)\| 00:00:01 \|
\| 16 \| SORT GROUP BY \| \| 1 \| 4 \| 1 (0)\| 00:00:01 \|
\|* 17 \| INDEX RANGE SCAN \| IDX_MGR \| 2 \| 8 \| 1 (0)\| 00:00:01 \|
------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_optimizer_use_feedback' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$D67CB2D2")
MATERIALIZE(@"SEL$1")
OUTLINE_LEAF(@"SEL$8F9407EC")
OUTLINE_LEAF(@"SEL$F26D6FDE")
UNNEST(@"SEL$4" UNNEST_SEMIJ_VIEW)
OUTLINE_LEAF(@"SEL$9384AC1D")
PUSH_PRED(@"SEL$2" "X2"@"SEL$2" 1)
OUTLINE_LEAF(@"SEL$2")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$5")
NO_ACCESS(@"SEL$2" "X1"@"SEL$2")
NO_ACCESS(@"SEL$2" "X2"@"SEL$2")
LEADING(@"SEL$2" "X1"@"SEL$2" "X2"@"SEL$2")
USE_NL(@"SEL$2" "X2"@"SEL$2")
NO_ACCESS(@"SEL$F26D6FDE" "VW_NSO_1"@"SEL$F26D6FDE")
INDEX(@"SEL$F26D6FDE" "EMP"@"SEL$3" ("EMP"."MGR"))
LEADING(@"SEL$F26D6FDE" "VW_NSO_1"@"SEL$F26D6FDE" "EMP"@"SEL$3")
USE_NL(@"SEL$F26D6FDE" "EMP"@"SEL$3")
NLJ_BATCHING(@"SEL$F26D6FDE" "EMP"@"SEL$3")
USE_HASH_AGGREGATION(@"SEL$F26D6FDE")
SEMI_TO_INNER(@"SEL$F26D6FDE" "VW_NSO_1"@"SEL$F26D6FDE")
INDEX(@"SEL$9384AC1D" "EMP"@"SEL$5" ("EMP"."MGR"))
NO_ACCESS(@"SEL$8F9407EC" "TF"@"SEL$4")
USE_HASH_AGGREGATION(@"SEL$8F9407EC")
FULL(@"SEL$D67CB2D2" "T1"@"SEL$D67CB2D2")
END_OUTLINE_DATA
*/

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

13 - access("MGR"="C")
filter("MGR" IS NOT NULL)
17 - access("MGR"="X1"."EMPNO")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4
---------------------------------------------------------------------------

0 - STATEMENT
- opt_param('_optimizer_use_feedback' 'false')

1 - SEL$2 / X2@SEL$2
- no_merge(x2)
- push_pred(x2)

2 - SEL$1
- MATERIALIZE

Statement 8

with tf as  
 (select--+ MATERIALIZE  
   *  
    from (select column_value c from table (select collect(2) from dual)))  
select --+ push_pred(x2) no_merge(x2) opt_param('_optimizer_use_feedback' 'false')  
 *  
  from (select empno, count(*)  
          from emp  
         where MGR in (select c from tf)  
         group by empno) x1,  
       (select mgr--, count(*)  
        from emp  --group by mgr  
        ) x2  
 where x1.empno = x2.mgr(+)

no data found

Statement 9

SELECT * FROM TABLE(dbms_xplan.display_cursor('1pjv3wh4p2wf5', 0, format => '+outline +HINT_REPORT'))


PLAN_TABLE_OUTPUT
SQL_ID  1pjv3wh4p2wf5, child number 0
-------------------------------------
with tf as    (select--+ MATERIALIZE      *       from (select 
column_value c from table (select collect(2) from dual)))   select --+ 
push_pred(x2) no_merge(x2) opt_param('_optimizer_use_feedback' 'false') 
   *     from (select empno, count(*)             from emp            
where MGR in (select c from tf)            group by empno) x1,          
(select mgr--, count(*)           from emp  --group by mgr           ) 
x2    where x1.empno = x2.mgr(+)
 
Plan hash value: 2297606441
 
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                             |       |       |    45 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION               |                             |       |       |            |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D7683_66CE9569 |       |       |            |          |
|   3 |    VIEW                                  |                             |  8168 |   103K|    34   (0)| 00:00:01 |
|   4 |     COLLECTION ITERATOR PICKLER FETCH    |                             |  8168 | 16336 |    34   (0)| 00:00:01 |
|   5 |      SORT AGGREGATE                      |                             |     1 |       |            |          |
|   6 |       FAST DUAL                          |                             |     1 |       |     2   (0)| 00:00:01 |
|   7 |   NESTED LOOPS OUTER                     |                             |     2 |    60 |    11  (10)| 00:00:01 |
|   8 |    VIEW                                  |                             |     1 |    26 |    10  (10)| 00:00:01 |
|   9 |     HASH GROUP BY                        |                             |     1 |    21 |    10  (10)| 00:00:01 |
|* 10 |      HASH JOIN SEMI                      |                             |     1 |    21 |     9   (0)| 00:00:01 |
|  11 |       TABLE ACCESS BY INDEX ROWID BATCHED| EMP                         |    13 |   104 |     2   (0)| 00:00:01 |
|* 12 |        INDEX FULL SCAN                   | IDX_MGR                     |    13 |       |     1   (0)| 00:00:01 |
|  13 |       VIEW                               | VW_NSO_1                    |  8168 |   103K|     7   (0)| 00:00:01 |
|  14 |        VIEW                              |                             |  8168 |   103K|     7   (0)| 00:00:01 |
|  15 |         TABLE ACCESS FULL                | SYS_TEMP_0FD9D7683_66CE9569 |  8168 |   103K|     7   (0)| 00:00:01 |
|  16 |    VIEW PUSHED PREDICATE                 |                             |     2 |     8 |     1   (0)| 00:00:01 |
|* 17 |     INDEX RANGE SCAN                     | IDX_MGR                     |     2 |     8 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SEL$335DD26A")
      MERGE(@"SEL$3" >"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$D67CB2D2")
      MATERIALIZE(@"SEL$1")
      OUTLINE_LEAF(@"SEL$3C459230")
      OUTLINE_LEAF(@"SEL$0C7B6CF6")
      UNNEST(@"SEL$7" UNNEST_SEMIJ_VIEW)
      OUTLINE_LEAF(@"SEL$81C74289")
      OLD_PUSH_PRED(@"SEL$5" "X2"@"SEL$5" ("EMP"."MGR"))
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$7")
      OUTLINE(@"SEL$6")
      OUTLINE(@"SEL$8")
      NO_ACCESS(@"SEL$5" "X1"@"SEL$5")
      NO_ACCESS(@"SEL$5" "X2"@"SEL$5")
      LEADING(@"SEL$5" "X1"@"SEL$5" "X2"@"SEL$5")
      USE_NL(@"SEL$5" "X2"@"SEL$5")
      INDEX(@"SEL$0C7B6CF6" "EMP"@"SEL$6" ("EMP"."MGR"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$0C7B6CF6" "EMP"@"SEL$6")
      NO_ACCESS(@"SEL$0C7B6CF6" "VW_NSO_1"@"SEL$0C7B6CF6")
      LEADING(@"SEL$0C7B6CF6" "EMP"@"SEL$6" "VW_NSO_1"@"SEL$0C7B6CF6")
      USE_HASH(@"SEL$0C7B6CF6" "VW_NSO_1"@"SEL$0C7B6CF6")
      USE_HASH_AGGREGATION(@"SEL$0C7B6CF6")
      INDEX(@"SEL$81C74289" "EMP"@"SEL$8" ("EMP"."MGR"))
      NO_ACCESS(@"SEL$3C459230" "TF"@"SEL$7")
      FULL(@"SEL$D67CB2D2" "T1"@"SEL$D67CB2D2")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      FULL(@"SEL$335DD26A" "KOKBF$0"@"SEL$3")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  10 - access("MGR"="C")
  12 - filter("MGR" IS NOT NULL)
  17 - access("MGR"="X1"."EMPNO")
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4 (U - Unused (1))
---------------------------------------------------------------------------
 
   0 -  STATEMENT
           -  opt_param('_optimizer_use_feedback' 'false')
 
   1 -  SEL$5 / X2@SEL$5
         U -  push_pred(x2)
           -  no_merge(x2)
 
   2 -  SEL$1
           -  MATERIALIZE
 

101 rows selected.

19c_VIEW_PUSHED_PREDICATE_table_or_gby3

Additional Information