1 – ASCENDING
An optional keyword that causes SQL to create ascending index
segments. If you omit the ASCENDING or DESCENDING keyword,
ascending is the default.
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 – column-name
The name of the column or columns that make up the index key.
You can create a multisegmented index key by naming two or more
columns, which are joined to form the index key. All the columns
must be part of the same table. Separate multiple column names
with commas.
NOTE
If column-name refers to a column defined as CHAR, VARCHAR
or LONG VARCHAR data type, the size of the column must be
less than or equal to 254 characters, or the SIZE IS clause
must be used.
5 – COMMENT IS 'string '
Adds a comment about the storage map definition for the index.
SQL displays the text of the comment when it executes a SHOW
INDEXES statement. Enclose the comment in single quotation marks
( ') and separate multiple lines in a comment with a slash mark
(/).
6 – DESCENDING
An optional keyword that causes SQL to create descending index
segments. If you omit the ASCENDING or DESCENDING keyword,
ascending is the default.
7 – DISABLE_COMPRESSION
Disables compression indexes.
If compression is disabled, no form of compression is used for
hashed indexes, and prefix compression or suffix compression is
used for sorted indexes. Prefix compression is the compression
of the first bytes of an index key that are common in consecutive
index keys. Prefix compression saves space by not storing these
common bytes of information. Conversely, suffix compression is
the compression of the last bytes from adjacent index keys. These
bytes are not necessary to guarantee uniqueness.
You cannot enable index compression using the ALTER INDEX
statement once you specified the DISABLE COMPRESSION clause of
the CREATE INDEX statement.
Index compression is disabled by default.
8 – DUPLICATES_ARE_COMPRESSED
Specifies that duplicates are compressed. If a sorted ranked
index allows duplicate entries, you can store many more records
in a small space when you compress duplicates, therefore,
minimizing I/O and increasing performance. Oracle Rdb uses
patented technology called byte-aligned bitmap compression
to represent the dbkeys for the duplicate entries instead of
chaining the duplicate entries together with uncompressed dbkeys.
Duplicates are compressed by default if you specify RANKED
without specifying the DUPLICATES ARE COMPRESSED clause.
You cannot use the DUPLICATES ARE COMPRESSED clause when you
create nonranked indexes or when you specify the UNIQUE keyword.
See the Oracle Rdb Guide to Database Design and Definition for
more information on sorted ranked B-tree indexes.
9 – ENABLE_COMPRESSION
Specifies that sorted and hashed indexes are stored in a
compressed form.
If compression is enabled, Oracle Rdb uses run-length compression
to compress a sequence of space characters (octets) from text
data types and binary zeros from nontext data types. Different
character sets have different representations of the space
character. Oracle Rdb compresses the representation of the space
character for the character sets of the columns comprising the
index values.
You cannot disable index compression using the ALTER INDEX
statement once you specified the ENABLE COMPRESSION clause of
the CREATE INDEX statement.
For more information on compressed indexes, see the Oracle Rdb
Guide to Database Design and Definition.
10 – IN area name
Associates the index directly with a single storage area. All
entries in the index are stored in the area you specify.
11 – index-name
The name of the index. You can use this name to refer to the
index in other statements. You must qualify the index name with
the authorization identifier if the schema is not the default
schema. When choosing a name, specify a valid name. See the User_
Supplied_Names HELP topic for more information.
12 – 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
in a CREATE INDEX statement allows you to specify which storage
area files are used to store the index entries:
o All index entries can be associated with a single storage
area.
o Index entries can be systematically distributed, or
partitioned, among several storage areas by specifying upper
limits on the values for a key in a particular storage area.
If you omit the storage map definition, the default is to store
all the entries for an index in the main default storage area.
You should define a storage area for an index that matches the
storage map for the table with which it is associated.
In particular, under the following conditions, the database
system stores the index entry for a row on or near the same data
page that contains the actual row:
o The storage areas for a table have a mixed page format.
o You specify an identical store clause for the index as exists
in the storage map for the table.
o The storage map for the table also names the index in the
PLACEMENT VIA INDEX clause.
Such coincidental clustering of indexes and rows can reduce I/O
operations. With hashed indexes and coincidental clustering, the
database system can retrieve rows for exact-match queries in one
I/O operation.
For sorted indexes, specifying an identical storage map reduces
I/O contention on index nodes.
13 – LOGGING
Syntax options:
LOGGING |NOLOGGING The LOGGING clause specifies that index nodes
and hash buckets be logged when written to the database. Logging
includes writing data and management records to the recovery-unit
journal file (.ruj) and after-image journal files (.aij). When
the NOLOGGING clause is specified then only a small number of
management records are logged in the recovery-unit journal file
(.ruj) and after-image journal files (.aij). See the Usage Notes
below for more information.
LOGGING and NOLOGGING can be specified per storage area
(partition) or as a default for the CREATE INDEX statement. The
LOGGING and NOLOGGING clauses are mutually exclusive; specify
only one. The LOGGING clause is the default.
14 – MAINTENANCE_IS_DISABLED
An index created using this clause is not maintained. The index
definition serves only as a template.
15 – 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.
16 – MAINTENANCE_IS_ENABLED_IMMEDIATE
This is the default behavior for CREATE INDEX.
17 – MAPPING_VALUES
l to h
A compression clause for all-numeric columns that translates the
column values into a compact, encoded form. You can mix mapped
and unmapped columns, but the most storage space is gained by
building indexes of multiple columns of data type SMALLINT or
INTEGER. Oracle Rdb attempts to compress all such columns into
the smallest possible space.
The l (low) through h (high) specifies the range of integers as
the value of the index key.
The Oracle Rdb SQL Reference Manual describes addiional
restrictions about this argument.
18 – MINIMUM_RUN_LENGTH
Specifies the minimum length of the sequence that Oracle Rdb
should compress. You cannot alter this value once you set it.
If you specify MINIMUM RUN LENGTH 2, Oracle Rdb compresses
sequences of two or more spaces or of two or more binary zeros
for single-octet character sets, and compresses one space or
one binary zero for multi-octet character sets. As it compresses
the sequences, Oracle Rdb replaces the sequence with the value
of the minimum run length plus 1 byte. If many of the index
values contain one space between characters in addition to
trailing spaces, use a minimum run length of 2, so that you do
not inadvertently expand the index beyond the 255-byte limit. If
you inadvertently expand the index beyond 255 bytes during index
creation, Oracle Rdb returns a warning message.
The default minimum run length value is 2. Valid values for the
minimum run length range from 1 to 127. Oracle Rdb determines
which characters are compressed.
19 – NODE_SIZE
The size in bytes of each index node.
The number and level of the resulting index nodes depend on:
o This number-bytes value
o The number and size of the index keys
o The value specified in the PERCENT FILL or USAGE clauses
If you omit the NODE SIZE clause, the default value is:
o 430 bytes if the total index key size is 120 bytes or less
o 860 bytes if the total index key size is more than 120 bytes
The index key size is the number of bytes it takes to represent
the column value in the sorted index.
See the Oracle Rdb SQL Reference Manual for details on
determining the valid range for a user-specified index node size.
20 – OTHERWISE IN area name
For partitioned storage maps only, specifies the storage area
that is used as the overflow partition. An overflow partition
is a storage area that holds any values that are higher than
those specified in the last WITH LIMIT TO clause. An overflow
partition holds those values that "overflow" the partitions that
have specified limits.
21 – PARTITION name
Names the partition. The name can be a delimited identifier.
Partition names must be unique within the index. If you do not
specify this clause, Oracle Rdb generates a default name for the
partition.
22 – PERCENT_FILL
Specifies the initial fullness percentage for each node in the
index structure being changed. 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. Specify either PERCENT FILL or USAGE,
but not both.
23 – 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.
24 – 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.
25 – 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.
26 – 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.
27 – 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.
28 – SIZE IS n
A compression clause for text or varying text index keys that
limits the number of characters used for retrieving data. The n
specifies the number of characters of the key that are used in
the index.
NOTE
Although you can create a SIZE IS index and specify the
UNIQUE clause, truncating the index key values may make the
key values non-unique. In this case, the index definition or
insert or update statements fail.
29 – STORE IN area name
Associates the index directly with a single storage area. All
entries in the index are stored in the area you specify.
30 – STORE_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
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 only include a subset
of the columns, you must include the leading segments of the
multisegmented index.
For example, if a CREATE INDEX statement specifies a
multisegmented index based on the columns LAST_NAME, FIRST_NAME,
and MIDDLE_INITIAL, then the USING clause must include the first
segment LAST_NAME, or the first two segments, LAST_NAME, and
FIRST_NAME, or all the segments of the index. This is true for
sorted indices only.
The database system uses the values of the columns specified in
the STORE USING clause as a key to determine in which storage
area an index entry associated with a new table row belongs.
There is no restriction for hashed scattered indexes. For hashed
ordered indexes, all segments listed, except the last segment
can be included. Also, HASHED ORDERED indexes have further
restrictions on the data type of the final column; it must be
a date/time or integer numeric data type.
31 – STORED_NAME_IS
Specifies a name that Oracle Rdb uses to access an index created
in a multischema database. The stored name allows you to access
multischema definitions using interfaces, such as Oracle RMU,
the Oracle Rdb management utility, that do not recognize multiple
schemas in one database. You cannot specify a stored name for
an index in a database that does not allow multiple schemas. For
more information about stored names, see the User_Supplied_Names
HELP topic.
32 – table-name
The name of the table that includes the index. The table must be
in the same schema as the index.
33 – 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. For more information about logical area thresholds,
see the CREATE STORAGE_MAP.
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 statements.
For more information about SPAM pages, see the Oracle Rdb Guide
to Database Design and Definition.
34 – 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.
35 – 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.
36 – TYPE_HASHED
Syntax options:
TYPE IS HASHED ORDERED | TYPE IS HASHED SCATTERED
Specifies that the index is a hashed index. If you specify
HASHED, you cannot use the NODE SIZE, PERCENT FILL, or USAGE
clauses. You can, however, specify if the data is ORDERED or
SCATTERED. SCATTERED is the default.
The TYPE IS HASHED SCATTERED clause is appropriate in situations
where data is not evenly distributed across storage areas. This
option places a record in a page that is chosen by applying a
hashing algorithm to the index key. As a result, the record
distribution pattern is not guaranteed to be even; therefore,
some pages may be chosen more often than others. The TYPE IS
HASHED SCATTERED clause is the default and is recommended unless
your data meets the following criteria for the TYPE IS HASHED
ORDERED clause:
o The last column of the index key must be one of the following
data types:
- TINYINT
- SMALLINT
- INTEGER
- BIGINT
- DATE (both ANSI and VMS)
- TIME
- TIMESTAMP
- INTERVAL
o The index must be ascending.
o The index must not be compressed or have mapping values.
The TYPE IS HASHED ORDERED clause is ideal for applications where
the index key values are evenly distributed across a given range.
This places a record in a page derived by applying an ordered
hashing algorithm to the index key. As a result, the distribution
pattern is guaranteed to follow the index key distribution. In
addition, if you know the range of values, you can size the
storage area and pages to minimize overflows. If the index
key values are not evenly distributed, use the TYPE IS HASHED
SCATTERED clause.
Hashed indexes must be stored in storage areas created with
mixed page format, which means they are valid only in multifile
databases.
Hashed indexes provide fast and direct access to specific rows
and are effective mainly for queries that specify an exact-match
retrieval on a column or columns that are also the key to a
hashed index. (For instance, SELECT EMPLOYEE_ID FROM EMPLOYEES
WHERE EMPLOYEE_ID = "00126", makes effective use of a hashed
index with EMPLOYEE_ID as the index key.)
In a hashed indexing scheme, the index key value is converted
mathematically to a relative page number in the storage area
of a particular table. A hash bucket is a data structure that
maintains information about an index key, and a list of internal
pointers, called database keys or dbkeys, to rows that contain
the particular value of the index key. To find a row using the
hashed index, the database system searches the hash bucket, finds
the appropriate dbkey, and then fetches the table row.
Hashed indexes are most effective for random, direct access when
the query supplies the entire index key on which the hashed index
is defined. For these types of access, I/O operations can be
significantly reduced. This is particularly useful for tables
with many rows and large indexes. For example, to retrieve a
row using a sorted index that is four levels deep, the database
system may need to perform five I/O operations. By using hashing,
the number of I/O operations is reduced to two, at most.
You can define a hashed index and a sorted index for the same
column. Then, depending on the type of query you use, the Oracle
Rdb optimizer chooses the appropriate method of retrieval. For
example, if your query contains an exact-match retrieval, the
optimizer uses hashed index access. If your query contains
a range retrieval, the optimizer uses the sorted index. This
strategy incurs the additional overhead of maintaining two
indexes, therefore, you need to consider the advantages of fast
retrieval against the disadvantages of updating two indexes for
every change to data.
See the Oracle Rdb Guide to Database Design and Definition for
a detailed discussion of the relative advantages of hashed and
sorted indexes.
37 – TYPE_IS_SORTED
Specifies that the index is a sorted, nonranked (B-tree) index.
If you omit the TYPE IS clause, SORTED is the default. Sorted
indexes improve the performance of queries that compare values
using range operators (like BETWEEN and greater than (>)). (For
example, SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE EMPLOYEE_ID
> 200 is a query that specifies a range retrieval and makes
effective use of a sorted index.)
You can define a hashed index and a sorted index for the same
column. Then, depending on the type of query you use, the Oracle
Rdb optimizer chooses the appropriate method of retrieval. For
example, if your query contains an exact-match retrieval, the
optimizer may use hashed index access. If your query contains
a range retrieval, the optimizer uses the sorted index. This
strategy incurs the additional overhead of maintaining two
indexes; however, you need to consider the advantages of fast
retrieval against the disadvantages of updating two indexes for
every change to data.
See the Oracle Rdb Guide to Database Design and Definition for
more information on the relative advantages of hashed and sorted
indexes.
If you specify a SORTED index, you can optionally specify
NODE SIZE, PERCENT FILL, and USAGE clauses that control the
characteristics of the nodes in the index.
38 – TYPE_IS_SORTED_RANKED
Specifies that the index is a sorted, ranked (B-tree) index.
The ranked B-tree index allows better optimization of queries,
particularly queries involving range retrievals. Oracle Rdb can
make better estimates of cardinality, reducing disk I/O and lock
contention. Oracle Rdb recommends using ranked sorted indexes.
39 – UNIQUE
A keyword that specifies whether or not each value of the index
must be unique. If you try to store the same value twice in a
column or set of columns that have an index defined as UNIQUE,
SQL returns an error message the second time and does not store
or modify the row that contains the value. This is true for null
values as well as any other value.
If you specify UNIQUE, SQL checks as it executes the CREATE INDEX
statement to see if the table already contains duplicate values
for the index.
40 – USAGE
Syntax options:
USAGE UPDATE | USAGE QUERY
Specifies a PERCENT FILL value appropriate for update- or query-
intensive applications. The USAGE UPDATE clause sets the PERCENT
FILL value at 70 percent. The USAGE QUERY clause sets the PERCENT
FILL value at 100 percent.
41 – 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 are. For multicolumn index keys,
specify a literal value for each column.
The number of literals in the list must be the same as the number
of columns in the USING clause. Repeat this clause to partition
the entries of an index among multiple storage areas. 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.
If you are creating a multisegmented index using multisegmented
keys and the STORE USING . . . WITH LIMIT clause, and if the
values for the first key are all the same, then set the limit
for the first key at that value. This ensures that the value of
the second key determines the storage area in which each row is
stored.