Oracle: composite partitioning

Range - List

create table tq84_part_range_list (
  col_dt   date,
  col_vc   varchar2(10),
  dat      varchar2(10)
)
   partition by range (col_dt) 
subpartition by list  (col_vc) (
------------------------------
  partition p1      values less than (date '2013-01-01') (
  ------------
     subpartition p1_foo  values ('foo'  ),
     subpartition p1_bar  values ('bar'  ),

     subpartition p1_baz  values ('baz'  ),
     subpartition p1_def  values (default)
  ),
  partition p13     values less than (date '2014-01-01') (
  -------------
     subpartition p13_foo values ('foo'  ),
     subpartition p13_bar values ('bar'  ),
     subpartition p13_baz values ('baz'  ),
     subpartition p13_def values (default)
  ),
  partition p14     values less than (date '2015-01-01') (
  -------------
     subpartition p14_foo values ('foo'  ),
     subpartition p14_bar values ('bar'  ),
     subpartition p14_baz values ('baz'  ),
     subpartition p14_def values (default)
  ),
  partition p99     values less than (maxvalue) (
  -------------
     subpartition p99_foo values ('foo'  ),
     subpartition p99_bar values ('bar'  ),
     subpartition p99_baz values ('baz'  ),
     subpartition p99_def values (default)
  )
);


select
  object_name,
  subobject_name,
  object_type
from
  user_objects
where
  object_name = 'TQ84_PART_RANGE_LIST'
--created > sysdate - 1 /24/60
;


select
  partitioning_type,
  subpartitioning_type,
  partition_count,
  def_subpartition_count
from
  user_part_tables
where
  table_name = 'TQ84_PART_RANGE_LIST';


set long 90
column high_value format a90
select
  composite,
  partition_name,
  subpartition_count,
  high_value
from
  user_tab_partitions
where
  table_name = 'TQ84_PART_RANGE_LIST';


select
  partition_name,
  subpartition_name,
  subpartition_position,
  high_value
from
  user_tab_subpartitions
where
  table_name = 'TQ84_PART_RANGE_LIST';

drop table tq84_part_range_list;

Range - Range

create table tq84_part_range_range (
  id     number primary key,
  col_1  varchar2(10),
  dt     date                not null,
  nm     number              not null
)
   partition by range(dt) interval (numtoyminterval(1, 'year'))
subpartition by range(nm)
subpartition template (
   subpartition lt_0       values less than (       0),
   subpartition lt_1000    values less than (    1000),
   subpartition lt_1000000 values less than ( 1000000),
   subpartition lt_max     values less than (maxvalue)
)
(
  partition tq84_p1 values less than (date '2000-01-01')
)
;

insert into tq84_part_range_range values (1, 'a', date '2017-05-03', 9999);

exec dbms_stats.gather_table_stats(user, 'tq84_part_range_range');


select
  p.partition_name,
  s.subpartition_name,
  p.high_value,
  s.high_value,
  s.subpartition_position,
  s.num_rows
from
  user_tab_subpartitions s join
  user_tab_partitions    p on s.table_name     = p.table_name and
                              s.partition_name = p.partition_name
where
  s.table_name = 'TQ84_PART_RANGE_RANGE';

drop table tq84_part_range_range purge;

List - Hash

create table tq84_part_list_hash (
  col_dt   date,
  col_vc   varchar2(10),
  dat      varchar2(10)
)
   partition by list (col_vc)
subpartition by hash (col_dt) subpartitions 4 (
  partition p_foo  values('foo'  ),
  partition p_bar  values('bar'  ),
  partition p_baz  values('baz'  ),
  partition p_null values( null  ),
  partition p_def  values(default)
);

insert into tq84_part_list_hash values (sysdate  , 'foo', 'one'  );
insert into tq84_part_list_hash values (sysdate+1, 'bar', 'two'  );
insert into tq84_part_list_hash values (sysdate  , 'baz', 'three');

select * 
  from tq84_part_list_hash
  partition (p_foo)         -- <== Partition extension clause
;

drop table tq84_part_list_hash purge;

Interval - List

create table tq84_part_interval_list (
  id   number,
  txt  varchar2(10),
  cat  varchar2( 3),
  dt   date
)
partition by range (dt) interval (numtoyminterval(1, 'month'))
    subpartition by list (cat)
        subpartition template (
          subpartition foo values ('foo'),
          subpartition bar values ('bar'),
          subpartition baz values ('baz')
        )
(
  partition values less than ( date '2017-01-01') 
);

insert into tq84_part_interval_list values (1, 'abc', 'foo', date '2017-03-01');
insert into tq84_part_interval_list values (2, 'def', 'bar', date '2017-03-02');
insert into tq84_part_interval_list values (3, 'ghi', 'baz', date '2017-03-03');
insert into tq84_part_interval_list values (4, 'jkl', 'foo', date '2017-12-01');
insert into tq84_part_interval_list values (5, 'mno', 'bar', date '2017-12-02');
insert into tq84_part_interval_list values (6, 'pqr', 'baz', date '2017-12-03');

select * from user_tab_subpartitions where table_name = 'TQ84_PART_INTERVAL_LIST';

drop table tq84_part_interval_list purge;

See also