SQL/MP Installation and Management Guide

Managing Database Applications
HP NonStop SQL/MP Installation and Management Guide—523353-004
10-15
Using Similarity Checks
Using Similarity Checks
A similarity check is a comparison made by SQL to determine whether two objects (or
the compile-time and execution time version of the same object) are sufficiently similar
that a serial execution plan compiled for one can work as an operable plan for the
other. For example, if a statement refers to a table at run time, which is similar to the
table the statement was compiled against, SQL/MP allows the statement to run without
automatic recompilation.
Similarity checks work by comparing information stored in an execution plan with
information current at recompilation time. Executing the similarity check is faster than
recompiling an execution plan, can potentially avoid a recompilation, and can therefore
reduce the downtime for an SQL program.
The similarity check is done on a per-statement and per-object basis. There are three
aspects of setting up similarity checking:
•
Specifying similarity checking at compile-time of a program
•
Specifying similarity checking at run time of the program
•
Enabling similarity checking for specific tables and collations accessed by the
program; it is explicitly enabled for all other objects except shorthand views
Because SQL/MP must have information about programs, objects, and views before
doing similarity checking, all three actions are necessary.
You can use similarity checking along with execution-time name resolution or to
recover from DDL compilations; in both cases you can avoid automatic recompilations.
This subsection includes this information about similarity checking:
•
Using the CHECK option to direct the SQL executor to perform the similarity check
at execution time for recompilations
•
Using the COMPILE option to direct the SQL compiler to perform the similarity
check during explicit SQL compilation
•
Enabling the similarity check for tables and protection views using DDL statements
•
Enabling similarity checking for table and protection views
Using the CHECK Option
The CHECK option determines the behavior of the SQL executor at run time, during an
automatic recompilation, when it executes an invalid SQL statement or a statement
that refers to a DEFINE that has changed since the last explicit SQL compilation.
You can direct the SQL executor to use the similarity check to determine if a
statement’s execution plan is operable and can run without automatic recompilation.
The SQL executor then recompiles only the SQL statements that fail the similarity
check; it executes other SQL statements using their existing plans.