15.10 - EXPLAIN Request Modifier and Parallel Steps - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

The EXPLAIN request modifier also reports whether or not requests will be processed in parallel.

The steps that can be executed in parallel are numbered, indented in the explanatory text, and preceded by the following message:

   ... we execute the following steps in parallel.

Parallel steps can be used to process a request submitted in a transaction (which can be a user-generated transaction, a multistatement request, a macro, or a solitary statement that affects multiple rows).

Up to 20 parallel steps can be processed per request if channels are not required, such as a request with an equality constraint based on a primary index value.

Up to 10 channels can be used for parallel processing when a request is not constrained to a primary index value.

The preceding limits apply to the number of steps that can be simultaneously executed in parallel. Another parallel step can be started once the number of steps that are executing drops below the limit.

For example, a non-primary-constrained request that does not involve redistribution of rows to other AMPs, such as a SELECT or UPDATE, requires only two channels. A request that does involve row redistribution, such as a join or an INSERT … SELECT, requires four channels.

The following BTEQ request is structured as a single transaction, and thus generates parallel-step processing.

In Teradata session mode, the transaction is structured as follows:

     BEGIN TRANSACTION
     ;INSERT Department (100,'Administration','NYC',10011)
     ;INSERT Department (600,'Manufacturing','CHI',10007)
     ;INSERT Department (500,'Engineering', 'ATL',10012)
     ;INSERT Department (600, 'Exec Office','NYC', 10018)
     ; END TRANSACTION ;

In ANSI session mode, the transaction is structured as follows.

     INSERT Department (100,'Administration','NYC',10011)
    ;INSERT Department (600,'Manufacturing','CHI',10007)
    ;INSERT Department (500,'Engineering', 'ATL',10012)
    ;INSERT Department (600, 'Exec Office','NYC', 10018)
    ;COMMIT ;

If you issue an EXPLAIN request modifier against these transactions, the request returns the identical explanation in either mode, except that the last line is not returned for an ANSI mode transaction.

Explanation
-------------------------------------------------------------------
1)  First, we execute the following steps in parallel.
    1)  We do an INSERT into PERSONNEL.Department
    2)  We do an INSERT into PERSONNEL.Department
    3)  We do an INSERT into PERSONNEL.Department
    4)  We do an INSERT into PERSONNEL.Department
2)  Finally, we send out an END TRANSACTION step to all AMPs involved in
    processing the request.
->  No rows are returned to the user as the result of statement 1.
    No rows are returned to the user as the result of statement 2.
    No rows are returned to the user as the result of statement 3.
    No rows are returned to the user as the result of statement 4.
    No rows are returned to the user as the result of statement 5.
    No rows are returned to the user as the result of statement 6.