Partition table & Partition Indexes 02_实践篇

二 实践
     1. 创建分区表
         创建分区表,索引和创建普通表,索引一样,除了在CREATE TABLE语句里必须包括PARTITION关键字外.
        可以在普通表(heap organized),索引组织表(index organized)上分区,除了包含LOANG, LOANG RAW的列. 也可以在分区表上创建分全局分区索引(non-partitioned global indexes), 范围或哈希分区的全局索引,本地索引.
       
      1.1 创建范围分区表(Range Partitioned Table)
            关键字: PARTITION BY RANGE
                          VALUE LESS THAN
            实例一:
            SQL> 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_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','dd-MON-yyyy'))         
              TABLESPACE tsa
           , PARTITION sales_q2_2009 VALUES LESS THAN (TO_DATE('01-JUL-2009','dd-MON-yyyy'))         
              TABLESPACE tsb         
           , PARTITION sales_q3_2009 VALUES LESS THAN (TO_DATE('01-OCT-2009','dd-MON-yyyy'))         
              TABLESPACE tsc         
           , PARTITION sales_q4_2010 VALUES LESS THAN (TO_DATE('01-JAN-2010','dd-MON-yyyy'))         
              TABLESPACE tsd         
           );
           以上SQL语句创建了一个分区表Sales. 该分区以time_id作为分区键,根据一年四个季度分为4个区,每个区有个分界点. 因此小于'01-APR-2009'的数据将存有分区一sales_q1_2009内.
         
           实例二:
            SQL>
           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_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','dd-MON-yyyy'))         
              TABLESPACE tsa STORAGE(INITIAL 20K NEXT 10K)
           , PARTITION sales_q2_2009 VALUES LESS THAN (TO_DATE('01-JUL-2009','dd-MON-yyyy'))         
              TABLESPACE tsb         
           , PARTITION sales_q3_2009 VALUES LESS THAN (TO_DATE('01-OCT-2009','dd-MON-yyyy'))         
              TABLESPACE tsc         
           , PARTITION sales_q4_2010 VALUES LESS THAN (TO_DATE('01-JAN-2010','dd-MON-yyyy'))         
              TABLESPACE tsd         
           )
           ENABLE ROW MOVEMENT;
           以上语句创建一个time_id为分区键的分区表sales.存储参数STORAGE和LOGGING属性被指定在表级别.取代了从表空间级别继承下来的 默认值,并且这个值也将继承到范围分区里.在分区一里由于在第一季度的业务量比较少,所以存储参数设的比较小. ENABLE ROW MOVEMENT语句指定允许自动移植一行到新分区, 如果更新一个key值,将会把这行放到新的分区.

       1.2 创建范围分区全局索引(Range-Partitioned Global Index)
             全局索引必须是前缀的(索引键值必须和分区键值相同)
             SQL>
            CREATE INDEX amount_sold_ix ON sales(amount_sold)
            GLOBAL PARTITION BY RANGE(amount_sold)
           ( PARTITION p_100 VALUES LESS THAN (100)
            ,PARTITION p_1000 VALUES LESS THAN (1000)
            ,PARTITION p_10000 VALUES LESS THAN (10000)
            ,PARTITION p_100000 VALUES LESS THAN (100000)
            ,PARTITION p_1000000 VALUES LESS THAN (1000000)
            ,PARTITION p_greater_than_1000000 VALUES LESS THAN(MAXVALUE)
           );
     
        1.3 Interval Partitioned Tables
             由于Interval 分区是Range分区的一个扩展,因此必须指定至少一个Range 分区. 分区键只能是来自表的单个列且为NUMBER, DATE类型.可选的STORE IN关键字用于指定一个或多个表空间存储interval partition数据,并使用round-robin循环算法为随后创建的interval Partition分配空间.
             实例:
            SQL> CREATE TABLE interval_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)
           INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
           ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008','DD-MM-YYYY')),
             PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2009','DD-MM-YYYY')),
             PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009','DD-MM-YYYY')),
             PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010','DD-MM-YYYY'))
           );
         以上语句创建了一个interval分区,p3分区的上界表示一个转折点(transition point). p3和所有比它小的分区都属于范围之内段(range section),然后在p3之上的分区将会属于interval段(interval section).数据库按一个月为间隔自动创建新分区.

      1.4 创建Hash-Partitioned tables
           关键字:
           PARTITION BY HASH
           PARTITIONS: 指定要创建的分区个数
           PARTITION:命名单个分区和它们的表空间
          TABLESPACE:指定要存储的表空间
          一个表的所有分区必须相同的属性(TABLESPACE除外),既从表级别继承下来的.
          实例:
          SQL> CREATE TABLE hash_par_dept
             ( deptno NUMBER,
               deptname VARCHAR2(32))
             STORAGE (INITIAL 10K)
             PARTITION BY HASH(deptno)
             ( PARTITION p1 TABLESPACE tsa,
               PARTITION p2 TABLESPACE tsb,
               PARTITION p3 TABLESPACE tsc,
               PARTITION p4 TABLESPACE tsd
             );

          1.5 创建Hash-Partitioned Global Index
              哈希分区全局索引对于竞争比较激烈的小叶子节点多用户OLTP系统可以提升性能. 对于涉及到索引分区键的等号(=)或IN操作, 哈希分区全局索引也非常有效.
              实例:
                SQL> CREATE INDEX hash_glb_idx_dept ON hash_par_dept(deptno, deptname) GLOBAL
                PARTITION BY HASH(deptno, deptname)
                (PARTITION p1 TABLESPACE tsa,
                 PARTITION p2 TABLESPACE tsb,
                 PARTITION p3 TABLESPACE tsc,
                 PARTITION p4 TABLESPACE tsd);

            1.6 创建列表分区表(List-partitioned tables)
                 列表分区和范围分区类型,只不过是用PARTITION BY LIST作为关键字. 当插入数据是将根据各分区列出的值来分布数据,如果和列出的值相等那就属于这个分区.可以用关键字DEFAULT来存储没有找到映射关系的数据.
                 实例:
                 SQL>CREATE TABLE sales_by_region
                          (item# INTEGER, qty INTEGER,
                           store_name VARCHAR(30), state_code VARCHAR(2),
                           sale_date DATE)
                   STORAGE(INITIAL 10K NEXT 20K) TABLESPACE tsa
                   PARTITION BY LIST (state_code)
                   (
                   PARTITION region_east
                      VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')
                      STORAGE (INITIAL 20K NEXT 40K PCTINCREASE 50)
                      TABLESPACE tsb,
                   PARTITION region_west
                      VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO')
                      NOLOGGING,
                   PARTITION region_south
                      VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'),
                   PARTITION region_central
                      VALUES ('OH','ND','SD','MO','IL','MI','IA'),
                   PARTITION region_null
                      VALUES (NULL),
                   PARTITION region_unknown
                      VALUES (DEFAULT)
                   );
                 
              1.7 创建引用分区表(Reference Partition table)
                    使用关键字PARTITION BY REFERENCE来创建引用分区表.这个关键字需要指定引用约束的名字,并作为分区引用约束. 这个引用约束必须是生效(enabled,enforced)的.
                     SQL> CREATE TABLE orders
                    ( order_id           NUMBER(12),
                      order_date         DATE,
                      order_mode         VARCHAR2(8),
                      customer_id        NUMBER(6),
                      order_status       NUMBER(2),
                      order_total        NUMBER(8,2),
                      sales_rep_id       NUMBER(6),
                      promotion_id       NUMBER(6),
                      CONSTRAINT orders_pk PRIMARY KEY(order_id)
                    )
                  PARTITION BY RANGE(order_date)
                    ( PARTITION Q1_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')),
                      PARTITION Q2_2009 VALUES LESS THAN (TO_DATE('01-JUL-2009','DD-MON-YYYY')),
                      PARTITION Q3_2009 VALUES LESS THAN (TO_DATE('01-OCT-2009','DD-MON-YYYY')),
                      PARTITION Q4_2009 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY'))
                    );
               
               
                   SQL> CREATE TABLE order_items
                    ( order_id           NUMBER(12) NOT NULL,
                      line_item_id       NUMBER(3)  NOT NULL,
                      product_id         NUMBER(6)  NOT NULL,
                      unit_price         NUMBER(8,2),
                      quantity           NUMBER(8),
                      CONSTRAINT order_items_fk
                      FOREIGN KEY(order_id) REFERENCES orders(order_id)
                    )
                    PARTITION BY REFERENCE(order_items_fk);

                   如果提供了分区描述符(Partition descriptor), 那么被列举的分区数量必须等于referenced table的分区数或子分区数. 如果是组合分区,那么这个表将有一个分区对应所有的自分区,否则将对应所有的父分区.

               1.8 创建组合分区表
                   关键字: PARTITION BY [ RANGE | LIST ]
                SUBPARTITION BY [ RANGE | LIST | HASH }

                  实例一: Range-Hash分区表
                  SQL> CREATE TABLE range_hash_par_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) SUBPARTITION BY HASH (cust_id)
                SUBPARTITIONS 8 STORE IN (tsa, tsb, tsc, tsd)
               ( PARTITION sales_q1_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','dd-MON-yyyy'))
               , PARTITION sales_q2_2009 VALUES LESS THAN (TO_DATE('01-JUL-2009','dd-MON-yyyy'))
               , PARTITION sales_q3_2009 VALUES LESS THAN (TO_DATE('01-OCT-2009','dd-MON-yyyy'))
               , PARTITION sales_q4_2009 VALUES LESS THAN (TO_DATE('01-JAN-2010','dd-MON-yyyy'))
               );

               SQL>CREATE TABLE emp (deptno NUMBER, empname VARCHAR(32), grade NUMBER)                 
                    PARTITION BY RANGE(deptno) SUBPARTITION BY HASH(empname)              
                       SUBPARTITIONS 8 STORE IN (ts1, ts3, ts5, ts7)              
                   (PARTITION p1 VALUES LESS THAN (1000),              
                    PARTITION p2 VALUES LESS THAN (2000)            
                       STORE IN (ts2, ts4, ts6, ts8),              
                    PARTITION p3 VALUES LESS THAN (MAXVALUE)              
                      (SUBPARTITION p3_s1 TABLESPACE ts4,              
                       SUBPARTITION p3_s2 TABLESPACE ts5));

                创建本地索引(local index):
                SQL> CREATE INDEX emp_idx ON emp(deptno) LOCAL STORE IN ( ts7, ts8, ts9);
                这个本地的索引有如下特性:
               1). 和基表有一样多的分区
               2). 任何一个索引分区和基表一样多的子分区
               3). 在给定基表的子分区里行的索引项都被存储在相对应的索引子分区里.

             1.9 创建Range-List分区表
                   实例:
                    SQL>CREATE TABLE quarterly_regional_sales
                      (deptno number, item_no varchar2(20),
                       txn_date date, txn_amount number, state varchar2(2))
                  TABLESPACE tsa
                  PARTITION BY RANGE (txn_date)
                    SUBPARTITION BY LIST (state)
                      (PARTITION q1_2009 VALUES LESS THAN (TO_DATE('1-APR-2009','DD-MON-YYYY'))
                         (SUBPARTITION q1_2009_northwest VALUES ('OR', 'WA'),
                          SUBPARTITION q1_2009_southwest VALUES ('AZ', 'UT', 'NM'),
                          SUBPARTITION q1_2009_northeast VALUES ('NY', 'VM', 'NJ'),
                          SUBPARTITION q1_2009_southeast VALUES ('FL', 'GA'),
                          SUBPARTITION q1_2009_northcentral VALUES ('SD', 'WI'),
                          SUBPARTITION q1_2009_southcentral VALUES ('OK', 'TX')
                         ),
                       PARTITION q2_2009 VALUES LESS THAN ( TO_DATE('1-JUL-2009','DD-MON-YYYY'))
                         (SUBPARTITION q2_2009_northwest VALUES ('OR', 'WA'),
                          SUBPARTITION q2_2009_southwest VALUES ('AZ', 'UT', 'NM'),
                          SUBPARTITION q2_2009_northeast VALUES ('NY', 'VM', 'NJ'),
                          SUBPARTITION q2_2009_southeast VALUES ('FL', 'GA'),
                          SUBPARTITION q2_2009_northcentral VALUES ('SD', 'WI'),
                          SUBPARTITION q2_2009_southcentral VALUES ('OK', 'TX')
                         ),
                       PARTITION q3_2009 VALUES LESS THAN (TO_DATE('1-OCT-2009','DD-MON-YYYY'))
                         (SUBPARTITION q3_2009_northwest VALUES ('OR', 'WA'),
                          SUBPARTITION q3_2009_southwest VALUES ('AZ', 'UT', 'NM'),
                          SUBPARTITION q3_2009_northeast VALUES ('NY', 'VM', 'NJ'),
                          SUBPARTITION q3_2009_southeast VALUES ('FL', 'GA'),
                          SUBPARTITION q3_2009_northcentral VALUES ('SD', 'WI'),
                          SUBPARTITION q3_2009_southcentral VALUES ('OK', 'TX')
                         ),
                       PARTITION q4_2009 VALUES LESS THAN ( TO_DATE('1-JAN-2010','DD-MON-YYYY'))
                         (SUBPARTITION q4_2009_northwest VALUES ('OR', 'WA'),
                          SUBPARTITION q4_2009_southwest VALUES ('AZ', 'UT', 'NM'),
                          SUBPARTITION q4_2009_northeast VALUES ('NY', 'VM', 'NJ'),
                          SUBPARTITION q4_2009_southeast VALUES ('FL', 'GA'),
                          SUBPARTITION q4_2009_northcentral VALUES ('SD', 'WI'),
                          SUBPARTITION q4_2009_southcentral VALUES ('OK', 'TX')
                         )
                      );

                1.10 创建Range-Range 分区表
                 SQL>
                CREATE TABLE shipments
                ( order_id      NUMBER NOT NULL
                , order_date    DATE NOT NULL
                , delivery_date DATE NOT NULL
                , customer_id   NUMBER NOT NULL
                , sales_amount  NUMBER NOT NULL
                )
                PARTITION BY RANGE (order_date)
                SUBPARTITION BY RANGE (delivery_date)
                ( PARTITION p_2006_jul VALUES LESS THAN (TO_DATE('01-AUG-2006','dd-MON-yyyy'))
                  ( SUBPARTITION p06_jul_e VALUES LESS THAN (TO_DATE('15-AUG-2006','dd-MON-yyyy'))
                  , SUBPARTITION p06_jul_a VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy'))
                  , SUBPARTITION p06_jul_l VALUES LESS THAN (MAXVALUE)
                  )
                , PARTITION p_2006_aug VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy'))
                  ( SUBPARTITION p06_aug_e VALUES LESS THAN (TO_DATE('15-SEP-2006','dd-MON-yyyy'))
                  , SUBPARTITION p06_aug_a VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
                  , SUBPARTITION p06_aug_l VALUES LESS THAN (MAXVALUE)
                  )
                , PARTITION p_2006_sep VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
                  ( SUBPARTITION p06_sep_e VALUES LESS THAN (TO_DATE('15-OCT-2006','dd-MON-yyyy'))
                  , SUBPARTITION p06_sep_a VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy'))
                  , SUBPARTITION p06_sep_l VALUES LESS THAN (MAXVALUE)
                  )
                , PARTITION p_2006_oct VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy'))
                  ( SUBPARTITION p06_oct_e VALUES LESS THAN (TO_DATE('15-NOV-2006','dd-MON-yyyy'))
                  , SUBPARTITION p06_oct_a VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy'))
                  , SUBPARTITION p06_oct_l VALUES LESS THAN (MAXVALUE)
                  )
                , PARTITION p_2006_nov VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy'))
                  ( SUBPARTITION p06_nov_e VALUES LESS THAN (TO_DATE('15-DEC-2006','dd-MON-yyyy'))
                  , SUBPARTITION p06_nov_a VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
                  , SUBPARTITION p06_nov_l VALUES LESS THAN (MAXVALUE)
                  )
                , PARTITION p_2006_dec VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
                  ( SUBPARTITION p06_dec_e VALUES LESS THAN (TO_DATE('15-JAN-2007','dd-MON-yyyy'))
                  , SUBPARTITION p06_dec_a VALUES LESS THAN (TO_DATE('01-FEB-2007','dd-MON-yyyy'))
                  , SUBPARTITION p06_dec_l VALUES LESS THAN (MAXVALUE)
                  )
                );
               
              1.11 创建List-*分区表
                实例一:List-Hash分区表
                 SQL> CREATE TABLE accounts
                 ( id NUMBER
                  ,account_number NUMBER
                  ,customer_id NUMBER
                  ,balance NUMBER
                  ,branch_id NUMBER
                  ,region VARCHAR2(2)
                  ,status VARCHAR2(1)
                 )
                 PARTITION BY LIST(region)
                 SUBPARTITION BY HASH(customer_id)
                 SUBPARTITIONS 8
                 ( PARTITION p_northwest VALUES('OR','WA')
                  ,PARTITION p_southwest VALUES('AZ','UT','NM')
                  ,PARTITION p_northeast VALUES('NY','VM','NJ')
                  ,PARTITION p_southeast VALUES('FL','GA')
                  ,PARTITION p_northcentral VALUES('SD','WI')
                  ,PARTITION p_southcentral VALUES('OK','TX')
                  ,PARTITION p_NULL VALUES(NULL)
                  ,PARTITION p_default VALUES(DEFAULT)
                 );

               实例二: List-List分区表
              SQL>                
                  CREATE TABLE list_list_accounts
                  ( id NUMBER
                   ,account_number NUMBER
                   ,customer_id NUMBER
                   ,balance NUMBER
                   ,branch_id NUMBER
                   ,region VARCHAR2(2)
                   ,status VARCHAR2(1)
                  )
                  PARTITION BY LIST(region)
                  SUBPARTITION BY LIST(status)
                  ( PARTITION p_northwest VALUES('OR','WA')
                      ( SUBPARTITION p_nw_bad VALUES('B')
                       ,SUBPARTITION p_nw_avg VALUES('A')
                       ,SUBPARTITION p_nw_good VALUES('G')
                       TABLESPACE tsa
                      )
                   ,PARTITION p_southwest VALUES('AZ','UT','NM')
                      ( SUBPARTITION p_sw_bad VALUES('B')
                       ,SUBPARTITION p_sw_avg VALUES('A')
                       ,SUBPARTITION p_sw_good VALUES ('G')
                      )
                   ,PARTITION p_northeast VALUES('NY','VM','NJ')
                      ( SUBPARTITION p_ne_bad VALUES('B')
                       ,SUBPARTITION p_ne_avg VALUES('A')
                       ,SUBPARTITION p_ne_good VALUES ('G')
                       )
                   ,PARTITION p_southeast VALUES('FL','GA')
                      ( SUBPARTITION p_se_bad VALUES('B')
                       ,SUBPARTITION p_se_avg VALUES('A')
                       ,SUBPARTITION p_se_good VALUES ('G')
                       )
                   ,PARTITION p_northcentral VALUES ('SD', 'WI')
                      ( SUBPARTITION p_nc_bad VALUES ('B')
                       ,SUBPARTITION p_nc_average VALUES ('A')
                       ,SUBPARTITION p_nc_good VALUES ('G')
                      )
                   ,PARTITION p_southcentral VALUES ('OK', 'TX')
                      ( SUBPARTITION p_sc_bad VALUES ('B')
                       ,SUBPARTITION p_sc_average VALUES ('A')
                       ,SUBPARTITION p_sc_good VALUES ('G')
                      )
                   ,PARTITION p_null VALUES(NULL)
                      ( SUBPARTITION p_sub_null VALUES(NULL))
                   ,PARTITION p_default VALUES(DEFAULT)
                      ( SUBPARTITION p_sub_default VALUES(DEFAULT))
                  );

                 实例三: List-Range分区表
                 SQL>
                CREATE TABLE list_range_accounts
                ( id             NUMBER
                , account_number NUMBER
                , customer_id    NUMBER
                , balance        NUMBER
                , branch_id      NUMBER
                , region         VARCHAR(2)
                , status         VARCHAR2(1)
                )
                PARTITION BY LIST (region)
                SUBPARTITION BY RANGE (balance)
                ( PARTITION p_northwest VALUES ('OR', 'WA')
                  ( SUBPARTITION p_nw_low VALUES LESS THAN (1000)
                  , SUBPARTITION p_nw_average VALUES LESS THAN (10000)
                  , SUBPARTITION p_nw_high VALUES LESS THAN (100000)
                  , SUBPARTITION p_nw_extraordinary VALUES LESS THAN (MAXVALUE)
                  )
                , PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')
                  ( SUBPARTITION p_sw_low VALUES LESS THAN (1000)
                  , SUBPARTITION p_sw_average VALUES LESS THAN (10000)
                  , SUBPARTITION p_sw_high VALUES LESS THAN (100000)
                  , SUBPARTITION p_sw_extraordinary VALUES LESS THAN (MAXVALUE)
                  )
                , PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')
                  ( SUBPARTITION p_ne_low VALUES LESS THAN (1000)
                  , SUBPARTITION p_ne_average VALUES LESS THAN (10000)
                  , SUBPARTITION p_ne_high VALUES LESS THAN (100000)
                  , SUBPARTITION p_ne_extraordinary VALUES LESS THAN (MAXVALUE)
                  )
                , PARTITION p_southeast VALUES ('FL', 'GA')
                  ( SUBPARTITION p_se_low VALUES LESS THAN (1000)
                  , SUBPARTITION p_se_average VALUES LESS THAN (10000)
                  , SUBPARTITION p_se_high VALUES LESS THAN (100000)
                  , SUBPARTITION p_se_extraordinary VALUES LESS THAN (MAXVALUE)
                  )
                , PARTITION p_northcentral VALUES ('SD', 'WI')
                  ( SUBPARTITION p_nc_low VALUES LESS THAN (1000)
                  , SUBPARTITION p_nc_average VALUES LESS THAN (10000)
                  , SUBPARTITION p_nc_high VALUES LESS THAN (100000)
                  , SUBPARTITION p_nc_extraordinary VALUES LESS THAN (MAXVALUE)
                  )
                , PARTITION p_southcentral VALUES ('OK', 'TX')
                  ( SUBPARTITION p_sc_low VALUES LESS THAN (1000)
                  , SUBPARTITION p_sc_average VALUES LESS THAN (10000)
                  , SUBPARTITION p_sc_high VALUES LESS THAN (100000)
                  , SUBPARTITION p_sc_extraordinary VALUES LESS THAN (MAXVALUE)
                  )
                ) ENABLE ROW MOVEMENT;

               1.12 创建Interval-*分区表
                实例一: 创建Interval-hash分区表(通过SUBPARTITIONS指定HASH子分区数)
                SQL>
                CREATE TABLE interval_hash_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) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
                 SUBPARTITION BY HASH (cust_id) SUBPARTITIONS 4
                 ( PARTITION before_2009 VALUES LESS THAN (TO_DATE('01-JAN-2009','dd-MON-yyyy')))
                PARALLEL;

               实例二: 创建Interval-Hash分区表(通过template模板指定HASH子分区数)
               SQL>
                CREATE TABLE interval_hash_template_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) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
                 SUBPARTITION BY hash(cust_id)
                   SUBPARTITION template
                   ( SUBPARTITION p1 TABLESPACE tsa
                   , SUBPARTITION p2 TABLESPACE tsb
                   , SUBPARTITION p3 TABLESPACE tsc
                   , SUBPARTITION P4 TABLESPACE tsd
                   )
                 ( PARTITION before_2009 VALUES LESS THAN (TO_DATE('01-JAN-2009','dd-MON-yyyy'))
                ) PARALLEL;

               实例三: 创建Interval-List分区表(唯一能定义列表子分区list subpartitions 给将来的interval partitions就是通过子分区模板. 如果不使用子分区模板,那么为所有interval 分区创建的子分区都是DEFAULT子分区.
               SQL>
                CREATE TABLE interval_list_template_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) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
                 SUBPARTITION BY LIST (channel_id)
                   SUBPARTITION TEMPLATE
                   ( SUBPARTITION p_catalog VALUES ('C')
                   , SUBPARTITION p_internet VALUES ('I')
                   , SUBPARTITION p_partners VALUES ('P')
                   , SUBPARTITION p_direct_sales VALUES ('S')
                   , SUBPARTITION p_tele_sales VALUES ('T')
                   )
                 ( PARTITION before_2009 VALUES LESS THAN (TO_DATE('01-JAN-2009','dd-MON-yyyy')))
                PARALLEL;

              实例四: 创建Interval-Range分区表(也只能通过模板来创建interval分区的range子分区.如果不使用子分区模板,那么创建的range子分区都是以MAXVALUE为上界.)
              SQL>
                CREATE TABLE interval_range_template_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) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
                 SUBPARTITION BY RANGE(amount_sold)
                   SUBPARTITION TEMPLATE
                   ( SUBPARTITION p_low VALUES LESS THAN (1000)
                   , SUBPARTITION p_medium VALUES LESS THAN (4000)
                   , SUBPARTITION p_high VALUES LESS THAN (8000)
                   , SUBPARTITION p_ultimate VALUES LESS THAN (maxvalue)
                   )
                 ( PARTITION before_2009 VALUES LESS THAN (TO_DATE('01-JAN-2009','dd-MON-yyyy')))
                PARALLEL;

               1.13 使用子分区模板创建组合分区表(Using Subpartition Templates to Describe Composite Partitioned Tables)
                    可以通过子分区模板来创建组合分区表.由于不用为每个分区指定子分区定义符,所有这大大的简化了代码. 当定义了子分区模板时,如果分区里没有定义子分区描述符,则使用定义的子分区模板,如果分区里定义了子分区,那么将不使用子分区模板而使用自己定义的子分 区.如果既没有子分区模板也没有定义自己的子分区,那么系统创建一个DEFAULT子分区.
                    子分区模板可以定义子分区的细节,也可以只定义HASH子分区的数量.
                    注: interval-[ list | range ]必须使用子分区模板
                   
                    实例一: Range-Hash分区表(使用子分区模板)
                    SQL>
                     CREATE TABLE range_hash_template_emp (deptno NUMBER, empname VARCHAR(32), grade NUMBER)
                     PARTITION BY RANGE(deptno) SUBPARTITION BY HASH(empname)
                     SUBPARTITION TEMPLATE
                         (SUBPARTITION a TABLESPACE tsa,
                          SUBPARTITION b TABLESPACE tsb,
                          SUBPARTITION c TABLESPACE tsc,
                          SUBPARTITION d TABLESPACE tsd
                         )
                    (PARTITION p1 VALUES LESS THAN (1000),
                     PARTITION p2 VALUES LESS THAN (2000),
                     PARTITION p3 VALUES LESS THAN (MAXVALUE)
                    );

                    以上语句创建了一个range-hash分区表,它有如下特性:
                     1). 每个分区有四个子分区(以子分区模板的方式定义的)
                     2). 每个子分区指定了一个表空间. 如果在子分区模板里一个子分区定义了表空间,那么所有的子分区都要定义表空间.
                     3). 除了interval-*子分区外,其它子分区的名字是一个组合:Partition name_subpartition name
                           Interval-*子分区的名字是系统产生的:SYS_SUBPn
 
                   实例二: Range-List分区表
                   可以通过子分区模板来实现条带化数据于各个表空间间(Stripe data across tablespaces).
                   SQL>
                   CREATE TABLE range_list_template_sales
                   ( deptno number, item_no varchar2(20),
                     txn_date date, txn_amount number, state varchar2(2))
                   PARTITION BY RANGE (txn_date)
                   SUBPARTITION BY LIST (state)
                   SUBPARTITION TEMPLATE
                       SUBPARTITION southwest VALUES ('AZ', 'UT', 'NM') TABLESPACE tbs_2,
                       SUBPARTITION northeast VALUES ('NY', 'VM', 'NJ') TABLESPACE tbs_3,
                       SUBPARTITION southeast VALUES ('FL', 'GA') TABLESPACE tbs_4,
                       SUBPARTITION midwest VALUES ('SD', 'WI') TABLESPACE tbs_5,
                       SUBPARTITION south VALUES ('AL', 'AK') TABLESPACE tbs_6,
                       SUBPARTITION others VALUES (DEFAULT ) TABLESPACE tbs_7
                      )
                  (PARTITION q1_2009 VALUES LESS THAN ( TO_DATE('01-APR-2009','DD-MON-YYYY')),
                   PARTITION q2_2009 VALUES LESS THAN ( TO_DATE('01-JUL-2009','DD-MON-YYYY')),
                   PARTITION q3_2009 VALUES LESS THAN ( TO_DATE('01-OCT-2009','DD-MON-YYYY')),
                   PARTITION q4_2009 VALUES LESS THAN ( TO_DATE('1-JAN-2010','DD-MON-YYYY'))
                  );

                  以上语句创建通过子分区模板了一个range-list分区表. 在子分区模板中为每个子分区定义了一个表空间. 因此每个分区有6个子分区分布于6个不同的表空间上,实现了垂直条带化分布(vertically striped). 如果在定义分区时指定表空间,则是实现水平条带化(horizontally striped),所有的子分区将分布于对应分区自己的表空间里.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9466564/viewspace-609311/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9466564/viewspace-609311/