Materialize (or spool) WITH clause queries

Database

Materialize (or spool) WITH clause queries

Hi:

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

2 REPLIES
Senior Apprentice

Re: Materialize (or spool) WITH clause queries

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...

Teradata Employee

Re: Materialize (or spool) WITH clause queries

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