[分区表] 全局分区索引

# 全局索引必须是前缀索引
# 分区表通过全局索引来保证唯一性, 而局部索引不能保证唯一性
# 索引按区间或散列分区, 可以采用与表不同的分区方式, 索引分区数可能不同于表分区数
# 如果要使用列表或组合分区的索引, 必须使用局部索引

1. 分区表
CREATE TABLE t (timestamp date,id int)
PARTITION BY RANGE (timestamp)
(
    PARTITION part_1 VALUES LESS THAN (to_date('01-jan-2000','dd-mon-yyyy')),
    PARTITION part_2 VALUES LESS THAN (to_date('01-jan-2001','dd-mon-yyyy'))
)

2. 在 id 列上创建全局分区索引
create index t_idx on t(id)
GLOBAL partition by range(id)
(
    partition part_1 values less than(1000),
    partition part_2 values less than (MAXVALUE)
)

3. 创建表主键, 此时会隐式使用先前创建的 id 列上的索引
alter table t add constraint t_pk primary key(id)

4. 索引不可删除
drop index partitioned_index;

ORA-02429: cannot drop index used for enforcement of unique/primary key

5. 可以创建前缀全局索引
create index t_idx1 on t (id,timestamp)
GLOBAL partition by range(id)
(
    partition part_1 values less than(1000),
    partition part_2 values less than (MAXVALUE)
)

6. 不能创建非前缀全局索引, 全局索引必须是前缀索引
create index t_idx2 on t (timestamp,id)
GLOBAL partition by range(id)
(
partition part_1 values less than(1000),
partition part_2 values less than (MAXVALUE)
)

ORA-14038: GLOBAL t index must be prefixed

create index t_idx3 on t (timestamp)
GLOBAL partition by range(id)
(
partition part_1 values less than(1000),
partition part_2 values less than (MAXVALUE)
)

ORA-14038: GLOBAL t index must be prefixed