Changes an index. The ALTER INDEX statement allows you to:
o Change the characteristics of index nodes (sorted indexes
only)
o Change the names of the storage areas that contain the index
o Enable or disable logging to the .aij and .ruj files
o Alter index partitions
o Change a partition name
o Change the description of a partition
o Specify whether or not the index is UNIQUE
You cannot change:
o The columns that comprise an index
o A hashed index to a sorted index
o A sorted index to a hashed index
o A sorted, nonranked index to a sorted, ranked index
o A sorted, ranked index to a sorted, nonranked index
o The duplicates compression of a sorted, ranked index
1 – Environment
You can use the ALTER 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;4mALTER[m[1m [1;4mINDEX[m[1m <index-name> qqqqqqqk [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmqqqwq> add-partition-clause qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>[m
[1m [m [1mtq> [1;4mBUILD[m[1m [1;4mPARTITION[m[1m <partition-name> qqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1mtq>[m [1;4mBUILD[m [1;4mALL[m [1;4mPARTITIONS[m [1mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m
[1m tq> [1;4mDROP[m[1m [1;4mPARTITION[m[1m <partition-name> qqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m [m [1mtq> [1;4mMOVE[m[1m [1;4mPARTITION[m[1m <partition-name> [1;4mTO[m[1m area-spec[m [1m qqqqqqqqqqqu [m
[1mtq>[m [1;4mREBUILD[m [1;4mPARTITION[m[1m <partition-name> qqqqqqqqqqqqqqqqqqqqqqqu[m
[1mtq>[m [1;4mREBUILD[m [1;4mALL[m [1;4mPARTITIONS[m [1mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m
[1m [m [1m tq> [1;4mRENAME[m[1m [1;4mPARTITION[m[1m <partition-name> [1;4mTO[m[1m <new-partition-name> u [m
[1mtq>[m [1;4mTRUNCATE[m [1;4mPARTITION[m [1m<partition-name> qqqqqqqqqqqqqqqqqqqqqqu[m
[1mtq>[m [1;4mTRUNCATE[m [1;4mALL[m [1;4mPARTITIONS[m [1mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m
[1m [m [1mmq> alter-index-attributes qwqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqj [m
[1m [m [1m mq> index-store-clause qqj [m [1m [m
[1m [m [1m [m [1m [m
(B)0[m[1madd-partition-clause =[m
[1mq> [1;4mADD[m [1;4mPARTITION[m[1m <partition-name> qqqk[m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj[m
[1mmq>[m [1;4mUSING[m[1m q>[m [1m( qwq> <column-name> qwq> ) qk[m
[1mmqqqqqqq , <qqqqqqqj[m [1mx[m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj[m
[1mmq> [1;4mIN[m[1m area-spec qqqqqqqqqqqqqqqqqqqqqqqqqk[m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj[m
[1mmqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>[m
[1m mq> [1;4mWITH[m [1;4mLIMIT[m [1;4mOF[m [1mq> ( qwq> <literal> qwq> ) qqj[m [1m [m
[1m [m [1mmqqqqqq , <qqqqj[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
(B)0[m[1malter-index-attributes=[m
[1mqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq>[m
[1mx[m [1mtq> threshold-clause[m [1mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x[m
[1mx[m [1mtq> [1;4mDUPLICATES[m [1mARE[m [1;4mALLOWED[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m [1mx[m
[1mx[m [1mtq> [1;4mLOGGING[m [1mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m [1mx[m
[1mx[m [1mtq> [1;4mNOLOGGING[m [1mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m [1mx[m
[1mx[m [1mtq> [1;4mNODE[m[1m [1;4mSIZE[m[1m <number-bytes> qqqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m [1mx[m
[1mx[m [1mtq> [1;4mPERCENT[m[1m [1;4mFILL[m[1m <percentage> qqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m [1mx[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[m [1mx[m [1mx[m
[1mx[m [1mx[m [1mmq>[m [1;4mDISABLED[m [1mqqqqqj[m [1mx[m [1mx[m
[1mx[m [1mtq> [1;4mUSAGE[m[1m qwq> [1;4mUPDATE[m[1m qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m [1mx[m
[1mx[m [1mx mq> [1;4mQUERY[m[1m qqj [m [1m [m [1mx[m [1mx[m
[1mx[m [1mtq> [1;4mCOMMENT[m[1m IS qwq> '<string>' qqwqqqqqqqqqqqqqqqqqqqqqqqqu[m [1mx[m
[1mx[m [1mx[m [1mmqqqqq / qqqqqqqqj [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[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[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
3 – Arguments
3.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.
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 – 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 (/).
3.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.
3.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.
3.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.
3.8 – index-name
The name of the index.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
4 – Examples
Example 1: Disabling an index
The following example shows how to disable an index that can be
deleted at a later time when the database table can be taken off
line:
SQL> alter index COLL_COLLEGE_CODE
cont> maintenance is disabled;
SQL> show index COLL_COLLEGE_CODE
Indexes on table COLLEGES:
COLL_COLLEGE_CODE with column COLLEGE_CODE
No Duplicates allowed
Type is Sorted
Key suffix compression is DISABLED
Index is no longer maintained
Node size 430
Example 2: Changing a Unique Index to Non-Unique
SQL> show table (index) DEPARTMENTS
Information for table DEPARTMENTS
Indexes on table DEPARTMENTS:
DEPARTMENTS_INDEX with column DEPARTMENT_CODE
No Duplicates allowed
Type is Sorted
Key suffix compression is DISABLED
Node size 430
SQL> insert into DEPARTMENTS (DEPARTMENT_CODE) values ('SUSO');
%RDB-E-NO_DUP, index field value already exists;
duplicates not allowed for DEPARTMENTS_INDEX
SQL> alter index DEPARTMENTS_INDEX duplicates are allowed;
SQL> insert into DEPARTMENTS (DEPARTMENT_CODE) values ('SUSO');
1 row inserted
SQL> show table (index) DEPARTMENTS
Information for table DEPARTMENTS
Indexes on table DEPARTMENTS:
DEPARTMENTS_INDEX with column DEPARTMENT_CODE
Duplicates are allowed
Type is Sorted
Key suffix compression is DISABLED
Node size 430
Example 3: Adding an Index Partition Before and After the Final
Partition
SQL> CREATE UNIQUE INDEX EMPLOYEES_INDEX
cont> ON EMPLOYEES (EMPLOYEE_ID)
cont> TYPE IS HASHED
cont> STORE USING (EMPLOYEE_ID)
cont> IN JOBS WITH LIMIT OF ('00999');
SQL> COMMIT;
SQL> -- To add a partition before the final partition requires
SQL> -- that the final partition (which now follows the new partition)
SQL> -- be scanned and matching keys moved to the new partition.
SQL> SET TRANSACTION READ WRITE
cont> RESERVING EMPLOYEES for EXCLUSIVE WRITE;
SQL> SET FLAGS INDEX_STATS;
SQL> ALTER INDEX EMPLOYEES_INDEX
cont> ADD PARTITION NEW_EMPS_200
cont> USING (EMPLOYEE_ID)
cont> IN EMP_INFO WITH LIMIT OF ('00200');
~Ai alter index "EMPLOYEES_INDEX" (hashed=1, ordered=0)
~Ai add partition "NEW_EMPS_200" : area "EMP_INFO"
~Ai storage area "EMP_INFO" larea=85
~Ai splitting partition #1
~Ai split complete: total 100 keys, moved 37 (dups 0)
~Ai reads: async 136 synch 30, writes: async 57 synch 0
SQL> COMMIT;
SQL> -- Now add a partition after the final partition of
SQL> -- the index. This requires no I/O to the partition because
SQL> -- there is no following partition and therefore no keys
SQL> -- to be moved.
SQL> SET TRANSACTION READ WRITE
cont> RESERVING EMPLOYEES FOR EXCLUSIVE WRITE;
SQL> ALTER INDEX EMPLOYEES_INDEX
cont> ADD PARTITION NEW_EMPS_1400
cont> USING (EMPLOYEE_ID)
cont> IN EMPIDS_OVER WITH LIMIT OF ('01400');
~Ai alter index "EMPLOYEES_INDEX" (hashed=1, ordered=0)
~Ai add partition "NEW_EMPS_1400" : area "EMPIDS_OVER"
~Ai storage area "EMPIDS_OVER" larea=122
~Ai adding new final partition 3
SQL> COMMIT;
SQL> -- Show the index. It shows the ADD PARTITION syntax appended
SQL> -- to the original source of the index.
SQL> SHOW INDEX EMPLOYEES_INDEX
Indexes on table EMPLOYEES:
EMPLOYEES_INDEX with column EMPLOYEE_ID
No Duplicates allowed
Type is Hashed Scattered
Key Suffix Compression is DISABLED
Store clause: STORE using (EMPLOYEE_ID)
in JOBS with limit of ('00999')
Add Partition partition NEW_EMPS_200
using (EMPLOYEE_ID)
in EMP_INFO with limit of ('00200')
Add Partition partition NEW_EMPS_1400
using (EMPLOYEE_ID)
in EMPIDS_OVER with limit of ('01400')
Example 4: Renaming a Partition
$ rmu /extract /item=index mf_personnel.rdb
.
.
.
create unique index EMPLOYEES_HASH
on EMPLOYEES (
EMPLOYEE_ID)
type is HASHED
store
using (EMPLOYEE_ID)
in EMPIDS_LOW(
partition "SYS_P00076"
)
with limit of ('00200')
in EMPIDS_MID(
partition "SYS_P00077"
)
with limit of ('00400')
otherwise in EMPIDS_OVER(
partition "SYS_P00078"
);
commit work;
$SQL$
SQL> ATTACH FILENAME MF_PERSONNEL.RDB;
SQL> ALTER INDEX EMPLOYEES_HASH
cont> RENAME PARTITION SYS_P00076 TO IDS_LOW;
SQL> ALTER INDEX EMPLOYEES_HASH
cont> RENAME PARTITION SYS_P00077 TO IDS_MID;
SQL> ALTER INDEX EMPLOYEES_HASH
cont> RENAME PARTITION SYS_P00078 TO IDS_HIGH;
SQL> COMMIT;
SQL> SHOW INDEX EMPLOYEES_HASH;
Indexes on table EMPLOYEES:
EMPLOYEES_HASH with column EMPLOYEE_ID
No Duplicates allowed
Type is Hashed Scattered
Key Suffix Compression is DISABLED
Store clause: STORE USING (EMPLOYEE_ID)
IN EMPIDS_LOW WITH LIMIT OF ('00200')
IN EMPIDS_MID WITH LIMIT OF ('00400')
OTHERWISE IN EMPIDS_OVER
Rename PARTITION SYS_P00076 TO IDS_LOW
Rename PARTITION SYS_P00077 TO IDS_MID
Rename PARTITION SYS_P00078 TO IDS_HIGH
Example 5: 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.
Example 6: Deleting a Large Index Partitioned Across Many Storage
Areas
First, disable the index:
SQL> ALTER INDEX TRANSACTIONS_INDEX MAINTENANCE IS DISABLED;
Next, submit batch jobs to truncate the partitions in parallel:
SQL> ALTER INDEX TRANSACTIONS_INDEX TRUNCATE PARTITION PART_1;
SQL> COMMIT;
Finally, after the batch jobs are complete, remove the metadata:
SQL> DROP INDEX TRANSACTIONS_INDEX;
This scheme has several advantages over a issuing a DROP INDEX
statement directly:
o The truncate actions can be run in parallel, which allows
better resource usage and reduced execution time for the index
deletion.
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 truncate partition runs in a separate transaction, can
easily be repeated if a step fails, and does not require
repeating the entire action.
o If any steps have failed, they will also be repeated by a DROP
INDEX statement.
Example 7: Using the TRUNCATE PARTITION statement
The following example illustrates using the TRUNCATE PARTITION
statement for the MF_PERSONNEL database.
SQL> show index (partition) EMPLOYEES_HASH
Indexes on table EMPLOYEES:
EMPLOYEES_HASH with column EMPLOYEE_ID
No Duplicates allowed
Type is Hashed Scattered
Key suffix compression is DISABLED
Partition information for index:
Partition: (1) SYS_P00076
Storage Area: EMPIDS_LOW
Partition: (2) SYS_P00077
Storage Area: EMPIDS_MID
Partition: (3) SYS_P00078
Storage Area: EMPIDS_OVER
SQL> alter index employees_hash truncate partition SYS_P00077;
%RDB-W-META_WARN, metadata successfully updated with the reported warning
-RDMS-W-IDXBLDPEND, index in build pending state - maintenance is disabled
SQL> insert into employees default values;
%RDB-E-READ_ONLY_REL, relation EMPLOYEES was reserved for read access; updates
not allowed
-RDMS-F-BUILDPENDING, index in build pending state - operation not permitted
Until the index is made complete it will not be used by the query
optimizer, nor can the table on which it is defined be updated.
The SHOW INDEX command reports this state.
SQL> show index employees_hash
Indexes on table EMPLOYEES:
EMPLOYEES_HASH with column EMPLOYEE_ID
No Duplicates allowed
Type is Hashed Scattered
Key suffix compression is DISABLED
Maintenance is Deferred - build pending