EXPLAIN and Stored Procedures - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

EXPLAIN and Stored Procedures

You cannot EXPLAIN compiled stored procedures.

Do use EXPLAIN while building your stored procedures. To do this, you must extract the SQL text from the procedure body and EXPLAIN the individual statements by themselves. This might require modification of the stored procedure SQL text in some cases. For example, you might have to remove an INTO clause or add a USING request modifier to represent stored procedure variables and parameters.

EXPLAIN works by parsing and optimizing SQL source code text just as it would be parsed and optimized if it were submitted by a client application for processing. You EXPLAIN a string of SQL text, not the name of an object that contains strings of SQL text. Once a stored procedure has been compiled, it is a database object and it can no longer be processed using an EXPLAIN modifier as if it were simple SQL text.

For example, if you compile a stored procedure under the name update_orders and then perform the following EXPLAIN, it will fail because update_orders is not a valid SQL statement.

     EXPLAIN update_orders;

While you are developing the SQL requests that constitute the UpdateOrders procedure, however, you should always use EXPLAINs to help you to write the most efficient SQL code possible.