partition global index 大全 UPDATE GLOBAL INDEXES

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