a set of square shelves each holding various jars of spices being a metaphor for a database partitioned table

Implementing dynamic partitions AND subpartitions

Posted by

One of the cool things to come out of the partitioning option many moons ago was the feature known as interval partitioning.

As a quick reprise/refresher, in times gone by, many a DBA has had that fateful call in the middle of the night because someone had tried to insert a row in a partitioned table and been unable to do so. The dreaded “ORA-14400: inserted partition key does not map to any partition” because your partitioned table only goes up to (say) December 2020 and the clock has just rolled over to January 2021. Sad smile

Interval partitioning solved this by dynamically creating partitions as required based on a scheme nominated by the DBA. No more late night callouts and no more unhappy customers. Full details on that are in the video below in case you’re new to the concept.

But what about tables that are in that next level of complexity, where the volumes or distribution has necessitated implementing a subpartitioning scheme within the partitioning scheme for a table? Let’s say I run a European telecommunications company and I’m tracking call data from all over the world. That is going to be a lot of data and I’ve built the following partitioning scheme for my table.


SQL> create table call_log (
  2      city          varchar2(20),
  3      call_type     varchar2(10),
  4      call_guid     varchar2(60),
  5      duration      number,
  6      bytes         number,
  7      cost          number,
  8      call_start    date
  9  )
 10  partition by range(call_start) interval( numtodsinterval(7,'day'))
 11  subpartition by list (city)
 12    subpartition template (
 13      subpartition paris     values ('paris'),
 14      subpartition berlin    values ('berlin'),
 15      subpartition rome      values ('rome'),
 16      subpartition milan     values ('milan'),
 17      subpartition amsterdam values ('amsterdam'),
 18      subpartition madrid    values ('madrid'),
 19      subpartition lisbon    values ('lisbon'),
 20      subpartition geneva    values ('geneva'),
 21      subpartition athens    values ('athens'),
 22      subpartition budapest  values ('budapest'),
 23    )
 24  (
 25    partition p_pre2020 values less than (date '2020-01-01')
 26  );

Table created.

I’m dividing my data up by week, and using interval partitioning for the date ranges to ensure I’m not going to get called out when each new week comes along. I’m also breaking up each week of data into cities that I currently provides telecomms services for. In this way, queries for date ranges get the benefit of partition pruning, but so do queries for a given city. Things like archival per city will also be easier in future.

Now probably, if a new city was to come online, I would know about it well in advance and could make the required changes to the subpartition template, but what if that was not the case? What if, as calls get routed back and forth across Europe I’m going to get new cities appearing unannounced in the data feed. Ideally, I’d like to use automatic list partitioning for the subpartitions.


SQL> create table call_log (
  2      city          varchar2(20),
  3      call_type     varchar2(10),
  4      call_guid     varchar2(60),
  5      duration      number,
  6      bytes         number,
  7      cost          number,
  8      call_start    date
  9  )
 10  partition by range(call_start) interval( numtodsinterval(7,'day'))
 11  subpartition by list (city) automatic
 12    subpartition template (
 13      subpartition paris     values ('paris'),
 14      subpartition berlin    values ('berlin'),
 15      subpartition rome      values ('rome'),
 16      subpartition milan     values ('milan'),
 17      subpartition amsterdam values ('amsterdam'),
 18      subpartition madrid    values ('madrid'),
 19      subpartition lisbon    values ('lisbon'),
 20      subpartition geneva    values ('geneva'),
 21      subpartition athens    values ('athens'),
 22      subpartition budapest  values ('budapest'),
 23    )
 24  (
 25    partition p_pre2020 values less than (date '2020-01-01')
 26  );
