An atomic request containing more than one SQL statement, each terminated by a SEMICOLON character.
ANSI Compliance
Multistatement requests are non-ANSI/ISO SQL:2011 standard.
Syntax
statement [;...] [;]
Rules and Restrictions
- Only one USING modifier is permitted per request, so only one USING modifier can be used per multistatement request.
This rule applies to interactive SQL only. Embedded SQL and stored procedures do not permit the USING modifier.
- A multistatement request cannot include a DDL statement.
However, a multistatement request can include one SET QUERY_BAND FOR TRANSACTION statement if it is the first statement in the request.
- A multistatement request cannot include a CALL statement.
- The keywords BEGIN REQUEST and END REQUEST must delimit a multistatement request in a stored procedure.
Power of Multistatement Requests
The multistatement request is application-independent. It improves performance for a variety of applications that can package more than one SQL statement at a time. BTEQ, CLI, and the SQL preprocessor all support multistatement requests.
Multistatement requests improve system performance by reducing processing overhead. By performing a series of statements as one request, performance for the client, the Parser, and the Database Manager are all enhanced.
Because of this reduced overhead, using multistatement requests also decreases response time. A multistatement request that contains 10 SQL statements could be as much as 10 times more efficient than the 10 statements entered separately (depending on the types of statements submitted).
Implicit Multistatement Transaction
In Teradata session mode, a multistatement request is one form of implicit transaction. As such, the outcome of an implicit multistatement transaction is typically all-or-nothing. If one statement in the request fails, the entire implicit transaction fails and the system rolls it back.
Statement Independence for Simple INSERTs
When a multistatement request includes only simple INSERT statements, a failure of one or more INSERTs does not cause the entire request to be rolled back. In these cases, errors are reported for the INSERT statements that failed, so those statements can be resubmitted. INSERT statements that completed successfully are not rolled back.
This behavior is limited to requests submitted directly to the database using an SQL INSERT multistatement request or submitted using a JDBC application request.
Parallel Step Processing
The database can perform some requests in parallel. This capability applies both to implicit transactions, such as macros and multistatement requests, and to Teradata-style transactions explicitly defined by BEGIN/END TRANSACTION statements.
Statements in a multistatement request are broken down by the Parser into one or more steps that direct the execution performed by the AMPs. It is these steps, not the actual statements, that are executed in parallel.
A handshaking protocol between the PE and the AMP allows the AMP to determine when the PE can dispatch the next parallel step.
Up to twenty 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 ten channels can be used for parallel processing when a request is not constrained to a primary index value.
For example, if an INSERT step and a DELETE step are allowed to run in parallel, the AMP informs the PE that the DELETE step has progressed to the point where the INSERT step will not impact it adversely. This handshaking protocol also reduces the chance of a deadlock.
- The statements in a multistatement request are broken down into a series of steps.
- The Optimizer determines which steps in the series can be executed in parallel.
- The steps are processed.
Each step undergoes some preliminary processing before it is executed, such as placing locks on the objects involved. These preliminary processes are not performed in parallel with the steps.
Related Information
For more information about multistatement requests or multistatement request processing, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 or Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.