分区管理

Range-Partitioned Table

创建范围分区表。

如果加上ENABLE ROW MOVEMENT，则在对time_id进行修改，导致记录不符合分区标准来，会自动移动到相应分区。

用scott用户创建分区表

CREATE TABLE sales1
  ( 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 ts1
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy')) TABLESPACE ts2
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy')) TABLESPACE ts3
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')) TABLESPACE ts4
);

分区表创建后不会立刻生成segment，因此无法从user_segements中查到分区信息。

通过查询user_tables 可看到这个表的partitioned属性为YES，并且由于跨越多个表空间，因此TABLESPACE_NAME不显示

SQL> select t.table_name, t.partitioned, t.tablespace_name from user_tables t where table_name = 'SALES1';

TABLE_NAME                 PAR TABLESPACE_NAME
------------------------------ --- ------------------------------
SALES1                      YES

SQL> select t.table_name, t.partition_name, t.tablespace_name from user_tab_partitions t where t.table_name = 'SALES1';

TABLE_NAME                 PARTITION_NAME                TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SALES1                      SALES_Q1_2006                TS1
SALES1                      SALES_Q2_2006                TS2
SALES1                      SALES_Q3_2006                TS3
SALES1                      SALES_Q4_2006                TS4

为分区表创建全局索引

索引的分区建必须和索引的建相同（下面的amount_sold）

CREATE INDEX amount_sold_ix ON sales1(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)
      );

确认分区索引创建成功

SQL> select t.index_name, t.partitioned, t.tablespace_name from user_indexes t where table_name = 'SALES1';

 

INDEX_NAME                     PARTITIONED TABLESPACE_NAME

------------------------------ ----------- ------------------------------

AMOUNT_SOLD_IX                 YES         

在不指定表空间的情况下，分区创建到默认表空间上

SQL> select t.index_name, t.partition_name, t.tablespace_name from user_ind_partitions t where t.index_name = 'AMOUNT_SOLD_IX';

 

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME

------------------------------ ------------------------------ ------------------------------

AMOUNT_SOLD_IX                 P_100                          USERS

AMOUNT_SOLD_IX                 P_1000                         USERS

AMOUNT_SOLD_IX                 P_10000                        USERS

AMOUNT_SOLD_IX                 P_100000                       USERS

AMOUNT_SOLD_IX                 P_1000000                      USERS

AMOUNT_SOLD_IX                 P_GREATER_THAN_1000000         USERS

 

6 rows selected

Interval-Partitioned Tables

Interval-Partitioned Table不许要一次创建所有分区，而是定义一个分区间，然后在插入数据时自动创建。

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

SQL> select t.table_name, t.partition_name, t.tablespace_name from user_tab_partitions t where t.table_name = 'INTERVAL_SALES';

 

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME

------------------------------ ------------------------------ ------------------------------

INTERVAL_SALES                 P0                             USERS

INTERVAL_SALES                 P1                             USERS

INTERVAL_SALES                 P2                             USERS

INTERVAL_SALES                 P3                             USERS

此时插入数据，如果数据满足已有分区条件，则使用现有分区：

SQL> select t.table_name, t.partition_name, t.tablespace_name, t.segment_created from user_tab_partitions t where t.table_name = 'INTERVAL_SALES';

 

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                SEGMENT_CREATED

------------------------------ ------------------------------ ------------------------------ ---------------

INTERVAL_SALES                 P0                             USERS                          NO

INTERVAL_SALES                 P1                             USERS                          YES

INTERVAL_SALES                 P2                             USERS                          YES

INTERVAL_SALES                 P3                             USERS                          NO

如果插入了现有分区条件之外的数据，oracle自动按照指定的 INTERVAL (NUMTOYMINTERVAL(  1  ,  'MONTH'  ))跨度建立分区。

SQL> insert into interval_sales (time_id) values (TO_DATE('5-6-2011', 'DD-MM-YYYY'));

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

SQL> select t.table_name, t.partition_name, t.tablespace_name, t.segment_created from user_tab_partitions t where t.table_name = 'INTERVAL_SALES';

 

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                SEGMENT_CREATED

------------------------------ ------------------------------ ------------------------------ ---------------

INTERVAL_SALES                 P0                             USERS                          NO

INTERVAL_SALES                 P1                             USERS                          YES

INTERVAL_SALES                 P2                             USERS                          YES