create table call_log (
*
ERROR at line 1:
ORA-14179: An unsupported partitioning method was specified in this context.

“Houston…” (or more correctly Europe) “…we have a problem” Smile 

Unfortunately the database does not yet support the concept of “multiple layers” of dynamically allocating partitions. For example, if I chose to flip the partitioning method around and partition by list and then subpartition by date, I would then be able to make the list element automatic, but not the date element.

So here are a few potential resolutions to this issue. One option is to use hash subpartitioning. Rather than explicitly list the cities, we’ll simply hash the city value that comes into the table and allocate a subpartition based on that hash value.


SQL> create table call_log (
  2      city          varchar2(20),
  3      call_type     varchar2(10),
  4      call_guid     varchar2(60),
  5      duration      number,
  6      bytes         number,
  7      cost          number,
  8      call_start    date
  9  )
 10  partition by range(call_start) interval( numtodsinterval(7,'day'))
 11  subpartition by hash (city) subpartitions 8
 12  (
 13    partition p_pre2020 values less than (date '2020-01-01')
 14  );

Table created.

The only problem with this approach is that we no longer have control over where the cities will “fall”. We can do some modelling using the ORA_HASH function to predict whether we will have any clashes with multiple cities falling into a single partition. Since ORA_HASH ranges from zero to an upper limit, nominating 7 as the second argument gives a hash size of 8, which aligns with the number of potential subpartitions. Cities that hash to the same hash value indicate that we’re likely to have multiple cities in the same partition, which might not suit our business requirements.


SQL> select x, ora_hash(x,7) from
  2  (
  3  select 'paris' x from dual union all
  4  select 'berlin' from dual union all
  5  select 'rome' from dual union all
  6  select 'milan' from dual union all
  7  select 'amsterdam' from dual union all
  8  select 'madrid' from dual union all
  9  select 'lisbon' from dual union all
 10  select 'geneva' from dual union all
 11  select 'athens' from dual union all
 12  select 'budapest' from dual
 13  )
 14  order by 2;

X         ORA_HASH(X,7)
--------- -------------
rome                  1
amsterdam             2
lisbon                2
milan                 2
geneva                3
athens                4
paris                 4
berlin                4
madrid                6
budapest              7

Clearly 8 subpartitions is not enough to get a city per partition, so we’ll experiment with bumping up the number of subpartitions in powers of 2 (because we always use powers of two).


SQL> select x, ora_hash(x,15) from
  2  (
  3  select 'paris' x from dual union all
  4  select 'berlin' from dual union all
  5  select 'rome' from dual union all
  6  select 'milan' from dual union all
  7  select 'amsterdam' from dual union all
  8  select 'madrid' from dual union all
  9  select 'lisbon' from dual union all
 10  select 'geneva' from dual union all
 11  select 'athens' from dual union all
 12  select 'budapest' from dual
 13  )
 14  order by 2;

X         ORA_HASH(X,15)
--------- --------------
rome                   1
amsterdam              2
lisbon                 2
athens                 4
paris                  4
milan                 10
geneva                11
berlin                12
madrid                14
budapest              15

10 rows selected.

SQL>
SQL> select x, ora_hash(x,31) from
  2  (
  3  select 'paris' x from dual union all
  4  select 'berlin' from dual union all
  5  select 'rome' from dual union all
  6  select 'milan' from dual union all
  7  select 'amsterdam' from dual union all
  8  select 'madrid' from dual union all
  9  select 'lisbon' from dual union all
 10  select 'geneva' from dual union all
 11  select 'athens' from dual union all
 12  select 'budapest' from dual
 13  )
 14  order by 2;

X         ORA_HASH(X,31)
--------- --------------
amsterdam              2
budapest              15
rome                  17
lisbon                18
paris                 20
athens                20
milan                 26
geneva                27
berlin                28
madrid                30

10 rows selected.

SQL>
SQL> select x, ora_hash(x,63) from
  2  (
  3  select 'paris' x from dual union all
  4  select 'berlin' from dual union all
  5  select 'rome' from dual union all
  6  select 'milan' from dual union all
  7  select 'amsterdam' from dual union all
  8  select 'madrid' from dual union all
  9  select 'lisbon' from dual union all
 10  select 'geneva' from dual union all
 11  select 'athens' from dual union all
 12  select 'budapest' from dual
 13  )
 14  order by 2;

X         ORA_HASH(X,63)
--------- --------------
budapest              15
lisbon                18
athens                20
berlin                28
amsterdam             34
rome                  49
paris                 52
milan                 58
geneva                59
madrid                62

10 rows selected.

So jumping to 64 subpartitions will give a single city per partition. Given that in recent versions of Oracle, we do not allocate space for segments of partitions or tables until they need it, this approach will not waste space, although the issue of the “next” city potentially being a hash collision with the existing cities still remains. We can never really guarantee that we are going to keep a single city per partition like we do in the explicit LIST partition strategy.

Another option is to retain the list partitioning scheme. For a list partitioning scheme, you can also nominate the reserved value of DEFAULT, namely, a partition into which all values will fall that do not match the values explicitly specified in all other list partitions.


SQL> create table call_log (
  2      city          varchar2(20),
  3      call_type     varchar2(10),
  4      call_guid     varchar2(60),
  5      duration      number,
  6      bytes         number,
  7      cost          number,
  8      call_start    date
  9  )
 10  partition by range(call_start) interval( numtodsinterval(7,'day'))
 11  subpartition by list (city)
 12    subpartition template (
 13      subpartition paris     values ('paris'),
 14      subpartition berlin    values ('berlin'),
 15      subpartition rome      values ('rome'),
 16      subpartition milan     values ('milan'),
 17      subpartition amsterdam values ('amsterdam'),
 18      subpartition madrid    values ('madrid'),
 19      subpartition lisbon    values ('lisbon'),
 20      subpartition geneva    values ('geneva'),
 21      subpartition athens    values ('athens'),
 22      subpartition budapest  values ('budapest'),
 23      subpartition others    values (default)
 24    )
 25  (
 26    partition p_pre2020 values less than (date '2020-01-01')
 27  );

Table created.

This ensures that any new cities that arrive will not have me getting called out in the middle of the night to fix the partitioning scheme, but that also means that it now becomes possible for multiple cities to quietly slip into the DEFAULT partition without me even noticing.

However that is where the cool partition maintenance facilities from 18c and above come into play. the SPLIT and MERGE partition options can now be done in online fashion without interrupting your application services. I just need to scan the table for any new cities and then move those rows into an appropriate new partition. My call log table is likely to be massive (after all, that is why I’m partitioning it) so I need to do this in the most efficient way possible. Here’s a rough outline of how to attack this task:

  • New city data will come into the most recent partitions, so I only need to scan those. Theoretically, someone could potentially come along and load some historical data, but I’ll assume that is not the case. (Even if it was, it will be a trivial change to my code as you’ll see shortly).
  • Within each recent partition, I only need to check the DEFAULT partition. If that partition is empty, then all the data has correctly gone into an existing subpartition for the nominatied city. Only if the partition is non-empty do I need to take any action.
  • For each non-empty DEFAULT subpartition, I need to get a distinct list of cities and run a SPLIT partition command to separate each city out into its own subpartition. That will eventually leave the DEFAULT subpartition empty once more, and the job is done.

First I’ll start a second session and have it streaming data into my CALL_LOG table. It will be loading some existing city data (Berlin) but also two new cities will “come online” during the operation. Copenhagen and Dublin will start loading call data as well, and I need my partitioning layout to automatically register those new cities.


SQL> begin
  2    for i in 1 .. 5000 loop
  3      insert into call_log values ('copenhagen','CALL','someguid',10,10,10,date '2020-01-01'+i/360);
  4      commit;
  5      dbms_session.sleep(0.01);
  6    end loop;
  7
  8    for i in 5001 .. 10000 loop
  9      insert into call_log values ('berlin','SMS','someguid',10,10,10,date '2020-01-01'+(i*2)/360);
 10      insert into call_log values ('dublin','DATA','someguid',10,10,10,date '2020-01-01'+(i*2+1)/360);
 11      commit;
 12      dbms_session.sleep(0.01);
 13    end loop;
 14  end loop;
 15  /

(running)

Now in another session, I’ll implement a PL/SQL block to manage any new cities that come along. The first run is before any inserts have taken place, and you see that the routine decides there is no work be to done.


SQL> set serverout on
SQL> declare
  2    --
  3    -- how far to look back for new citys data
  4    -- you would pick a number that you're happy with
  5    -- eg, if this task ran every 2 minutes you might
  6    -- look back 2 hours to cover the last 2 partitions
  7    --
  8    -- I've just hard coded it here for simplicity
  9    --
 10    l_start_time date := date '2019-01-01';
 11
 12    --
 13    -- Any new citys will be collected into this array
 14    --
 15    l_new_citys sys.odcivarchar2list := sys.odcivarchar2list();
 16
 17    l_boundary varchar2(200);
 18    l_dateval  date;
 19
 20    --
 21    -- template DDL for splitting the subpartition
 22    --
 23    l_split_ddl varchar2(1000) :=
 24      q'{alter table call_log split subpartition @@subpar@@
 25         values ('@@city@@') into (
 26         subpartition @@par@@_@@city@@,
 27         subpartition @@subpar@@
 28       ) online}';
 29
 30    l_ddl varchar2(1000) ;
 31  begin
 32    for i in (
 33      --
 34      -- start with the most recent partitions and loop
 35      -- through getting the "OTHERS" subpartition name
 36      --
 37      select *
 38      from (
 39        select s.partition_name,
 40               s.subpartition_name,
 41               p.high_value,
 42               p.partition_position,
 43               s.subpartition_position,
 44               count(*) over ( partition by p.partition_name ) as parcnt
 45        from   user_tab_partitions p,
 46               user_tab_subpartitions s
 47        where p.table_name = 'CALL_LOG'
 48        and   s.table_name = 'CALL_LOG'
 49        and   p.partition_position = s.partition_position
 50      )
 51      where subpartition_position = parcnt
 52      order by partition_position desc
 53      )
 54    loop
 55       l_boundary := i.high_value;
 56       execute immediate 'select '||l_boundary||' from dual' into l_dateval;
 57       exit when l_dateval <= l_start_time;
 58
 59       --
 60       -- for each of these "OTHERS" partitions, see if there is any data in there,
 61       -- which would mean a new city has come along
 62       --
 63       dbms_output.put_line('Scanning '||i.partition_name||','||i.subpartition_name);
 64       execute immediate
 65         'select distinct city from  call_log subpartition ( '||i.subpartition_name||')'
 66       bulk collect into l_new_citys;
 67
 68       --
 69       -- for each new city found, split the OTHERS partition into a dedicated partition
 70       -- for this new city
 71       --
 72       if l_new_citys.count > 0 then
 73         dbms_output.put_line(l_new_citys.count||' new city(s) found');
 74         for r in 1 .. l_new_citys.count loop
 75           l_ddl := replace(l_split_ddl,'@@subpar@@',i.subpartition_name);
 76           l_ddl := replace(l_ddl,'@@city@@',l_new_citys(r));
 77           l_ddl := replace(l_ddl,'@@par@@',i.partition_name);
 78           dbms_output.put_line('Running the following DDL:');
 79           dbms_output.put_line(l_ddl);
 80           execute immediate l_ddl;
 81         end loop;
 82       else
 83         dbms_output.put_line('Nothing to do for '||i.partition_name||','||i.subpartition_name);
 84       end if;
 85    end loop;
 86  end;
 87  /
