创建范围分区
xm:姓名 GZ:工资
CREATE TABLE TEST1
(
XM VARCHAR2(20) NOT NULL
, GZ NUMBER(8,2) NOT NULL
)
PARTITION BY RANGE (GZ)
(
PARTITION FQ1 VALUES LESS THAN (1000)
, PARTITION FQ2 VALUES LESS THAN (2000)
, PARTITION FQ3 VALUES LESS THAN (3000)
);
查看分区
select * from user_tab_partitions;
插入数据,测试
1000:小于1000 2000:大于等于1000,小于2000
3000:只能小于3000
查看分区
SELECT * from test1 PARTITION(FQ1);
SELECT * from test1 PARTITION(FQ2);
SELECT * from test1 PARTITION(FQ3);
修改分区,让它能超过3000
ALTER TABLE TEST1
ADD PARTITION FQ4 VALUES LESS THAN (MAXVALUE);
查看增加的分区
插入数据
SELECT * from test1 PARTITION(FQ4);
散列分区(平均分配到不同分区)
XM:姓名 GZ:工资
CREATE TABLE TEST2
(
XM VARCHAR2(20) NOT NULL
, GZ NUMBER(8,2) NOT NULL
)
PARTITION BY HASH (XM)
(
PARTITION P1
, PARTITION P2
);
查看分区
select * from user_tab_partitions
WHERE TABLE_NAME=‘TEST2’;
插入数据
select * from test2 PARTITION (p1);
select * from test2 PARTITION (p2);
列表分区
XM:姓名 CHENGSHI:城市
Bj:北京 sh:上海 tj:天津
CREATE TABLE TABLE1
(
XM VARCHAR2(20) NOT NULL
, CHENGSHI VARCHAR2(20) NOT NULL
)
PARTITION BY LIST (CHENGSHI)
(
PARTITION P1 VALUES (‘bj’, ‘sh’)
, PARTITION P2 VALUES (‘tj’)
);
插入数据
查看分区
select * from table1 PARTITION(p1);
select * from table1 PARTITION(p2);
复合分区
范围和散列分区
CREATE TABLE XUESHENG
(
ID NUMBER NOT NULL
, XINGMING VARCHAR2(20) NOT NULL
)
PARTITION BY RANGE (ID)
SUBPARTITION BY HASH (ID)
SUBPARTITIONS 4
(
PARTITION P1 VALUES LESS THAN (1000)
, PARTITION P2 VALUES LESS THAN (2000)
, PARTITION P3 VALUES LESS THAN (MAXVALUE)
);