{パーティション}パーティションテーブルを作成

 

https://docs.oracle.com/cd/F19136_01/vldbg/partition-create-tables-indexes.html
https://docs.oracle.com/cd/F19136_01/vldbg/create-composite-partition-table.html

https://docs.oracle.com/cd/F19136_01/vldbg/partition-concepts.html#GUID-3DA6E035-FC4A-408C-AE9A-76108A6883D3

 

conn test/test
select * from user_part_tables;
select * from user_tab_subpartitions;

      単一レベル・パーティション化
           レンジ
create table range
( col1 number,
  col2 date
)
partition by range(col1)
(partition p1 values less than (10) tablespace users,
 partition p2 values less than (maxvalue) tablespace users
);

           ハッシュ
create table hash
( col1 number,
  col2 date
)
partition by hash(col1)
(partition p1 tablespace users,
 partition p2 tablespace users
);

           リスト
create table list
( col1 number,
  col2 date
)
partition by list(col1)
(partition p1 values(1,2,3),
 partition p2 values(4,5,6),
 partition p3 values(default)
);

select * from user_part_tables;
select * from user_tab_partitions;

      パーティション化の拡張
           時間隔
create table interval
( col1 number,
  col2 date
)
partition by range(col2)
interval(numtoyminterval(1,'MONTH'))
(partition p1 values less than(to_date('20140101','yyyymmdd')),
 partition p2 values less than(to_date('20140201','yyyymmdd'))
);

           参照
create table orders
(order_id number,
 order_date date,
constraint orders_pk primary key(order_id)
)
partition by range(order_date)
(partition p1 values less than(to_date('20140101','yyyymmdd')),
 partition p2 values less than(maxvalue)
);

create table order_items
(order_id number not null,
 item_id number not null,
constraint order_items_fk foreign key(order_id) references orders(order_id)
)
partition by reference(order_items_fk)
;

           仮想列
create table vcol
( col1 number,
  col2 number,
  col3 as (col1 + col2)
)
partition by range(col3)
(partition p1 values less than (100));

 

           システムパーティション

create table system
( col1 number,
  col2 date
)
partition by system
(partition p1 tablespace users,
 partition p2 tablespace users
);

insert into system partition(p1) values(1,sysdate);
commit;

 

select * from user_part_tables;
select * from user_tab_partitions;

      コンポジットパーティション

           ハッシュ-ハッシュ
CREATE TABLE hash_hash
(col1 number,
 col2 number
)     
PARTITION BY HASH(col1)
SUBPARTITION BY HASH (col2) 
SUBPARTITIONS 32 PARTITIONS 16
;

 

           レンジ-ハッシュ

create table range_hash
(col1 number,
 col2 number
)
partition by range(col1)
subpartition by hash(col2)
(partition p1 values less than (10)
 (subpartition sp11,
  subpartition sp12
 ),
 partition p2 values less than (maxvalue)
 (subpartition sp21,
  subpartition sp22
 )
);

 

           レンジ-リスト
create table range_list
( col1 number,
  col2 varchar2(10)
)
partition by range(col1)
subpartition by list(col2)
(partition p1 values less than (100)
 (subpartition p11 values ('1','2'),
  subpartition p12 values ('3')
 ),
 partition p2 values less than (maxvalue)
 (subpartition p21 values (default)
 )
);

 

           レンジ-レンジ
create table range_range
(col1 number,
 col2 number
)
partition by range(col1)
subpartition by range(col2)
(partition p1 values less than (100)
 (subpartition p11 values less than (100)
 )
);

 

           リスト-ハッシュ
create table list_hash
(col1 number,
 col2 number
)
partition by list(col1)
subpartition by hash(col2)
(partition p1 values (10)
 (subpartition p11,
  subpartition p12
 ),
 partition p2 values(default)
 (subpartition p21,
  subpartition p22,
  subpartition p23
 )
);

           リスト-リスト
create table list_list
(col1 number,
 col2 number
)
partition by list(col1)
subpartition by list(col2)
(partition p1 values(1,2,3)
 (subpartition p11 values(1,2,3),
  subpartition p12 values(default)
 ),
 partition p2 values(default)
 (subpartition p21 values(1,2,3),
  subpartition p22 values(default)
 )
);

           リスト-レンジ
create table list_range
(col1 number,
 col2 number
)
partition by list(col1)
subpartition by range(col2)
(partition p1 values(1,2,3)
 (subpartition p11 values less than(10)
 ),
 partition p2 values(4,5,6)
 (subpartition p21 values less than(maxvalue)
 )
);

           時間隔-ハッシュ
create table interval_hash
(col1 number,
 col2 number
)
partition by range(col1)
interval(200)
subpartition by hash(col2)
(partition p1 values less than (100)
 (subpartition p11
 ),
 partition p2 values less than (300)
 (subpartition p21,
  subpartition p22
 )
);

insert into interval_hash values(101,1);
insert into interval_hash values(301,1);
commit;

select * from user_tab_subpartitions
where table_name ='INTERVAL_HASH'
;

           時間隔-リスト

create table interval_list
(col1 date,
 col2 varchar2(10)
)
partition by range(col1)
interval(numtodsinterval(1,'MINUTE'))
subpartition by list(col2)
(partition p1 values less than(to_date('20140101 12:11:12','yyyymmdd hh24:mi:ss'))
 (subpartition p11 values ('1'),
  subpartition p12 values (default)
 )
);

 

           時間隔-レンジ

create table interval_range
(col1 date,
 col2 number
)
partition by range(col1)
interval(numtoyminterval(22,'YEAR'))
subpartition by range(col2)
(partition p1 values less than (to_date('20010101','yyyymmdd'))
 (subpartition p11 values less than (maxvalue)
 )
);

select * from user_part_tables;
select * from user_tab_subpartitions;

col TABLE_NAME for a20
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20

select TABLE_NAME,PARTITIONING_TYPE,SUBPARTITIONING_TYPE from user_part_tables;
select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,HIGH_VALUE from user_tab_subpartitions;