Schlagwort-Archive: Partitioning

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

INTERVAL-LIST Composite Partitioning

ORACLE 11g: INTERVAL-LIST Composite Partitioning

For the automatic creation of new Partitions in the Intervalpartitioning there are two options:
NUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH literal. (Use ‚YEAR‘ or ‚MONTH‘)
NUMTODSINTERVAL converts n to an INTERVAL DAY TO SECOND literal. (Use ‚DAY‘)

EXAMPLE

CREATE TABLE AUDIT_partitioned
(
TICKETID NUMBER(11, 0)
, CHANGETO VARCHAR2(200 BYTE)
, CHANGEDT DATE
, CHANGEUSER VARCHAR2(200 BYTE)
, ACTIONNO NUMBER(6, 0)
, ACTIONTEXT VARCHAR2(4000 BYTE)
, BANKNO VARCHAR2(10 BYTE)
)
PARTITION BY RANGE (CHANGEDT) INTERVAL(NUMTODSINTERVAL (1, 'DAY')) -- daily partitions
--PARTITION BY RANGE (CHANGEDT) INTERVAL(NUMTOYMINTERVAL (1, 'MONTH')) -- monthly partitions
SUBPARTITION BY LIST (BANKNO)
SUBPARTITION TEMPLATE
(
SUBPARTITION bank001 VALUES ('001'),
SUBPARTITION bank011 VALUES ('011'),
SUBPARTITION bank014 VALUES ('014'),
SUBPARTITION bankdef VALUES (DEFAULT)
)
( PARTITION p_genaudit_20140101 VALUES LESS THAN (TO_DATE('20140102', 'YYYYMMDD'))
);