Scanning P_PRE2020,P_PRE2020_OTHERS
Nothing to do for P_PRE2020,P_PRE2020_OTHERS

PL/SQL procedure successfully completed.

Now as my data population script runs, it will find any DEFAULT subpartitions, scan them for cities and then take the appropriate action. I’ve commented out the “execute immediate” clause, so you can see how the list of actions to take grows as more data arrives from the other session


SQL> set serverout on
SQL> declare
  2    --
  3    -- how far to look back for new citys data
  4    -- you would pick a number that you're happy with
  5    -- eg, if this task ran every 2 minutes you might
  6    -- look back 2 hours to cover the last 2 partitions
  7    --
  8    -- I've just hard coded it here for simplicity
  9    --
 10    l_start_time date := date '2020-01-01';
 11
 12    --
 13    -- Any new citys will be collected into this array
 14    --
 15    l_new_citys sys.odcivarchar2list := sys.odcivarchar2list();
 16
 17    l_boundary varchar2(200);
 18    l_dateval  date;
 19
 20    --
 21    -- template DDL for splitting the subpartition
 22    --
 23    l_split_ddl varchar2(1000) :=
 24      q'{alter table call_log split subpartition @@subpar@@
 25         values ('@@city@@') into (
 26         subpartition @@par@@_@@city@@,
 27         subpartition @@subpar@@
 28       ) online}';
 29
 30    l_ddl varchar2(1000) ;
 31  begin
 32    for i in (
 33      --
 34      -- start with the most recent partitions and loop
 35      -- through getting the "OTHERS" subpartition name
 36      --
 37      select *
 38      from (
 39        select s.partition_name,
 40               s.subpartition_name,
 41               p.high_value,
 42               p.partition_position,
 43               s.subpartition_position,
 44               count(*) over ( partition by p.partition_name ) as parcnt
 45        from   user_tab_partitions p,
 46               user_tab_subpartitions s
 47        where p.table_name = 'CALL_LOG'
 48        and   s.table_name = 'CALL_LOG'
 49        and   p.partition_position = s.partition_position
 50      )
 51      where subpartition_position = parcnt
 52      order by partition_position desc
 53      )
 54    loop
 55       l_boundary := i.high_value;
 56       execute immediate 'select '||l_boundary||' from dual' into l_dateval;
 57       exit when l_dateval <= l_start_time;
 58
 59       --
 60       -- for each of these "OTHERS" partitions, see if there is any data in there,
 61       -- which would mean a new city has come along
 62       --
 63       dbms_output.put_line('Scanning '||i.partition_name||','||i.subpartition_name);
 64       execute immediate
 65         'select distinct city from  call_log subpartition ( '||i.subpartition_name||')'
 66       bulk collect into l_new_citys;
 67
 68       --
 69       -- for each new city found, split the OTHERS partition into a dedicated partition
 70       -- for this new city
 71       --
 72       if l_new_citys.count > 0 then
 73         dbms_output.put_line(l_new_citys.count||' new city(s) found');
 74         for r in 1 .. l_new_citys.count loop
 75           l_ddl := replace(l_split_ddl,'@@subpar@@',i.subpartition_name);
 76           l_ddl := replace(l_ddl,'@@city@@',l_new_citys(r));
 77           l_ddl := replace(l_ddl,'@@par@@',i.partition_name);
 78           dbms_output.put_line('Running the following DDL:');
 79           dbms_output.put_line(l_ddl);
 80           --execute immediate l_ddl;
 81         end loop;
 82       else
 83         dbms_output.put_line('Nothing to do for '||i.partition_name||','||i.subpartition_name);
 84       end if;
 85    end loop;
 86  end;
 87  /
