Query Bands and Session-Level Performance - Analytics Database - Teradata Vantage

The session-level query band request is more expensive than the transaction-level query band request because the FOR SESSION option adds the overhead of updating the queryband column in DBC.SessionTbl, which enables it to be recovered after a system restart.

The VOLATILE option for SET QUERY_BAND FOR SESSION requests enables you to select performance over recoverability by reducing the cost of the request at the expense of being able to recover it after a system restart occurs.

For example, if you want a query band to be restored after a restart, you should submit a SET QUERY_BAND FOR SESSION request. However, if you prefer performance over the capability to restore an active query band, you should specify the VOLATILE keyword with a SET QUERY_BAND FOR SESSION request using this syntax:

SET QUERY_BAND = 'name=value;' FOR SESSION VOLATILE;

Note that you can specify both the UPDATE and the VOLATILE option within the same SET QUERY_BAND request. You can also use a combination of both versions of a SET QUERY_BAND FOR SESSION request. An application can set some name:value pairs FOR SESSION that are saved in DBC.SessionTbl, while specifying those name:value pairs that are specific to the request using the SET QUERY_BAND UPDATE and VOLATILE options. On a restart, Vantage will restore the query band saved in DBC.SessionTbl, but not the query band name-value pairs set using the VOLATILE option. For example:

SET QUERY_BAND = 'clientuser=x11;group=acct;' FOR SESSION;
SEL ...
SET QUERY_BAND = 'job=a1;' UPDATE FOR SESSION VOLATILE;
INS ...
INS ...
SET QUERY_BAND = 'job=b2;' UPDATE FOR SESSION VOLATILE;
INS ...
INS ...