Undocumented Oracle optimizer hint “MATERIALIZE”, by Zahar Hilkevich

Every time you use an in-line view or a WITH clause Oracle optimizer may (or may not) choose to use a temporary table based on the in-line view result set.

For example, for the following query Oracle optimizer does not use a temp table:

WITH b AS
(SELECT deptno, MAX(sal) max_sal
 FROM emp
 GROUP BY deptno
)
SELECT a.ename, a.sal, a.deptno
FROM emp a JOIN b ON a.deptno=b.deptno AND a.sal=b.max_sal

Execution Plan:

Plan hash value: 269884559

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    39 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN           |      |     1 |    39 |     8  (25)| 00:00:01 |
|   2 |   VIEW               |      |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |      |     3 |    21 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMP  |    14 |   182 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

To force Oracle optimizer using a temporary table instead of in-line view use materialize Oracle optimizer hint:

WITH b AS
(SELECT /*+materialize */ deptno, MAX(sal) max_sal
 FROM emp
 GROUP BY deptno
)
SELECT a.ename, a.sal, a.deptno
FROM emp a JOIN b ON a.deptno=b.deptno AND a.sal=b.max_sal

Execution Plan:

Plan hash value: 3092985208

----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |     3 |   117 |    10  (20)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6A06_F425182A |       |       |            |          |
|   3 |    HASH GROUP BY           |                             |     3 |    21 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL      | EMP                         |    14 |    98 |     3   (0)| 00:00:01 |
|*  5 |   HASH JOIN                |                             |     3 |   117 |     6  (17)| 00:00:01 |
|   6 |    VIEW                    |                             |     3 |    78 |     2   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6A06_F425182A |     3 |    21 |     2   (0)| 00:00:01 |
|   8 |    TABLE ACCESS FULL       | EMP                         |    14 |   182 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

P.S. If you like this tip, you can find many more in my book “Oracle SQL Tricks and Workarounds”

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s