Scanning SYS_P37830,SYS_SUBP37829
Nothing to do for SYS_P37830,SYS_SUBP37829
Scanning SYS_P37821,SYS_SUBP37820
Nothing to do for SYS_P37821,SYS_SUBP37820
Scanning SYS_P37812,SYS_SUBP37811
Nothing to do for SYS_P37812,SYS_SUBP37811
Scanning SYS_P37803,SYS_SUBP37802
1 new city(s) found
Running the following DDL:
alter table call_log split subpartition SYS_SUBP37802
       values ('copenhagen') into (
       subpartition SYS_P37803_copenhagen,
       subpartition SYS_SUBP37802
     ) online
Scanning SYS_P37794,SYS_SUBP37793
1 new city(s) found
Running the following DDL:
alter table call_log split subpartition SYS_SUBP37793
       values ('copenhagen') into (
       subpartition SYS_P37794_copenhagen,
       subpartition SYS_SUBP37793
     ) online

PL/SQL procedure successfully completed.

We found “copenhagen” and we build (but not execute in this case) a DDL to split the DEFAULT subpartition into a dedicated ‘copenhagen’ subpartition for this particular week of data.

A little later, when more data has arrived for different weeks and new cities, the routine now has a larger scope of work


SQL> set serverout on
SQL> declare
  2    --
  3    -- how far to look back for new citys data
  4    -- you would pick a number that you're happy with
  5    -- eg, if this task ran every 2 minutes you might
  6    -- look back 2 hours to cover the last 2 partitions
  7    --
  8    -- I've just hard coded it here for simplicity
  9    --
 10    l_start_time date := date '2020-01-01';
 11
 12    --
 13    -- Any new citys will be collected into this array
 14    --
 15    l_new_citys sys.odcivarchar2list := sys.odcivarchar2list();
 16
 17    l_boundary varchar2(200);
 18    l_dateval  date;
 19
 20    --
 21    -- template DDL for splitting the subpartition
 22    --
 23    l_split_ddl varchar2(1000) :=
 24      q'{alter table call_log split subpartition @@subpar@@
 25         values ('@@city@@') into (
 26         subpartition @@par@@_@@city@@,
 27         subpartition @@subpar@@
 28       ) online}';
 29
 30    l_ddl varchar2(1000) ;
 31  begin
 32    for i in (
 33      --
 34      -- start with the most recent partitions and loop
 35      -- through getting the "OTHERS" subpartition name
 36      --
 37      select *
 38      from (
 39        select s.partition_name,
 40               s.subpartition_name,
 41               p.high_value,
 42               p.partition_position,
 43               s.subpartition_position,
 44               count(*) over ( partition by p.partition_name ) as parcnt
 45        from   user_tab_partitions p,
 46               user_tab_subpartitions s
 47        where p.table_name = 'CALL_LOG'
 48        and   s.table_name = 'CALL_LOG'
 49        and   p.partition_position = s.partition_position
 50      )
 51      where subpartition_position = parcnt
 52      order by partition_position desc
 53      )
 54    loop
 55       l_boundary := i.high_value;
 56       execute immediate 'select '||l_boundary||' from dual' into l_dateval;
 57       exit when l_dateval <= l_start_time;
 58
 59       --
 60       -- for each of these "OTHERS" partitions, see if there is any data in there,
 61       -- which would mean a new city has come along
 62       --
 63       dbms_output.put_line('Scanning '||i.partition_name||','||i.subpartition_name);
 64       execute immediate
 65         'select distinct city from  call_log subpartition ( '||i.subpartition_name||')'
 66       bulk collect into l_new_citys;
 67
 68       --
 69       -- for each new city found, split the OTHERS partition into a dedicated partition
 70       -- for this new city
 71       --
 72       if l_new_citys.count > 0 then
 73         dbms_output.put_line(l_new_citys.count||' new city(s) found');
 74         for r in 1 .. l_new_citys.count loop
 75           l_ddl := replace(l_split_ddl,'@@subpar@@',i.subpartition_name);
 76           l_ddl := replace(l_ddl,'@@city@@',l_new_citys(r));
 77           l_ddl := replace(l_ddl,'@@par@@',i.partition_name);
 78           dbms_output.put_line('Running the following DDL:');
 79           dbms_output.put_line(l_ddl);
 80           --execute immediate l_ddl;
 81         end loop;
 82       else
 83         dbms_output.put_line('Nothing to do for '||i.partition_name||','||i.subpartition_name);
 84       end if;
 85    end loop;
 86  end;
 87  /
