ITPub博客

首页 > 数据库 > Oracle > 分区表和索引

分区表和索引

翻译 Oracle 作者:liiinuuux 时间:2014-01-28 16:42:54 0 删除 编辑
分区表和索引


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

          操作表分区时,局部索引会被自动维护,而全局索引不会。
          因此在操作表分区时,一定要加上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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 分区管理
请登录后发表评论 登录
全部评论

注册时间:2012-11-12

  • 博文量
    94
  • 访问量
    308804