Neoview Query Guide (R2.3, R2.4, R2.5)

1 Using Explain
A query execution plan represents the specific steps and actions that the Neoview database takes
to execute a query. Query plans show the exact access paths to database structures and provide
the estimated number of rows resulting from each step, as well as the relative cost. Query plans
are composed of a set of preexisting functions or methods called operators that implement a
particular task for a query. Operators and the operations they perform are discussed in Chapter 2
(page 17).
Why do you want to study query plans? You might want to verify something about how the
subsystem is working or find a solution to a specific problem. By reviewing the query plan, you
can determine if the performance is optimal.
This section provides information about:
• “Generating Query Plans”
• “Capturing Query Plans for Review” (page 12)
Generating Query Plans
The Neoview data warehousing platform provides three methods for generating query plans:
• “Neoview Performance Analyzer”
• “Explain Statement”
• “Explain Function”
Neoview Performance Analyzer
The Neoview Performance Analyzer (NPA) is a graphical user interface (GUI) product used to
analyze workloads and review query plans generated by the Neoview optimizer for DML
statements. NPA presents visual query plans in a tree form. The query plan portion of NPA is
located in the Query Workbench tab. You can download NPA from the HP Software Depot at:
www.software.hp.com.
Explain Statement
The EXPLAIN statement is a Neoview SQL statement derived from the EXPLAIN function and
improves the readability, content, and usefulness of the displayed query plans. Use the EXPLAIN
statement to quickly review query plans.
Explain Function
The EXPLAIN function is a table-valued stored function that presents the explain output in a
table format. This format makes it easy to capture explain details from queries and maintain
them in the database table, such as an explain table. Explain tables are useful for detailed analysis
of many query plans or to maintain a historical database of explain plans. The EXPLAIN statement
and EXPLAIN function can be executed from ODBC, JDBC, and Neoview Script but you cannot
use the EXPLAIN statement or EXPLAIN function in DB Admin. Use the EXPLAIN function to
capture query plans for further analysis.
The EXPLAIN statement and EXPLAIN function can be executed from ODBC, JDBC, and Neoview
Script but you cannot use the EXPLAIN statement or EXPLAIN function in DB Admin.
The syntax and considerations for using the EXPLAIN statement and EXPLAIN function are
included in the Neoview SQL Reference Manual.
Generating Query Plans 11