分区表和索引

分区表和索引


分区类型：
     局部索引和全局索引：
          全局索引是一个表一个索引，这个索引涵盖表的所有分区。
          局部索引的结构是和表的分区相对应的，一个表分区对应一个索引分区。

           操作表分区时，局部索引会被自动维护，而全局索引不会。

          因此在操作表分区时，一定要加上update indexes，或者rebuild全局索引，或者rebuild全局索引的相应分区。
    
     Range Partitioning
          按某列值得范围分区，数据分布均匀时性能最好。
          CREATE TABLE sales
              ( invoice_no NUMBER,
                sale_year  INT NOT NULL,
                sale_month INT NOT NULL,
                sale_day   INT NOT NULL )
             PARTITION BY RANGE (sale_year, sale_month, sale_day)
              ( PARTITION sales_q1  VALUES LESS THAN (1999, 04, 01)
               TABLESPACE tsa,
                PARTITION sales_q2 VALUES LESS THAN (1999, 07, 01)
               TABLESPACE tsb,
                PARTITION sales_q3 VALUES LESS THAN (1999, 10, 01)
               TABLESPACE tsc,
                PARTITION sales_q4 VALUES LESS THAN (2000, 01, 01)
               TABLESPACE tsd );
     Hash Partitioning
          按某列的hash值分区，可以带来均匀的数据和IO分布。
          CREATE TABLE scubagear
               (id NUMBER,
                name VARCHAR2 (60))
              PARTITION BY HASH (id)
             PARTITIONS  4
              STORE IN (tbs1, tbs2, tbs2, tbs4);
     List Partitioning
          指定某列的哪些值得记录放在哪个分区里。
          CREATE TABLE q1_sales_by_region
                (deptno number,
                 deptname varchar2(20),
                 quarterly_sales number(10, 2),
                 state varchar2(2))
              PARTITION BY LIST (state)
                (PARTITION q1_northwest  VALUES ('OR', 'WA'),
                 PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
                 PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),
                 PARTITION q1_southeast VALUES ('FL', 'GA'),
                 PARTITION q1_northcentral VALUES ('SD', 'WI'),
                 PARTITION q1_southcentral VALUES ('OK', 'TX'));
     Range-Hash Partitioning
          用range分区，分区内再用hash分区
          CREATE TABLE scubagear (equipno NUMBER, equipname VARCHAR(32), price NUMBER)
             PARTITION BY RANGE (equipno)  SUBPARTITION BY HASH(equipname)
               SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)
                (PARTITION p1  VALUES LESS THAN (1000),
                 PARTITION p2 VALUES LESS THAN (2000),
                 PARTITION p3 VALUES LESS THAN ( MAXVALUE));
     Range-List Partitioning
          CREATE TABLE quarterly_regional_sales
                (deptno number, item_no varchar2(20),
                 txn_date date, txn_amount number, state varchar2(2))
            TABLESPACE ts4
             PARTITION BY RANGE (txn_date)
               SUBPARTITION BY LIST (state)
                (PARTITION q1_1999  VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
               (SUBPARTITION q1_1999_northwest  VALUES ('OR', 'WA'),
                 SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
                 SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
                 SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
                 SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
                 SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')
               ),
                 PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))
               (SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
                 SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
                 SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
                 SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
                 SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
                 SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
               ),
                 PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))
               (SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
                 SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
                 SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'),
                 SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'),
                 SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'),
                 SUBPARTITION q3_1999_southcentral VALUES ('OK', 'TX')
               ),
                 PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
               (SUBPARTITION q4_1999_northwest VALUES ('OR', 'WA'),
                 SUBPARTITION q4_1999_southwest VALUES ('AZ', 'UT', 'NM'),
                 SUBPARTITION q4_1999_northeast VALUES ('NY', 'VM', 'NJ'),
                 SUBPARTITION q4_1999_southeast VALUES ('FL', 'GA'),
                 SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),
                 SUBPARTITION q4_1999_southcentral VALUES ('OK', 'TX')
               )
                );


创建分区表：
     在create table语句后面加上ENABLE ROW MOVEMENT，当记录更新后，会移动到新的合适的分区。
创建分区索引：
     CREATE INDEX month_ix ON sales(sales_month)
        GLOBAL PARTITION BY RANGE(sales_month)
           (PARTITION pm1_ix VALUES LESS THAN (2)
            PARTITION pm2_ix VALUES LESS THAN (3)
            PARTITION pm3_ix VALUES LESS THAN (4)
            PARTITION pm4_ix VALUES LESS THAN (5)
            PARTITION pm5_ix VALUES LESS THAN (6)
            PARTITION pm6_ix VALUES LESS THAN (7)
            PARTITION pm7_ix VALUES LESS THAN (8)
            PARTITION pm8_ix VALUES LESS THAN (9)
            PARTITION pm9_ix VALUES LESS THAN (10)
            PARTITION pm10_ix VALUES LESS THAN (11)
            PARTITION pm11_ix VALUES LESS THAN (12)
            PARTITION pm12_ix VALUES LESS THAN (MAXVALUE));
       创建局部索引
          CREATE INDEX loc_dept_ix ON dept(deptno)  LOCAL;
    
