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.
I now want to convert this Hash Partitioned Table to a Range Partitioned Table online.
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.
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.