Scanning SYS_P37860,SYS_SUBP37859
1 new city(s) found
Running the following DDL:
alter table call_log split subpartition SYS_SUBP37859
       values ('copenhagen') into (
       subpartition SYS_P37860_copenhagen,
       subpartition SYS_SUBP37859
     ) online

PL/SQL procedure successfully completed.

SQL> /
Scanning SYS_P37932,SYS_SUBP37931
1 new city(s) found
Running the following DDL:
alter table call_log split subpartition SYS_SUBP37931
       values ('dublin') into (
       subpartition SYS_P37932_dublin,
       subpartition SYS_SUBP37931
     ) online
Scanning SYS_P37920,SYS_SUBP37919
1 new city(s) found
Running the following DDL:
alter table call_log split subpartition SYS_SUBP37919
       values ('dublin') into (
       subpartition SYS_P37920_dublin,
       subpartition SYS_SUBP37919
     ) online
Scanning SYS_P37908,SYS_SUBP37907
1 new city(s) found
Running the following DDL:
alter table call_log split subpartition SYS_SUBP37907
       values ('dublin') into (
       subpartition SYS_P37908_dublin,
       subpartition SYS_SUBP37907
     ) online
Scanning SYS_P37896,SYS_SUBP37895
1 new city(s) found
Running the following DDL:
alter table call_log split subpartition SYS_SUBP37895
       values ('dublin') into (
       subpartition SYS_P37896_dublin,
       subpartition SYS_SUBP37895
     ) online