维护分区表：
     增加range分区
          ALTER TABLE sales
                 ADD PARTITION jan96 VALUES LESS THAN ( '01-FEB-1999' )
                TABLESPACE tsx;
     增加hash分区
          ALTER TABLE scubagear  ADD PARTITION;

          ALTER TABLE scubagear
                 ADD PARTITION p_named TABLESPACE gear5;
     增加list分区
           无法为一个有默认分区的list分区表增加分区。
     增加Range-Hash分区
          ALTER TABLE sales  ADD PARTITION q1_2000
                VALUES LESS THAN (2000, 04, 01) COMPRESS
                 SUBPARTITIONS 8  STORE IN tbs5;
          ALTER TABLE diving  MODIFY PARTITION locations_us
                ADD SUBPARTITION us_locs5 TABLESPACE us1;
     增加Range-List分区
          ALTER TABLE quarterly_regional_sales
              ADD PARTITION q1_2000 VALUES LESS THAN (TO_DATE('1-APR-2000','DD-MON-YYYY'))
                STORAGE (INITIAL 20K NEXT 20K) TABLESPACE ts3 NOLOGGING
               (
                  SUBPARTITION q1_2000_northwest VALUES ('OR', 'WA'),
                 SUBPARTITION q1_2000_southwest VALUES ('AZ', 'UT', 'NM'),
                 SUBPARTITION q1_2000_northeast VALUES ('NY', 'VM', 'NJ'),
                 SUBPARTITION q1_2000_southeast VALUES ('FL', 'GA'),
                 SUBPARTITION q1_2000_northcentral VALUES ('SD', 'WI'),
                 SUBPARTITION q1_2000_southcentral VALUES ('OK', 'TX')
               );
          ALTER TABLE quarterly_regional_sales
              MODIFY PARTITION q1_1999
                 ADD SUBPARTITION q1_1999_south
               VALUES ('AR','MS','AL') tablespace ts2;

维护分区index：
     修改index的默认表空间，这样新分配的空间,如分区，就会在这个表空间上。
          ALTER INDEX q1_sales_by_region_locix
                MODIFY DEFAULT ATTRIBUTES TABLESPACE tbs_4;
     将某个分区移动到指定表空间
          ALTER INDEX q1_sales_by_region_locix
                REBUILD PARTITION q1_nonmainland  TABLESPACE tbs_4;
     增加hash分区
          ALTER INDEX hgidx ADD PARTITION p5;
     增加range分区
          索引的最高range分区通常是less than maxvalue。因此不能增加分区。如果要增加，只能用
               ALTER INDEX ... SPLIT PARTITION

合并分区：
     用来减少hash方式和range方式分区的表或索引。
     当hash分区合并时，数据会重新分配、移动到剩余分区。
     合并表分区后时，需要在alter table语句中加上update indexes，否则全局索引不可用。

     压缩hash分区表，分区数量减少1
          ALTER TABLE ouu1
               COALESCE PARTITION;
     压缩range分区中的hash子分区
          ALTER TABLE diving MODIFY PARTITION us_locations
               COALESCE SUBPARTITION;
     压缩hash分区索引
          ALTER INDEX hgidx COALESCE PARTITION;
    
删除分区：
     可以删除range, list, range-list分区，而hash, hash subpartition 或 range-hash只能压缩。
     需要在alter table语句中加上update indexes，否则全局索引不可用。
    
     删除涉及全局索引的表分区：
     方法一：
          删除分区
               ALTER TABLE sales DROP PARTITION dec98;
               ALTER INDEX sales_area_ix REBUILD;
          重建分区索引
               ALTER INDEX sales_area_ix REBUILD PARTITION jan99_ix;
               ALTER INDEX sales_area_ix REBUILD PARTITION feb99_ix;
               ALTER INDEX sales_area_ix REBUILD PARTITION mar99_ix;
               ...
               ALTER INDEX sales_area_ix REBUILD PARTITION dec99_ix;
     方法二：
          DELETE FROM sales WHERE TRANSID < 10000;
          ALTER TABLE sales DROP PARTITION dec98;
     方法三：
          ALTER TABLE sales DROP PARTITION dec98
          UPDATE INDEXES;

     删除有完整性约束的表分区：
     方法一：
          ALTER TABLE sales
             DISABLE CONSTRAINT dname_sales1;
          ALTER TABLE sales DROP PARTITTION dec98;
          ALTER TABLE sales
             ENABLE CONSTRAINT dname_sales1;
     方法二：
          DELETE FROM sales WHERE TRANSID < 10000;
          ALTER TABLE sales DROP PARTITION dec94;
    
     删除索引的分区：
          无法删除局部索引分区，除非先删除对应的表分区。
          对于全局索引，可以删除分区。但是如果删除的分区有数据，则需要rebuild此分区下最高range的分区。
               ALTER INDEX npr DROP PARTITION P1;
               ALTER INDEX npr REBUILD PARTITION P2;

分区表和非分区表相互转换

     将stock_table_3的数据独到stocks的p3分区。
          ALTER TABLE stocks
               EXCHANGE PARTITION p3 WITH TABLE stock_table_3;
      

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

转载于:http://blog.itpub.net/26239116/viewspace-1076277/