With as and materialize hints

With as: A subquery is a part of the independent, sometimes to improve the readability of SQL statements, sometimes to Improve the Performance of SQL statements. If a table in an SQL statement is accessed multiple times with the same access conditions, you can use with as to improve the performance. Note: If the with as phrase is not called more than twice, CBO will not talk about the data obtained by this phrase into the temp table, if you want to add data to the temp table, you need to use materialize hint. If the with as phrase is called more than twice, CBO will automatically put the data with as phrase into a temporary table, in this case, you do not need to write materialize hint. For example (this example is based on the Scott user) SQL> explain plan forwith a as (select/* + materialize */ename, job, deptno from emp where sal> (select avg (sal) from emp) select * from a; 2 3Explained. SQL> select * from table (dbms_xplan.display); PLAN_TABLE_OUTPUT ---------- Explain Plan hash value: 2006423466 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | SELECT S TATEMENT | 1 | 26 | 8 (0) | 00:00:01 | 1 | temp table transformation | 2 | load as select | SYS_TEMP_0FD9D6605_E16CE | * 3 | table access full | EMP | 1 | 21 | 3 (0) | 00:00:01 | 4 | sort aggregate | 1 | 4 | 5 | table access full | EMP | 14 | 56 | 3 (0) | 00:00:01 | 6 | VIEW | 1 | 26 | 2 (0) | 00:00:01 | 7 | table access full | SYS_TEMP_0FD9D6605_E16CE | 1 | 17 | 2 (0) | 00:00:01 | identified Predicate Information (identified by operation id): latency 3-filter ("SAL"> (select avg ("SAL") FROM "EMP" "EMP ")) 19 rows selected. remove/* + materialize */. Because only a is accessed once, CBO does not generate a temporary table SQL> explain plan forwith a as (select ename, job, deptno From emp where sal> (select avg (sal) from emp) select * from a; 2 3Explained. SQL> select * from table (dbms_xplan.display); PLAN_TABLE_OUTPUT partition Plan hash value: 1876299339 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | --------------------- --------------------------------------------------- | 0 | select statement | 1 | 21 | 6 (0) | 00:00:01 | * 1 | table access full | EMP | 1 | 21 | 3 (0) | 00:00:01 | 2 | sort aggregate | 1 | 4 | 3 | table access full | EMP | 14 | 56 | 3 (0) | 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id ):------------ ------------------------------------- 1-filter ("SAL"> (select avg ("SAL") FROM "EMP" "EMP") 15 rows selected. if the with as statement is called once and used multiple times, you need to write hints. If the table is only scanned once, some materialize hints results are read once and also written to temp, when the temporary table is read from temp again, it is written once, but it needs to be read multiple times. Continue test: SQL> explain plan forwith a as (select ename, job, deptno from emp where sal> (select avg (sal) from emp )) select * from a union all select * from a; 2 3 Explained. SQL> select * from table (dbms_xplan.display (); PLAN_TABLE_OUTPUT partition Plan hash value: 2575088720 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 0 | select statement | 2 | 52 | 4 (50) | 00:00:01 | 1 | temp table transformation | 2 | load as select | SYS_TEMP_0FD9D6601_4DC46A | * 3 | table access full | EMP | 1 | 39 | 3 (0) | 00:00:01 | 4 | sort aggregate | 1 | 13 | 5 | table access full | EMP | 14 | 182 | 3 (0) | 00:00:01 | 6 | UNION-ALL | 7 | VIEW | 1 | 26 | 2 (0) | 00:00:01 | 8 | table access full | SYS_TEMP_0FD9D6601_4DC46A | 1 | 26 | 2 (0) | 00:00:01 | 9 | VIEW | 1 | 26 | 2 (0) | 00:00:01 | 10 | table access full | SYS_TEMP_0FD9D6601_4DC46A | 1 | 26 | 2 (0) | 00:00:01 | identified Predicate Information (identified by operation id ): limit 3-filter ("SAL"> (select avg ("SAL") FROM "EMP" "EMP") Note ------dynamic sampling used for this statement (level = 2) 26 rows selected. full Proof: 1. if the with as statement is not called more than twice, if the table needs to be accessed multiple times, you need to add hints/* + materialize */2. if the with as statement is called more than two times, the data of the with as phrase is automatically put into a temporary table. In this case, materialize hint is not required.