SQL/MP Installation and Management Guide

Managing Database Applications
HP NonStop SQL/MP Installation and Management Guide—523353-004
10-18
Using the CHECK Option
Example: Preventing Recompilations After a DDL Change
To prevent recompilation, enable the similarity check for all referenced tables and
protection views and compile the program with the CHECK INOPERABLE PLANS
option. To do this, follow these steps:
1. Enable the similarity check for each table or protection view specified in the SQL
statements as follows:
•
For existing tables, use the ALTER TABLE or ALTER VIEW statement with the
SIMILARITY CHECK ENABLE clause.
•
If you are creating a new table or protection view, use the CREATE TABLE or
CREATE VIEW statement with the SIMILARITY CHECK ENABLE clause.
2. Explicitly SQL compile the program with the CHECK INOPERABLE PLANS option
to enable the similarity check.
3. Run the program as usual. These DDL operations do not invalidate the program,
because it was compiled with the CHECK INOPERABLE PLANS option and uses
the similarity check for any referenced tables or protection views:
•
ALTER TABLE...ADD PARTITION statement
•
ALTER TABLE...ADD COLUMN statement (for more information, including
restrictions, see Enabling the Similarity Check for Tables and Protection Views
on page 10-26)
•
ALTER TABLE statement to move or split partitions (including a simple move,
one-way split, or two-way split)
•
ALTER TABLE...DROP PARTITION statement
•
ALTER INDEX...DROP PARTITION statement
•
ALTER INDEX statement to move or split index partitions
•
CREATE INDEX statement
•
UPDATE STATISTICS...RECOMPILE statement
Also, if a DDL operation does cause a program to be invalidated, the SQL executor
still performs the similarity check. If the similarity check passes for an SQL
statement, the SQL executor executes the statement without recompiling it.