1 – ADD_PARTITION
Adds the named partition to an existing hashed index. The
partition name must be unique within the index being altered.
No other clauses may appear in the same ALTER INDEX statement.
2 – BUILD_ALL_PARTITIONS
This clause operates on an index in build-pending state (created
using MAINTENANCE IS ENABLED DEFERRED) and builds all incomplete
partitions. If the index is not in build-pending state then the
statement completes successfully with a warning.
No other clauses may appear in the same ALTER INDEX statement.
3 – BUILD PARTITION partition-name
This clause operates on an index in build-pending state (created
using MAINTENANCE IS ENABLED DEFERRED) and builds the named
partition. If the index is not in build-pending state then the
statement completes successfully with a warning.
No other clauses may appear in the same ALTER INDEX statement.
4 – COMMENT_IS
Adds a comment about the index. SQL displays the text of the
comment when it executes a SHOW INDEX statement. Enclose the
comment in single quotation marks (') and separate multiple lines
in a comment with a slash mark (/).
5 – DROP_PARTITION
Specifies that the data in the named partition be migrated to
the next partition in the map and the named partition be dropped.
The last partition in the index cannot be dropped. The referenced
storage area is not dropped, only the index partition stored in
that area.
6 – DUPLICATES_ARE_ALLOWED
Converts a UNIQUE index to a non-unique index. An index altered
in this manner allows duplicate key values into the index. Note
that there is no way for you to reverse this change once you
commit the ALTER INDEX statement, other than by dropping and
redefining the index.
7 – IN
When specified as part of an ADD PARTITION clause, the IN area-
spec inserts a new partition in the index. If you do not specify
a WITH LIMIT OF clause or OTHERWISE clause, the IN area-spec
clause creates a new final partition.
When specified as part of an index STORE clause, the IN area-spec
clause associates the index directly with a single storage area,
and all entries in the index are stored in the area you specify.
8 – index-name
The name of the index.
9 – index-store-clause
A storage map definition for the index. You can specify a store
clause for indexes in a multifile database only. The STORE clause
lets you specify which storage area files are used to store the
index entries.
If you omit the storage map definition, the default is to store
all entries for the index in the default storage area.
See the the CREATE INDEX statement for details of the arguments
in an index store clause.
10 – LOGGING
Syntax options:
LOGGING | NOLOGGING
The LOGGING clause specifies that updates to new index partitions
should be logged in the recovery-unit journal file (.ruj) and
after-image journal file (.aij).
The NOLOGGING clause specifies that updates to new index
partitions should not be logged in the recovery-unit journal
file (.ruj) and after-image journal file (.aij).
If no store clause is used, then these attributes provide the
setting for the ALTER INDEX statement.
The LOGGING and NOLOGGING clauses are mutually exclusive; specify
only one. The LOGGING clause is the default.
11 – MAINTENANCE_IS_DISABLED
Disables, but does not delete, the specified index.
When managing a very large database, an index can become corrupt
or unsuitable for query optimization. If the table on which the
index has been defined is very large, it may take a considerable
amount of time to execute the DROP INDEX statement. Using the
MAINTENANCE IS DISABLED clause of the ALTER INDEX statement
disables the index so that it is no longer used by the optimizer
nor is it maintained. You can then execute the DROP INDEX
statement at a later time even when the table is in use.
Once an index has been disabled, it may be enabled again using
the REBUILD PARTITION clause.
To disable an index, you must have DROP privileges to the table
on which the index is defined, and there can be no active queries
on the table.
12 – MAINTENANCE_IS_ENABLED_DEFERRED
An index created using this clause does not contain index keys
for the current rows in the table. Until this index is built
(using ALTER INDEX . . . BUILD), the index is placed in a build-
pending state. Any table with a build-pending index can not be
updated using the INSERT, DELETE, or UPDATE statements.
13 – MAINTENANCE_IS_ENABLED_IMMEDIATE
This is the default behavior for CREATE INDEX. This clause
on ALTER INDEX allows a build-pending index to be made fully
operational.
14 – MOVE_PARTITION
Specifies that the data in the named partition be moved to
the partition identified in the area-spec clause and that the
current partition is to be dropped after the data is migrated.
For example, this clause allows a single hashed index partition
to be moved to a larger storage area when too many mixed area
extends are observed.
No other clauses may appear in the same ALTER INDEX statement.
15 – NODE_SIZE
The size, in bytes, of each index node in a sorted index. You
cannot specify this argument in an ALTER INDEX statement that
refers to a hashed index. See the CREATE INDEX statement for
details of the NODE SIZE clause.
This new node size is not applied to the existing index.
However, it will be used in subsequent rebuild operations and
by EXPORT/IMPORT to rebuild the database.
16 – PARTITION
Names the partition. The name can be a delimited identifier if
the dialect is set to SQL99 or quoting rules are set to SQL99.
Partition names must be unique within the index. If you do not
specify this clause, Oracle Rdb generates a default name for
the partition. The partition name is stored in the database and
validated.
17 – PERCENT_FILL
Specifies how much each index node should be filled as a
percentage of its size. You cannot specify this argument in an
ALTER INDEX statement that refers to a hashed index. The valid
range is 1 percent to 100 percent. The default is 70 percent.
Both the PERCENT FILL and USAGE clauses specify how full an index
node should be initially. You should specify either the PERCENT
FILL or USAGE clause but not both.
18 – PREFIX_CARDINALITY_COLLECTION_IS_DISABLED
This setting disables the cardinality collection and, instead,
uses a fixed scaling algorithm which assumes a well balanced
index tree. The action of this clause will also set the existing
index column cardinalities to zero.
19 – PREFIX_CARDINALITY_COLLECTION_IS_ENABLED
This is the default behavior for CREATE INDEX. The Oracle Rdb
optimizer collects approximate cardinality values for the index
columns to help in future query optimization. Note that no extra
I/O is incurred to collect these values and, therefore, adjacent
key values from other index nodes can not be checked. Hence, some
inaccuracy may be seen for these indexes. In most cases, this
is adequate for query optimizations. If this clause is used on
an index that is currently set to PREFIX CARDINALITY COLLECTION
DISABLED, the RMU Collect Optimizer_Statistics command needs to
be executed as soon as possible to load the correct values.
20 – PREFIX_CARDINALITY_COLLECTION_IS_ENABLED_FULL
This setting requests that extra I/O be performed, if required,
to ensure that the cardinality values reflect the key value
changes of adjacent index nodes. If this clause is used on an
index which is currently set to PREFIX CARDINALITY COLLECTION
DISABLED or ENABLED, the RMU Collect Optimizer_Statistics command
needs to be executed as soon as possible to load the correct
values.
21 – REBUILD_ALL_PARTITIONS
This clause combines the TRUNCATE and BUILD actions into a single
function. No other clauses may appear in the same ALTER INDEX
statement.
22 – REBUILD PARTITION partition-name
This clause combines the TRUNCATE and BUILD actions into a single
function for the named partition. No other clauses may appear in
the same ALTER INDEX statement.
23 – RENAME_PARTITION
Changes the name of a partition. This clause can be applied to
all types of indexes. It is particularly useful for specifying
a more meaningful name for the default partition. Use the SHOW
INDEX (PARTITION) statement to display the default names of the
partitions. See Example 4 in the Examples section.
No other clauses may appear in the same ALTER INDEX statement.
24 – threshold-clause
Specifies one, two, or three default threshold values for logical
areas that contain the index in storage areas with uniform
page formats. By setting threshold values, you can make sure
that Oracle Rdb does not overlook a page with sufficient space
to store compressed data. The threshold values (val1, val2,
and val3) represent a fullness percentage on a data page and
establish three possible ranges of guaranteed free space on the
data pages.
If you use data compression, you should use logical area
thresholds to obtain optimum storage performance.
You cannot specify the thresholds for the storage map attribute
for any area that is a mixed page format. If you have a mixed
page format, set the thresholds for the storage area using the
ADD STORAGE AREA or CREATE STORAGE AREA clause of the ALTER
DATABASE, CREATE DATABASE, or IMPORT statement.
For more information about SPAM pages, see the Oracle Rdb Guide
to Database Design and Definition.
25 – TRUNCATE_ALL_PARTITIONS
This clause operates in a similar way to TRUNCATE TABLE, but just
on one index. The index is automatically set to MAINTENANCE IS
ENABLED DEFERRED (i.e. build-pending state) if it was currently
ENABLED IMMEDIATE. Otherwise is stays in a disabled state.
No other clauses may appear in the same ALTER INDEX statement.
26 – TRUNCATE PARTITION partition-name
This clause operates on just the named index partition. The index
is automatically set to MAINTENANCE IS ENABLED DEFERRED (that
is, build-pending state) if it was currently ENABLED IMMEDIATE.
Otherwise is stays in a disabled state.
No other clauses may appear in the same ALTER INDEX statement.
27 – USAGE
Syntax options:
USAGE UPDATE | USAGE QUERY
Specifies a PERCENT FILL value appropriate for update-intensive
or query-intensive applications. You cannot specify this argument
in an ALTER INDEX statement that refers to a hashed index. The
USAGE UPDATE argument sets the PERCENT FILL value at 70 percent.
The USAGE QUERY argument sets the PERCENT FILL value at 100
percent.
You should specify either the PERCENT FILL or USAGE clause, but
not both.
28 – USING
Specifies columns whose values are used as limits for
partitioning the index across multiple storage areas. You cannot
name columns not specified as index key segments.
If the index key is multisegmented, you can include some or all
of the columns that are joined to form the index key. You must
specify the columns in the order in which they were specified
when the index key was defined. If you include only a subset of
the columns from the multisegmented index, you must include the
leading columns of the index key.
29 – WITH_LIMIT_OF
Specifies the highest value for the index key that resides in a
particular storage area if ASCENDING is defined. If DESCENDING
is defined, the lowest value is specified for the index key
that resides in a particular storage area. For multicolumn index
keys, specify a literal value for each column listed in the USING
clause.
The WITH LIMIT OF clause must specify a new unique set of values
for the partition. The number of literals in the list must be
the same as the number of columns in the USING clause. The data
type of the literals must agree with the data type of the column.
For character columns, enclose the literals in single quotation
marks.