読者です 読者をやめる 読者になる 読者になる

Gblog

おもにTips

今度はコンポジット

 パーティションの CREATE 文は書きましたが、今度はコンポジット・パーティション。主なものだけですが。

  • レンジ - ハッシュ
CREATE  TABLE tab1 (
  col1 CHAR(2),
  col2 CHAR(8),
  col3 VARCHAR2(100),
  CONSTRAINT tab1_pk PRIMARY KEY(col1) )
  PARTITION BY RANGE (col1) SUBPARTITION BY HASH (col2)
    subPARTITIONs 4
    ( PARTITION tab1_part1 VALUES LESS THAN (10)
        ( SUBPARTITION tab1_subpart1,
          SUBPARTITION tab1_subpart2
        ),
      PARTITION tab1_part2 VALUES LESS THAN (20)
        ( SUBPARTITION tab1_subpart3,
          SUBPARTITION tab1_subpart4
        ),
      PARTITION tab1_part3 VALUES LESS THAN (30)
        ( SUBPARTITION tab1_subpart5,
          SUBPARTITION tab1_subpart6
        ),
      PARTITION tab1_part4 VALUES LESS THAN (maxvalue)
        ( SUBPARTITION tab1_subpart7,
          SUBPARTITION tab1_subpart8
        )
    ); 

 サブパーティションのテンプレートを使うと楽。

CREATE  TABLE tab1 (
  col1 CHAR(2),
  col2 CHAR(8),
  col3 VARCHAR2(100),
  CONSTRAINT tab1_pk PRIMARY KEY(col1) )
  PARTITION BY RANGE (col1) SUBPARTITION BY HASH (col2)
     SUBPARTITION TEMPLATE
         (SUBPARTITION subpart1,
          SUBPARTITION subpart2,
          SUBPARTITION subpart3,
          SUBPARTITION subpart4
         )
    ( PARTITION tab1_part1 VALUES LESS THAN (10),
      PARTITION tab1_part2 VALUES LESS THAN (20),
      PARTITION tab1_part3 VALUES LESS THAN (30),
      PARTITION tab1_part4 VALUES LESS THAN (maxvalue)
    );

 名前を指定しないなら、もっと簡単

CREATE  TABLE tab1 (
  col1 CHAR(2),
  col2 CHAR(8),
  col3 VARCHAR2(100),
  CONSTRAINT tab1_pk PRIMARY KEY(col1) )
  PARTITION BY RANGE (col1) SUBPARTITION BY HASH (col2)
    SUBPARTITIONS 4
    ( PARTITION tab1_part1 VALUES LESS THAN (10),
      PARTITION tab1_part2 VALUES LESS THAN (20),
      PARTITION tab1_part3 VALUES LESS THAN (30),
      PARTITION tab1_part4 VALUES LESS THAN (maxvalue)
    );

 

  • リスト - ハッシュ
CREATE  TABLE tab1 (
  col1 CHAR(2),
  col2 CHAR(8),
  col3 VARCHAR2(100),
  CONSTRAINT tab1_pk PRIMARY KEY(col1))
  PARTITION BY LIST (col1) SUBPARTITION BY HASH (col2)
    SUBPARTITIONS 4
    ( PARTITION tab1_part1 VALUES ('00','01')
        ( SUBPARTITION tab1_subpart1,
          SUBPARTITION tab1_subpart2
        ),
      PARTITION tab1_part2 VALUES ('02','03')
        ( SUBPARTITION tab1_subpart3,
          SUBPARTITION tab1_subpart4
        ),
      PARTITION tab1_part3 VALUES ('04','05')
        ( SUBPARTITION tab1_subpart5,
          SUBPARTITION tab1_subpart6
        ),
      PARTITION tab1_part4 VALUES (DEFAULT)
        ( SUBPARTITION tab1_subpart7,
          SUBPARTITION tab1_subpart8
        )
    );

 他のパターンは割愛。

 

  • レンジ - リスト
CREATE  TABLE tab1 (
  col1 CHAR(2),
  col2 CHAR(2),
  col3 VARCHAR2(100),
  CONSTRAINT tab1_pk PRIMARY KEY(col1))
  PARTITION BY RANGE (col1) SUBPARTITION BY LIST (col2)
    ( PARTITION tab1_part1 VALUES LESS THAN (10)
        ( SUBPARTITION tab1_subpart1 VALUES ('00','01'),
          SUBPARTITION tab1_subpart2 VALUES ('02','03'),
          SUBPARTITION tab1_subpart3 VALUES ('04','05'),
          SUBPARTITION tab1_subpart4 VALUES (DEFAULT)
        ),
      PARTITION tab1_part2 VALUES LESS THAN (20)
        ( SUBPARTITION tab1_subpart5 VALUES ('00','01'),
          SUBPARTITION tab1_subpart6 VALUES ('02','03'),
          SUBPARTITION tab1_subpart7 VALUES ('04','05'),
          SUBPARTITION tab1_subpart8 VALUES (DEFAULT)
        ),
      PARTITION tab1_part3 VALUES LESS THAN (30)
        ( SUBPARTITION tab1_subpart9 VALUES ('00','01'),
          SUBPARTITION tab1_subpartA VALUES ('02','03'),
          SUBPARTITION tab1_subpartB VALUES ('04','05'),
          SUBPARTITION tab1_subpartC VALUES (DEFAULT)
        ),
      PARTITION tab1_part4 VALUES LESS THAN (maxvalue)
        ( SUBPARTITION tab1_subpartD VALUES ('00','01'),
          SUBPARTITION tab1_subpartE VALUES ('02','03'),
          SUBPARTITION tab1_subpartF VALUES ('04','05'),
          SUBPARTITION tab1_subpartG VALUES (DEFAULT)
        )
    );

 これは、テンプレートの方が楽だな。。。

CREATE  TABLE tab1 (
  col1 CHAR(2),
  col2 CHAR(2),
  col3 VARCHAR2(100),
  CONSTRAINT tab1_pk PRIMARY KEY(col1))
  PARTITION BY RANGE (col1) SUBPARTITION BY LIST (col2)
    SUBPARTITION TEMPLATE
      ( SUBPARTITION subpart1 VALUES ('00','01'),
        SUBPARTITION subpart2 VALUES ('02','03'),
        SUBPARTITION subpart3 VALUES ('04','05'),
        SUBPARTITION subpart4 VALUES (DEFAULT)
      )
    ( PARTITION tab1_part1 VALUES LESS THAN (10),
      PARTITION tab1_part2 VALUES LESS THAN (20),
      PARTITION tab1_part3 VALUES LESS THAN (30),
      PARTITION tab1_part4 VALUES LESS THAN (maxvalue)
    );