Oracle-范围分区(Range Partitioning)的创建

• 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
);