Thinking Out Loud

February 17, 2016

Converting to Interval Paritions

Filed under: 11g,partitioning — mdinh @ 11:24 pm

Just a quick note to demonstrate how to convert date range partition into interval partition.

Create test case:

ARROW:(MDINH@hawklas):PRIMARY> @test_part.sql
ARROW:(MDINH@hawklas):PRIMARY> set echo on
ARROW:(MDINH@hawklas):PRIMARY> drop table t_part purge;

Table dropped.

ARROW:(MDINH@hawklas):PRIMARY> create table t_part (
  2  col_date DATE
  3  )
  4  PARTITION BY RANGE (col_date)
  5  (
  6  PARTITION P2014 VALUES LESS THAN (TO_DATE('2015-01-01','YYYY-MM-DD')),
  7  PARTITION P2015 VALUES LESS THAN (TO_DATE('2016-01-01','YYYY-MM-DD')),
  8  PARTITION PMAX VALUES LESS THAN (MAXVALUE)
  9  );

Table created.

ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2014-01-01','YYYY-MM-DD'));

1 row created.

ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2015-01-01','YYYY-MM-DD'));

1 row created.

ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2016-01-01','YYYY-MM-DD'));

1 row created.

ARROW:(MDINH@hawklas):PRIMARY> commit;

Commit complete.

ARROW:(MDINH@hawklas):PRIMARY> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'T_PART',cascade=>DBMS_STATS.AUTO_CASCADE,method_opt => 'FOR ALL COLUMNS SIZE AUTO',estimate_percent => NULL);

PL/SQL procedure successfully completed.

Check partitions:

ARROW:(MDINH@hawklas):PRIMARY> @part.sql
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
  2  FROM dba_tab_partitions i
  3  WHERE table_name='T_PART'
  4  ;

TABLE_OWNER          TABLE_NAME           INT PARTITION_NAME         NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH                T_PART               NO  P2014                         1                  0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2015                         1                  0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  PMAX                          1                  0 MAXVALUE

ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
  2    table_name,
  3    partitioning_type,
  4    subpartitioning_type,
  5    partition_count,
  6    def_subpartition_count,
  7    partitioning_key_count,
  8    def_tablespace_name
  9  FROM DBA_PART_TABLES
 10  WHERE partitioning_type='RANGE'
 11  AND table_name='T_PART'
 12  ;

OWNER                TABLE_NAME           PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH                T_PART               RANGE     NONE                    3                      0                      1 USERS

Failed due to PMAX partition as shown above:

ARROW:(MDINH@hawklas):PRIMARY> alter table t_part set interval(numtodsinterval(1,'year'));
alter table t_part set interval(numtodsinterval(1,'year'))
*
ERROR at line 1:
ORA-14759: SET INTERVAL is not legal on this table.

Split partition to remove PMAX:

ARROW:(MDINH@hawklas):PRIMARY> alter table t_part split partition PMAX AT(TO_DATE('2017-01-01', 'YYYY-MM-DD')) into (partition P2017, partition PMAX);

Table altered.

ARROW:(MDINH@hawklas):PRIMARY> alter table t_part drop partition pmax;

Table altered.

Check partition:

ARROW:(MDINH@hawklas):PRIMARY> @part.sql
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
  2  FROM dba_tab_partitions i
  3  WHERE table_name='T_PART'
  4  ;

TABLE_OWNER          TABLE_NAME           INT PARTITION_NAME         NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH                T_PART               NO  P2014                         1                  0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2015                         1                  0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2017                         1                  0 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')


ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
  2    table_name,
  3    partitioning_type,
  4    subpartitioning_type,
  5    partition_count,
  6    def_subpartition_count,
  7    partitioning_key_count,
  8    def_tablespace_name
  9  FROM DBA_PART_TABLES
 10  WHERE partitioning_type='RANGE'
 11  AND table_name='T_PART'
 12  ;

OWNER                TABLE_NAME           PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH                T_PART               RANGE     NONE                    3                      0                      1 USERS

Wrong syntax:

ARROW:(MDINH@hawklas):PRIMARY> alter table t_part set interval(numtodsinterval(1,'year'));
alter table t_part set interval(numtodsinterval(1,'year'))
                                                  *
ERROR at line 1:
ORA-14752: Interval expression is not a constant of the correct type

Correct syntax:

ARROW:(MDINH@hawklas):PRIMARY> alter table t_part set interval(NUMTOYMINTERVAL(1,'year'));

Table altered.

Check partition:

ARROW:(MDINH@hawklas):PRIMARY> @part
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
  2  FROM dba_tab_partitions i
  3  WHERE table_name='T_PART'
  4  ;

TABLE_OWNER          TABLE_NAME           INT PARTITION_NAME         NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH                T_PART               NO  P2014                         1                  0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2015                         1                  0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2017                         1                  0 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
  2    table_name,
  3    partitioning_type,
  4    subpartitioning_type,
  5    partition_count,
  6    def_subpartition_count,
  7    partitioning_key_count,
  8    def_tablespace_name
  9  FROM DBA_PART_TABLES
 10  WHERE partitioning_type='RANGE'
 11  AND table_name='T_PART'
 12  ;

OWNER                TABLE_NAME           PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH                T_PART               RANGE     NONE              1048575                      0                      1 USERS

Add data:

ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2018-01-01','YYYY-MM-DD'));

1 row created.

ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2019-01-01','YYYY-MM-DD'));

1 row created.


ARROW:(MDINH@hawklas):PRIMARY> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'T_PART',cascade=>DBMS_STATS.AUTO_CASCADE,method_opt => 'FOR ALL COLUMNS SIZE AUTO',estimate_percent => NULL);

PL/SQL procedure successfully completed.

Check partition:

ARROW:(MDINH@hawklas):PRIMARY> @part.sql
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
2 FROM dba_tab_partitions i
3 WHERE table_name='T_PART'
4 ;

TABLE_OWNER          TABLE_NAME           INT PARTITION_NAME         NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH                T_PART               NO  P2014                         1                  0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2015                         1                  0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2017                         1                  0 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               YES SYS_P541                      1                  0 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               YES SYS_P542                      1                  0 TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')
ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
2 table_name,
3 partitioning_type,
4 subpartitioning_type,
5 partition_count,
6 def_subpartition_count,
7 partitioning_key_count,
8 def_tablespace_name
9 FROM DBA_PART_TABLES
10 WHERE partitioning_type='RANGE'
11 AND table_name='T_PART'
12 ;

OWNER TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH T_PART RANGE NONE 1048575 0 1 USERS

ARROW:(MDINH@hawklas):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:hawklas:/media/sf_working/sql
$

Create a free website or blog at WordPress.com.