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