oracle创建分区表

创建分区

create table pt_range_list_test(
   pid         number(10),
   pname       varchar2(30),
   sex         varchar2(10),
   create_date date
) partition by range(create_date) 
  subpartition by list(sex)(
    partition p1 values less than(to_date('2020-01-01', 'YYYY-MM-DD')) tablespace tetstbs1(
      subpartition sub1p1 values('MAN') tablespace tetstbs1,
      subpartition sub2p1 values('WOMAN') tablespace tetstbs1,
      subpartition sub3p1 values(default) tablespace tetstbs1
    ),
    partition p2 values less than(to_date('2021-01-01', 'YYYY-MM-DD')) tablespace tetstbs2(
      subpartition sub1p2 values('MAN') tablespace tetstbs2,
      subpartition sub2p2 values('WOMAN') tablespace tetstbs2,
      subpartition sub3p2 values(default) tablespace tetstbs2
    ),
    partition p3 values less than(maxvalue) tablespace tetstbs3(
      subpartition sub1p3 values('MAN') tablespace tetstbs3,
      subpartition sub2p3 values('WOMAN') tablespace tetstbs3,
      subpartition sub3p3 values(default) tablespace tetstbs3
    )
  ) enable row movement;

局部索引

-- 创建测试分区表
create table local_index_example
(
 id number(2),
 name varchar2(50),
 sex varchar2(10)
)

partition by range (id)
(
  partition part_1 values less than (5),
  partition part_2 values less than (10)
)
--创建局部前缀索引；分区键（id）作为索引定义的第一列
create index local_prefixed_index on local_index_example (id, name) local;

--创建局部非前缀索引；分区键未作为索引定义的第一列
create index local_nonprefixed_index on local_index_example (name, id) local;

注意：判断局部索引是前缀还是非前缀的只需要看分区键是否作为索引定义的第一列

①： select … from local_index_example where id = :id and name = :name;

②： select … from local_index_example where name = :name;

对于以上两个查询来说，如果查询第一步是走索引的话，则：

局部前缀索引 local_prefixed_index 只对 ① 有用；

局部非前缀索引 local_nonprefixed_index 则对 ① 和 ② 均有用；

如果你有多个类似 ① 和 ② 的查询的话，则可以考虑建立局部非前缀索引；如果平常多使用查询 ① 的话，则可以考虑建立局部前缀索引；

总之，重点是你要尽可能保证查询包含的谓词允许索引分区消除

全剧索引

对于全局分区索引来说，索引的实际分区数可能不同于表的分区数量；

全局索引的分区机制有别于底层表，例如表可以按 done_date 列划分为10个分区，表上的一个全局索引可以按 id 列划分为5个分区。

与局部索引不同，全局索引只有一类，即全局前缀索引（prefixed global index），索引分区键必须作为索引定义的第一列，否则执行会报错。

--创建示例表，按id进行范围分区
create table global_index_example
(
 id number(2),
 name varchar2(50),
 age number(2)
)

partition by range (id)
(
  partition part_1 values less than (5),
  partition part_2 values less than (10)
)

--创建按age进行范围分区的全局分区索引
create index global_index on global_index_example(age) global

partition by range (age) 
(
  partition index_part_1 values less than (20),
  partition index_part_2 values less than (maxvalue)
)