Oracle数据分区

--范围分区
--范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据序号分区，根据业务记录的创建日期进行分区等
create table SUBAREA
(
PID number not null,
PITEM varchar2(200),
PDATA date not null
)
partition by range(PID)
(
partition part_01 values less than(100000),
partition part_02 values less than(200000),
partition part_03 values less than(300000),
partition part_04 values less than(400000),
partition part_05 values less than(maxvalue)
)
--添加分区
ALTER TABLE SUBAREA DROP PARTITION part_01;
ALTER TABLE SUBAREA DROP PARTITION part_02;
ALTER TABLE SUBAREA DROP PARTITION part_03;
ALTER TABLE SUBAREA DROP PARTITION part_04;
ALTER TABLE SUBAREA DROP PARTITION part_05;


alter table subarea add partition part_03 values less than(200000)
alter table subarea add partition part_04 values less than(300000)
alter table subarea add partition part_05 values less than(maxvalue)

--更改分区名
alter table subarea rename Partition part_01 to part_01_01;

--合并分区
ALTER TABLE subarea MERGE PARTITIONS part_01,part_02 INTO PARTITION part_02;
--拆分分区
ALTER TABLE subarea split partition part_03 AT(250000) INTO (PARTITION part_03_01,PARTITION part_03_02);

select t.*,t.rowid from subarea partition(part_01) t where t.pid<50000 order by t.rowid
select * from subarea partition(part_02) where pid<100000 order by pid
select * from subarea partition(part_03) where pid>50000 order by pid
select max(pid) from subarea

--hash 分区技术
--散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。
create table SUBAREA_HASH
(
PID number not null,
PITEM varchar2(200),
PDATA date not null
)
partition by hash(PID)
(
partition part_h01,
partition part_h02,
partition part_h03
)

INSERT INTO SUBAREA_HASH(PID,PITEM,PDATA) SELECT PID ,PITEM,PDATA FROM SUBAREA

select t.*,t.rowid from SUBAREA_HASH partition(part_h02) t ORDER BY PID

select t.*,rownum from subarea partition(part_03_01) t

---复合分区
create table SUBAREA_FH
(
PID number not null,
PITEM varchar2(200),
PDATA date not null
)
partition by RANGE(PID)
SUBPARTITION BY HASH(PID)
(
partition part_R01 VALUES LESS THAN (100000)
(
SUBPARTITION PART_R01_01,
SUBPARTITION PART_R01_02
),
partition part_R02 VALUES LESS THAN (200000),
partition part_R03 VALUES LESS THAN (MAXVALUE)
(
SUBPARTITION PART_R03_01,
SUBPARTITION PART_R03_02
)
)


select t.*,t.rowid from SUBAREA_FH subpartition(PART_R01_01) t ORDER BY PID;
select t.*,t.rowid from SUBAREA_FH subpartition(PART_R01_02) t ORDER BY PID