SQL/MP Reference Manual

ManualsBrandsHP ManualsServerHP NonStop G-Series

HP NonStop SQL/MP Reference Manual—523352-013

A-19

Considerations—ALTER INDEX



To alter an index, you must be a generalized owner of the index and the underlying

table. In addition, you must have authority to read and write to the affected

catalogs.

ALTER INDEX executes only if the specified index or partition is accessible. Unless

you are altering file attributes for a partition, all partitions of the index must be

accessible.

Only one DDL statement can operate on a given SQL object (or partition of an SQL

object) at a time. An error occurs if you attempt to run an ALTER INDEX statement

while another process is executing a DDL operation on the same object. The

specific error depends on the DDL operation involved and the phase of the

operation at which the conflict occurs. For more information, see DDL (Data

Definition Language) Statements on page D-20.

Additional authorization and access requirements that exist for some ALTER

INDEX operations are described in the coming subsections.



You cannot use ALTER INDEX in a user-defined transaction if the index is not

audited, if the index is audited and the operation requires data movement, or if the

operation is a simple move or a two-way split.



You cannot rename an index within a user-defined transaction.



Altering file attributes for indexes



To alter security attributes for an index, both the index and its underlying table

must be accessible.

You should normally avoid altering the SECURE attribute of an index

independent of the SECURE attribute for the underlying table. Restricting read

and write access to an index by users who have access to the underlying table

can cause some queries on the table (those that use the index) to fail for

security reasons while other queries on the same table by the same users

succeed. (Note that SQL automatically changes the SECURE and OWNER

attributes for an index when you change them for the underlying table, but not

the reverse.)



Changing the SECURE attribute of an index can affect processes using the

index when the change occurs. Such processes can continue using the index

when they have it open, but might not be able to reopen the index after closing

it.

Changing the OWNER for a table automatically changes the OWNER of

indexes and protection views defined on the table.



ALTER INDEX changes file attributes for an index by changing information in

the file label for the index and in the catalog tables of the catalog that describes

the index.