Display the query plan

Before you change a query, display its query plan to determine the kind and amount of resources that the query requires. The query plan shows what parallel scans are used, the maximum number of threads required, and the indexes used.

After you study the query plan, examine your data model to see if the changes this chapter suggests will improve the query.

You can display the query plan with one of the following methods:
  • Execute one of the following SET EXPLAIN statements just before the query:
    • SET EXPLAIN ON

      This SQL statement displays the chosen query plan. For a description of the SET EXPLAIN ON output, see Report that shows the query plan chosen by the optimizer.

    • SET EXPLAIN ON AVOID_EXECUTE

      This SQL statement displays the chosen query plan and does not execute the query.

  • Use one of the following EXPLAIN directives in the query:
    • EXPLAIN
    • EXPLAIN AVOID_EXECUTE

For more information about these EXPLAIN directives, see EXPLAIN directives.