パーティションの 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)
);
