Report that shows the query plan chosen by the optimizer

Any user who runs a query can use the SET EXPLAIN statement or the EXPLAIN directive to display the query plan that the optimizer chooses.

For information about how to specify the directives, see EXPLAIN directives. The user enters the SET EXPLAIN ON statement or the SET EXPLAIN ON AVOID_EXECUTE statement before the SQL statement for the query, as the following example shows.
SET EXPLAIN ON AVOID_EXECUTE;
SELECT * FROM customer, orders
WHERE customer.customer_num = orders.customer_num
   AND customer.lname = "Higgins";

If a user does not have any access to SQL code source, the Database Administrator can set dynamically the SET EXPLAIN using the onmode -Y command.

After the database server executes the SET EXPLAIN ON statement or sets dynamically the SET EXPLAIN with onmode -Y command, the server writes an explanation of each query plan to a file for subsequent queries that the user enters.


Copyright© 2018 HCL Technologies Limited