INTERVAL_SALES                 P3                             USERS                          NO

INTERVAL_SALES                 SYS_P41                        USERS                          YES

 

SQL> insert into interval_sales (time_id) values (TO_DATE('8-8-2011', 'DD-MM-YYYY'));

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

SQL> select t.table_name, t.partition_name, t.tablespace_name, t.segment_created from user_tab_partitions t where t.table_name = 'INTERVAL_SALES';

 

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                SEGMENT_CREATED

------------------------------ ------------------------------ ------------------------------ ---------------

INTERVAL_SALES                 P0                             USERS                          NO

INTERVAL_SALES                 P1                             USERS                          YES

INTERVAL_SALES                 P2                             USERS                          YES

INTERVAL_SALES                 P3                             USERS                          NO

INTERVAL_SALES                 SYS_P41                        USERS                          YES

INTERVAL_SALES                 SYS_P42                        USERS                          YES

 

6 rows selected

Hash-Partitioned Tables

通过PARTITION BY HASH创建hash分区表。hash分区表只需要指定表空间和分区数量，oracle会将数据均匀地分布到各个分区。

SQL> CREATE TABLE scubagear

  2       (id NUMBER,

  3        name VARCHAR2 (60))

  4     STORAGE (INITIAL 100K)

  5     PARTITION BY HASH (id)

  6     PARTITIONS 4

  7     STORE IN (ts1, ts2, ts3, ts4);

 

Table created

 

SQL>

如果指定的 STORAGE (INITIAL 100K)，则每个分区的初始大小都是100k，而不是整个表100k:

SQL> insert into scubagear values(1, 'asd');

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

SQL> select t.segment_name, t.partition_name, t.initial_extent from user_segments t where t.segment_name = 'SCUBAGEAR';

 

SEGMENT_NAME                                                                     PARTITION_NAME                 INITIAL_EXTENT

-------------------------------------------------------------------------------- ------------------------------ --------------

SCUBAGEAR                                                                        SYS_P54                                106496

为分区表创建局部索引

给上面的分区表创建局部索引，索引的分区与表的分区一一对应，并且默认创建到索引分区的表空间上。

索引创建的时候同样不统一segment，而是根据需要创建。

SQL> CREATE INDEX loc_scubagear_ix ON scubagear(id) LOCAL;

 

Index created

 

SQL> select t.index_name, t.partition_name, t.tablespace_name, t.segment_created from user_ind_partitions t where t.index_name = 'LOC_SCUBAGEAR_IX';

 

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                SEGMENT_CREATED

------------------------------ ------------------------------ ------------------------------ ---------------

LOC_SCUBAGEAR_IX               SYS_P55                        TS1                            NO

LOC_SCUBAGEAR_IX               SYS_P56                        TS2                            NO

LOC_SCUBAGEAR_IX               SYS_P57                        TS3                            NO

LOC_SCUBAGEAR_IX               SYS_P58                        TS4                            YES

创建基于list的分区表

下面的分区表，分区默认创建到表空间ts1，但是region_east分区创建到ts2，并且初始区的大小是8M

SQL> CREATE TABLE sales_by_region (item# INTEGER, qty INTEGER,

  2               store_name VARCHAR(30), state_code VARCHAR(2),

  3               sale_date DATE)

  4       STORAGE(INITIAL 10K NEXT 20K) TABLESPACE ts1

  5       PARTITION BY LIST (state_code)

  6       (

  7       PARTITION region_east

  8          VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')

  9          STORAGE (INITIAL 8M)

 10          TABLESPACE ts2,

 11       PARTITION region_west

 12          VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO')

 13          NOLOGGING,

 14       PARTITION region_south

 15          VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'),

 16       PARTITION region_central

 17          VALUES ('OH','ND','SD','MO','IL','MI','IA'),

 18       PARTITION region_null

 19          VALUES (NULL),

 20       PARTITION region_unknown

 21          VALUES (DEFAULT)

 22       );

 

Table created

SQL> select t.table_name,

  2         t.partition_name,

  3         t.tablespace_name,

  4         t.segment_created,

  5         t.initial_extent,

  6         t.next_extent

  7    from user_tab_partitions t

  8   where t.table_name = 'SALES_BY_REGION';

 

 

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                SEGMENT_CREATED INITIAL_EXTENT NEXT_EXTENT

