oracle分区
来源:互联网 发布:淘宝祖国版手办 编辑:程序博客网 时间:2024/06/09 18:17
create table emp (id NUMBER,salary NUMBER(10,2))
partition by range (salary)
(
partition p1 VALUES less than (1000),
partition p2 VALUES less than (2000),
partition p3 VALUES less than (3000)
)
select * from emp;
--查看风区表
select * from user_tab_partitions;
select * from emp partition(p1);
select * from emp partition(p2);
select * from emp partition(p3);
select * from emp partition(p4);
--插入数据
insert into emp values (1,500);
insert into emp values (2,1500);
insert into emp values (3,2500);
alter table emp add partition p4 values less than(maxvalue);
insert into emp values (4,3500);
alter table emp drop partition p4;
--使得修改range的字段能自动移行
alter table emp enable row movement;
update emp set salary = 1300 where id = 1;
--分区索引
--local(推荐)
create index idx_salary on emp(salary) local;
--查询索引
select * from user_ind_partitions;
select * from recyclebin;
purge recyclebin;
--动态创建分区
create table interval_sale (sid int, sdate timestamp)
partition by range(sdate)
interval(NUMTOYMINTERVAL(1,'MONTH'))
(
partition p1 VALUES less than(TO_DATE('2014-05-01','YYYY-MM-DD'))
);
insert into interval_sale values(1,TO_TIMESTAMP('2014-04-01 00:00:00:00', 'YYYY-MM-DD HH24:MI:SS:FF'));
insert into interval_sale values(2,TO_TIMESTAMP('2014-05-01 00:00:00:00', 'YYYY-MM-DD HH24:MI:SS:FF'));
insert into interval_sale values(3,TO_TIMESTAMP('2014-09-01 00:00:00:00', 'YYYY-MM-DD HH24:MI:SS:FF'));
select * from interval_sale;
SELECT * from interval_sale partition(p1);
SELECT * from interval_sale partition(sys_p203);
select * from user_tab_partitions;
partition by range (salary)
(
partition p1 VALUES less than (1000),
partition p2 VALUES less than (2000),
partition p3 VALUES less than (3000)
)
select * from emp;
--查看风区表
select * from user_tab_partitions;
select * from emp partition(p1);
select * from emp partition(p2);
select * from emp partition(p3);
select * from emp partition(p4);
--插入数据
insert into emp values (1,500);
insert into emp values (2,1500);
insert into emp values (3,2500);
alter table emp add partition p4 values less than(maxvalue);
insert into emp values (4,3500);
alter table emp drop partition p4;
--使得修改range的字段能自动移行
alter table emp enable row movement;
update emp set salary = 1300 where id = 1;
--分区索引
--local(推荐)
create index idx_salary on emp(salary) local;
--查询索引
select * from user_ind_partitions;
select * from recyclebin;
purge recyclebin;
--动态创建分区
create table interval_sale (sid int, sdate timestamp)
partition by range(sdate)
interval(NUMTOYMINTERVAL(1,'MONTH'))
(
partition p1 VALUES less than(TO_DATE('2014-05-01','YYYY-MM-DD'))
);
insert into interval_sale values(1,TO_TIMESTAMP('2014-04-01 00:00:00:00', 'YYYY-MM-DD HH24:MI:SS:FF'));
insert into interval_sale values(2,TO_TIMESTAMP('2014-05-01 00:00:00:00', 'YYYY-MM-DD HH24:MI:SS:FF'));
insert into interval_sale values(3,TO_TIMESTAMP('2014-09-01 00:00:00:00', 'YYYY-MM-DD HH24:MI:SS:FF'));
select * from interval_sale;
SELECT * from interval_sale partition(p1);
SELECT * from interval_sale partition(sys_p203);
select * from user_tab_partitions;
阅读全文
0 0
- oracle分区
- oracle分区
- Oracle 分区
- oracle分区
- Oracle分区
- oracle 分区
- oracle分区
- oracle分区
- oracle 分区
- oracle 分区
- Oracle分区
- oracle 分区
- Oracle -- 分区
- oracle分区
- Oracle分区
- oracle分区
- Oracle分区
- oracle分区
- 温故而知新篇
- eclipse如何根据类名查找该类所属的maven组件
- 汉诺塔问题解决收集
- 为什么需要消息队列,及使用消息队列的好处?
- 文档表格中重复标题行失效了怎么办
- oracle分区
- 在Fragment中绑定Service
- 微信小程序开发--购物车动画与加减
- 第十四章:类型信息
- NFS服务
- Github删除forked项目
- 跨域资源共享CORS详解
- 20170720工作记账流水
- Java Web学习之路