If the content of the WITH clause is sufficiently complex, Oracle may decide to resolve the subquery into a global temporary table.The MATERIALIZE and INLINE optimizer hints can be used to influence the decision.
The undocumented MATERIALIZE hint tells the optimizer to resolve the subquery as a global temporary table, while the INLINE hint tells it to process the query inline.
--Function - test_func Created
SQL> CREATE OR REPLACE FUNCTION test_func (p_num NUMBER)
2 RETURN NUMBER AS
3 BEGIN
4 DBMS_LOCK.SLEEP(5);
5 RETURN p_num;
6 END;
7 /
Function created.
SQL> SET TIMING ON
SQL> SET AUTOTRACE ON EXPLAIN
--Call The Function - test_func As INLINE VIEW.
SQL> SELECT * FROM (
2 SELECT test_func(ROWNUM) val
3 FROM dual
4 CONNECT BY LEVEL <= 3
5 ) t1,
6 (
7 SELECT test_func(ROWNUM) val
8 FROM dual
9 CONNECT BY LEVEL <= 3
10 ) t2
11 WHERE t1.val = t2.val;
VAL VAL
---------- ----------
1 1
2 2
3 3
Elapsed: 00:00:35.11
Execution Plan
----------------------------------------------------------
Plan hash value: 155859586
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 4 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 26 | 4 (0)| 00:00:01 |
| 2 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | COUNT | | | | | |
|* 4 | CONNECT BY WITHOUT FILTERING| | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 6 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 7 | COUNT | | | | | |
|* 8 | CONNECT BY WITHOUT FILTERING| | | | | |
| 9 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."VAL"="T2"."VAL")
4 - filter(LEVEL<=3)
8 - filter(LEVEL<=3)
--As per the execution plan, it is clearly seen that the function - test_func is invoked 3 times each from the two virtual table and then joining the two virtual table which is causing the query to run for 00:00:35.11.
--Call The Function - test_func Using WITH Clause Along With INLINE Hint.
SQL> WITH gtt AS
2 (
3 SELECT /*+ INLINE */ test_func(ROWNUM) val
4 FROM dual CONNECT BY LEVEL <= 3
5 )
6 SELECT * FROM gtt t1,gtt t2
7 WHERE t1.val = t2.val;
VAL VAL
---------- ----------
1 1
2 2
3 3
Elapsed: 00:00:35.02
Execution Plan
----------------------------------------------------------
Plan hash value: 155859586
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 4 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 26 | 4 (0)| 00:00:01 |
| 2 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | COUNT | | | | | |
|* 4 | CONNECT BY WITHOUT FILTERING| | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 6 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 7 | COUNT | | | | | |
|* 8 | CONNECT BY WITHOUT FILTERING| | | | | |
| 9 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."VAL"="T2"."VAL")
4 - filter(LEVEL<=3)
8 - filter(LEVEL<=3)
--As per the execution plan, it is clearly seen that the function - test_func is invoked 6 times because with INLINE hint, the optimizer will process the query as inline meaning it will create two virtual table,populate the tables and and then joining the two virtual table which is causing the query to run for 00:00:35.02.
--Call The Function - test_func Using WITH Clause Only.
SQL> WITH gtt AS
2 (
3 SELECT test_func(ROWNUM) val
4 FROM dual CONNECT BY LEVEL <= 3
5 )
6 SELECT * FROM gtt t1,gtt t2
7 WHERE t1.val = t2.val;
VAL VAL
---------- ----------
2 2
3 3
1 1
Elapsed: 00:00:15.02
Execution Plan
----------------------------------------------------------
Plan hash value: 2390486683
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 6 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D67BC_1084DF3 | | | | |
| 3 | COUNT | | | | | |
|* 4 | CONNECT BY WITHOUT FILTERING | | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 6 | HASH JOIN | | 1 | 26 | 4 (0)| 00:00:01 |
| 7 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D67BC_1084DF3 | 1 | 13 | 2 (0)| 00:00:01 |
| 9 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D67BC_1084DF3 | 1 | 13 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(LEVEL<=3)
6 - access("T1"."VAL"="T2"."VAL")
--As per the execution plan, it is clearly seen that the function - test_func is invoked 3 times only this time because the optimizer think that resolving the above query as temporary table will give the best cost, in doing so it is calling the function thrice and loading the temporary table and caching it in TEMP tablespace for future processing like join which is causing the query to run for 00:00:15.02.
--Call The Function - test_func Using WITH Clause Along With MATERIALIZE Hint.
SQL> WITH gtt AS
2 (
3 SELECT /*+ MATERIALIZE */ test_func(ROWNUM) val
4 FROM dual CONNECT BY LEVEL <= 3
5 )
6 SELECT * FROM gtt t1,gtt t2
7 WHERE t1.val = t2.val;
VAL VAL
---------- ----------
2 2
3 3
1 1
Elapsed: 00:00:15.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2390486683
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 6 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D67BE_1084DF3 | | | | |
| 3 | COUNT | | | | | |
|* 4 | CONNECT BY WITHOUT FILTERING | | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 6 | HASH JOIN | | 1 | 26 | 4 (0)| 00:00:01 |
| 7 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D67BE_1084DF3 | 1 | 13 | 2 (0)| 00:00:01 |
| 9 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D67BE_1084DF3 | 1 | 13 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(LEVEL<=3)
6 - access("T1"."VAL"="T2"."VAL")
--As per the execution plan, it is clearly seen that the function - test_func is invoked 3 times only because of the explicit MATERIALIZE hint and the optimizer think that resolving the above query as temporary table will give the best cost, in doing so it is loading the temporary table and caching it in TEMP tablespace for future processing like join which is causing the query to run for 00:00:15.03.
