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