------------------------------ ------------------------------ ------------------------------ --------------- -------------- -----------

SALES_BY_REGION                REGION_CENTRAL                 TS1                            NO                       16384       24576

SALES_BY_REGION                REGION_EAST                    TS2                            NO                     8388608       24576

SALES_BY_REGION                REGION_NULL                    TS1                            NO                       16384       24576

SALES_BY_REGION                REGION_SOUTH                   TS1                            NO                       16384       24576

SALES_BY_REGION                REGION_UNKNOWN                 TS1                            NO                       16384       24576

SALES_BY_REGION                REGION_WEST                    TS1                            NO                       16384       24576

 

6 rows selected

创建Reference-Partitioned Table

Reference-Partitioned用在主子表关系中，主表是分区表，字表与主表通过外键关联。这样子表的分区就依照主表的来建立，子表数据放到哪个分区也由主表相应记录的分区决定。

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_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),
      PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),
      PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),
      PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY')) );

Table created.

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

Table created.

在主表插入第一、二季度的数据，然后在子表插入相应数据

SQL> insert into orders (order_id, order_date) values (1, to_date('2005-1-1', 'yyyy-mm-dd'));

 

1 row inserted

 

SQL> insert into orders (order_id, order_date) values (2, to_date('2005-6-1', 'yyyy-mm-dd'));

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

SQL> insert into order_items values (1, 1, 1, 1, 1);

 

1 row inserted

 

SQL> insert into order_items values (2, 2, 2, 2, 2);

 

1 row inserted

 

SQL> commit;

 

Commit complete

此时查看主、子表的分区情况，发现子表数据插入了和主表相对应的分区中。

SQL>

SQL> select t.table_name,

  2         t.partition_name,

  3         t.tablespace_name,

  4         t.segment_created,

  5         t.initial_extent,

  6         t.next_extent

  7    from user_tab_partitions t

  8   where t.table_name = 'ORDERS';

 

 

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                SEGMENT_CREATED INITIAL_EXTENT NEXT_EXTENT

------------------------------ ------------------------------ ------------------------------ --------------- -------------- -----------

ORDERS                         Q1_2005                        USERS                          YES                    8388608     1048576

ORDERS                         Q2_2005                        USERS                          YES                    8388608     1048576

ORDERS                         Q3_2005                        USERS                          NO                            

ORDERS                         Q4_2005                        USERS                          NO                            

SQL> select t.table_name,

  2         t.partition_name,

  3         t.tablespace_name,

  4         t.segment_created,

  5         t.initial_extent,

  6         t.next_extent

  7    from user_tab_partitions t

  8   where t.table_name = 'ORDER_ITEMS';

 

 

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                SEGMENT_CREATED INITIAL_EXTENT NEXT_EXTENT

------------------------------ ------------------------------ ------------------------------ --------------- -------------- -----------

ORDER_ITEMS                    Q1_2005                        USERS                          YES                    8388608     1048576

ORDER_ITEMS                    Q2_2005                        USERS                          YES                    8388608     1048576

ORDER_ITEMS                    Q3_2005                        USERS                          NO                            

ORDER_ITEMS                    Q4_2005                        USERS                          NO                            

复合分区表

在范围分区下建立HASH分区。

其中一些范围分区单独指定了存储位置

CREATE TABLE emp1 (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));

插入测试数据

SQL> insert into emp1 values(1500, 'asd', 1);

 

1 row inserted

 

SQL> commit;

 

Commit complete

查看分区情况

主分区显示为USERS表空间，并没有创建，因为主分区只是逻辑存在。

SQL> select t.table_name,

  2         t.partition_name,

  3         t.tablespace_name,

  4         t.segment_created,

  5         t.initial_extent,

  6         t.next_extent

  7    from user_tab_partitions t

  8   where t.table_name = 'EMP1';

 

 

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                SEGMENT_CREATED INITIAL_EXTENT NEXT_EXTENT

------------------------------ ------------------------------ ------------------------------ --------------- -------------- -----------

EMP1                           P1                             USERS                          NONE                          

EMP1                           P2                             USERS                          NONE                          

EMP1                           P3                             USERS                          NONE                          

子分区中，建表语句SUBPARTITIONS 8 STORE IN (ts1, ts3, ts5, ts7)处指定了8个子分区，因此子分区位于TS11335577。

P2的子分区位于临时指定的TS2，4，6，8。

