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. 
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” 
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.
Learning is not a spectator sport 
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
“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.
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
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.
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
Thanks for that excellent additional info
Hi,
I need to do range partitioning, but so that each partition is in a different tablespace and that automatically newly created partitions also create new tablespaces for themselves.
For now I have something like this (without tablespace):
ALTER TABLE BAZA_COMTRADE MODIFY PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1,’MONTH’))
(PARTITION P00 VALUES LESS THAN (DATE ‘2010-01-01’));
I will be grateful for your help.
You can pre-allocate a partition simply by trying to lock the table for that partition, eg
lock table T partition for ( my_timestamp) in share mode;
and then move the empty partition to your preferred tablespace.
If you have a discrete set, you can also use STORE IN