SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

Name Resolution, Similarity Checks, and Automatic
Recompilation
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
8-11
Similarity Check
run time might not always match the compile-time value, causing the similarity
check to fail.)
•
Both tables must have the same number of key columns. Corresponding key
columns must have the same:
°
Position, offset, and column number
°
Column attributes
•
If a DML statement uses an index, the run-time table must have an index that has
the same attributes as the index that was used by the compile-time table. For an
index on the run-time table to be considered similar to the compile-time index:
°
Both must be either unique or nonunique.
°
Both must have the same keytag value (SQL/MP indexes). (See the CREATE
INDEX statement in the SQL/MP Reference Manual for details about the
keytag specification of an index. If the index is created without specifying the
keytag, for the indexes of old and new tables to match, they must have been
created in the same sequence.)
°
Both must have the same number of key columns.
°
Both must have the same key column attributes.
•
For a SELECT statement, the tables must not have any unique indexes. If the
tables have unique indexes, the similarity check fails.
•
Both tables must have the same partitioning scheme (hash or range partitioned),
the same number of partitions, and the same partitioning keys if the plan does not
use OLT optimization.
•
Both tables must not have any check constraints or referential integrity constraints.
•
The query must not use compound statements.
•
ESP parallelism must not have been used.
•
The query must not include any views.
•
A CALL statement must not refer to a stored procedure in Java (SPJ) that has
been dropped and re-created in NonStop SQL/MX.
If a statement fails the similarity check, the SQL/MX executor, by default, invokes the
SQL/MX compiler to automatically recompile the SQL plan. For more information, see
Automatic Recompilation on page 8-12.
Note. If the plan uses OLT optimization, the number of partitions does not affect the
similarity check. For more information on OLT optimization, see the SQL/MX Query Guide.