At the end of my presentation to the UKOUG Database SIG yesterday I summed up (most of) the points I’d made with a slide making the claim:
In 12.2 you can: convert a simple heap table to partitioned with multi-column automatic list partitions, partially indexed, with read only segments, filtering out unwanted data, online in one operation.
Last night I decided I ought to demonstrate the claim – so here’s a little code to create a simple heap table that I can demonstrate on:
rem
rem Script: 122_features.sql
rem Author: Jonathan Lewis
rem Dated: June 2017
rem
rem Last tested
rem 12.2.0.1
rem
create table t1(
date_start not null,
date_end not null,
id not null,
client_id,
resort_code,
uk_flag,
v1,
padding,
constraint t1_range_ck check ((date_end - date_start) in (7, 14, 21))
)
segment creation immediate
nologging
as
with generator as (
select
rownum id
from dual
connect by
level <= 1e4
)
select
trunc(sysdate,'yyyy') + 7 * mod(rownum, 8) date_start,
trunc(sysdate,'yyyy') + 7 * (mod(rownum, 8) + trunc(dbms_random.value(1,4))) date_end,
rownum id,
trunc(dbms_random.value(1e5,2e5)) client_id,
trunc(dbms_random.value(1e4,2e4)) resort_code,
case when mod(rownum,275) = 0 then 1 end uk_flag,
lpad(rownum,10,'0') v1,
lpad('x',100,'x') padding
from
generator v1,
generator v2
where
rownum <= 1e5 -- > "GT" inserted to avoid WordPress formatting issue
;
create index t1_client_idx on t1(client_id);
create index t1_resort_idx on t1(resort_code);
create index t1_ukflag_idx on t1(uk_flag);
alter table t1 add constraint t1_pk primary key(id);
I’ve got a table which models a travel company that arranges holidays that last exactly one, two, or three weeks and (for convenience) they all start on the same day of the week. So I generate a start and end date for each row, making sure the start date is a multiple of seven days from a base date while the end date is 7, 14, or 21 days later. I’ve got a few indexes on the data and a primary key constraint. There’s a special flag column on the table for holidays in the UK which happens to identify a small parcentage of the holidays booked.
Eventually, when the data set gets too big, I decide that I want to partition the table and the obvious partitioning idea that springs to mind is to partition it so that holidays with the same start date and duration are all in the same partition and each partition holds a single start/duration.
I’ve also decided that I’m going to make old data read-only, and I’m not interested in the UK holidays once they’ve gone into history so I’m going to get rid of some of them.
The index protecting the primary key will have to be global since it won’t contain the partition key; since the index on uk_flag covers a small amount of data I’m going to keep that global as well, but I want the other two indexes to be local – but for the older data I’m not really interested in keeping the index on client id.
And I don’t want to stop the application while I’m restructuring the data.
So here’s my one SQL statement:
alter table t1 modify
partition by list (date_start, date_end) automatic (
partition p11 values (to_date('01-Jan-2017'),to_date('08-Jan-2017')) indexing off read only,
partition p12 values (to_date('01-Jan-2017'),to_date('15-Jan-2017')) indexing off read only,
partition p13 values (to_date('01-Jan-2017'),to_date('22-Jan-2017')) indexing off read only,
partition p21 values (to_date('08-Jan-2017'),to_date('15-Jan-2017')) indexing off read only,
partition p22 values (to_date('08-Jan-2017'),to_date('22-Jan-2017')) indexing off read only,
partition p23 values (to_date('08-Jan-2017'),to_date('29-Jan-2017')) indexing off read only,
partition p31 values (to_date('15-Jan-2017'),to_date('22-Jan-2017')) indexing off read only,
partition p32 values (to_date('15-Jan-2017'),to_date('29-Jan-2017')) indexing off read only,
partition p33 values (to_date('15-Jan-2017'),to_date('05-Feb-2017')) indexing off read only
)
including rows where uk_flag is null or (date_start > to_date('01-feb-2017','dd-mon-yyyy'))
online
update indexes (
t1_client_idx local indexing partial,
t1_resort_idx local,
t1_ukflag_idx indexing partial
)
;
Key Points
- partition by list (date_start, date_end) — partitioned by a multi-column list
- automatic — if data arrives for which there is no existing partition a new one will be created
- indexing off — some of my partitions (the pre-defined (oldest) ones) will be subject to partial indexing
- read only — some of my partitions (the pre-defined (oldest) ones) will be made read only
- including rows where — some of my rows will disappear during copying [1]
- online — Oracle will be journalling the data while I copy and will apply the journal after the copying completes
- update indexes – specify some details about indexes [2]
- local — some of the rebuilt indexes will be local
- indexing partial — some of the rebuilt indexes will not hold data (viz: for the partitions declared “indexing off”)
I’ve footnoted a couple of the entries:
[1] – the copy is done read-consistently so data inserted while the copy takes place will still appear in the final table, even if it looks as if it should have failed the including rows clause.
[2] – indexes which include the partition key will automatically be created as local indexes (but at this point you can declare them here as global, or globally partitioned if you want to). The manual is a little confusing at this point; it suggests that prefixed indexes will be created as local indexes but then defines “prefixed” to mean “contains the partition key” rather than the usual “starts with the partition key”.
Job done – except for
- the exhaustive tests that it’s been done correctly
- the load test to see how it behaves when lots of new holidays are being booked and current ones being modified
- a little bit of clearing up of “surprise” partitions that shouldn’t be there
- changing some of the automatically generated table partitions to be “indexing off” (if and when necessary)
Here are a few queries – with results – showing the effects this one statement had:
select count(*) from t1;
/*
COUNT(*)
----------
99773
-- some rows (old UK) have disappeared from the original 100,000
*/
select
index_name, partitioned, status, leaf_blocks, num_rows , indexing, orphaned_entries
from
user_indexes
where table_name = 'T1'
order by
partitioned, index_name
;
/*
INDEX_NAME PAR STATUS LEAF_BLOCKS NUM_ROWS INDEXIN ORP
-------------------- --- -------- ----------- ---------- ------- ---
T1_PK NO VALID 263 99773 FULL NO
T1_UKFLAG_IDX NO VALID 1 136 PARTIAL NO
T1_CLIENT_IDX YES N/A 149 62409 PARTIAL NO
T1_RESORT_IDX YES N/A 239 99773 FULL NO
-- Indexes: Local or global, full or partial.
*/
select
segment_type, segment_name, count(*)
from
user_segments
group by
segment_type, segment_name
order by
segment_type desc, segment_name
;
/*
SEGMENT_TYPE SEGMENT_NAME COUNT(*)
------------------ ------------------------- ----------
TABLE PARTITION T1 24
INDEX PARTITION T1_CLIENT_IDX 15
INDEX PARTITION T1_RESORT_IDX 24
INDEX T1_PK 1
INDEX T1_UKFLAG_IDX 1
-- One local index has fewer segments than the other
*/
set linesize 180
set trimspool on
column high_value format a85
break on index_name skip 1
set pagesize 200
select
index_name, status, leaf_blocks, num_rows, partition_name, high_value
from
user_ind_partitions
where
index_name = 'T1_CLIENT_IDX'
-- index_name like 'T1%'
order by
index_name, partition_position
;
/*
INDEX_NAME STATUS LEAF_BLOCKS NUM_ROWS PARTITION_NAME HIGH_VALUE
-------------------- -------- ----------- ---------- ---------------------- -------------------------------------------------------------------------------------
T1_CLIENT_IDX UNUSABLE 0 0 P11 ( TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
, TO_DATE(' 2017-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
)
UNUSABLE 0 0 P12 ( TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
, TO_DATE(' 2017-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
)
UNUSABLE 0 0 P13 ( TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
, TO_DATE(' 2017-01-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
)
UNUSABLE 0 0 P21 ( TO_DATE(' 2017-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
, TO_DATE(' 2017-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
)
UNUSABLE 0 0 P22 ( TO_DATE(' 2017-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
, TO_DATE(' 2017-01-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
)
UNUSABLE 0 0 P23 ( TO_DATE(' 2017-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
, TO_DATE(' 2017-01-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
)
UNUSABLE 0 0 P31 ( TO_DATE(' 2017-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
, TO_DATE(' 2017-01-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
)
UNUSABLE 0 0 P32 ( TO_DATE(' 2017-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
, TO_DATE(' 2017-01-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
)
UNUSABLE 0 0 P33 ( TO_DATE(' 2017-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
, TO_DATE(' 2017-02-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
)
USABLE 10 4126 SYS_P1528 ( TO_DATE(' 2017-01-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
, TO_DATE(' 2017-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
)
USABLE 10 4198 SYS_P1529 ( TO_DATE(' 2017-01-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
, TO_DATE(' 2017-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
)
USABLE 10 4211 SYS_P1530 ( TO_DATE(' 2017-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
, TO_DATE(' 2017-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
)
USABLE 10 4214 SYS_P1531 ( TO_DATE(' 2017-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
, TO_DATE(' 2017-02-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
)
USABLE 10 4195 SYS_P1532 ( TO_DATE(' 2017-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
, TO_DATE(' 2017-03-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
)
USABLE 10 4113 SYS_P1533 ( TO_DATE(' 2017-01-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
, TO_DATE(' 2017-01-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
)
USABLE 9 4027 SYS_P1534 ( TO_DATE(' 2017-01-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
, TO_DATE(' 2017-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
)
USABLE 10 4217 SYS_P1535 ( TO_DATE(' 2017-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
, TO_DATE(' 2017-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
)
USABLE 10 4167 SYS_P1536 ( TO_DATE(' 2017-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
, TO_DATE(' 2017-03-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
)
USABLE 10 4230 SYS_P1537 ( TO_DATE(' 2017-01-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
, TO_DATE(' 2017-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
)
USABLE 10 4113 SYS_P1538 ( TO_DATE(' 2017-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
, TO_DATE(' 2017-02-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
)
USABLE 10 4069 SYS_P1539 ( TO_DATE(' 2017-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
, TO_DATE(' 2017-03-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
)
USABLE 10 4215 SYS_P1540 ( TO_DATE(' 2017-01-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
, TO_DATE(' 2017-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
)
USABLE 10 4138 SYS_P1541 ( TO_DATE(' 2017-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
, TO_DATE(' 2017-02-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
)
USABLE 10 4176 SYS_P1542 ( TO_DATE(' 2017-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
, TO_DATE(' 2017-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
)
*/
I’ve limited the index partition output to the index with partial indexing enabled to show that its pre-defined partitions are marked as unusable and, as you can infer from the segment summary, those unusable index partition don’t have any segment space allocated to them.
Stress tests are left to the interested reader.
Update Feb 2020
I’ve just been reminded of a problem I discovered a few months after writing this note that might be relevant. If you load data into an interval partitioned table you may find that you need a very large PGA allocation for no obvious reason, and whatever’s going on in the PGA may result in a massive amount of I/O on the temporary tablespace. The problem relates in some way to the number of partitions that Oracle has to create automatically, and doesn’t appear when you’re loading a range-partitioned table.
I haven’t yet tested whether the same phenomenon appears when you try to modify a table to be interval partitioned, but it’s a test you should do before you commit to using the method on a production system so that you can allow for the necessary memory/space/IO.
Oracle Scratchpad 
[…] 12.2 you can: Convert a simple table to partitioned with multi-column automatic list partitions, […]
Pingback by Log Buffer #513: A Carnival of the Vanities for DBAs – Cloud Data Architect — June 13, 2017 @ 10:19 am BST Jun 13,2017 |
select count(*) from t1;
/*
COUNT(*)
———-
99773
— some rows (old UK) have disappeared from the original 10,000
*/
99,773 is more than 10,000.
Comment by buildingKofi — February 28, 2019 @ 9:51 am GMT Feb 28,2019 |
Thanks for the note, I’ll fix that as soon as I have a few moments on a safe machine.
It started with 1e5, so I must have missed a zero while typing the comment without noticing.
Comment by Jonathan Lewis — February 28, 2019 @ 10:22 am GMT Feb 28,2019 |
Now corrected – along with a few other typos and formatting details.
Comment by Jonathan Lewis — March 2, 2019 @ 2:53 pm GMT Mar 2,2019 |
[…] wrote a short note a little while ago demonstrating how flexible Oracle 12.2 can be about physically rebuilding a table online to introduce or change the partitioning while discarding […]
Pingback by Re-partitioning | Oracle Scratchpad — May 23, 2019 @ 11:46 am BST May 23,2019 |
[…] wrote a note a few years ago giving an example of converting a simple heap table to a partitioned table – while eliminating the data you don’t want […]
Pingback by Massive Deletes | Oracle Scratchpad — July 12, 2020 @ 7:36 pm BST Jul 12,2020 |
[…] about the “alter table … modify” command options that you can use to convert a non-partitioned table into a partitioned table, or change the partitioning of an already partitioned table (which may need extra preparatory […]
Pingback by Partitioning existing data | Oracle Scratchpad — May 29, 2021 @ 10:35 am BST May 29,2021 |
[…] makes it very easy to handle the task of re-engineering partitioned tables online with a single “alter table” […]
Pingback by Split Partition | Oracle Scratchpad — June 16, 2021 @ 12:33 pm BST Jun 16,2021 |
[…] data from a 222GB table with 900M rows. Since this is running on 11.2.0.4 none of the really nice options that appeared in 12.2 is available (and even then there might be time, space, or performance costs that would make the […]
Pingback by Quiz Night | Oracle Scratchpad — July 12, 2021 @ 10:24 am BST Jul 12,2021 |