- Oracle-范围分区(Range Partitioning)的创建
- -- 范围分区(Range Partitioning)的创建
- -- 官网: https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/loe.html#List-of-Examples
(1) 范围分区作用:
-- 以某一个范围进行分区。eg:时间段划分。
(2) 范围分区特点(如何选择的关键) :
1 )最早、最经典、应用最广的分区算法
2 )范围分区通过对分区字段值的范围进行分区
3 )范围分区特别适合于按时间周期进行数据的存储。日、周、月、年等。
4 )数据管理能力强(数据迁移,数据备份,数据交换)
5 )范围分区的数据可能不均匀。
6 )范围分区与记录值相关,实施难度和可维护性相对较差。
(3) 官方案例
① 官方案例1
CREATE TABLE sales
( prod_id NUMBER(6)
,cust_id NUMBER
,time_id DATE
,channel_id CHAR(1)
,promo_id NUMBER(6)
,quantity_sold NUMBER(3)
,amount_sold NUMBER(10,2)
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006' ,'dd-MON-yyyy')) TABLESPACE tsa
,PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy')) TABLESPACE tsb
,PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-0CT-2006','dd-MON-yyyy')) TABLESPACE tsc
,PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')) TABLESPACE tsd
);
② 官方案例2
CREATE TABLE sales
( prod_id NUMBER(6)
,cust_id NUMBER
,time_id DATE
,channel_id CHAR(1)
,promo_id NUMBER(6)
,quantity_sold NUMBER(3)
,amount_sold NUMBER(10,2)
storage (initial 100k next 50k) logging
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006' ,'dd-MON-yyyy')) TABLESPACE tsa
- -- 范围分区(Range Partitioning)的创建
-- 官网: https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/loe.html#List-of-Examples
CREATE TABLE sales
( prod_id NUMBER(6)
,cust_id NUMBER
,time_id DATE
,channel_id CHAR(1)
,promo_id NUMBER(6)
,quantity_sold NUMBER(3)
,amount_sold NUMBER(10,2)
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006' ,'dd-MON-yyyy')) TABLESPACE tsa
,PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy')) TABLESPACE tsb
,PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-0CT-2006','dd-MON-yyyy')) TABLESPACE tsc
,PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')) TABLESPACE tsd
);