HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Partitioning strategy for fact tables - 10g vs. 11g
ORACLE STUFFS
09-Sep-2008
New features of Oracle 11g simplified management of range partitions allowing INTERVAL partitioning. It allows move part of functionality solved currently by ETL pre-wrappers to default processing of RDBMS defined in Data dictionary metadata.

Simple example of range - interval partitioning with monthly period.

create table tt3 (
  a integer
 ,b date
)partition by range (b) interval (NUMTOYMINTERVAL(1,'MONTH'))
(partition P1 values less than(to_date('10000101','yyyymmdd')))
/

SQL> select partition_name,high_value from user_tab_partitions where table_name='TT3'; PARTITION_NAME HIGH_VALUE ------------------------------ -------------------------------------------------------- P1 TO_DATE(' 1000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'

Insert into tt3 (a,b) values(1,to_date('20080430','yyyymmdd'))
/

Formerly it would be cause of an error.
In 11g with an INTERVAL specified new partition is automatically created.

SQL> select partition_name,high_value from user_tab_partitions where table_name='TT3'; PARTITION_NAME HIGH_VALUE ------------------------------ -------------------------------------------------------- P1 TO_DATE(' 1000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' SYS_P41 TO_DATE(' 2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'

Disadvantage:
System generates its own name of partition out of comfortable convention. Supporting programs should analyze HIGH_VALUE (Long data type) instead of PARTITION_NAME.
Advantage:
It supports both adding new partition at the end of table and inside of existing partition. You should not create complex mechanism to prepare partitions before, system creates partitions when they are needed.

Insert into tt3 (a,b) values(1,to_date('20080330','yyyymmdd'))
/

SQL> select partition_name,high_value from user_tab_partitions where table_name='TT3'; PARTITION_NAME HIGH_VALUE ------------------------------ -------------------------------------------------------- P1 TO_DATE(' 1000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' SYS_P41 TO_DATE(' 2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' SYS_P42 TO_DATE(' 2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'

See: Avoiding system generated names in 11g
See: Partitionin stratey patterns
Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky