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!
