Oracle: Partitioned tables

Partition techniques

Each record in a partitioned table is assigned to an unambiguous partition.

There are three distribution techniques to determine the partition into which a record falls:

A variant of the range partioning is interval partitioning: Oracle will automatically create the new partitions when an inserted (or updated?) value requires it.

Composite partitioned

With composite partioning, two partition techniques are combined so that there are two levels: partition and sub-partition.

The top level allows all four partitioning types. The second level does not allow interval partitioning. Thus, the total combinations of composite partitions is 12 (=4*3), for example:

Data dictionary

Available characteristics and other data pertaining to partitions tables can be queried in the data dictionary from dba_tab_partitions and dba_tab_subpartitions.

The views pertaining to partioned indexes are all_ind_partitions and all_ind_subpartitions.

A partitioned table is marked with YES in the column partitioned of dba_tables:

select
   partitioned,   -- 'YES'
   num_rows,
   blocks
from
   dba_tables
where
   owner      = 'RENE' and
   table_name = 'HIST_ABC';

The individual partitions that belong to a partitioned table are found in dba_tab_partitions:

select
   composite,
   partition_position,
   partition_name,
   subpartition_count,
   high_value,
   num_rows,
   blocks
   high_value_length
from
   dba_tab_partitions
where
   table_owner  = 'RENE'                and
   table_name   = 'HIST_ABC';

If the table is also sub-partitioned, the sub-partitions can be queried from dba_tab_subpartitions:

select
   subpar.partition_name,
   subpar.subpartition_name,
   tabpar.high_value             tabpar_high_value,
   subpar.high_value             subpar_high_value,
   subpar.num_rows               subpar_num_rows,
   subpar.blocks                 subpar_blocks,
   tabpar.high_value_length      tabpar_high_value_length,
   subpar.high_value_length      subpar_high_value_length,
   subpar.partition_position,
   subpar.subpartition_position
from
   dba_tab_partitions    tabpar     left join
   dba_tab_subpartitions subpar on
       tabpar.table_owner    = subpar.table_owner     and
       tabpar.table_name     = subpar.table_name      and
       tabpar.partition_name = subpar.partition_name
where
   subpar.table_owner = 'RENE' and
   subpar.table_name  = 'HIST_ABC'
order by
   subpar.partition_position,
   subpar.subpartition_position;

Partitioning columns

The columns on whose values a table is partitioned:

select
   column_name
from
   all_part_key_columns
where
   owner  = 'RENE'                 and
   name   = 'HIST_ABC'
order by
   column_position;

The columns on whose values a sub-partitioned table is partitioned:

select
   column_name
from
   all_subpart_key_columns
where
   owner = 'RENE' and
   name  = 'HIST_ABC';

Combining the two queries:

select
   tab.owner,
   tab.table_name,
   tab.num_rows,
   tap.partitioning_type,
   tap.subpartitioning_type,
   pkc.column_name              col_part_nam,
   pkc.column_position          col_part_pos,
   tap.interval,
   psc.column_name              col_subpart_nam,
   psc.column_position          col_subpart_pos,
   tap.interval_subpartition,
   tap.autolist,
   tap.autolist_subpartition,
   tap.partitioning_key_count,
   tap.subpartitioning_key_count,
   tap.partition_count,
   tap.def_subpartition_count
from
   dba_tables                tab                                                 left join
   dba_part_tables           tap on tab.owner           = tap.owner       and
                                    tab.table_name      = tap.table_name         left join
   dba_part_key_columns      pkc on tab.owner           = pkc.owner       and
                                    tab.table_name      = pkc.name               left join
   dba_subpart_key_columns   psc on tab.owner           = psc.owner       and
                                    tab.table_name      = psc.name          
where
-- tab.owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'CTXSYS', 'APPQOSSYS', 'WMSYS', 'XDB')
   tab.owner = user
order by
   tap.def_subpartition_count desc nulls last
;

Partition type

A table's partition type can be queried from dba_part_tables.

Dropping a partition

create table tq84_list_partition (
  id     number,
  txt    varchar2(10),
  --
  constraint tq84_list_partition_pk primary key (id)
)
partition by list (txt) (
  partition tq84_list_partition_foo  values ('foo'  ),
  partition tq84_list_partition_bar  values ('bar'  ),
  partition tq84_list_partition_baz  values ('baz'  ),
  partition tq84_list_partition_else values (default)
);


column    object_name format a24
column subobject_name format a24
column    object_type format a15

select
  object_name,
  subobject_name,
  object_type
from
  user_objects
where
  object_name like 'TQ84_LIST_PARTITION%';
