SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-16
Optimizing Index Use
>> DROP TABLE $vol.data.table1;
>> DROP TABLE $vol2.data.table2;
.
.
>> DROP CATALOG $vol.cat1;
You can use the CLEANUP utility to purge SQL objects (except SQL programs stored
in OSS files) and the catalogs in which they are described. Normally, the CLEANUP
utility is not recommended for undamaged objects. In cases where a test database is
self-contained in a test catalog, however, the CLEANUP utility can be used to purge
the objects and the associated catalog.
Be sure not to apply the CLEANUP utility to a catalog in which a production database
is described.
This example demonstrates purging objects with the CLEANUP utility:
25> SQLCI
>> CLEANUP !
+> (*.*.* FROM CATALOG $vol.testcat) CATALOGS;
In the CLEANUP command, $vol.testcat is the name of a test catalog.
Before deleting any database, consider saving it as a test database for regression tests
on modified applications at your site.
Optimizing Index Use
An index on a table provides an alternate access path that differs from the inherent
access path (primary key). Indexes improve application performance for data retrieval
operations. When compiling a statement, the SQL compiler selects the execution plan
for a statement by choosing the best access path to the data. If an index exists, the
SQL compiler evaluates using the index.
Indexes can also improve performance by eliminating the need for the disk process to
access the underlying table. If the query can be satisfied by the columns contained in
the index and the access returns unique rows, the underlying table will not be
accessed.
When evaluating whether to use an index or a table scan, SQL compares the number
of base table scan I/Os and the I/Os for index access. The use of sequential cache for
a scan increases the performance of the scan and increases the likelihood of its use.
Index-only access is faster than a table scan. A sort prevented by index access must
be looked at closely, however, because the cost of a scan plus a sort might be less
than the cost of index and base table access. For more information about selectivity
and cost, see the SQL/MP Query Guide.
Note. To drop an SQL program stored in an OSS file, use the corresponding OSS utility. For
more information, see the Open System Services Shell and Utilities Manual.