Conserve spool

Database
Enthusiast

Conserve spool

I am working with a large data set and need to ensure that i do not exceed my spool usage. 

My question is related to the way teradata executes a query we see a lot of Spool1 (Last Used) I want to do the same on my volatile tables in the queries that they are last used and not have to explicitly write a DROP TABLE statement to release spool. Thay way at no point in time i have double the space needed before the DROP statement.

 

For Example 

Currently 

CREATE MULTISET VOLATILE TABLE TBL1 AS (<Query>) WITH DATA PRIMARY INDEX (COL1,COL2) ON COMMIT PRESERVE ROWS;

CREATE MULTISET VOLATILE TABLE TBL2 AS (<Query> FROM TBL1) WITH DATA PRIMARY INDEX (COL1,COL2) ON COMMIT PRESERVE ROWS;

DROP TABLE TBL1;

 

I want to 

CREATE MULTISET VOLATILE TABLE TBL1 AS (<Query>) WITH DATA PRIMARY INDEX (COL1,COL2) ON COMMIT PRESERVE ROWS;

CREATE MULTISET VOLATILE TABLE TBL2 AS (<Query> FROM TBL1 (Last Used) ) WITH DATA PRIMARY INDEX (COL1,COL2) ON COMMIT PRESERVE ROWS;

--No need for a DROP Statement and maybe reduce spool usage

 

I know internally at some point the data must be present in the spool but i was find a more efficent way to write SQLs and not enforce DROP statements.

2 REPLIES
Senior Apprentice

Re: Conserve spool

Hi,

 

If you use 'on commit preserve rows' then the data will be kept - as SPOOL space - until you explicitly DROP the table or DELETE the rows. This is the way Teradata works.

 

If you do not use the 'on commit preserve rows' then the data will be removed automatically at the end of the txn. But you have to then think about when your txn ends.

 

If the content of (in your example) the two volatile tables are only used for a single query, then use derived tables instead. These only exist for the duration of the request, and typically will be removed (last use) before the query actually finishes.

 

Essentially, the content of a volatile table is kept until:

- the txn ends (on commit delete rows - the default)

- you execute a DELETE command (on commit preserve rows)

- you DROP the table (on commit preserve rows)

- you logoff (either).

 

A derived table lasts for the duration of the query - at the most.

 

Those are your basic choices, you have to decide which one suits you best.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Conserve spool

If the Teradata query optimizer optimization is desired, then the optimizer has to be given the chance to do its job...

 

If the query is written with derived tables rather than multiple SQL statements and volatile tables, then the optimizer will identify when the spool is no longer needed and will release it before the next query step.

 

Writing the query as multiple steps and determining the volatile tables like this means that the query writer is being the optimizer and therefore needs to write the DROPs as well when the volatile table is no longer needed. It also means that many other possible optimizations (push down, common spool elimination, parallel steps, incremental planning, ...) will not be available between the multiple queries.