--
-- OBJECT_NAME              SUBOBJECT_NAME           OBJECT_TYPE
-- ------------------------ ------------------------ ---------------
-- TQ84_LIST_PARTITION_PK                            INDEX
-- TQ84_LIST_PARTITION      TQ84_LIST_PARTITION_ELSE TABLE PARTITION
-- TQ84_LIST_PARTITION      TQ84_LIST_PARTITION_BAZ  TABLE PARTITION
-- TQ84_LIST_PARTITION      TQ84_LIST_PARTITION_BAR  TABLE PARTITION
-- TQ84_LIST_PARTITION                               TABLE


insert into tq84_list_partition values ( 1, 'foo');
insert into tq84_list_partition values ( 2, 'bar');
insert into tq84_list_partition values ( 3, 'baz');
insert into tq84_list_partition values ( 4, '???');

alter table tq84_list_partition drop partition tq84_list_partition_foo;

select * from tq84_list_partition;
-- 
--         ID TXT
-- ---------- ----------
--          2 bar
--          3 baz
--          4 ???

select
  object_name,
  subobject_name,
  object_type
from
  user_objects
where
  object_name like 'TQ84_LIST_PARTITION%';
-- 
-- OBJECT_NAME              SUBOBJECT_NAME           OBJECT_TYPE
-- ------------------------ ------------------------ ---------------
-- TQ84_LIST_PARTITION_PK                            INDEX
-- TQ84_LIST_PARTITION      TQ84_LIST_PARTITION_ELSE TABLE PARTITION
-- TQ84_LIST_PARTITION      TQ84_LIST_PARTITION_BAZ  TABLE PARTITION
-- TQ84_LIST_PARTITION      TQ84_LIST_PARTITION_BAR  TABLE PARTITION
-- TQ84_LIST_PARTITION                               TABLE

drop table tq84_list_partition purge;

Github repository Oracle-Patterns, path: /DatabaseObjects/Tables/Partitions/drop_partition.sql

Truncating a partition

create table tq84_tab (
  period  varchar2(5) not null check (period in ('DAY', 'WEEK', 'MONTH')),
  col_1   number,
  col_2   varchar2(10)
)
partition by list (period) (
  partition tq84_tab_day   values ('DAY'  ),
  partition tq84_tab_week  values ('WEEK' ),
  partition tq84_tab_month values ('MONTH')
);

insert into tq84_tab values ('DAY'  , 4, 'four'  );
insert into tq84_tab values ('WEEK' , 9, 'nine'  );
insert into tq84_tab values ('MONTH',11, 'eleven');

alter table tq84_tab truncate partition tq84_tab_week;

select * from tq84_tab;
-- PERIO      COL_1 COL_2
-- ----- ---------- ----------
-- DAY            4 four
-- MONTH         11 eleven

drop table tq84_tab purge;

Github repository Oracle-Patterns, path: /DatabaseObjects/Tables/Partitions/truncate-partition.sql

Local partitioned index

--
--     Local partitioned indexes can be further divided into 
--        o  local prefixed indexes and
--        o  local nonprefixed indexes
--    (See -> local_prefixed_vs_non_prefixed_index.sql).
--

create table tq84_range_partition (
  id   number,
  txt  varchar2(10),
  dt   date
)
partition by range (dt) (
  partition tq84_range_partition_2010  values less than (date '2011-01-01'),
  partition tq84_range_partition_2011  values less than (date '2012-01-01'),
  partition tq84_range_partition_2012  values less than (date '2013-01-01'),
  partition tq84_range_partition_9999  values less than (      maxvalue   )
);

create index tq84_range_partition_ix_local on tq84_range_partition(id) local;
create index tq84_range_partition_ix_      on tq84_range_partition(dt)      ;

--
select 
  ix.index_name, 
     partition_name
from 
  user_tab_partitions tb join
  user_ind_partitions ix using (partition_name)
where
  tb.table_name = 'TQ84_RANGE_PARTITION';
--

select 
  index_type,
  partitioned/*,
  prefix_length*/
from 
  user_indexes
where
  table_name = 'TQ84_RANGE_PARTITION';

drop table tq84_range_partition purge;

Github repository Oracle-Patterns, path: /DatabaseObjects/Tables/Partitions/local_partitioned_index.sql

Misc

create table tq84_range_partition (
  id   number,
  txt  varchar2(10),
  dt   date
)
partition by range (dt) (
  partition tq84_range_partition_2010  values less than (date '2011-01-01'),
  partition tq84_range_partition_2011  values less than (date '2012-01-01'),
  partition tq84_range_partition_2012  values less than (date '2013-01-01'),
  partition tq84_range_partition_9999  values less than (      maxvalue   )
);


create index tq84_local_prefixed on tq84_range_partition(dt) local;
create index tq84_non_prefixed   on tq84_range_partition(id) local;

select
  index_name,
  partitioned/*,
  prefix_length*/
from
  user_indexes
where
  table_name = 'TQ84_RANGE_PARTITION';

drop table tq84_range_partition purge;

Github repository Oracle-Patterns, path: /DatabaseObjects/Tables/Partitions/local_prefixed_vs_non_prefixed_index.sql