16.20 - Improving Performance with Phase Reductions - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

The Tbl.getPhaseEx() options give you more control of table phase transitions when developing variable mode table functions. You can use them to reduce the number of phase transitions required during execution of a table function, thus reducing the number of UDF invocations and improving table function performance.

The following table compares the number of phase transitions required for each row if you do not use the Tbl.getPhaseEx() options and if you do use the various options. The phases are P (TBL_PRE_INIT), I (TBL_INIT), B (TBL_BUILD), B EOF (TBL_BUILD signalling EOF, not TBL_BUILD_EOF), F (TBL_FINI), and E (TBL_END). X is the scale factor of input to output rows.

Processing Mode Required phases if the Tbl.getPhaseEx() options are not used Required phases if the specified Tbl.getPhaseEx() options are used
1:1

(one row in : one row out)

I, B, B EOF, F B, if the TBL_NEWROW option is set.
1:M

(one row in : many rows out)

I, B*X, B EOF, F B * X, if the TBL_NEWROWEOF option is set.
M:1

(many rows in : one row out)

I, B EOF, F B * X, if the TBL_NEWROW | TBL_LASTROW options are set.

For example:

In a 1:1 processing mode, use the TBL_NEWROW option to get a new row on every TBL_BUILD call.

tbl.getPhaseEx(phase, tbl.TBL_NEWROW);

In this case, the function passes through the following phases:



In a 1:M processing mode, use the TBL_NEWROWEOF option to get a new row when EOF is signaled.

tbl.getPhaseEx(phase, tbl.TBL_NEWROWEOF);

In this case, the function passes through the following phases:



In a M:1 processing mode, you can use:

tbl.getPhaseEx(phase, tbl.TBL_LASTROW | tbl.TBL_NEWROW);

In a combined M:1 and 1:M processing mode, you can use the following that does not pass a new row until EOF:

tbl.getPhaseEx(phase, tbl.TBL_LASTROW |tbl.TBL_NEWROWEOF);

In this case, the function passes through the following phases:



For more information, see getPhaseEx(int[] phase, int option).