I wouldn’t say that Simon is obsessed about Luton Town FC, but he has been known to express his affection through the medium of creosote :
Needless to say, he’s quite looking forward to next season as the Hatters have completed their Lazarus-like resurrection from the depths of the Conference to the pinnacle of English football – the Premier League.
Over the years, I’ve occasionally accompanied Simon to Kennelworth Road to watch Luton battle their way through the divisions, so it’s only fitting that I should pay tribute to their achievement in the examples that follow.
The technical subject at hand here is the advantages available when using Interval Partitioning syntax to Range Partition a table by a date.
As we’re talking about partitioning, he’s the standard warning about licensing…
Partitioning is usually an additional cost option on the Enterprise Edition license. It’s a really good idea to make sure that whatever platform you’re on is appropriately licensed before you use this feature.
For this post, I’ll be using 23c Free, which does include Partitioning.
We’ll start by creating a conventional Range Partitioned table and see what happens when we try to create data in a non-existent partition.
We’ll then try the same thing, but this time using an Interval Partitioned Table.
Finally, we’ll take a look at the SQL functions available to define intervals when creating Interval Partitions.
Partitioning by Date using conventional Range Partitioning
For the purposes of this example, I’m going to create a table which holds details of Luton’s final record for a League Season. I’m going to arbitrarily “end” each season on 30th June each year.
I’m going to separate each year into it’s own partition.
Whilst this may not be a realistic use of partitioning – you’re unlikely to create a table to hold a single row per partition – it does simplify the examples that follow :
create table luton_seasons
(
season_end_date date,
competition varchar2(25),
games_played number,
games_won number,
games_drawn number,
games_lost number,
goals_for number,
goals_against number,
points number,
finishing_position number,
notes varchar2(4000)
)
partition by range( season_end_date)
(
partition season_2013_14 values less than (to_date ('01-JUL-2014', 'DD-MON-YYYY')),
partition season_2014_15 values less than (to_date ('01-JUL-2015', 'DD-MON-YYYY')),
partition season_2015_16 values less than (to_date ('01-JUL-2016', 'DD-MON-YYYY')),
partition season_2016_17 values less than (to_date ('01-JUL-2017', 'DD-MON-YYYY')),
partition season_2017_18 values less than (to_date ('01-JUL-2018', 'DD-MON-YYYY'))
)
/
Populating the table works as you’d expect…
-- 2013-14
insert into luton_seasons
(
season_end_date,
competition,
games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
finishing_position, notes
)
values
(
to_date('01-JUN-2014', 'DD-MON-YYYY'),
'Conference',
46, 30, 11, 5, 102, 35, 101,
1, 'Promoted back to the Football League !'
);
-- 2014-15
insert into luton_seasons
(
season_end_date,
competition,
games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
finishing_position, notes
)
values
(
to_date('01-JUN-2015', 'DD-MON-YYYY'),
'League 2',
46, 19, 11, 16, 54, 44, 68,
8, null
);
-- 2015-16
insert into luton_seasons
(
season_end_date,
competition,
games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
finishing_position, notes
)
values
(
to_date('01-JUN-2016', 'DD-MON-YYYY'),
'League 2',
46, 19, 9, 18, 63, 61, 66,
11, null
);
-- 2016-17
insert into luton_seasons
(
season_end_date,
competition,
games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
finishing_position, notes
)
values
(
to_date('01-JUN-2017', 'DD-MON-YYYY'),
'League 2',
46, 20, 17, 9, 70, 43, 77,
4, 'Lost in Promotion Play-Off Semi-Final'
);
-- 2017-18
insert into luton_seasons
(
season_end_date,
competition,
games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
finishing_position, notes
)
values
(
to_date('01-JUN-2018', 'DD-MON-YYYY'),
'League 2',
46, 25, 13, 8, 94, 46, 88,
2, 'Promoted'
);
commit;
select season_end_date, competition, finishing_position, notes
from luton_seasons
order by season_end_date
/
SEASON_END_DATE COMPETITION FINISHING_POSITION NOTES
--------------- ----------- -------------------- ----------------------------------------
01-JUN-2014 Conference 1 Promoted back to the Football League !
01-JUN-2015 League 2 8
01-JUN-2016 League 2 11
01-JUN-2017 League 2 4 Lost in Promotion Play-Off Semi-Final
01-JUN-2018 League 2 2 Promoted
5 rows selected. …until we try to insert a record for which a partition does not exist…
-- 2018-19
insert into luton_seasons
(
season_end_date,
competition,
games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
finishing_position, notes
)
values
(
to_date('01-JUN-2019', 'DD-MON-YYYY'),
'League 1',
46, 27, 13, 6, 90, 42, 94,
1, 'Champions ! Promoted to The Championship'
);
…when we are met with :
ORA-14400: no partition found in table MIKE.LUTON_SEASONS for inserted partition key "2019-06-01T00:00:00"
Wouldn’t it be good if we could just get Oracle to create partitions automatically, as and when they were needed ? Well, we can…
Creating an Interval Partitioned Table.
We’re going to re-create the table, but this time, we’re going to use Interval Partitioning :
drop table luton_seasons;
create table luton_seasons
(
season_end_date date,
competition varchar2(25),
games_played number,
games_won number,
games_drawn number,
games_lost number,
goals_for number,
goals_against number,
points number,
finishing_position number,
notes varchar2(4000)
)
partition by range( season_end_date)
interval (numtoyminterval(1, 'year'))
(
partition season_2013_14 values less than (to_date('01-JUL-2014', 'DD-MON-YYYY'))
)
/
We’re still partitioning by range. However, we’re now specifying an interval, in this case 1 year.
For now though, our chances of avoiding ORA-14400 don’t look good as we’ve only created one partition :
select partition_name, high_value
from user_tab_partitions
where table_name = 'LUTON_SEASONS'
order by 1;
PARTITION_NAME HIGH_VALUE
-------------------- ------------------------------------------------------------------------------------------
SEASON_2013_14 TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
Side Note – In 23c Oracle have added HIGH_VALUE_CLOB and HIGH_VALUE_JSON to USER_TAB_PARTITIONS so you can access the partition HIGH_VALUE via a datatype that is more malleable than the LONG HIGH_VALUE column that’s been in this view since David Pleat was last Luton manager.
Anyhow, let’s give it a go :
insert into luton_seasons
(
season_end_date,
competition,
games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
finishing_position, notes
)
values
(
to_date('01-JUN-2014', 'DD-MON-YYYY'),
'Conference',
46, 30, 11, 5, 102, 35, 101,
1, 'Promoted back to the Football League !'
);
-- 2014-15
insert into luton_seasons
(
season_end_date,
competition,
games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
finishing_position, notes
)
values
(
to_date('01-JUN-2015', 'DD-MON-YYYY'),
'League 2',
46, 19, 11, 16, 54, 44, 68,
8, null
);
-- 2015-16
insert into luton_seasons
(
season_end_date,
competition,
games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
finishing_position, notes
)
values
(
to_date('01-JUN-2016', 'DD-MON-YYYY'),
'League 2',
46, 19, 9, 18, 63, 61, 66,
11, null
);
-- 2016-17
insert into luton_seasons
(
season_end_date,
competition,
games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
finishing_position, notes
)
values
(
to_date('01-JUN-2017', 'DD-MON-YYYY'),
'League 2',
46, 20, 17, 9, 70, 43, 77,
4, 'Lost in Promotion Play-Off Semi-Final'
);
-- 2017-18
insert into luton_seasons
(
season_end_date,
competition,
games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
finishing_position, notes
)
values
(
to_date('01-JUN-2018', 'DD-MON-YYYY'),
'League 2',
46, 25, 13, 8, 94, 46, 88,
2, 'Promoted'
);
-- 2018-19
insert into luton_seasons
(
season_end_date,
competition,
games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
finishing_position, notes
)
values
(
to_date('01-JUN-2019', 'DD-MON-YYYY'),
'League 1',
46, 27, 13, 6, 90, 42, 94,
1, 'Champions ! Promoted to The Championship'
);
-- 2019-20
insert into luton_seasons
(
season_end_date,
competition,
games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
finishing_position, notes
)
values
(
to_date('01-JUN-2020', 'DD-MON-YYYY'),
'Championship',
46, 14, 9, 23, 54, 82, 15,
19, null
);
-- 2020-21
insert into luton_seasons
(
season_end_date,
competition,
games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
finishing_position, notes
)
values
(
to_date('01-JUN-2021', 'DD-MON-YYYY'),
'Championship',
46, 17, 11, 18, 41, 52, 62,
12, null
);
-- 2021-22
insert into luton_seasons
(
season_end_date,
competition,
games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
finishing_position, notes
)
values
(
to_date('01-JUN-2022', 'DD-MON-YYYY'),
'Championship',
46, 21, 12, 13, 63, 55, 75,
6, 'Lost in Promotion Play-Off Semi-Final'
);
-- 2022-23
insert into luton_seasons
(
season_end_date,
competition,
games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
finishing_position, notes
)
values
(
to_date('01-JUN-2023', 'DD-MON-YYYY'),
'Championship',
46, 21, 17, 8, 57, 39, 80,
3, 'Won Play-Off Final and promoted to Premiership !'
);
commit;
Oh look, that all worked :
…and the new partitions have been created automatically…
exec dbms_stats.gather_table_stats(user, 'LUTON_SEASONS');
select partition_name, high_value, num_rows
from user_tab_partitions t
where table_name = 'LUTON_SEASONS'
order by partition_position
/
We can further confirm that a particular partition holds just one record. For example :
select season_end_date, competition, finishing_position
from luton_seasons partition (SYS_P996);
…or, as we’re partitioning by date…
select season_end_date, competition, finishing_position
from luton_seasons partition for (to_date ('31-MAY-2019', 'DD-MON-YYYY'));
…confirms a single row in the partition…
SEASON_END_DATE COMPETITION FINISHING_POSITION ------------------------------ ---------------- ------------------ 01-JUN-2019 League 1 1 1 row selected.
Interval Partitioining Clairvoyance
Now, you’re probably thinking that Interval partitioning will ensure that your table is equi-partitioned in terms of time periods, provided the value of the partition key is inserted sequentially.
But what happens if you enter dates out-of-sequence ?
For example :
insert into luton_seasons
(
season_end_date,
competition,
games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
finishing_position, notes
)
values
(
to_date('01-JUN-2025', 'DD-MON-YYYY'),
'Premier League',
38, 38, 0, 0, 112, 7, 114,
1, 'Clean sweep of trophies and a perfect season in one go !'
);
insert into luton_seasons
(
season_end_date,
competition,
games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
finishing_position, notes
)
values
(
to_date('01-JUN-2024', 'DD-MON-YYYY'),
'Premier League',
38, 20, 10, 8, 64, 40, 70,
4, 'Champions League here we come !'
);
commit;
Now, you might think that Oracle would create the partition for values less than 01-JUL-2025 on the creation of the first record and then assign the second record to that partition as it already exists.
However, it’s actually smart enough to create the “missing” partition for the second insert :
select season_end_date, competition, finishing_position
from luton_seasons partition for (to_date ('01-JUN-2024', 'DD-MON-YYYY'))
order by 1;
SEASON_END_DATE COMPETITION FINISHING_POSITION
------------------------------ ------------------- ------------------
01-JUN-2024 Premier League 4
1 row selected.
We can see that the table’s last partition by position has a lower system-generated number in it’s name than it’s predecessor, indicating that it was created first :
select partition_name, partition_position, high_value
from user_tab_partitions
where table_name = 'LUTON_SEASONS'
order by partition_position;
The Interval Functions
There are two functions that you can use when specifying an interval for your partitions :
Despite the camel-case formatting of these function names they are in-fact case insensitive when being used in SQL or PL/SQL.
NumToYMInterval lets you specify an interval in either ‘YEARS’ or ‘MONTHS’ :
select to_date('29-FEB-2020', 'DD-MON-YYYY') + numtoyminterval(4, 'year') as next_leap_year_day
from dual;
NEXT_LEAP_YEAR_DAY
------------------------------
29-FEB-2024
select to_date('01-JUL-2023', 'DD-MON-YYYY') + numtoyminterval(3, 'month') as start_q4_2023
from dual;
START_Q4_2023
------------------------------
01-OCT-2023
NumToDSInterval allows you to specify more granular intervals – i.e :
- DAY
- HOUR
- MINUTE
- SECOND
How long is a football match ?
select numToDSInterval(5400, 'second') as match_length
from dual;
select numtodsinterval(90, 'minute') as match_length
from dual;
select numToDSInterval(1.5, 'hour') as match_length
from dual;
select numtodsinterval(1.5/24, 'day') as match_length
from dual;
Each of these queries return :
MATCH_LENGTH
------------------------------
+00 01:30:00.000000
Note that I’ve included the FROM clause in the above queries because, until the full 23c release sees the light of day, we’ll still need to use it in the day job.
Whatever you’re planning to do to pass the time until …
alter session set nls_date_format='DD-MON-YYYY HH24:MI';
select
to_date('12-AUG_2023', 'DD-MON-YYYY') + numtodsinterval(15, 'hour') as kick_off
from dual;
…using interval partitioning means that it should involve less manual partition creation.
Share this:
The Anti-Kyte 