# 分区表

1、时间分区：
CREATE TABLE test_time
(
id NUMBER NOT NULL PRIMARY KEY,
time DATE
)
PARTITION BY RANGE (time)
(
PARTITION test1 VALUES LESS THAN (TO_DATE('01-MAY-2010','DD-MON-YYYY')) TABLESPACE ggx01,
PARTITION test2 VALUES LESS THAN (TO_DATE('01-MAY-2011','DD-MON-YYYY')) TABLESPACE ggx02,
PARTITION test3 VALUES LESS THAN (TO_DATE('01-MAY-2012','DD-MON-YYYY')) TABLESPACE ggx03
)

2、范围分区（MAXVALUE）
CREATE TABLE test_max
(
id NUMBER NOT NULL PRIMARY KEY,
age number
)
PARTITION BY RANGE (age)
(
PARTITION test1 VALUES LESS THEN (10) TABLESPACE ggx01,
PARTITION test1 VALUES LESS THEN (20) TABLESPACE ggx02,
PARTITION test2 VALUES LESS THEN (MAXVALUE) TABLESPACE ggx03
);

3、等值分区：
CREATE TABLE test_lb
(
id NUMBER NOT NULL PRIMARY KEY,
name varchar(20),
class varchar(20)
)
PARTITION BY LIST (class)
(
PARTITION text1 VALUES ('a') TABLESPACE ggx01,
PARTITION text2 VALUES ('b') TABLESPACE ggx02
);

---按多个数值划分
PARTITION BY LIST (class)
(
PARTITION text1 VALUES ('a','b') TABLESPACE ggx01,
PARTITION text2 VALUES ('c','d') TABLESPACE ggx02
);

4、散列分区
CREATE TABLE test_sl
(
id NUMBER NOT NULL PRIMARY KEY,
name varchar(20),
)
PARTITION BY HASH (name)
(
PARTITION test1 TABLESPACE ggx01,
PARTITION test2 TABLESPACE ggx02,
PARTITION test3 TABLESPACE ggx03
)

=========================================================

1、查分区个数
SELECT count(*) FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='test'
2、删除分区表中数据
alter table test truncate partition test1;
3、添加分区
ALTER TABLE test ADD PARTITION text3 VALUES('e') tablespace test4;
4、删除分区
ALTER TABLE test DROP PARTITION text4;
5、合并分区
ALTER TABLE test MERGE PARTITIONS test1,test2 INTO PARTITION test12 tablespace text3;
6、重命名分区
ALTER TABLE test RENAME PARTITION test1 TO test;
7、查询一个分区表数据
select count(*) from test partition(test1);
8、创建索引
CREATE INDEX IND_TESTPAR ON test(id) LOCAL
(PARTITION test1 TABLESPACE par1,
PARTITION test2 TABLESPACE par2,
PARTITION test3 TABLESPACE par3,
PARTITION test4 TABLESPACE par4
);

• 博文量
41
• 访问量
110401