Using the MATERIALIZE hint correctly

 I see queries like this from time to time:

 

SELECT /*+ materialize */ * FROM (SELECT bla-bla-bla…);

 

I’m pretty sure that the  MATERIALIZE hint is doing nothing when used in this way.  The MATERIALIZE hint is for CTEs (common table expressions) crated using the WITH clause.  I am unable to see what is does for any other use.  If someone can send me a repeatable test case that I can run showing it does something in other cases I’m open to being proven wrong. 

 

This is the type of statement where the hint works as expected.  This creates a structure that is similar to a Global Temp Table, this structure is dropped after the statement completes. 

 

WITH emp_dept_cte AS (

    SELECT /*+ materialize */ empno, sal, dname

    FROM

        emp,dept

    WHERE

        emp.deptno = dept.deptno

)

SELECT * FROM emp_dept_cte; 

 

But it doesn’t work when I write something like this:

 

Select /*+ materialize */  * from (SELECT empno, sal, dname

    FROM

        emp, dept

    WHERE

        emp.deptno = dept.deptno )

 

In this query no matter where I put the MATERIALIZE hint, no temp table like object is created and used.  

In the plan for the first query above I can clearly see the temp table being created in the auto trace with the step LOAD AS SELECT and an object name that starts with SYS_TEMP_ followed by an apparent random combination of numbers and letters. (I’m pretty sure it’s a hexadecimal number for the internal object.)

 

Using the MATERLIZE hint when not using the WITH clause, I do not see this step in the plan.  Hence no temp table like structure.

 

 

Have a great day!