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.