ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 分区表

分区表

原创 Linux操作系统 作者:gaiguoxiang 时间:2012-05-20 13:47:56 0 删除 编辑

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
);

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25517773/viewspace-730322/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-06-22

  • 博文量
    41
  • 访问量
    105640