Scanning SYS_P37884,SYS_SUBP37883
1 new city(s) found
Running the following DDL:
alter table call_log split subpartition SYS_SUBP37883
       values ('dublin') into (
       subpartition SYS_P37884_dublin,
       subpartition SYS_SUBP37883
     ) online
Scanning SYS_P37872,SYS_SUBP37871
1 new city(s) found
Running the following DDL:
alter table call_log split subpartition SYS_SUBP37871
       values ('copenhagen') into (
       subpartition SYS_P37872_copenhagen,
       subpartition SYS_SUBP37871
     ) online
Scanning SYS_P37860,SYS_SUBP37859
1 new city(s) found
Running the following DDL:
alter table call_log split subpartition SYS_SUBP37859
       values ('copenhagen') into (
       subpartition SYS_P37860_copenhagen,
       subpartition SYS_SUBP37859
     ) online

PL/SQL procedure successfully completed.

This time I’ll run the routine the again but comment back in the execution of the DDL. So now, whilst the other session is still inserting new data, I can maintain my partitions in online fashion without interrupting the feed.


SQL> set serverout on
SQL> declare
  2    --
  3    -- how far to look back for new citys data
  4    -- you would pick a number that you're happy with
  5    -- eg, if this task ran every 2 minutes you might
  6    -- look back 2 hours to cover the last 2 partitions
  7    --
  8    -- I've just hard coded it here for simplicity
  9    --
 10    l_start_time date := date '2020-01-01';
 11
 12    --
 13    -- Any new citys will be collected into this array
 14    --
 15    l_new_citys sys.odcivarchar2list := sys.odcivarchar2list();
 16
 17    l_boundary varchar2(200);
 18    l_dateval  date;
 19
 20    --
 21    -- template DDL for splitting the subpartition
 22    --
 23    l_split_ddl varchar2(1000) :=
 24      q'{alter table call_log split subpartition @@subpar@@
 25         values ('@@city@@') into (
 26         subpartition @@par@@_@@city@@,
 27         subpartition @@subpar@@
 28       ) online}';
 29
 30    l_ddl varchar2(1000) ;
 31  begin
 32    for i in (
 33      --
 34      -- start with the most recent partitions and loop
 35      -- through getting the "OTHERS" subpartition name
 36      --
 37      select *
 38      from (
 39        select s.partition_name,
 40               s.subpartition_name,
 41               p.high_value,
 42               p.partition_position,
 43               s.subpartition_position,
 44               count(*) over ( partition by p.partition_name ) as parcnt
 45        from   user_tab_partitions p,
 46               user_tab_subpartitions s
 47        where p.table_name = 'CALL_LOG'
 48        and   s.table_name = 'CALL_LOG'
 49        and   p.partition_position = s.partition_position
 50      )
 51      where subpartition_position = parcnt
 52      order by partition_position desc
 53      )
 54    loop
 55       l_boundary := i.high_value;
 56       execute immediate 'select '||l_boundary||' from dual' into l_dateval;
 57       exit when l_dateval <= l_start_time;
 58
 59       --
 60       -- for each of these "OTHERS" partitions, see if there is any data in there,
 61       -- which would mean a new city has come along
 62       --
 63       dbms_output.put_line('Scanning '||i.partition_name||','||i.subpartition_name);
 64       execute immediate
 65         'select distinct city from  call_log subpartition ( '||i.subpartition_name||')'
 66       bulk collect into l_new_citys;
 67
 68       --
 69       -- for each new city found, split the OTHERS partition into a dedicated partition
 70       -- for this new city
 71       --
 72       if l_new_citys.count > 0 then
 73         dbms_output.put_line(l_new_citys.count||' new city(s) found');
 74         for r in 1 .. l_new_citys.count loop
 75           l_ddl := replace(l_split_ddl,'@@subpar@@',i.subpartition_name);
 76           l_ddl := replace(l_ddl,'@@city@@',l_new_citys(r));
 77           l_ddl := replace(l_ddl,'@@par@@',i.partition_name);
 78           dbms_output.put_line('Running the following DDL:');
 79           dbms_output.put_line(l_ddl);
 80           execute immediate l_ddl;
 81         end loop;
 82       else
 83         dbms_output.put_line('Nothing to do for '||i.partition_name||','||i.subpartition_name);
 84       end if;
 85    end loop;
 86  end;
 87  /
Scanning SYS_P37932,SYS_SUBP37931
1 new city(s) found
Running the following DDL:
alter table call_log split subpartition SYS_SUBP37931
       values ('dublin') into (
       subpartition SYS_P37932_dublin,
       subpartition SYS_SUBP37931
     ) online
Scanning SYS_P37920,SYS_SUBP37919
1 new city(s) found
Running the following DDL:
alter table call_log split subpartition SYS_SUBP37919
       values ('dublin') into (
       subpartition SYS_P37920_dublin,
       subpartition SYS_SUBP37919
     ) online
