17.10 - EXPLAIN Request Modifier and Parallel Steps - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

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

Parallel Steps

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 at a time 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. 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 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. Therefore, the number of parallel steps listed in an EXPLAIN output is not restricted by the above limits.

Parallel Steps Example

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.

Implicit Multistatement INSERT Example

In the following BTEQ multistatement request, which is treated as an implicit transaction, the statements are processed concurrently.

In Teradata session mode, the EXPLAIN request modifier generates the following response for this request:

EXPLAIN 
INSERT Charges (30001, 'AP2-0004', 890825, 45.0)
; INSERT Charges (30002, 'AP2-0004', 890721, 12.0)
; INSERT Charges (30003, 'AP2-0004', 890811, 2.5)
; INSERT Charges (30004, 'AP2-0004', 890831, 37.5
; INSERT Charges (30005, 'AP2-0004', 890825, 11.0)
; INSERT Charges (30006, 'AP2-0004', 890721, 24.5)
; INSERT Charges (30007, 'AP2-0004', 890811, 40.5)
; INSERT Charges (30008, 'AP2-0004', 890831, 32.0
; INSERT Charges (30009, 'AP2-0004', 890825, 41.5)
; INSERT Charges (30010, 'AP2-0004', 890721, 22.0) ;
Explanation
---------------------------------------------------------
1) First, we execute the following steps in parallel.
    1)    We do an INSERT into PERSONNEL.charges.
    2)    We do an INSERT into PERSONNEL.charges.
    3)    We do an INSERT into PERSONNEL.charges.
    4)    We do an INSERT into PERSONNEL.charges.
    5)    We do an INSERT into PERSONNEL.charges.
    6)    We do an INSERT into PERSONNEL.charges.
    7)    We do an INSERT into PERSONNEL.charges.
    8)    We do an INSERT into PERSONNEL.charges.
    9)    We do an INSERT into PERSONNEL.charges.
    10)   We do an INSERT into PERSONNEL.charges.
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.
     No rows are returned to the user as the result of
     statement 7.
     No rows are returned to the user as the result of
     statement 8.
     No rows are returned to the user as the result of
     statement 9.
     No rows are returned to the user as the result of
     statement 10.