drop table emp create table emp as select * from scott.emp create index idx_mgr on emp(mgr) 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(+) 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 |
|---|
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(+) 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 |
|---|
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(+) 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 |
|---|