PARTITION BY RANGE
CREATE TABLE customer
  ( cust_name      varchar2(20)
  , cust_id       NUMBER
  , time_id       DATE
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('2010-04-01','yyyy-mm-dd'))
 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('2010-07-01','yyyy-mm-dd'))
 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('2010-10-01','yyyy-mm-dd'))
 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('2011-01-01','yyyy-mm-dd'))
, partition sales_q_other VALUES LESS THAN(maxvalue)
 );


PARTITION BY LIST
CREATE TABLE customer2
      (cust_name      varchar2(20)
  , cust_id       NUMBER
  , time_id       DATE)
   PARTITION BY LIST (cust_name)
      (PARTITION q1_northwest VALUES ('huangqian'),
       PARTITION q1_southwest VALUES ('huangqi'),
       PARTITION q1_northeast VALUES  ('hungqi'),
       PARTITION q1_southeast VALUES ('huangqin'));


PARTITION BY HASH
CREATE TABLE customer3
(cust_name      varchar2(20)
, cust_id       NUMBER
, time_id       DATE)
PARTITION BY HASH (cust_id)
PARTITIONS 3; 
--会自动生成三个分区，即出现三个user_tab_partitions.PARTITION_NAME
--一般建议PARTITIONS数目为偶数


INTERVAL Partition
CREATE TABLE customer4
(cust_name      varchar2(20)
, cust_id       NUMBER
, time_id       DATE)
PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p01 VALUES LESS THAN (to_date('2003-01-01','yyyy-mm-dd')));


Reference Partition
create table customers101
(
  cust_id number primary key,
  cust_name varchar2(64),
  rating varchar2(1) not null
)
partition by list(rating)
(
  partition p_1 values('A'),
  partition p_2 values('B')
);

create table sales101
  (
    sales_id number primary key,
    cust_id number not null,
    sales_amt number,
    constraint fk_sales foreign key(cust_id) references customers101(cust_id)
  )
partition by reference(fk_sales);
--此次cust_id必须加上not null，否则会报错ORA-14652: reference partitioning foreign key is not supported

select table_name,partitioning_type,partition_count from user_part_tables where table_name='CUSTOMERS101';
TABLE_NAME                     PARTITION PARTITION_COUNT
------------------------------ --------- ---------------
CUSTOMERS101                   LIST                    2

select table_name,partition_name,high_value from user_tab_partitions where table_name='CUSTOMERS101';
TABLE_NAME                     PARTITION_NAME     HIGH_VALUE
--------------------------------------------------------------------------------
CUSTOMERS101                   P_1                 'A'
CUSTOMERS101                   P_2                 'B'

select table_name,partitioning_type,partition_count,ref_ptn_constraint_name from user_part_tables where table_name='SALES101';
TABLE_NAME  PARTITION  PARTITION_COUNT  REF_PTN_CONSTRAINT_NAME
---------------------------------------------------------------------
SALES101    REFERENCE       2            FK_SALES

select table_name,partition_name from user_tab_partitions where table_name='SALES101';
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
SALES101                       P_1
SALES101                       P_2

insert into customers101 values(1001,'Oracle','A');
insert into customers101 values(1002,'IBM','B');
insert into sales101 values(101,1001,1);
insert into sales101 values(102,1001,2);
insert into sales101 values(103,1002,1);
SQL> select * from sales101 partition (p_1);
  SALES_ID    CUST_ID  SALES_AMT
---------- ---------- ----------
       101       1001          1
       102       1001          2

SQL> select * from sales101 partition (p_2);
  SALES_ID    CUST_ID  SALES_AMT
---------- ---------- ----------
       103       1002          1