{"id":33,"date":"2016-10-04T16:36:00","date_gmt":"2016-10-04T16:36:00","guid":{"rendered":"\/colin\/morethanfour\/post\/An-exercise-in-Interval-Partitioning"},"modified":"2016-10-04T16:36:00","modified_gmt":"2016-10-04T16:36:00","slug":"an-exercise-in-interval-partitioning","status":"publish","type":"post","link":"https:\/\/spacefold.com\/colin\/morethanfour\/2016\/10\/04\/an-exercise-in-interval-partitioning\/","title":{"rendered":"An exercise in Interval Partitioning"},"content":{"rendered":"

Consider a table, LOG_DATA, with lots of rows:<\/p>\n

\n
select<\/span> trunc( LOG_TIME, 'MM'<\/span> ) as<\/span> MONTH<\/span>, count<\/span>(1) as<\/span> Count<\/span>
from<\/span> Log_Data
group<\/span> by<\/span> trunc(LOG_TIME,'MM'<\/span>)
order<\/span> by<\/span> trunc(LOG_TIME,'MM'<\/span>);<\/pre>\n
<\/div>\n
\"image\"<\/p>\n
Perhaps this would be better served by a table with RANGE partitioning, using the INTERVAL syntax of 11g to automatically create partitions as data accumulates?<\/p>\n
Unfortunately, LOG_TIME is data type timestamp with time zone, and we can’t create an interval partition on timestamp columns (yet!)<\/p>\n
Also, we can’t use an expression in the interval definition. So, we need a DATE column:<\/p>\n
\n
create<\/span> table<\/span> Log_Data_P
(
   ID integer<\/span>,
   PARENT_ID integer<\/span>,
   LOG_TIME timestamp<\/span> with<\/span> time<\/span> zone<\/span> default<\/span> systimestamp,
   STACK_LEVEL integer<\/span>,
   PACKAGE_NAME varchar2(30),
   PROCEDURE_NAME varchar2(30),
   LOG_LABEL varchar2(100),
   LOG_VALUE varchar2(1000),
   LOG_DURATION number,
   PROC_DURATION number,
   STEP_DURATION number,
   LOG_CLOSED integer<\/span>,LOG_DATE date<\/span> default<\/span> sysdate
)
partition by<\/span> range ( LOG_DATE )
interval<\/span> ( numtoyminterval( 1, 'MONTH'<\/span> ) )
(
   partition part_01 values<\/span> less<\/span> than<\/span> ( date<\/span> '2016-05-01'<\/span> )
);<\/pre>\n
<\/div>\n
Here, we have added a LOG_DATE column to the end of the table structure. I’ve given it a default value and checked the code that writes to the table, to ensure there are no conflicts (there aren’t). <\/p>\n
Now let’s insert into the new table, with the rows from the original (spanning several months):<\/p>\n
\n
insert into<\/span> Log_Data_P
select<\/span>
   ID, PARENT_ID, LOG_TIME, STACK_LEVEL, PACKAGE_NAME, PROCEDURE_NAME, LOG_LABEL, LOG_VALUE,
   LOG_DURATION, PROC_DURATION, STEP_DURATION, LOG_CLOSED, cast<\/span>( LOG_TIME as<\/span> DATE<\/span> )
from<\/span> Log_Data;
commit<\/span>;<\/pre>\n
<\/div>\n
No errors… let’s see what partitions we have, at the end of this process:<\/p>\n
\n
select<\/span> PARTITION_NAME, INTERVAL<\/span>, HIGH_VALUE
   from<\/span> DBA_Tab_Partitions
   where<\/span> TABLE_NAME = 'LOG_DATA_P'<\/span>
   order<\/span> by<\/span> PARTITION_POSITION;
 
PARTITION_NAME     INTERVAL<\/span>   HIGH_VALUE
------------------ ---------- -----------------------------------------------------------<\/span>
PART_01            NO<\/span>         TO_DATE(' 2016-05-01 00:00:00'<\/span>, 'SYYYY-MM-DD HH24:MI:SS'<\/span> )
SYS_P15277         YES        TO_DATE(' 2016-06-01 00:00:00'<\/span>, 'SYYYY-MM-DD HH24:MI:SS'<\/span> )
SYS_P15278         YES        TO_DATE(' 2016-07-01 00:00:00'<\/span>, 'SYYYY-MM-DD HH24:MI:SS'<\/span> )
SYS_P15280         YES        TO_DATE(' 2016-08-01 00:00:00'<\/span>, 'SYYYY-MM-DD HH24:MI:SS'<\/span> )
SYS_P15281         YES        TO_DATE(' 2016-09-01 00:00:00'<\/span>, 'SYYYY-MM-DD HH24:MI:SS'<\/span> )
SYS_P15279         YES        TO_DATE(' 2016-10-01 00:00:00'<\/span>, 'SYYYY-MM-DD HH24:MI:SS'<\/span> )
SYS_P15282         YES        TO_DATE(' 2016-11-01 00:00:00'<\/span>, 'SYYYY-MM-DD HH24:MI:SS'<\/span> )
 
7 rows<\/span> selected.<\/pre>\n
<\/div>\n
Interesting… we have 6 new partitions over and above the original “PART_01” partition that we created. Obviously, Oracle has allocated some automatically-generated names to the partitions. <\/p>\n
OK, so now the time has come to perform some pruning on our log table. Let’s delete the oldest partition:<\/p>\n
\n
SQL<\/span>> alter<\/span> table<\/span> Log_Data_P drop<\/span> partition PART_01;
 
ORA-14758: Last<\/span> partition in<\/span> the range section<\/span> cannot be dropped<\/pre>\n
<\/div>\n
Uh-oh. It’s not as simple as we’d hoped. More about dropping partitions in this excellent post here:<\/p>\n