db2数据库表分区的讨论.txt

db2 指定某个字符串中的第几个字符的更新

  CU-DB2讨论区精华帖   
 

ChinaUnix.net > 论坛首页 > 精华首页 > DB2精华区 > 正文  
 
[讨论]DB2数据库表分区的讨论 
http://www.chinaunix.net 作者:windrain  发表于：2003-09-16 11:25:00 

3000sunqin 和hujingyu  两位大虾，请教一个概念。分区键的功能就是用来指示相应的数据在哪个数据库分区上的，也就是说是数据库分布在不同的节点上，通过节点组分布表空间，在上面进行数据分布。 
1、在节点组这一层，没有问题 
2、分布表空间指定了每个节点的容器，数据以hash的方式分布于容器中 
3、建表的时候指定了表空间，则表数据以hash的方式分布于表空间容器中 
4、建表的时候指定PARTITIONING KEY (MIX_INT) USING HASHING，数据不能够分布在不同的表空间吧。 
5、对于表空间，分布在不同的节点上，你能指定表数据存储在哪个节点的容器上，我认为不可以。 
6、之所以作分区，是为了提高性能，但是如果按照这种方法，应该还是要操作整个表，而不是一个表的分区 
我们可以看一个oracle数据库的分区例子， 
CREATE TABLE FO_GINCALL 
( 
  DAY_ID          NUMBER(, 
  HOUR_ID         NUMBER(2), 
  CALLKND_ID      NUMBER(4)                     NOT NULL, 
  CITYCODE_ID     NUMBER(4), 
  FEEKND_ID       NUMBER(16), 
  CALLTIME        VARCHAR2(14 BYTE)             NOT NULL, 
  SVC_ID          VARCHAR2(20 BYTE)             NOT NULL, 
  OPPOSENUMBER    VARCHAR2(20 BYTE)             NOT NULL, 
  BALANCE         NUMBER(12,2), 
  CALLDURATION    NUMBER(12), 
  CALLFEE         NUMBER(12,2), 
  FEATUREFLAG     NUMBER(1), 
  BEARERFLAG      NUMBER(1), 
  FORWARDINGFLAG  NUMBER(1), 
  ISFIRSTCALL     NUMBER(1), 
  TIMESTAMP       DATE, 
  SUBPARTNO       NUMBER(2)                     NOT NULL 
) 
TABLESPACE TBS_ODS 
PCTUSED    0 
PCTFREE    10 
INITRANS   1 
MAXTRANS   60 
STORAGE    ( 
            INITIAL          300M 
            MINEXTENTS       1 
            MAXEXTENTS       255 
            PCTINCREASE      0 
            
NOLOGGING 
PARTITION BY RANGE (SUBPARTNO)  
(   
  PARTITION P_GINCALL_01 VALUES LESS THAN (2) 
    NOLOGGING 
    NOCOMPRESS 
    TABLESPACE TBS_IN01 
    PCTFREE    10 
    INITRANS   1 
    MAXTRANS   255 
    STORAGE    ( 
                INITIAL          50M 
                MINEXTENTS       1 
                MAXEXTENTS       2147483645 
                BUFFER_POOL      DEFAULT 
               ,   
  PARTITION P_GINCALL_02 VALUES LESS THAN (3) 
    NOLOGGING 
    NOCOMPRESS 
    TABLESPACE TBS_IN02 
    PCTFREE    10 
    INITRANS   1 
    MAXTRANS   255 
    STORAGE    ( 
                INITIAL          50M 
                MINEXTENTS       1 
                MAXEXTENTS       2147483645 
                BUFFER_POOL      DEFAULT 
               ,   
  PARTITION P_GINCALL_03 VALUES LESS THAN (4) 
    NOLOGGING 
    NOCOMPRESS 
    TABLESPACE TBS_IN03 
    PCTFREE    10 
    INITRANS   1 
    MAXTRANS   255 
    STORAGE    ( 
                INITIAL          50M 
                MINEXTENTS       1 
                MAXEXTENTS       2147483645 
                BUFFER_POOL      DEFAULT 
               ,   
  PARTITION P_GINCALL_04 VALUES LESS THAN (5) 
    NOLOGGING 
    NOCOMPRESS 
    TABLESPACE TBS_IN04 
    PCTFREE    10 
    INITRANS   1 
    MAXTRANS   255 
    STORAGE    ( 
                INITIAL          50M 
                MINEXTENTS       1 
                MAXEXTENTS       2147483645 
                BUFFER_POOL      DEFAULT 
               ,   
  PARTITION P_GINCALL_05 VALUES LESS THAN (6) 
    NOLOGGING 
    NOCOMPRESS 
    TABLESPACE TBS_IN05 
    PCTFREE    10 
    INITRANS   1 
    MAXTRANS   255 
    STORAGE    ( 
                INITIAL          50M 
                MINEXTENTS       1 
                MAXEXTENTS       2147483645 
                BUFFER_POOL      DEFAULT 
               ,   
  PARTITION P_GINCALL_06 VALUES LESS THAN (7) 
    NOLOGGING 
    NOCOMPRESS 
    TABLESPACE TBS_IN06 
    PCTFREE    10 
    INITRANS   1 
    MAXTRANS   255 
    STORAGE    ( 
                INITIAL          50M 
                MINEXTENTS       1 
                MAXEXTENTS       2147483645 
                BUFFER_POOL      DEFAULT 
               ,   
  PARTITION P_GINCALL_07 VALUES LESS THAN ( 
    NOLOGGING 
    NOCOMPRESS 
    TABLESPACE TBS_IN07 
    PCTFREE    10 
    INITRANS   1 
    MAXTRANS   255 
    STORAGE    ( 
                INITIAL          50M 
                MINEXTENTS       1 
                MAXEXTENTS       2147483645 
                BUFFER_POOL      DEFAULT 
               ,   
  PARTITION P_GINCALL_08 VALUES LESS THAN (9) 
    NOLOGGING 
    NOCOMPRESS 
    TABLESPACE TBS_IN08 
    PCTFREE    10 
    INITRANS   1 
    MAXTRANS   255 
    STORAGE    ( 
                INITIAL          50M 
                MINEXTENTS       1 
                MAXEXTENTS       2147483645 
                BUFFER_POOL      DEFAULT 
               ,   
  PARTITION P_GINCALL_09 VALUES LESS THAN (10) 
    NOLOGGING 
    NOCOMPRESS 
    TABLESPACE TBS_IN09 
    PCTFREE    10 
    INITRANS   1 
    MAXTRANS   255 
    STORAGE    ( 
                INITIAL          50M 
                MINEXTENTS       1 
                MAXEXTENTS       2147483645 
                BUFFER_POOL      DEFAULT 
               ,   
  PARTITION P_GINCALL_10 VALUES LESS THAN (11) 
    NOLOGGING 
    NOCOMPRESS 
    TABLESPACE TBS_IN10 
    PCTFREE    10 
    INITRANS   1 
    MAXTRANS   255 
    STORAGE    ( 
                INITIAL          50M 
                MINEXTENTS       1 
                MAXEXTENTS       2147483645 
                BUFFER_POOL      DEFAULT 
               ,   
  PARTITION P_GINCALL_11 VALUES LESS THAN (12) 
    NOLOGGING 
    NOCOMPRESS 
    TABLESPACE TBS_IN11 
    PCTFREE    10 
    INITRANS   1 
    MAXTRANS   255 
    STORAGE    ( 
                INITIAL          50M 
                MINEXTENTS       1 
                MAXEXTENTS       2147483645 
                BUFFER_POOL      DEFAULT 
               ,   
  PARTITION P_GINCALL_12 VALUES LESS THAN (13) 
    NOLOGGING 
    NOCOMPRESS 
    TABLESPACE TBS_IN12 
    PCTFREE    10 
    INITRANS   1 
    MAXTRANS   255 
    STORAGE    ( 
                INITIAL          50M 
                MINEXTENTS       1 
                MAXEXTENTS       2147483645 
                BUFFER_POOL      DEFAULT 
               ,   
  PARTITION P_GINCALL_13 VALUES LESS THAN (14) 
    NOLOGGING 
    NOCOMPRESS 
    TABLESPACE TBS_IN13 
    PCTFREE    10 
    INITRANS   1 
    MAXTRANS   60 
    STORAGE    ( 
                INITIAL          50M 
                MINEXTENTS       1 
                MAXEXTENTS       2147483645 
                BUFFER_POOL      DEFAULT 
               ,   
  PARTITION P_GINCALL_14 VALUES LESS THAN (15) 
    NOLOGGING 
    NOCOMPRESS 
    TABLESPACE TBS_IN14 
    PCTFREE    10 
    INITRANS   1 
    MAXTRANS   255 
    STORAGE    ( 
                INITIAL          50M 
                MINEXTENTS       1 
                MAXEXTENTS       2147483645 
                BUFFER_POOL      DEFAULT 
               ,   
  PARTITION P_GINCALL_15 VALUES LESS THAN (16) 
    NOLOGGING 
    NOCOMPRESS 
    TABLESPACE TBS_IN15 
    PCTFREE    10 
    INITRANS   1 
    MAXTRANS   255 
    STORAGE    ( 
                INITIAL          50M 
                MINEXTENTS       1 
                MAXEXTENTS       2147483645 
                BUFFER_POOL      DEFAULT 
               ,   
  PARTITION P_GINCALL_16 VALUES LESS THAN (17) 
    NOLOGGING 
    NOCOMPRESS 
    TABLESPACE TBS_IN16 
    PCTFREE    10 
    INITRANS   1 
    MAXTRANS   255 
    STORAGE    (