Partitioning -- 15 : Online Modification of Partitioning Type (Strategy)

Oracle 18c introduces the ability to convert a Partitioned Table from one Type to another -- e.g. from Hash Partitioning to Range Partitioning.  This is effectively a change of the Partitioning strategy for a table without actually having to manually rebuild the table.

I start with a Hash Partitioned Table.

SQL> create table customers(customer_id number, customer_name varchar2(200), customer_city_code number)
  2  partition by hash (customer_id) partitions 4;

Table created.

SQL> select partitioning_type from user_part_tables
  2  where table_name = 'CUSTOMERS'
  3  /

PARTITION
---------
HASH

SQL> select partition_name from user_tab_partitions
  2  where table_name = 'CUSTOMERS'
  3  /

PARTITION_NAME
--------------------------------------------------------------------------------
SYS_P221
SYS_P222
SYS_P223
SYS_P224

SQL>
SQL> insert into  customers
  2  select dbms_random.value(1,1000001), dbms_random.string('X',25), mod(rownum,5)
  3  from dual
  4  connect by level < 1000001
  5  /

1000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','CUSTOMERS');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows
  2  from user_tab_partitions
  3  where table_name = 'CUSTOMERS'
  4  /

PARTITION_NAME     NUM_ROWS
---------------- ----------
SYS_P221             250090
SYS_P222             249563
SYS_P223             250018
SYS_P224             250329

SQL>

I now want to convert this Hash Partitioned Table to a Range Partitioned Table online.

SQL> alter table customers
  2  modify
  3  partition by range (customer_id)
  4  (partition P_100K values less than (100001),
  5   partition P_200K values less than (200001),
  6   partition P_300K values less than (300001),
  7   partition P_400K values less than (400001),
  8   partition P_500K values less than (500001),
  9   partition P_600K values less than (600001),
 10   partition P_700K values less than (700001),
 11   partition P_800K values less than (800001),
 12   partition P_900K values less than (900001),
 13   partition P_1MIL values less than (1000001),
 14   partition P_2MIL values less than (2000001),
 15   partition P_MAXVALUE values less than (MAXVALUE))
 16  online;

Table altered.

SQL>
SQL> select partitioning_type
  2  from user_part_tables
  3  where table_name = 'CUSTOMERS'
  4  /

PARTITION
---------
RANGE

SQL> exec dbms_stats.gather_table_stats('','CUSTOMERS');

PL/SQL procedure successfully completed.

SQL> col high_value format a12
SQL> select partition_name, high_value, num_rows
  2  from user_tab_partitions
  3  where table_name = 'CUSTOMERS'
  4  order by partition_position
  5  /

PARTITION_NAME   HIGH_VALUE     NUM_ROWS
---------------- ------------ ----------
P_100K           100001           100116
P_200K           200001            99604
P_300K           300001            99941
P_400K           400001           100048
P_500K           500001            99841
P_600K           600001            99920
P_700K           700001           100081
P_800K           800001           100024
P_900K           900001           100123
P_1MIL           1000001          100302
P_2MIL           2000001               0
P_MAXVALUE       MAXVALUE              0

12 rows selected.

SQL>

The Hash Partitioned Table is now converted to a Range Partitioned Table.  The number of Partitions has been changed.  And the operation was performed online with the ONLINE keyword added to the ALTER TABLE ... statement.  The UPDATE INDEXES clauses can also be used to update existing Indexes on the Table.

Everyone should read this

An excellent article that anyone promising, developing, maintaining or using any system that is non-trivial should read :

https://embeddedartistry.com/blog/2019/4/1/what-can-software-organizations-learn-from-the-boeing-737-max-saga

Partitioning -- 14 : Converting a non-Partitioned Table to a Partitioned Table

Pre-12cRelease2, there were only three methods to convert a non-Partitioned Table to a Partitioned Table

(a) Create a new, empty, Partitioned Table and copy (using INSERT .... AS SELECT ... ) all the data from the non-Partitioned Table to the new, Partitioned Table (and subsequently rename the new Partitioned Table after renaming or dropping the old non-Partitioned Table)

(b) Create a new, empty, Partitioned Table and use EXCHANGE PARTITION to switch the non-Partitioned Table into the Partitioned Table (and then run subsequent SPLIT PARTITION or ADD PARTITION commands as needed to create the additional Partitions)

(c) Create an interim Partitioned Table and use DBMS_REDEFINITION to do an online copy of the data to the interim Partitioned Table and automatically switch the name at the end


12.2 introduced the ability to use ALTER TABLE  ... MODIFY PARTITION ... to convert a non-Partitioned Table to a Partitioned Table

I start with a non-Partitioned Table :

SQL> select table_name, partitioned
  2  from user_tables
  3  where table_name = 'SALES_DATA_NONPARTITIONED'
  4  /

TABLE_NAME                     PAR
------------------------------ ---
SALES_DATA_NONPARTITIONED      NO

SQL> select index_name, uniqueness, partitioned
  2  from user_indexes
  3  where table_name = 'SALES_DATA_NONPARTITIONED'
  4  /

INDEX_NAME                     UNIQUENES PAR
------------------------------ --------- ---
SALES_DATA_UK                  UNIQUE  NO

SQL> 

I then convert it to a Range-Partitioned Table.

SQL> alter table sales_data_nonpartitioned
  2  modify 
  3  partition by range (sale_date)
  4  (
  5  partition P_2015 values less than (to_date('01-JAN-2016','DD-MON-YYYY')),
  6  partition P_2016 values less than (to_date('01-JAN-2017','DD-MON-YYYY')),
  7  partition P_2017 values less than (to_date('01-JAN-2018','DD-MON-YYYY')),
  8  partition P_2018 values less than (to_date('01-JAN-2019','DD-MON-YYYY')),
  9  partition P_2019 values less than (to_date('01-JAN-2020','DD-MON-YYYY')),
 10  partition p_MAXVALUE values less than (MAXVALUE) 
 11  )
 12  online 
 13  update indexes
 14  /

Table altered.

SQL> 
SQL> alter table sales_data_nonpartitioned rename to sales_data;

Table altered.

SQL>
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'SALES_DATA'
  4  order by partition_position
  5  /

PARTITION_NAME   HIGH_VALUE
---------------- --------------------------
P_2015           TO_DATE(' 2016-01-01 00:00
P_2016           TO_DATE(' 2017-01-01 00:00
P_2017           TO_DATE(' 2018-01-01 00:00
P_2018           TO_DATE(' 2019-01-01 00:00
P_2019           TO_DATE(' 2020-01-01 00:00
P_MAXVALUE       MAXVALUE

6 rows selected.

SQL> 
SQL> select index_name, partitioned, uniqueness, status
  2  from user_indexes
  3  where table_name = 'SALES_DATA'
  4  /

INDEX_NAME                     PAR UNIQUENES STATUS
------------------------------ --- --------- --------
SALES_DATA_UK                  NO  UNIQUE    VALID

SQL> 

The SALES_DATA_NONPARTITIONED was converted to a Range Partitioned Table.  If I didn't have to rename the table (e.g. if the table name was actually, properly SALES_DATA only), then there would be no need to lock the table as the RENAME command does.