Scanning SYS_P37908,SYS_SUBP37907
1 new city(s) found
Running the following DDL:
alter table call_log split subpartition SYS_SUBP37907
       values ('dublin') into (
       subpartition SYS_P37908_dublin,
       subpartition SYS_SUBP37907
     ) online
Scanning SYS_P37896,SYS_SUBP37895
1 new city(s) found
Running the following DDL:
alter table call_log split subpartition SYS_SUBP37895
       values ('dublin') into (
       subpartition SYS_P37896_dublin,
       subpartition SYS_SUBP37895
     ) online
Scanning SYS_P37884,SYS_SUBP37883
1 new city(s) found
Running the following DDL:
alter table call_log split subpartition SYS_SUBP37883
       values ('dublin') into (
       subpartition SYS_P37884_dublin,
       subpartition SYS_SUBP37883
     ) online
Scanning SYS_P37872,SYS_SUBP37871
1 new city(s) found
Running the following DDL:
alter table call_log split subpartition SYS_SUBP37871
       values ('copenhagen') into (
       subpartition SYS_P37872_copenhagen,
       subpartition SYS_SUBP37871
     ) online
Scanning SYS_P37860,SYS_SUBP37859
1 new city(s) found
Running the following DDL:
alter table call_log split subpartition SYS_SUBP37859
       values ('copenhagen') into (
       subpartition SYS_P37860_copenhagen,
       subpartition SYS_SUBP37859
     ) online

PL/SQL procedure successfully completed.

Eventually when my insertion script has finished, and all of the partition maintenance has been done, you can see that the routine still runs efficiently by just scanning the DEFAULT subpartitions for any nominated date range of my choosing.


SQL> set serverout on
SQL> declare
  2    --
  3    -- how far to look back for new citys data
  4    -- you would pick a number that you're happy with
  5    -- eg, if this task ran every 2 minutes you might
  6    -- look back 2 hours to cover the last 2 partitions
  7    --
  8    -- I've just hard coded it here for simplicity
  9    --
 10    l_start_time date := date '2020-01-01';
 11
 12    --
 13    -- Any new citys will be collected into this array
 14    --
 15    l_new_citys sys.odcivarchar2list := sys.odcivarchar2list();
 16
 17    l_boundary varchar2(200);
 18    l_dateval  date;
 19
 20    --
 21    -- template DDL for splitting the subpartition
 22    --
 23    l_split_ddl varchar2(1000) :=
 24      q'{alter table call_log split subpartition @@subpar@@
 25         values ('@@city@@') into (
 26         subpartition @@par@@_@@city@@,
 27         subpartition @@subpar@@
 28       ) online}';
 29
 30    l_ddl varchar2(1000) ;
 31  begin
 32    for i in (
 33      --
 34      -- start with the most recent partitions and loop
 35      -- through getting the "OTHERS" subpartition name
 36      --
 37      select *
 38      from (
 39        select s.partition_name,
 40               s.subpartition_name,
 41               p.high_value,
 42               p.partition_position,
 43               s.subpartition_position,
 44               count(*) over ( partition by p.partition_name ) as parcnt
 45        from   user_tab_partitions p,
 46               user_tab_subpartitions s
 47        where p.table_name = 'CALL_LOG'
 48        and   s.table_name = 'CALL_LOG'
 49        and   p.partition_position = s.partition_position
 50      )
 51      where subpartition_position = parcnt
 52      order by partition_position desc
 53      )
 54    loop
 55       l_boundary := i.high_value;
 56       execute immediate 'select '||l_boundary||' from dual' into l_dateval;
 57       exit when l_dateval <= l_start_time;
 58
 59       --
 60       -- for each of these "OTHERS" partitions, see if there is any data in there,
 61       -- which would mean a new city has come along
 62       --
 63       dbms_output.put_line('Scanning '||i.partition_name||','||i.subpartition_name);
 64       execute immediate
 65         'select distinct city from  call_log subpartition ( '||i.subpartition_name||')'
 66       bulk collect into l_new_citys;
 67
 68       --
 69       -- for each new city found, split the OTHERS partition into a dedicated partition
 70       -- for this new city
 71       --
 72       if l_new_citys.count > 0 then
 73         dbms_output.put_line(l_new_citys.count||' new city(s) found');
 74         for r in 1 .. l_new_citys.count loop
 75           l_ddl := replace(l_split_ddl,'@@subpar@@',i.subpartition_name);
 76           l_ddl := replace(l_ddl,'@@city@@',l_new_citys(r));
 77           l_ddl := replace(l_ddl,'@@par@@',i.partition_name);
 78           dbms_output.put_line('Running the following DDL:');
 79           dbms_output.put_line(l_ddl);
 80           execute immediate l_ddl;
 81         end loop;
 82       else
 83         dbms_output.put_line('Nothing to do for '||i.partition_name||','||i.subpartition_name);
 84       end if;
 85    end loop;
 86  end;
 87  /
