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