oracle分区

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;