Posted by We’re being asked to store more and more data, yet keep backup windows, query performance and the like unchanged, no matter how much we store. As a result, more and more database shops are needing to partition their data. The problem is – partitioning data is a significant restructure of the data, which thus incurs a large outage and the accompanying planning and coordination.
Unless you’re on 12.2.
Here’s a demo where we can take an existing table and
- convert it to partitioned on a range scheme, also using intervals so we don’t need to worry about ongoing range creation maintenance, and
- take one of the existing indexes, and convert it to a local index to mirror the table partitioning scheme, and
- take the other existing index, and globally partition it.
Oh…and of course, we will do the whole thing online without service disruption
Oh…and of course, we will do it with a single SQL command.
Ab…So….Lute….Ly awesome !
SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 22 09:53:37 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Dec 19 2016 13:38:54 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production
SQL> create table T as
2 select d.*
3 from dba_Objects d,
4 ( select 1 from dual
5 connect by level <= 20 ) 6 where d.object_id is not null; Table created. SQL> create index IX on t ( object_id );
Index created.
SQL> create index IX2 on t ( created, object_name );
Index created.
SQL> alter table T modify
2 partition by range (object_id) interval (10000)
3 (
4 partition p1 values less than (20000)
5 ) online
6 update indexes
7 ( ix local,
8 ix2 global partition by range (created)
9 (
10 partition ix2_p1 values less than (date '2016-08-01'),
11 partition ix2_p2 values less than (maxvalue)
12 )
13 );
Table altered.
SQL> select partition_name
2 from user_tab_partitions
3 where table_name = 'T';
PARTITION_NAME
--------------------------------------------------------------------------------------------------------------------------------
P1
SYS_P1145
SYS_P1146
SYS_P1147
SYS_P1148
SYS_P1149
SYS_P1150
7 rows selected.
SQL> select index_name, partition_name
2 from user_ind_partitions
3 where index_name like 'IX%';
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
IX P1
IX SYS_P1145
IX SYS_P1146
IX SYS_P1147
IX SYS_P1148
IX SYS_P1149
IX SYS_P1150
IX2 IX2_P1
IX2 IX2_P2
9 rows selected.
SQL>
Learning is not a spectator sport 
A great example of why I like the Oracle Cloud.
This was asked for since the initial days of release 8.0 and the initial partitioning availability.
It took until now – when Oracle has to suffer itself the mayhem everyone has been going through since then – for them to do something about it!
Better late than never. And good on Cloud for forcing them to wake up to the reality of using their software in production environments instead of Powerpoint!
🙂
Ooooooo…. I can see this being so very, very useful to me! Now, when is 12.2 being released for on-premise? 😉
I’ll ask my boss’s boss’s boss’s boss’s boss’s boss’s boss’s boss’s boss’s boss’s boss’s boss’s boss’s boss…
🙂
Another challenge that we see performance during this partitioning.