Oracle 分区表

创建范围分区

/*
===========================================================
|          创建范围分区表
============================================================
*/
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);

 

分区表的管理

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

--查询分区情况
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前

--插入数据
INSERT INTO sales_range1 VALUES (2000,'p1',to_date('2014-01-01','yyyy-mm-dd'),1000,'A1');

--添加分区
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);

SELECT * FROM sales_range1 PARTITION (part5);--15前

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

SELECT * FROM sales_range1 WHERE sales_id=2000;

--移动分区
ALTER TABLE sales_range1 MOVE PARTITION part1 TABLESPACE tp_sales_bak;
--表空间只读后测试插入数据，失败。
INSERT INTO sales_range1 VALUES (3000,'p1',to_date('2009-01-01','yyyy-mm-dd'),1000,'A1');


--表空间
CREATE TABLESPACE tp_sales_bak 
       DATAFILE 'd:\data\tp_sales_bak.dbf' SIZE 100M;      
ALTER USER A_oe QUOTA UNLIMITED ON tp_sales_bak;

--移动完表空间后将表空间设置为只读
ALTER TABLESPACE tp_sales_bak READ ONLY;

ALTER TABLESPACE tp_sales_bak READ WRITE;

 

间隔分区表

/*
===========================================================
|          间隔分区表
============================================================
*/
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 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个解决办法：一个是添加分区；一个是创建间隔分区