Neoview Guide to Stored Procedures in Java (R2.3, R2.4)
Using the EXPLAIN Function
You can also prepare the CALL statement and select specific columns from the result table of
the EXPLAIN function, as shown:
SQL>prepare spj1 from call persnl.adjustsalary(202,5.5,?);
--- SQL command prepared.
SQL>select substring(operator,1,8) as "OPERATOR", operator_cost,
+>substring(description,1,500) as "DESCRIPTION"
+>from table (explain(null, 'SPJ1'));
OPERATOR OPERATOR_COST DESCRIPTION
-------- --------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CALL 1.74125E-6 fragment_id: 0 parent_frag: (none) fragment_type: master parameter_modes: I I O
routine_name: NEO.PERSNL.ADJUSTSALARY sql_access_mode: MODIFIES SQL DATA external_name: adjustSalary external_file:
Payroll signature: (Ljava/math/BigDecimal;D[Ljava/math/BigDecimal;)V language: Java max_result_sets: 0
input_values: cast(202), cast(cast((cast(5.5) / cast(10))))
ROOT 7.31325E-9 fragment_id: 0 parent_frag: (none) fragment_type: master statement_index: 0 affinity_value:
1140064381 statement: call persnl.adjustsalary(202,5.5,?) xn_autoabort_interval: -1 plan_version: 2300
DETAILED_STATISTICS: ACCUMULATED AFFINITY_VALUE: -1 SCHEMA: NEO.INVENT CATALOG: NEO select_list: NUMERIC(8,2)
SIGNED input_variables: ?
--- 2 row(s) selected.
SQL>
For a CALL statement, the OPERATOR column of the result table contains a row named CALL.
The DESCRIPTION column contains special token pairs for the CALL operator. For more
information about the CALL operator, see the Neoview Query Guide. For the syntax of the EXPLAIN
function, see the Neoview SQL Reference Manual.
Displaying an Execution Plan of a CALL Statement 75