Displaying the Query Plan

Before you change a query, study its query plan to determine the kind and amount of resources it requires. The query plan shows what parallel scans are used, the maximum number of threads required, the indexes used, and so on. Then examine your data model to see if the changes this chapter suggests will improve it.

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 Query Plan Report.

  • 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 on these EXPLAIN directives, see EXPLAIN Directives.