HP NonStop SQL/MP, Programming Manual

Page: 302 / 331

Maximizing Local Autonomy
HP NonStop SQL/MP Programming Manual for C—429847-008
C-2
Using TACL DEFINEs
The next example uses the concept of maximizing local autonomy. The parts table is a
partitioned table that resides on these nodes:
A program declares an SQL cursor as follows:
EXEC SQL DECLARE get_part_cursor CURSOR FOR
SELECT partnum, partdesc, price, qty_available
FROM =parts
WHERE parts.partnum < 5000
AND parts.partdesc = "V8 DISK OPTION";
The program running on \NEWYORK uses a DEFINE to associate the PARTS table
with the first partition located at \NEWYORK.
If \PARIS is unavailable at compile time, the SQL compiler can still compile the
program because enough information is available in the catalogs on \NEWYORK,
where the first partition is registered.
Suppose that the compiler uses the index on \PARIS in the optimized execution plan. If
\PARIS is still unavailable at run time, the SQL executor invokes the SQL compiler to
automatically recompile the statement. The SQL compiler determines an execution
plan that does not use the index IXPART but sequentially scans the rows in the first
partition to find all parts that have “V8 DISK OPTION” in the PARTDESC column.
Using TACL DEFINEs
By using TACL DEFINEs in a program to refer to tables and associating those
DEFINEs with local partitions, you increase the number of successful compilations of
programs that access a distributed database. All SQL compilations are affected,
including explicit compilations and automatic recompilations.
Using Current Statistics
For a partitioned table to have local autonomy, the UPDATE STATISTICS statement
must be run on the table at least once. If the SQL catalog in which a table is registered
does not have any statistics for the table, the SQL optimizer does a catalog look-up
operation for each partition of the table to estimate the aggregate number of nonempty
blocks and records. Also, if the statistics for an unavailable partitioned table have not
been updated, you will receive an SQL warning and file-system error even if your query
does not try to retrieve any rows from the unavailable partition. Executing the UPDATE
STATISTICS statement eliminates both these problems.
\NEWYORK The first partition contains all rows in which PARTS.PARTNUM (the
primary key) is less than 5000.
\PARIS The second partition contains all rows in which PARTS.PARTNUM is
5000 or greater. An index on the PARTDESC column of table PARTS
is named IXPART.