Oracle分区表

间隔分区的特点：

　　　　1.由range分区派生而来

　　　　2.以定长宽度创建分区（比如年、月、具体的数字（比如100、500等））

　　　　3.分区字段必须是number或date类型

　　　　4.必须至少指定一个range分区（永久分区）

　　　　5.当有记录插入时，系统根据需要自动创建新的分区和本地索引

　　　　6.已有的范围分区可被转换成间隔分区（通过ALTER TABLE SET INTERVAL选项完成）

　　　　7.IntervalPartitioning不支持支持索引组织表

　　　　8.在Interval Partitioning表上不能创建domain index

---

 

 

/*
===========================================================
| 分区表的管理
============================================================
*/

--查询分区
SELECT table_name,partition_name
FROM user_tab_partitions
WHERE table_name=UPPER('sales_range1');

SELECT * FROM sales_range1 PARTITION (part1);--11前
SELECT * FROM sales_range1 PARTITION (part2);--12前
SELECT * FROM sales_range1 PARTITION (part3);--13前
SELECT * FROM sales_range1 PARTITION (part4);--14前

--添加分区
ALTER TABLE sales_range1 ADD PARTITION part5 VALUES LESS THAN (to_date('2015-01-01','yyyy-mm-dd'));
ALTER TABLE sales_range1 ADD PARTITION part6 VALUES LESS THAN (MAXVALUE);

--删除分区
ALTER TABLE sales_range1 DROP PARTITION part5

--移动分区
ALTER TABLE sales_range1 MOVE PARTITION part1 TABLESPACE tp_sales_bak;

/*
===========================================================
| 现有表创建范围分区表
============================================================
*/

CREATE TABLE sales
(sales_id NUMBER NOT NULL,
product_id VARCHAR2(5),
sales_date DATE,
sales_cost NUMBER(10),
areacode VARCHAR2(5)
)

CREATE TABLE sales_range2
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 (to_date('2014/01/01','yyyy/mm/dd'))
)
as select * from sales;
--问题1 2014/01/01的数据落在哪个分区？
--问题2 2015年的数据落在哪个分区？

--2个解决办法：一个是添加分区；一个是创建间隔分区

/*
===========================================================
| 创建范围分区表
============================================================
*/
CREATE TABLE sales_range1
(sales_id NUMBER NOT NULL,
product_id VARCHAR2(5),
sales_date DATE,
sales_cost NUMBER(10),
areacode VARCHAR2(5)
)
partition by range(sales_date)
(partition part1 values less than (to_date('2011/01/01','yyyy/mm/dd')) TABLESPACE tp_orders,
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 (to_date('2014/01/01','yyyy/mm/dd'))
);

--查询分区情况
SELECT table_name,partition_name
FROM user_tab_partitions
WHERE table_name=UPPER('sales_range1');

--插入数据
insert into sales_range1 values (1000,'p1',to_date('2011-01-01','yyyy-mm-dd'),1000,'A1');

--查询数据

select * from sales_range1 PARTITION (part2);

/*
===========================================================
| 间隔分区表
============================================================
*/
CREATE TABLE sales_interval1
(sales_id NUMBER NOT NULL,
product_id VARCHAR2(5),
sales_date DATE,
sales_cost NUMBER(10),
areacode VARCHAR2(5)
)
PARTITION BY RANGE(sales_date)
INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))
(PARTITION part1 VALUES LESS THAN (to_date('2011/01/01','yyyy/mm/dd')))

--查询分区情况
SELECT table_name,partition_name,tablespace_name
FROM user_tab_partitions
WHERE table_name=UPPER('sales_interval1');

INSERT INTO sales_interval1 VALUES (1000,'p1',SYSDATE,2000,'A2');

SELECT * FROM sales_interval1 PARTITION (SYS_P142);

--现有表创建新表
CREATE TABLE sales_interval2
PARTITION BY RANGE(sales_date)
INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))
(PARTITION part1 VALUES LESS THAN (to_date('2011/01/01','yyyy/mm/dd')))
AS SELECT * FROM sales;

 

 

 

/*
===========================================================
| 范围分区
============================================================
*/

CREATE TABLE SALES1
(
SALES_ID NUMBER,
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE NOT NULL
)
PARTITION BY RANGE (SALES_DATE)
(
PARTITION P1 VALUES LESS THAN (to_date('2013-04-1', 'yyyy-mm-dd')),
PARTITION P2 VALUES LESS THAN (to_date('2013-07-1', 'yyyy-mm-dd')),
PARTITION P3 VALUES LESS THAN (to_date('2013-10-1', 'yyyy-mm-dd')),
PARTITION P4 VALUES LESS THAN (to_date('2014-01-1', 'yyyy-mm-dd')),
PARTITION P5 VALUES LESS THAN (maxvalue)
);

--要查看在第三季度的数据
SELECT * FROM SALES1 partition(P3);

--要删除第三季度的数据
DELETE FROM SALES1 partition(P3);

 

/*
===========================================================
| 间隔分区
============================================================
*/

--创建间隔分区表
CREATE TABLE SALES2
(
SALES_ID NUMBER,
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE NOT NULL
)
PARTITION BY RANGE(SALES_DATE)
INTERVAL(NUMTOYMINTERVAL(3,'MONTH'))
(PARTITION P1 VALUES LESS THAN (to_date('2013-04-1','yyyy/mm/dd')));
--插入数据
INSERT INTO sales2 VALUES (1,'a',to_date('2013-08-1'),10,'1');
--获得分区情况
SELECT table_name,partition_name
FROM user_tab_partitions
WHERE table_name=UPPER('sales2');
--查询输出结果，系统自动根据输入数据情况创建新分区“SYS_P82”
TABLE_NAME PARTITION_NAME
----------------------------
SALES2 P1
SALES2 SYS_P82
--查询分区数据
SELECT * FROM sales2 PARTITION(sys_P82);

/*
===========================================================
| 利用间隔分区将开始创建时没有分区的表创建为新的间隔分区表
============================================================
*/
/*准备工作*/
--1.创建普通SALES表
CREATE TABLE SALES
(
SALES_ID NUMBER,
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE NOT NULL
);

--2.自行向SALES表插入数据

/*实施步骤*/
--1.创建间隔分区表SALES3
CREATE TABLE SALES3
PARTITION BY RANGE(SALES_DATE)
INTERVAL(NUMTOYMINTERVAL(3,'MONTH'))
(PARTITION P1 VALUES LESS THAN (to_date('2013-04-1','yyyy/mm/dd')))
AS SELECT * FROM SALES; --SALES表为已经创建的表

--2.查询分区情况
SELECT table_name,partition_name
FROM user_tab_partitions
WHERE table_name=UPPER('sales3');
--3.查询某一分区数据
--4.自行向SALES3表插入数据
--5.再次查询某一分区数据