Choose the appropriate partition key

根据业务需求选择分区键，通常是为了将数据按某一字段条件分开segment存放。
范围分区和哈希分区，分区键可以使用多个字段，列表分区的分区键只能使用1个字段。
 
 
多个字段的范围分区：
create table PART_TAB6
(
  id       NUMBER(20),
  name     VARCHAR2(30),
  location VARCHAR2(20),
  ttime    DATE,
  ecode    NUMBER(15)
)
partition by range (TTIME, ID)
(
  partition P1 values less than (TO_DATE('2012-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 1000),
  partition P2 values less than (TO_DATE('2012-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2000),
  partition P3 values less than (TO_DATE('2012-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 3000),
  partition P4 values less than (TO_DATE('2012-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 4000),
  partition P5 values less than (TO_DATE('2012-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 5000),
  partition P6 values less than (TO_DATE('2012-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 6000),
  partition P7 values less than (TO_DATE('2012-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 7000),
  partition PMAX values less than (MAXVALUE, MAXVALUE)
);
 
多个字段的哈希分区：
create table part_tab7(
id number(20),
name varchar2(30),
location varchar(20),
ttime date,
ecode number(15)
)
PARTITION BY HASH (ecode,ID,location)
PARTITIONS 5;
 
多个字段的组合分区：
create table PART_TAB8
(
  id       NUMBER(20),
  name     VARCHAR2(30),
  location VARCHAR2(20),
  ttime    DATE,
  ecode    NUMBER(15)
)
partition by range (TTIME,id)
SUBPARTITION BY HASH (ecode,location)
SUBPARTITIONS 4
 
(
  partition P1 values less than (TO_DATE('2012-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),4000),
  partition P2 values less than (TO_DATE('2012-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),5000),
  partition P3 values less than (TO_DATE('2012-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),6000),
  partition P4 values less than (TO_DATE('2012-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),7000),
  partition P5 values less than (TO_DATE('2012-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),8000),
  partition P6 values less than (TO_DATE('2012-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),9000),
  partition P7 values less than (TO_DATE('2012-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),10000),
  partition PMAX values less than (MAXVALUE, MAXVALUE)
);