ORACLE Interval-Partitioning with NUMBER

ORACLE 11.2
We needed a listpartitioning for every load of an DWH, but of course we wanted to have the comfort of the autmatic partition-generation.
This can be achieved with Interval-Partitioning not with Date but with Number.
It was not easy to find a example so I want to show here a simple Table for which every new Load or Better Insert with a new C_LADE_LAUF_CID generates a new Partition.
We used PCTFREE 0 (of course on partition-level) to avoid unused space, because there were no updates on this table.
We set the initial extend to 1M  to avoid having  a lot of empty partitions allocating unnecessarily storage on creating the partition(s).

To change the behavior of the big inital extend(8M instead of 64k) on partitioning beginning with ORACLE 11.2.0.2 on system-level  read this.

drop table INTERVAL_NUMBER purge;
CREATE TABLE interval_number (
C_LADE_LAUF_CID   NUMBER(5)
,begdate           DATE
,enddate           DATE
,description       VARCHAR2(100)
)
PARTITION BY RANGE (C_LADE_LAUF_CID) INTERVAL (1)
( PARTITION P1 VALUES LESS THAN (2) ) PCTFREE 0 STORAGE (INITIAL 1M)
;


insert into interval_number values (1,sysdate,sysdate,'partition 1');
insert into interval_number values (2,sysdate,sysdate,'partition 2');
insert into interval_number values (3,sysdate,sysdate,'partition 3');
commit;

After generation of statistics, we can check what happened:

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'CORE_TEST',tabname => 'interval_number',estimate_percent => dbms_stats.auto_sample_size);


Select for the partitions
select segment_name, partition_name, blocks, bytes/1024 as KB
from dba_segments
where segment_name = 'INTERVAL_NUMBER';

SEGMENT_NAME    PARTITION_NAME BLOCKS KB

INTERVAL_NUMBER P1             128 1024
INTERVAL_NUMBER SYS_P12666     128 1024
INTERVAL_NUMBER SYS_P12667     128 1024


Select for the complete table
(pctfree is on partitionlevel, so here null)
select table_name, pct_free, num_rows, partitioned
from user_tables
where table_name = 'INTERVAL_NUMBER';

TABLE_NAME      PCT_FREE NUM_ROWS PARTITIONED

INTERVAL_NUMBER null     3        YES


Select for the pctfree-value on partition-level
select def_pct_free
from USER_PART_TABLES
where table_name = 'INTERVAL_NUMBER';
DEF_PCT_FREE
0

Select for the initial extend for the partitions
select partition_name, initial_extent/1024 as initial_extent_kb
from user_tab_partitions
where table_name = 'INTERVAL_NUMBER';
PARTITION_NAME, INITIAL_EXTEND_KB
P1              1024
SYS_P13229      1024
SYS_P13230      1024

Bulk Load vs Direct Path Load

Overview about the main differences between direct path loads and bulk loads.
Direct path load (insert /*+append*/ as select…)

  • very fast (especially if no indexes on the target table)
  • always loads ABOVE the high water mark, so any existing free space not reclaimed
  • locks the object
  • you must commit/rollback at completion

Bulk Load (bulk collect into/forall insert)

  • fast (better than row at a time), but not as fast as direct load
  • can utilise existing free space
  • does not lock the object
  • additional work can be done in same transaction

Found by asktom.