数据库分区概念及简单运用

--创建分区表 此表尚未创建
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)
);
--向表中批量添加数据  注意最后的select * from dual不要漏掉
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,areacode) VALUES(113,'14',TO_DATE('2012-05-01','yyyy-mm-dd'),620,'china' )
into sales(s_id,product_id,sales_date,sales_cost,areacode) VALUES(114,'15',TO_DATE('2013-06-01','yyyy-mm-dd'),340,'china' )
into sales(s_id,product_id,sales_date,sales_cost,areacode) VALUES(115,'16',TO_DATE('2014-08-01','yyyy-mm-dd'),720,'china' )
into sales(s_id,product_id,sales_date,sales_cost,areacode) VALUES(116,'13',TO_DATE('2016-01-01','yyyy-mm-dd'),320,'china' )
into sales(s_id,product_id,sales_date,sales_cost,areacode) VALUES(117,'18',TO_DATE('2018-03-01','yyyy-mm-dd'),220,'china' )
SELECT * FROM dual
--复制一份该表并创建表分区
CREATE TABLE sales_range
PARTITION BY  RANGE(sales_date)(
	PARTITION part1 VALUES less than (TO_DATE('2011-01-01','yyyy-mm-dd')),
	PARTITION part2 VALUES less than (TO_DATE('2012-01-01','yyyy-mm-dd')),
	PARTITION part3 VALUES less than (TO_DATE('2013-01-01','yyyy-mm-dd')),
	PARTITION part4 VALUES less than (MAXVALUE)
)
AS SELECT * FROM sales;
--查询分区数据
SELECT * FROM sales_range PARTITION(part4);