分区解决的问题:主要是可以提升查询效率
目录
范围(range)分区
创建Range(范围)分区表:通过表中的一个字段建立范围分区
--创建分区表 此表尚未创建
create table student(
s_id number(3) primary key,
s_name varchar2(10),
s_sex char(2),
s_age number(3)
)
partition by range(s_age)(
partition p1 values less than(20),
partition p2 values less than(40),
partition p3 values less than(maxvalue) --分区列中的最大值
)
--向student表中添加数据
insert into student values (111,'张三','男',18);
insert into student values (222,'赵四','男',16);
insert into student values (333,'王五','男',15)
insert into student values (444,'李一','男',20)
insert into student values (555,'李七','男',32)
insert into student values (666,'徐八','男',40)
insert into student values (777,'佟九','男',49)
--查询表
select * from student;
--查询分区数据
select * from student partition(p1);
select * from student partition(p2);
select * from student partition(p3);
将已存在的表修改为分区表:
--创建sales表
CREATE TABLE sales(
s_id NUMBER NOT NULL,
product_id varchar2(5),
sales_date DATE,
sales_cost NUMBER(10),
areacode varchar2(5)
);
--向表中批量添加数据
INSERT ALL INTO sales(s_id,product_id,sales_date,sales_cost,areacode) VALUES(111,'12',TO_DATE('2011-01-01','yyyy-mm-dd'),300,'china')
into sales(s_id,product_id,sales_date,sales_cost,areacode) VALUES(112,'13',TO_DATE('2012-03-01','yyyy-mm-dd'),320,'china' )
into sales(s_id,product_id,sales_date,sales_cost,a