createtable scott.partition(
p_id number,
p_id2 number,
p_name varchar2(50),
p_date date)partitionby range(p_id)(partition p1 values less than (20000),partition p2 values less than (40000),partition p3 values less than (80000),partition p4 values less than (100000),partition p5 values less than (maxvalue));-- 试图创建本地分区索引-- ORA-14024: local 索引的分区数必须等于基础表的分数数,如: 3 != 5createindex scott.partition_local on scott.partition(p_id)local(partition p1,partition p2,partition p3);-- 创建本地分区索引createindex scott.partition_local on scott.partition(p_id)local(partition p1,-- 索引分区个数 必须与 表分区数 完全对应partition p2,partition p3,partition p4,partition p5);-- drop index scott.partition_local;-- 等同上述,写法简洁,推荐createindex scott.partition_local on scott.partition(p_id)local;-- 默认:普通索引(非分区索引)createindex scott.partition_normal on scott.partition(p_name);-- p_id 是 表分区列,故 scott.partition_local 为 本地前缀分区索引-- p_id2 不是 ..., 故 scott.partition_local2 为 本地非...createindex scott.partition_local2 on scott.partition(p_id2)local;
2.2 全局分区索引
createindex scott.partition_global on scott.partition(p_date)globalpartitionby range(p_date)(partition pg1 values less than(to_date('2020-01-01','YYYY-MM-DD')),partition pg2 values less than(to_date('2021-01-01','YYYY-MM-DD')),partition pg3 values less than(to_date('2022-01-01','YYYY-MM-DD')),partition pg4 values less than(to_date('2023-01-01','YYYY-MM-DD')),partition pg5 values less than(maxvalue));-- drop index scott.partition_global;-- 同理,若分区表已存在列分区,以下为简洁写法createindex scott.partition_global on scott.partition(p_date)global;