Creates an index for a table. An index allows direct access to
the rows in the table to avoid sequential searching.
You define an index by listing the columns in a table that make
up the index. You can define more than one index for a table. The
index can be made up of one column, or two or more columns. An
index made up of two or more columns is called a multisegmented
index.
Optional arguments to the CREATE INDEX statement let you specify:
o The type of index structure (hashed, sorted nonranked, or
sorted ranked)
o The names of a storage area or storage areas that contain the
index
o Physical characteristics of a sorted index structure, such as
index node size and the initial fullness percentage of each
node
o Compression characteristics, including compressed key suffixes
for text indexes and integer column compression for smallint
or integer numeric columns
o Compression of space characters from text data types and of
binary zeros from nontext data types
o Thresholds for the logical storage areas that contain the
index
o A comment for the index definition
o Whether logging to the .ruj and .aij files is enabled or
disabled for the create index operation
1 – Environment
You can use the CREATE INDEX statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1;4mCREATE[m[1m qwqqqqqqqqqqqwq> [1;4mINDEX[m[1m <index-name> qqqqqqqqqqqqqqqqqqk [m
[1m mq> [1;4mUNIQUE[m[1m qj x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> [1;4mON[m[1m <table-name> qqk [m
[1m mq> [1;4mSTORED[m[1m [1;4mNAME[m[1m IS <stored-name> qqqj x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mm> ( w> <column-name> qqk [m
[1m x lqqqqqqqqqqqqqqj [m
[1m x mqwqqqqqqqqqqqqqwwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq ) qk [m
[1m x t> ASCENDING qut> SIZE IS <n> qqqqqqqqqqqqqqqu x x [m
[1m x m> DESCENDING jm> MAPPING VALUES <l> TO <h> qj x x [m
[1m mqqqqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqqqqqqqj x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmwqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqk [m
[1m mq> type-clause qj mq> index-attributes-clause qj x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmwqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq> [m
[1m mq> index-store-clause qj [m
[1m [m
(B)0[m[1mtype-clause = [m
[1m [m
[1mq> [1;4mTYPE[m[1m IS w> [1;4mHASHED[m[1m wqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqw> [m
[1m x t> [1;4mORDERED[m[1m qqqu x [m
[1m x m> [1;4mSCATTERED[m[1m qj x [m
[1m m> [1;4mSORTED[m[1m wqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqkx [m
[1m m> [1;4mRANKED[m[1m qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwj xx [m
[1m m> [1;4mDUPLICATES[m[1m ARE [1;4mCOMPRESSED[m[1m qj xx [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqjx [m
[1m mqwqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqj [m
[1m m> sorted-index-clause qj [m
[1m [m
(B)0[m[1msorted-index-clause = [m
[1m [m
[1mqqwqwq> [1;4mNODE[m[1m [1;4mSIZE[m[1m <number-bytes> qqqqqqqqqqwqwqq> [m
[1m x tq> [1;4mPERCENT[m[1m [1;4mFILL[m[1m <percentage> qqqqqqqqqu x [m
[1m x mq> [1;4mUSAGE[m[1m qwq> [1;4mUPDATE[m[1m qwqqqqqqqqqqqqqqqj x [m
[1m x mq> [1;4mQUERY[m[1m qqj x [m
[1m mqqqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqqj [m
[1m [m
(B)0[m[1mindex-attributes-clause = [m
[1m [m
[1mqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq>[m
[1m x tq> [1;4mENABLE[m[1m [1;4mCOMPRESSION[m[1m [m [1mrlc-attr qqqqqqqqqqqqqqqqqqqqqqqqqu x [m
[1m x tq> [1;4mDISABLE[m[1m [1;4mCOMPRESSION[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x [m
[1m x[m [1mtq> threshold-clause qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m [1mx[m
[1m x tq> [1;4mLOGGING[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x [m
[1m x tq> [1;4mNOLOGGING[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x [m
[1m x tq> [1;4mCOMMENT[m[1m IS qwq> '<string>' qwqqqqqqqqqqqqqqqqqqqqqqqqqu x [m
[1m x x mqqqqqqq / <qqqqj [m [1m x x [m
[1mx[m [1mtq> [1;4mPREFIX[m [1;4mCARDINALITY[m [1;4mCOLLECTION[m[1m IS qwq>[m [1;4mENABLED[m [1mqqqqqqwqu[m [1mx[m
[1mx[m [1mx[m [1mtq>[m [1;4mENABLED[m [1;4mFULL[m [1mqu x[m [1mx[m
[1mx[m [1mx[m [1mmq>[m [1;4mDISABLED[m [1mqqqqqj[m [1mx[m [1mx[m
[1mx[m [1mmq> [1;4mMAINTENANCE[m[1m IS qwq> [1;4mDISABLED[m [1mqqqqqqqqqqqqqqqqqwqqqqqqqj x[m
[1mx[m [1mmq>[m [1;4mENABLED[m[1m qwqqqqqqqqqqqqqqwqj[m [1mx[m
[1mx[m [1mtq>[m [1;4mDEFERRED[m [1mqqu [m [1mx[m
[1mx[m [1mmq> [1;4mIMMEDIATE[m[1m qj[m [1mx[m
[1mmqqqqqqqqqqqqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj[m
(B)0[m[1mrlc-attr =[m
qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq[1mwq[mqqqqqqqq>
mqq> [1m(MINIMUM RUN LENGTH <n> ) qqj[m
(B)0[m[1mindex-store-clause = [m
[1m [m
[1;4mSTORE[m[1m qqqqqqqqqqqqqqqqqqqqqqk [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmwq> [1;4mIN[m[1m area-spec qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqw> [m
[1m mq> [1;4mUSING[m[1m q> ( qwqq> <column-name> qwq> ) qqqqqqqqqqk x [m
[1m mqqqqqqq , <qqqqqqqqj x x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x [m
[1m mw> [1;4mIN[m[1m area-spec qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqk x [m
[1m x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x [m
[1m x mq> [1;4mWITH[m[1m [1;4mLIMIT[m[1m [1;4mOF[m[1m q> ( qwqq> <literal> qwq> ) qqqwqqqk x [m
[1m x mqqqqqq , <qqqqqj x x x [m
[1m mqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqj x x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x [m
[1mmwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwj [m
[1m mq> [1;4mOTHERWISE[m[1m IN area-spec qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
(B)0[m[1mthreshold-clause = [m
[1m [m
[1mqqwq> [1;4mTHRESHOLD[m[1m qwq> IS qwq> ( qq> <val1> qq> ) qqqqqqqqqwq> [m
[1m x mq> OF qj x [m
[1m x x [m
[1m mq> [1;4mTHRESHOLDS[m[1m qwq> ARE qwqqqqqqqqqqqk x [m
[1m mq> OF qj x x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x [m
[1m mq> ( qq> <val1> qwqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> ) qj [m
[1m mq> , <val2> qwqqqqqqqqqqqqqu [m
[1m mq> , <val3> qj [m
[1m [m
(B)0[m[1marea-spec =[m [1m [m
[1m [m
[1mqq> <area-name> qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> [m
[1m mq> ( qwwq> threshold-clause qqqqqqqqqqqqqqqwwq> ) qj [m
[1m xtq> [1;4mLOGGING[m[1m qqqqqqqqqqqqqqqqqqqqqqqqux [m
[1m xtq> [1;4mNOLOGGING[m[1m qqqqqqqqqqqqqqqqqqqqqqux [m
[1m xtq> [1;4mPARTITION[m[1m <name> qqqqqqqqqqqqqqqux [m
[1m xmq> [1;4mCOMMENT[m[1m IS qwqq> 'string' qqqwqqjx [m
[1m x mqqqqqqq / <qqqqqj x [m
[1m mqqqqqqqqqqqqqqqq , qqqqqqqqqqqqqqqqqj [m
3 – Arguments
3.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.
3.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.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.
3.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.
3.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
(/).
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.14 – MAINTENANCE_IS_DISABLED
An index created using this clause is not maintained. The index
definition serves only as a template.
3.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.
3.16 – MAINTENANCE_IS_ENABLED_IMMEDIATE
This is the default behavior for CREATE INDEX.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.32 – table-name
The name of the table that includes the index. The table must be
in the same schema as the index.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
4 – Examples
Example 1: Creating a simple table index
This statement names the index (EMP_EMPLOYEE_ID) and names the
column to serve as the index key (EMPLOYEE_ID).
The UNIQUE argument causes SQL to return an error message if a
user tries to store an identification number that is already
assigned.
SQL> CREATE UNIQUE INDEX EMP_EMPLOYEE_ID ON EMPLOYEES
cont> (EMPLOYEE_ID);
Example 2: Creating an index with descending index segments
This statement names the index (EMP_EMPLOYEE_ID) and names
the column to serve as the descending index key (EMPLOYEE_ID
DESCENDING).
The DESCENDING keyword causes the keys to be sorted in descending
order. If you do not specify DESCENDING or ASCENDING, SQL sorts
the keys in ascending order.
SQL> CREATE UNIQUE INDEX EMP_EMPLOYEE_ID ON EMPLOYEES
cont> (EMPLOYEE_ID DESCENDING);
Example 3: Creating a multisegmented index
SQL> CREATE INDEX EMP_FULL_NAME ON EMPLOYEES
cont> (LAST_NAME,
cont> FIRST_NAME,
cont> MIDDLE_INITIAL);
This statement names three columns to be used in the index EMP_
FULL_NAME. SQL concatenates these three columns to make the
multisegmented index.
Example 4: Creating a compressed numeric index
SQL> CREATE INDEX YEAR1_IND ON DEGREES
cont> (YEAR_GIVEN ASCENDING MAPPING VALUES 1950 TO 1970);
This statement creates ascending index segments for the YEAR_
GIVEN column in the DEGREES table, compressing the year values.
Example 5: Creating a truncated text index
SQL> CREATE INDEX COL_NAME_IND ON COLLEGES
cont> (COLLEGE_NAME SIZE IS 20);
This statement creates a compressed index, COL_NAME_IND, on the
COLLEGES table so that the number of octets from the COLLEGE_NAME
column that are used as a key cannot exceed 20 octets.
Example 6: Creating an index in a uniform storage area with
thresholds.
SQL> ALTER DATABASE FILENAME mf_personnel
cont> ADD STORAGE AREA UNIFORM1 PAGE FORMAT IS UNIFORM;
SQL> ALTER DATABASE FILENAME mf_personnel
cont> ADD STORAGE AREA UNIFORM2 PAGE FORMAT IS UNIFORM;
SQL> ATTACH 'FILENAME mf_personnel';
SQL> CREATE UNIQUE INDEX EMP_THRESHOLDS ON EMPLOYEES (EMPLOYEE_ID)
cont> TYPE IS SORTED
cont> STORE USING (EMPLOYEE_ID)
cont> IN RDB$SYSTEM (THRESHOLDS ARE (60,75,90))
cont> WITH LIMIT OF ('00200')
cont> IN UNIFORM1 (THRESHOLD IS (65))
cont> WITH LIMIT OF ('00400')
cont> OTHERWISE IN UNIFORM2
cont> (THRESHOLD OF (90));
%RDB-W-META_WARN, metadata successfully updated with the reported warning
-RDMS-W-IDXCOLEXIST, an index with this column list already exists
SQL> --
SQL> SHOW INDEX EMP_THRESHOLDS
Indexes on table EMPLOYEES:
EMP_THRESHOLDS with column EMPLOYEE_ID
No Duplicates allowed
Type is Sorted
Key suffix compression is DISABLED
Node size 430
Store clause: STORE USING (EMPLOYEE_ID)
IN RDB$SYSTEM (THRESHOLDS ARE (60,75,90))
WITH LIMIT OF ('00200')
IN UNIFORM1 (THRESHOLD IS (65))
WITH LIMIT OF ('00400')
OTHERWISE IN UNIFORM2
(THRESHOLD OF (90))
This statement uses the STORE clause to partition the index into
different uniform page format storage areas and apply thresholds.
In Examples 7 and 8, the table COLOURS in the database MIA_CHAR_
SET is defined as:
SQL> CREATE TABLE COLOURS
cont> (ENGLISH MCS_DOM,
cont> FRENCH MCS_DOM,
cont> JAPANESE KANJI_DOM,
cont> ROMAJI DEC_KANJI_DOM,
cont> KATAKANA KATAKANA_DOM,
cont> HINDI HINDI_DOM,
cont> GREEK GREEK_DOM,
cont> ARABIC ARABIC_DOM,
cont> RUSSIAN RUSSIAN_DOM);
Example 7: Creating a simple table index using the octets
character length, which is the default
SQL> SET CHARACTER LENGTH 'OCTETS';
SQL> CREATE INDEX COLOUR_INDEX ON COLOURS (JAPANESE SIZE IS 4)
cont> TYPE IS SORTED;
SQL> SHOW INDEX COLOUR_INDEX;
Indexes on table COLOURS:
COLOUR_INDEX with column JAPANESE
size of index key is 4 octets
Duplicates are allowed
Type is Sorted
The previous statement creates a compressed index key of 4
octets.
Example 8: Creating an index using the CHARACTERS character
length
SQL> SET CHARACTER LENGTH 'CHARACTERS';
SQL> CREATE INDEX COLOUR_INDEX_2 ON COLOURS (JAPANESE SIZE IS 4)
cont> TYPE IS SORTED;
SQL> SHOW INDEX COLOUR_INDEX_2;
Indexes on table COLOURS:
COLOUR_INDEX_2 with column JAPANESE
size of index key is 4 characters
Duplicates are allowed
Type is Sorted
The previous statement creates a compressed index key of 4
characters.
Example 9: Creating an index that enables compression
The following example shows how to create an index and enable
compression with a minimum run length of 2:
SQL> CREATE INDEX EMP_NDX ON EMPLOYEES
cont> (EMPLOYEE_ID SIZE IS 4)
cont> ENABLE COMPRESSION (MINIMUM RUN LENGTH 2);
SQL> SHOW INDEX EMP_NDX;
Indexes on table EMPLOYEES:
EMP_NDX with column EMPLOYEE_ID
size of index key is 4
Duplicates are allowed
Type is Sorted
Compression is ENABLED (Minimum run length 2)
Example 10: Using the Index Attributes Clause
SQL> CREATE UNIQUE INDEX JOB_JOB_CODE
cont> ON JOBS (
cont> JOB_CODE
cont> ASC)
cont> TYPE IS SORTED
cont> THRESHOLDS ARE (75,83,90)
cont> ENABLE COMPRESSION
cont> NOLOGGING
cont> COMMENT IS 'Used for translation of job codes';
%RDB-W-META_WARN, metadata successfully updated with the reported warning
-RDMS-W-DATACMIT, unjournaled changes made; database may not be recoverable
SQL> -- SQL returned this message because the NOLOGGING attribute
SQL> -- was set.
Example 11: Creating an Index and Displaying the Default Node
Size
SQL> -- Create a simple table upon which to define
SQL> -- some indexes
SQL>
SQL> CREATE TABLE TEST_INDEX_TABLE
cont> (A CHAR(70),
cont> B INTEGER);
SQL>
SQL> -- Default value is 430 bytes
SQL>
SQL> CREATE UNIQUE INDEX TEST_INDEX_DEF
cont> ON TEST_INDEX_TABLE (A, B)
cont> TYPE IS SORTED
cont> USAGE UPDATE;
SQL>
SQL> SHOW TABLE (INDEX) TEST_INDEX_TABLE
Information for table TEST_INDEX_TABLE
TEST_INDEX_DEF with column A
and column B
No Duplicates allowed
Type is Sorted
Compression is DISABLED
Node size 430
Percent fill 70
Example 12: Naming Partitions
SQL> -- Alter mf_personnel database to add three slots
SQL> -- for storage areas and then add three storage areas.
SQL> ALTER DATABASE FILENAME MF_PERSONNEL
cont> RESERVE 3 STORAGE AREAS;
%RDMS-W-DOFULLBCK, full database backup should be done to ensure future recovery
SQL> ALTER DATABASE FILENAME MF_PERSONNEL
cont> ADD STORAGE AREA WAGE_LOW;
SQL> ALTER DATABASE FILENAME MF_PERSONNEL
cont> ADD STORAGE AREA WAGE_MID;
SQL> ALTER DATABASE FILENAME MF_PERSONNEL
cont> ADD STORAGE AREA WAGE_HIGH;
SQL> ATTACH 'FILENAME MF_PERSONNEL.RDB';
SQL> -- Create an index on the JOBS table and name the partitions
SQL> CREATE INDEX WAGE_CLASS_IDX ON JOBS (WAGE_CLASS)
cont> TYPE IS SORTED
cont> STORE USING (WAGE_CLASS)
cont> IN WAGE_LOW (PARTITION WAGE_LOW) WITH LIMIT OF ('1')
cont> IN WAGE_MID (PARTITION WAGE_MID) WITH LIMIT OF ('3')
cont> OTHERWISE IN WAGE_HIGH (PARTITION WAGE_HIGH);
Example 13: Creating a Large Index Partitioned Across Many
Storage Areas
First, create the database definition:
SQL> CREATE INDEX ... MAINTENANCE IS ENABLED DEFERRED ...;
Next submit batch jobs to build each partition in parallel. For
example, each batch job would execute a script similar to the
following:
ATTACH 'filename testdatabase';
SET FLAGS 'index_stats';
ALTER INDEX TRANSACTIONS_INDEX BUILD PARTITION PART_1;
COMMIT;
Finally, after the batch jobs have completed, the database
administrator must make the index active for query usage by
changing the maintenance mode to ENABLED IMMEDIATE. A BUILD
ALL PARTITIONS clause could be added in case any step failed
(possibly due to resource limitations or a failed node).
SQL> SET FLAGS 'index_stats';
SQL> SET TRANSLATION READ WRITE RESERVING...FOR EXCLUSIVE WRITES;
SQL> ALTER INDEX ... BUILD ALL PARTITIONS;
SQL> ALTER INDEX ... MAINTENANCE IS ENABLED IMMEDIATE;
SQL> COMMIT;
This scheme has several advantages over issuing a CREATE INDEX
statement directly:
o The build actions can be run in parallel, which allows
better resource usage (read and sort fewer rows), and reduced
execution time for the index creation.
o The partitions being processed are relatively small when
compared to the full index and, therefore, smaller quantities
of data will be processed. This will result in smaller .ruj
files and less AIJ file space for these transactions.
o Each build partition runs in a separate transaction, can
easily be repeated if a step fails, and does not require
repeating the entire CREATE INDEX statement.
o If any steps have failed, they will also be repeated by the
BUILD ALL PARTITIONS clause included in the script.