All Forums Database
khaledjamoos 1 post Joined 07/12
05 Jan 2016
Materialize (or spool) WITH clause queries

I was wondering if there is a way to do somethign similar to the "materialize" hint that oracle has when using the WITH clause. I am trying to avoid creating temp tables for this, but in my query there is a section of it that goes through some large tables but the end result is a relatively small subset of the data, and as I am using that part a couple of times in my query i do not wish for the database to re-evaluate the whole thing again, since it already evaluated once before (At least this is what the excution plan appears to be telling me).
Any help on this would be appreciated.
Kind Regards
Khaled Jamoos

dnoeth 4628 posts Joined 11/04
05 Jan 2016

Hi Khaled, 
there's no way to tell the optimizer to spool a WITH (he will do it in some cases, in others he won't).
I manually create a Volatile Table in such a case, this also allows choosing the best PI...


ToddAWalter 316 posts Joined 10/11
06 Jan 2016

The query and Explain would be helpful. The optimizer has the capability to automatically reuse the results of any subquery (including WITH) if appropriate.

You must sign in to leave a comment.