HP NonStop SQL/MP User Manual

Program Invalidation and Automatic SQL
Recompilation
HP NonStop SQL/MP Programming Manual for C—429847-008
8-14
Preventing Automatic Recompilations
An SQL statement uses unqualified column names and the additional columns make
one of the column names used in the statement ambiguous. When the statement is
compiled, the column names are resolved unambiguously. However, if the execution
plan for the statement is executed against a RUN-TIME-TABLE with more columns
than the COMPILE-TIME-TABLE, the column names might not be resolved
unambiguously.
For example, consider these SQLCI commands:
CREATE TABLE table1 (a INTEGER, b INTEGER);
INSERT INTO table1 VALUES (11,22);
CREATE TABLE table2 (c INTEGER, d INTEGER);
INSERT INTO table2 VALUES (33,44);
PREPARE statement1 FROM SELECT a,b,c,d FROM table1, table2;
EXECUTE statement1; -- Returns 11,22,33,44
ALTER TABLE table1 ADD COLUMN c INTEGER DEFAULT NULL;
PREPARE statement1; -- Returns an error because the compiler
-- cannot resolve column c unambiguously
A similar situation occurs when you specify the CHECK INOPERABLE PLANS option
and execution-time name resolution. When the SQL executor tries to use the plan with
a new set of tables, it retains the association of unqualified column names with tables
established when the statement was explicitly compiled. However, if the similarity
check fails and automatic recompilation is attempted, the recompilation also fails
because of the ambiguity.
If an INSERT statement does not specify the column-name list, the statement must
specify values for all columns in the table, as follows:
INSERT INTO table1 VALUES (1,2,3,4);
INSERT INTO table1 (SELECT a,b,c,d FROM table2);
For these statements to compile successfully, table1 must have four columns at both
compile time and run time. A
program cannot use the CHECK INOPERABLE PLANS
option to run the statement against table1 after a column has been added to the run-
time version of table1. In this case, the similarity check fails and the statement is
automatically recompiled.
SELECT columna FROM table1
WHERE EXISTS
(SELECT [DISTINCT] * FROM table2)
TABLE1 = Pass
TABLE2 = Fail
INSERT INTO table1
(SELECT [DISTINCT] * FROM table2)
TABLE1 = Fail
TABLE2 = Fail
SELECT table1.*,table2.x
FROM table1,table2
TABLE1 = Fail,
TABLE2 = Pass
Statement Similarity Check Results

HP NonStop SQL/MP Specifications

General

Brand	HP
Model	NonStop SQL/MP
Category	Software
Language	English