EXPLAIN Request Modifier and Parallel Steps - Teradata VantageCloud Lake

The EXPLAIN request modifier also reports whether requests are processed in parallel.

Parallel Steps

The steps that can be run 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 multiple-statement request, a macro, or a solitary statement that affects multiple rows).

Up to 20 parallel steps can be processed at a time for each request if channels are optional, 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 run in parallel. Another parallel step can be started when the number of steps that are running drops below the limit. Therefore, the number of parallel steps listed in an EXPLAIN output is not restricted by the preceding 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 Multiple-Statement INSERT Example

In the following BTEQ multiple-statement 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.