Spool Release

Database
Enthusiast

Spool Release

Hi,

If a query is utilizing many spools are they released only when the query ends? for example if my query is using till spool20

then the sum of all the 20 spools is utilized till the query execution finishes even though the result set may not be so big ?

How is spool usage when explain plan reads - "execute the following steps in parallel."

please let me know at the earliest

Thanks

5 REPLIES
Enthusiast

Re: Spool Release

Spool falls into three categories:

VOLATILE SPOOL - content is retained until

* Transaction completes (unless the table was created with  ON COMMIT PRESERVE ROW)

* Table is dropped manually during the session

* Session ends

* Teradata RDBMS resets

INTERMEDIATE SPOOL - whose results are retained until no longer needed. You can determine when intermediate spool is flushed by examining the output of an EXPLAIN.

Note: The first step performed after intermediate spool has been flushed is designated “Last Use.”

OUTPUT SPOOL - Output spool results are the final information returned for a query or the rows updated within, inserted into, or deleted from a base table. The length of time output spool is retained depends on the subsystem and various system conditions

Enthusiast

Re: Spool Release

Thanks for the insight -

so if i were to estimate the total spool a query might require at each step

then will it be appropriate to add up all the spools reported in the explain plan till that particular step and

subtract the ones which have “Last Use”  till that step ?

looking for your valuable inputs also can you suggest a source where i can read more about these

Enthusiast

Re: Spool Release

Yes, I think that should give you a fair idea of how much spool the query will require.

Enthusiast

Re: Spool Release

The capacity planning section should get you started...

www.teradataforum.com/teradata_pdf/b035-1093-061a.pdf

Enthusiast

Re: Spool Release

Thanks Aftab