I think I have found out why some folks are resisting using this hint when defining a CTE (Common Table Expression). There apparently is a misunderstanding about what this hint does.
This doesn’t cause the CTE to be kept in memory (the PGA) after the query finishes. This only forces the optimizer to not “merge” it back into the main query. A materialized CTE will store the results set in a GTT (Global Temp Table) like structure during the run of the statement. This GTT is dropped once the query finishes.
I say again, the GTT is dropped and is not kept in the PGA once the query finishes.
So, what does the hint do and why do I recommend using it pretty much all the time?
What it does is force the optimizer to keep the CTE as a separate unit and not merge (refactor) it back into the main query. Generally merging the CTE back into the query is not what we want to have happen. We just spent all this time and effort to write the code with a CTE and then the optimizer negates all that work.
I can count on one hand the number of times that the materialize hint made a query worse. Sometimes it doesn’t matter, the optimizer will materialize a CTE with or without the hint. For example, if the CTE is referenced more than one time, the optimizer will automatically materialize the CTE.
One more time, with feeling, the GTT used during the run of a query for a materialized CTE is DROPPED after the statement finishes.
I have several blog posts raving about all the wonderful things a CTE can do to make queries run better faster and stronger. Please see these for ideas on how you can use them to speed up your code.