P3的子分区位名字和数量都完全按具体指定的创建，分别位于TS2和TS5。

插入的数据位于P2的 SYS_SUBP74分区

SQL> select t.table_name,

  2         t.partition_name,

  3         t.subpartition_name,

  4         t.tablespace_name,

  5         t.segment_created

  6    from user_tab_subpartitions t

  7   where t.table_name = 'EMP1'

  8   order by t.partition_name, t.tablespace_name;

 

 

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME                SEGMENT_CREATED

------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------

EMP1                           P1                             SYS_SUBP59                     TS1                            NO

EMP1                           P1                             SYS_SUBP63                     TS1                            NO

EMP1                           P1                             SYS_SUBP60                     TS3                            NO

EMP1                           P1                             SYS_SUBP64                     TS3                            NO

EMP1                           P1                             SYS_SUBP65                     TS5                            NO

EMP1                           P1                             SYS_SUBP61                     TS5                            NO

EMP1                           P1                             SYS_SUBP62                     TS7                            NO

EMP1                           P1                             SYS_SUBP66                     TS7                            NO

EMP1                           P2                             SYS_SUBP71                     TS2                            NO

EMP1                           P2                             SYS_SUBP67                     TS2                            NO

EMP1                           P2                             SYS_SUBP68                     TS4                            NO

EMP1                           P2                             SYS_SUBP72                     TS4                            NO

EMP1                           P2                             SYS_SUBP69                     TS6                            NO

EMP1                           P2                             SYS_SUBP73                     TS6                            NO

EMP1                           P2                             SYS_SUBP74                     TS8                            YES

EMP1                           P2                             SYS_SUBP70                     TS8                            NO

EMP1                           P3                             P3_S1                          TS4                            NO

EMP1                           P3                             P3_S2                          TS5                            NO

 

18 rows selected

给上面分区表创建局部索引

CREATE INDEX emp_ix ON emp1(deptno) LOCAL STORE IN (ts1, ts2, ts3);

和表象他，局部索引的主分区同样只是逻辑存在

SQL>  select t.index_name,

  2          t.partition_name,

  3          t.tablespace_name,

  4          t.segment_created

  5     from user_ind_partitions t

  6    where t.index_name = 'EMP_IX';

 

 

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                SEGMENT_CREATED

------------------------------ ------------------------------ ------------------------------ ---------------

EMP_IX                         P1                                                            N/A

EMP_IX                         P2                                                            N/A

EMP_IX                         P3                                                            N/A

而局部索引的子分区出来存储位置被DDL语句指定意外，子分区的个数完全与表分区对应

SQL> select t.index_name,

  2           t.partition_name,

  3           t.subpartition_name,

  4           t.tablespace_name,

  5           t.segment_created

  6      from user_ind_subpartitions t

  7     order by t.partition_name, t.tablespace_name;

 

 

INDEX_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME                SEGMENT_CREATED

------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------

EMP_IX                         P1                             SYS_SUBP59                     TS1                            NO

EMP_IX                         P1                             SYS_SUBP62                     TS1                            NO

EMP_IX                         P1                             SYS_SUBP65                     TS1                            NO

EMP_IX                         P1                             SYS_SUBP60                     TS2                            NO

EMP_IX                         P1                             SYS_SUBP63                     TS2                            NO

EMP_IX                         P1                             SYS_SUBP66                     TS2                            NO

EMP_IX                         P1                             SYS_SUBP61                     TS3                            NO

EMP_IX                         P1                             SYS_SUBP64                     TS3                            NO

EMP_IX                         P2                             SYS_SUBP67                     TS1                            NO

EMP_IX                         P2                             SYS_SUBP73                     TS1                            NO

EMP_IX                         P2                             SYS_SUBP70                     TS1                            NO

EMP_IX                         P2                             SYS_SUBP74                     TS2                            YES

EMP_IX                         P2                             SYS_SUBP71                     TS2                            NO

EMP_IX                         P2                             SYS_SUBP68                     TS2                            NO

EMP_IX                         P2                             SYS_SUBP72                     TS3                            NO

EMP_IX                         P2                             SYS_SUBP69                     TS3                            NO

EMP_IX                         P3                             P3_S1                          TS1                            NO

EMP_IX                         P3                             P3_S2                          TS2                            NO

 

18 rows selected

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

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