Scanning SYS_P37932,SYS_SUBP37931
Nothing to do for SYS_P37932,SYS_SUBP37931
Scanning SYS_P37920,SYS_SUBP37919
Nothing to do for SYS_P37920,SYS_SUBP37919
Scanning SYS_P37908,SYS_SUBP37907
Nothing to do for SYS_P37908,SYS_SUBP37907
Scanning SYS_P37896,SYS_SUBP37895
Nothing to do for SYS_P37896,SYS_SUBP37895
Scanning SYS_P37884,SYS_SUBP37883
Nothing to do for SYS_P37884,SYS_SUBP37883
Scanning SYS_P37872,SYS_SUBP37871
Nothing to do for SYS_P37872,SYS_SUBP37871
Scanning SYS_P37860,SYS_SUBP37859
Nothing to do for SYS_P37860,SYS_SUBP37859

PL/SQL procedure successfully completed.

As I mentioned earlier, if there is a risk of cities being loaded into earlier timescales, all I need do it alter the ‘l_start_time’ constant, or just not refer to it at all, and I’ll scan every DEFAULT subpartition. Since we’re expecting them to almost always be empty, this will still be a very fast process.

Similarly, if I had indexes on the table, I can simply add the UPDATE INDEXES clause to the DDLs to ensure that they also stay active throughout the duration of partition maintenance.

Perhaps in a future Oracle version there will be automatic partition generation for both partitions and subpartitions, but in the interim, a little PL/SQL and the cool online partition maintenance features are all you need to handle this requirement.

6 comments

  1. Hi Connor,

    Very nice idea 🙂
    I wonder whether a possible addition to your procedure, as a last step, could be to also modify the current SUBPARTITION TEMPLATE by adding a subpartition for each of the NEW cities found.
    True, this is currently NOT an ONLINE operation … but it is a very fast one anyway.

    As you always like to say, each time when you solve a problem, you learn something new about the Oracle documentation … this time I learned that the subpartition templates are stored in USER_SUBPARTITION_TEMPLATES, so they are readily available to be modified 🙂
    This way, further runs will have even less work to do.

    A little “uglier” problem could be that of using the city names as part of the subpartition names …
    where you should deal with possible spaces or other weird characters included (like apostrophes, etc) …

    Just my two pennies to your wonderful idea , as always 🙂 🙂

    Cheers & Best Regards,
    Iudith

  2. “So jumping to 64 subpartitions will give a single city per partition. Given that in recent versions of Oracle, we do not allocate space for segments of partitions or tables until they need it, this approach will not waste space”

    Not near a database right now but I’m pretty sure deferred segment creation only applies at table and partition level not subpartition so 64 partitions, whilst offering advantages, will waste some space. Will check later.

    1. SQL> create table t ( x int, y int )
      2 partition by list (x)
      3 subpartition by list (y)
      4 subpartition template (
      5 subpartition s1 values (1),
      6 subpartition s2 values (2),
      7 subpartition s3 values (3),
      8 subpartition s4 values (4)
      9 )
      10 (
      11 partition p1 values(10),
      12 partition p2 values(20),
      13 partition p3 values(30)
      14 );

      Table created.

      SQL>
      SQL>
      SQL> select partition_name from dba_segments
      2 where segment_name = ‘T’;

      no rows selected

      SQL>
      SQL> insert into t values (10,1);

      1 row created.

      SQL> commit;

      Commit complete.

      SQL> select partition_name from dba_segments
      2 where segment_name = ‘T’;

      PARTITION_NAME
      ——————————
      P1_S1

      SQL>
      SQL> insert into t values (20,4);

      1 row created.

      SQL> commit;

      Commit complete.

      SQL>
      SQL> select partition_name from dba_segments
      2 where segment_name = ‘T’;

      PARTITION_NAME
      ——————————
      P1_S1
      P2_S4

  3. Not sure if my previous comment disappeared on login or is in an approval queue. Will repost anyway.

    “So jumping to 64 subpartitions will give a single city per partition. Given that in recent versions of Oracle, we do not allocate space for segments of partitions or tables until they need it, this approach will not waste space”

    Not near a database right now but pretty sure deferred segment creation only applies at table and partition level, not subpartition. So there would be some wastage. Will check later.

  4. Hi,
    two remarks to your excellent post
    1. For online operations “UPDATE INDEXES” clause is optional.
    “For ONLINE operations, split indexes are always updated by default, regardless whether you specify the UPDATE INDEXES clause.”
    https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/maintenance-partition-tables-indexes.html#GUID-6BB84952-7021-4CBA-91ED-180E0656E02B

    2. From my experience ONLINE split is never performed in “Fast Split” mode, even when a new partiion segment is empty.
    During online split the source segment is also recreated and original data is copied into a new segment.
    So from the performance point of view, it’s important to:
    – keep the number of rows in the source partition small (run split often)
    – do not run split process during massive inserts into that partition (or other DMLs)

    Regards,
    Volodymyr

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.