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 
Leave a reply to Mark Hoxey Cancel reply