For all DBAs maintaining partitioned tables owning GLOBAL indexes.
DETAILS
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
Prerequisites
1. Set up a RANGE partitioned table and a GLOBAL index:
SQL> create table orders (
order_no number,
part_no varchar2(40),
ord_date date
)
partition by range (ord_date)
(partition Q1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY')),
partition Q2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
partition Q3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
partition Q4 values less than (TO_DATE('03-JAN-2000','DD-MON-YYYY'))
);
Table created.
SQL> create index orders_global_idx
on orders(ord_date)
global partition by range (ord_date)
(partition GLOBAL1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY')),
partition GLOBAL2 values less than (TO_DATE('01-SEP-1999','DD-MON-YYYY')),
partition GLOBAL3 values less than (TO_DATE('01-DEC-2000','DD-MON-YYYY')),
partition GLOBAL4 values less than (MAXVALUE)
);
Index created.
SQL> select substr(index_name,1,20) index_name, substr(partition_name,1,20)
part_name , status
from dba_ind_partitions
where index_name= 'ORDERS_GLOBAL_IDX' order by partition_name;
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
ORDERS_GLOBAL_IDX GLOBAL1 USABLE
ORDERS_GLOBAL_IDX GLOBAL2 USABLE
ORDERS_GLOBAL_IDX GLOBAL3 USABLE
ORDERS_GLOBAL_IDX GLOBAL4 USABLE
SQL> insert into orders values (1,100,TO_DATE('02-FEB-1999','DD-MON-YYYY'));
2. Set up a HASH partitioned table and a GLOBAL index:
SQL> CREATE TABLE emp_hpart(
empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
sal NUMBER(7,2))
PARTITION BY HASH(sal)
(PARTITION H1, PARTITION H2, PARTITION H3, PARTITION H4);
Table created.
SQL> CREATE INDEX emp_global_HASH_idx ON emp_hpart(ename)
GLOBAL PARTITION BY RANGE (ename)
(PARTITION p1 VALUES LESS THAN ('N') ,
PARTITION p2 VALUES LESS THAN (MAXVALUE));
Index created.
SQL> select substr(index_name,1,20) index_name,
substr(partition_name,1,20) part_name,status
from dba_ind_partitions
where index_name= 'EMP_GLOBAL_HASH_IDX' order by partition_name;
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
EMP_GLOBAL_HASH_IDX P1 USABLE
EMP_GLOBAL_HASH_IDX P2 USABLE
SQL> insert into emp_hpart values (1,'AAA',100);
3. Set up a COMPOSITE partitioned table and a GLOBAL index:
SQL> CREATE TABLE emp_composite(
empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
sal NUMBER(6))
PARTITION BY RANGE(empno)
SUBPARTITION BY HASH(sal) SUBPARTITIONS 4
(PARTITION p1 VALUES LESS THAN (50),
PARTITION p2 VALUES LESS THAN (100),
PARTITION p3 VALUES LESS THAN (150),
PARTITION p4 VALUES LESS THAN (MAXVALUE));
Table created.
SQL> CREATE INDEX emp_global_composite_idx ON emp_composite(ename)
GLOBAL PARTITION BY RANGE (ename)
(PARTITION p1 VALUES LESS THAN ('N') ,
PARTITION p2 VALUES LESS THAN (MAXVALUE));
Index created.
SQL> select substr(index_name,1,20) index_name,
substr(partition_name,1,20) part_name,status
from dba_ind_partitions
where index_name= 'EMP_GLOBAL_COMPOSITE_IDX' order by partition_name;
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
EMP_GLOBAL_COMPOSITE P1 USABLE
EMP_GLOBAL_COMPOSITE P2 USABLE
SQL> insert into emp_composite values (1,'AAA',100);
4. Set up a LIST partitioned table and a GLOBAL index:
SQL> CREATE TABLE test(
location_id NUMBER, street_address VARCHAR2(80), postal_code CHAR(12),
city VARCHAR2(80), state_province CHAR(2), country_id VARCHAR2(20))
PARTITION BY LIST (state_province)
(PARTITION region_east
VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ'),
PARTITION region_west
VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO'),
PARTITION region_south
VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'),
PARTITION region_central
VALUES ('OH','ND','SD','MO','IL','MI',NULL,'IA'));
Table created.
SQL> create index loc_global_idx
on test (state_province)
global partition by range (state_province)
(partition p1 values less than ('NV'),
partition p2 values less than (maxvalue));
Index created.
SQL> INSERT INTO test VALUES
( 1000,'1297 Via Cola di Rie','00989','Roma',NULL,'IT');
1 row created.
SQL> select substr(index_name,1,20) index_name, substr(partition_name,1,20)
part_name , status
from dba_ind_partitions
where index_name= 'LOC_GLOBAL_IDX' order by partition_name;
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
LOC_GLOBAL_IDX P1 USABLE
LOC_GLOBAL_IDX P2 USABLE
Version 8/8i : Without UPDATE GLOBAL INDEXES clause
SQL> select substr(index_name,1,20) index_name,
substr(partition_name,1,20) part_name,status
from dba_ind_partitions
where index_name= 'ORDERS_GLOBAL_IDX' order by partition_name;
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
ORDERS_GLOBAL_IDX GLOBAL1 USABLE
ORDERS_GLOBAL_IDX GLOBAL2 USABLE
ORDERS_GLOBAL_IDX GLOBAL3 USABLE
ORDERS_GLOBAL_IDX GLOBAL4 USABLE
SQL> ALTER TABLE orders DROP PARTITION q2;
Table altered.
SQL> select substr(index_name,1,20) index_name, substr(partition_name,1,20)
part_name, status
from dba_ind_partitions
where index_name= 'ORDERS_GLOBAL_IDX' order by partition_name;
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
ORDERS_GLOBAL_IDX GLOBAL1 UNUSABLE
ORDERS_GLOBAL_IDX GLOBAL2 UNUSABLE
ORDERS_GLOBAL_IDX GLOBAL3 UNUSABLE
ORDERS_GLOBAL_IDX GLOBAL4 UNUSABLE
----19C
INDEX_NAME PART_NAME STATUS
1 ORDERS_GLOBAL_IDX GLOBAL1 USABLE
2 ORDERS_GLOBAL_IDX GLOBAL2 USABLE
3 ORDERS_GLOBAL_IDX GLOBAL3 USABLE
4 ORDERS_GLOBAL_IDX GLOBAL4 USABLE
In version pre-9i, any DDL operation on a partition of a partitioned table would invalidate the use of GLOBAL indexes, if the corresponding partition isnt empty.
In 9i, UPDATE GLOBAL INDEXES clause allows automatic maintenance of GLOBAL indexes while performing DDL operations on a partition of the table.
Some combinations are nevertheless not allowed.
1. UPDATE GLOBAL INDEXES clause and ADD PARTITION clause
1. RANGE partitioned tables:
SQL> ALTER TABLE orders ADD PARTITION q5
values less than (TO_DATE('03-JUN-2000','DD-MON-YYYY'))
UPDATE GLOBAL INDEXES;
ALTER TABLE orders ADD PARTITION q5
*
ERROR at line 1:
ORA-30564: Index maintainence clause not allowed for ADD partition to RANGE
partitioned tables
Note: The clause UPDATE GLOBAL INDEXES is allowed only for adding a partition to a HASH partitioned table subpartition to a composite partitioned table.
So You can use the conventional way for RANGE partitioned tables
SQL> ALTER TABLE orders ADD PARTITION q5
values less than (TO_DATE('03-JUN-2000','DD-MON-YYYY'));
Table altered.
SQL> @sel
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
ORDERS_GLOBAL_IDX GLOBAL1 USABLE
ORDERS_GLOBAL_IDX GLOBAL2 USABLE
ORDERS_GLOBAL_IDX GLOBAL3 USABLE
ORDERS_GLOBAL_IDX GLOBAL4 USABLE
2. HASH partitioned tables:
Use UPDATE GLOBAL INDEXES way with HASH partitioned tables or the global index is left UNUSABLE
SQL> ALTER TABLE emp_hpart ADD PARTITION q5
UPDATE GLOBAL INDEXES;
Table altered.
SQL> @sel
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
EMP_GLOBAL_HASH_IDX P1 USABLE
EMP_GLOBAL_HASH_IDX P2 USABLE
3. COMPOSITE partitioned tables:
Use UPDATE GLOBAL INDEXES way with COMPOSITE partitioned tables or the global index is left UNUSABLE
SQL> ALTER TABLE emp_composite MODIFY PARTITION p1 add subpartition h5
UPDATE GLOBAL INDEXES;
Table altered.
SQL> @sel
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
EMP_GLOBAL_COMPOSITE P1 USABLE
EMP_GLOBAL_COMPOSITE P2 USABLE
4. LIST partitioned tables
Use the conventional way for LIST partitioned tables
SQL> alter table test ADD
partition nomansland values ('XX');
Table altered.
SQL> @sel
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
LOC_GLOBAL_IDX P1 USABLE
LOC_GLOBAL_IDX P2 USABLE
2. UPDATE GLOBAL INDEXES clause and DROP PARTITION clause
1. RANGE partitioned tables:
Use UPDATE GLOBAL INDEXES way with RANGE partitioned tables or the global index is left UNUSABLE
SQL> ALTER TABLE orders DROP PARTITION q2 UPDATE GLOBAL INDEXES;
Table altered.
2. HASH partitioned tables:
No DROP allowed with HASH partitioned tables. Use COALESCE instead. (See at the end of the bulletin)
3. COMPOSITE partitioned tables:
Use UPDATE GLOBAL INDEXES way with COMPOSITE partitioned tables or the global index is left UNUSABLE
SQL> ALTER TABLE emp_composite DROP PARTITION p2
UPDATE GLOBAL INDEXES;
Table altered.
4. LIST partitioned tables:
Use UPDATE GLOBAL INDEXES way with LIST partitioned tables or the global index is left UNUSABLE
SQL> alter table test DROP PARTITION
region_south
UPDATE GLOBAL INDEXES;
Table altered.
3. UPDATE GLOBAL INDEXES clause and SPLIT PARTITION clause
1. RANGE partitioned tables
Use UPDATE GLOBAL INDEXES way with RANGE partitioned tables or the global index is left UNUSABLE
SQL> ALTER TABLE orders SPLIT PARTITION q3 AT
(TO_DATE('15-SEP-1999','DD-MON-YYYY'))
INTO (PARTITION q3_1, PARTITION q3_2)
UPDATE GLOBAL INDEXES;
Table altered.
2. HASH partitioned tables
No SPLIT allowed with HASH partitioned tables. Use ADD instead. (See above)
3. COMPOSITE partitioned tables
Use UPDATE GLOBAL INDEXES way with COMPOSITE partitioned tables or the global index is left UNUSABLE
SQL> ALTER TABLE emp_composite SPLIT PARTITION p2 AT (80)
INTO (PARTITION p2_1, PARTITION p2_2)
UPDATE GLOBAL INDEXES;
Table altered.
4. LIST partitioned tables:
Use UPDATE GLOBAL INDEXES way with LIST partitioned tables or the global index is left UNUSABLE
SQL> alter table test SPLIT PARTITION region_east
VALUES ('MA','NJ')
INTO (PARTITION region_east_1, PARTITION region_east_2)
UPDATE GLOBAL INDEXES;
Table altered.
4. UPDATE GLOBAL INDEXES clause and MERGE PARTITION clause
1. RANGE partitioned tables
Use UPDATE GLOBAL INDEXES way with RANGE partitioned tables or the global index is left UNUSABLE
SQL> ALTER TABLE orders MERGE PARTITIONS q2, q3 INTO PARTITION q3
UPDATE GLOBAL INDEXES;
Table altered.
2. HASH partitioned tables
No MERGE allowed with HASH partitioned tables. Use COALESCE instead. (See at the end of the bulletin)
3. COMPOSITE partitioned tables
Use UPDATE GLOBAL INDEXES way with COMPOSITE partitioned tables or the global index is left UNUSABLE
SQL> ALTER TABLE emp_composite MERGE PARTITIONS p1, p2
INTO PARTITION p2
UPDATE GLOBAL INDEXES;
Table altered.
4. LIST partitioned tables:
Use UPDATE GLOBAL INDEXES way with LIST partitioned tables or the global index is left UNUSABLE
SQL> alter table test MERGE PARTITIONS region_east,region_west
INTO PARTITION region_north
UPDATE GLOBAL INDEXES;
Table altered.
5. UPDATE GLOBAL INDEXES clause and EXCHANGE PARTITION clause
1. RANGE partitioned tables
Use UPDATE GLOBAL INDEXES way with RANGE partitioned tables or the global index is left UNUSABLE
SQL> ALTER TABLE orders EXCHANGE PARTITION q3 WITH TABLE t_orders
UPDATE GLOBAL INDEXES;
Table altered.
If GLOBAL indexes exist on the TABLE of exchange, they are left UNUSABLE:
SQL> create index t_orders_global_idx
on t_orders(ord_date)
global partition by range (ord_date)
(partition GLOBAL1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY')),
partition GLOBAL2 values less than (TO_DATE('01-SEP-1999','DD-MON-YYYY')),
partition GLOBAL3 values less than (TO_DATE('01-DEC-1999','DD-MON-YYYY')),
partition GLOBAL4 values less than (MAXVALUE) );
Index created.
SQL> ALTER TABLE orders EXCHANGE PARTITION q3 WITH TABLE t_orders
UPDATE GLOBAL INDEXES;
Table altered.
SQL> @sel
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
T_ORDERS_GLOBAL_IDX GLOBAL1 UNUSABLE
T_ORDERS_GLOBAL_IDX GLOBAL2 UNUSABLE
T_ORDERS_GLOBAL_IDX GLOBAL3 UNUSABLE
T_ORDERS_GLOBAL_IDX GLOBAL4 UNUSABLE
ORDERS_GLOBAL_IDX GLOBAL1 USABLE
ORDERS_GLOBAL_IDX GLOBAL2 USABLE
ORDERS_GLOBAL_IDX GLOBAL3 USABLE
ORDERS_GLOBAL_IDX GLOBAL4 USABLE
2. HASH partitioned tables
Use UPDATE GLOBAL INDEXES way with HASH partitioned tables or the global index is left UNUSABLE
SQL> ALTER TABLE emp_hpart EXCHANGE PARTITION H1 WITH TABLE t_emp_hpart
UPDATE GLOBAL INDEXES;
Table altered.
3. COMPOSITE partitioned tables
SQL> ALTER TABLE emp_composite EXCHANGE PARTITION p1 WITH TABLE t_emp_composite;
ALTER TABLE emp_composite EXCHANGE PARTITION p1 WITH TABLE t_emp_composite
*
ERROR at line 1:
ORA-14291: cannot EXCHANGE a composite partition with a non-partitioned table
Use UPDATE GLOBAL INDEXES way with COMPOSITE partitioned tables or the global index is left UNUSABLE
SQL> ALTER TABLE emp_composite EXCHANGE SUBPARTITION SYS_SUBP286
WITH TABLE t_emp_composite
UPDATE GLOBAL INDEXES;
Table altered.
4. LIST partitioned tables:
Use UPDATE GLOBAL INDEXES way with LIST partitioned tables or the global index is left UNUSABLE
SQL> ALTER TABLE test EXCHANGE PARTITION region_east
WITH TABLE t_locations
UPDATE GLOBAL INDEXES;
Table altered.
6. UPDATE GLOBAL INDEXES clause and MOVE PARTITION clause
1. RANGE partitioned tables
Use UPDATE GLOBAL INDEXES way with RANGE partitioned tables or the global index is left UNUSABLE
SQL> ALTER TABLE orders MOVE PARTITION q3 TABLESPACE example
UPDATE GLOBAL INDEXES;
Table altered.
2. HASH partitioned tables
Use UPDATE GLOBAL INDEXES way with HASH partitioned tables or the global index is left UNUSABLE
SQL> ALTER TABLE emp_hpart MOVE PARTITION H1 TABLESPACE example
UPDATE GLOBAL INDEXES;
Table altered.
3. COMPOSITE partitioned tables
SQL> ALTER TABLE emp_composite MOVE PARTITION p1 TABLESPACE example;
ALTER TABLE emp_composite MOVE PARTITION p1 TABLESPACE example
*
ERROR at line 1:
ORA-14257: cannot move partition other than a Range or Hash partition
SQL> ALTER TABLE emp_composite MOVE PARTITION p1 TABLESPACE example
UPDATE GLOBAL INDEXES;
ALTER TABLE emp_composite MOVE PARTITION p1 TABLESPACE example
*
ERROR at line 1:
ORA-14257: cannot move partition other than a Range or Hash partition
4. LIST partitioned tables:
Use UPDATE GLOBAL INDEXES way with LIST partitioned tables or the global index is left UNUSABLE
SQL> ALTER TABLE test MOVE PARTITION region_east
TABLESPACE TS_DATA1
UPDATE GLOBAL INDEXES;
Table altered.
7. UPDATE GLOBAL INDEXES clause and TRUNCATE PARTITION clause
1. RANGE partitioned tables
Use UPDATE GLOBAL INDEXES way with RANGE partitioned table or the global index is left UNUSABLE
SQL> ALTER TABLE orders TRUNCATE PARTITION q3
UPDATE GLOBAL INDEXES;
Table truncated.
2. HASH partitioned tables
Use UPDATE GLOBAL INDEXES way with HASH partitioned tables or the global index is left UNUSABLE
SQL> ALTER TABLE emp_hpart TRUNCATE PARTITION H1
UPDATE GLOBAL INDEXES;
Table truncated.
3. COMPOSITE partitioned tables
Use UPDATE GLOBAL INDEXES way with COMPOSITE partitioned tables or the global index is left UNUSABLE
SQL> ALTER TABLE emp_composite TRUNCATE PARTITION p1
UPDATE GLOBAL INDEXES;
Table truncated.
4. LIST partitioned tables:
Use UPDATE GLOBAL INDEXES way with LIST partitioned tables or the global index is left UNUSABLE
SQL> ALTER TABLE test TRUNCATE PARTITION region_east
UPDATE GLOBAL INDEXES;
Table truncated.
8. UPDATE GLOBAL INDEXES clause and COALESCE PARTITION clause
1. RANGE partitioned tables
Not available with range partitioned tables.
2. HASH partitioned tables
Use UPDATE GLOBAL INDEXES way with HASH partitioned tables or the global index is left UNUSABLE
SQL> ALTER TABLE emp_hpart COALESCE PARTITION
UPDATE GLOBAL INDEXES;
Table altered.
3. COMPOSITE partitioned tables
Not available with composite partitioned tables.
4. LIST partitioned tables
Not available with list partitioned tables.
Which performance considerations would lead you to choose REBUILD INDEX rather than use UPDATE GLOBAL INDEXES :
If UPDATE GLOBAL INDEXES is used:
Partition DDL operations will take longer to complete because the global indexes that were previously marked unusable will now be updated
DROP, TRUNCATE, EXCHANGE will no longer be fast, though data-dictionary only operations because a scan of all rows in the partition will be done
Updates to the global index will be logged, hence, redo and rollback will be generated
Update index is favorable when the amount of row work is low
Update index ensures application performance does not drastically fall until the index is rebuilt
If INDEX REBUILD is used:
Rebuilding the entire index may make the index more efficient.
Rebuilding the index allows the user to reorganize the index.
Restriction on Updating Global Indexes:
If the global index is a global domain index defined on a LOB column, then Oracle Database marks the domain index UNUSABLE instead of updating it.