Recent Blog Series on Partitioning

A recent series of posts on Partitioning

1.  1.  Introduction   (Aug-18)

2.  2.  Simple Range Partitioning  (Aug-18)

3.  3a.  Indexes on a Partitioned Table  (Aug-18)

4.  3b.  More Indexes on a Partitioned Table  (Aug-18)

5.  3c.  Unique Index[es] on a Partitioned Table  (Aug-18)

6.  3d.  Partial Indexing (in 11g)  (Sep-18)

7.  4.  Row Movement  (Sep-18)

8.  5.  List Partitioning  (Sep-18)

9.  6.  Hash Partitioning  (Sep-18)

10.  7.  Interval Partitioning  (Oct-18)

11.  8.  Reference Partitioning  (Nov-18)

12.  9.  System Partitioning  (Nov-18)

13.  10.  Virtual Column Based Partitioning  (Nov-18)

14.  11.  Composite Partitioning  (Dec-18)

15.  12.  Data Dictionary Queries  (Dec-18)

16.  13a.  Relocating a Partition  (Dec-18)

17. 13b.  Splitting a Partition  (Jan-19)

18.  13c.  Merging Partitions  (Mar-19)

19.  13d.  TRUNCATE and DROP Partitions and Global Indexes  (Mar-19)

20.  14.  Converting a non-Partitioned Table to a Partitioned Table  (Mar-19)

21.  15.  Online Modification of Partitioning Type (Strategy)  (Apr-19)

22.  16.  Hybrid Partitioning  (May-19)

Addition :

23.  17.  Partition by Expression (in 26.1)   (Feb-26)

Partitioning -- 16 : Hybrid Partitioning

Oracle 19c introduces Hybrid Partitioning whereby you can have external and internal Partitions co-existing.  External Partitions are on storage (filesystem) outside the database.

Let's say we have a List Partitioned table for the widgets that we manufacture. The table is Partitioned by WIDGET_CLASS_ID, based on an ISO standard.  So all companies that manufacture widgets adopt the same WIDGET_CLASS_ID:

SQL> desc widgets_list
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 WIDGET_CLASS_ID                                    VARCHAR2(5)
 WIDGET_ID                                          VARCHAR2(32)
 WIDGET_NAME                                        VARCHAR2(32)
 WIDGET_DESCRIPTION                                 VARCHAR2(128)

SQL>
SQL> l
  1  select table_name, partitioning_type, partition_count
  2  from user_part_tables
  3* where table_name = 'WIDGETS_LIST'
SQL> /

TABLE_NAME                       PARTITION PARTITION_COUNT
-------------------------------- --------- ---------------
WIDGETS_LIST                     LIST                    3

SQL>
SQL> l
  1  select partition_name,high_value, num_rows
  2  from user_tab_partitions
  3* where table_name = 'WIDGETS_LIST'
SQL> /

PARTITION_NAME   HIGH_VALUE         NUM_ROWS
---------------- ---------------- ----------
P_A              'A'                    1520
P_B              'B'                     520
P_C              'C'                     119

SQL>

Later, another widget manufacturer that manufactures widgets of CLASS_ID 'X' is acquired.  The WIDGETS_LIST table is in a non-Oracle database and is received as a CSV file.  We accept the CSV file onto a filesystem location :

sh-4.2$ pwd
/home/oracle/ACQUIRED_COMPANY
sh-4.2$ cat AC_Widgets_List.CSV
'X','ABCXX2','The1','cddfdaxx'
'X','XXD2','The2','dda3'
'X','XRC34','The3','ff33355312'
sh-4.2$

So, we have a CSV file "AC_Widgets_List.CSV" listing the widgets manufactured by this company. We want to add it to our WIDGETS_LIST table.

Enter user-name: / as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> create directory acquired_company as '/home/oracle/ACQUIRED_COMPANY';

Directory created.

SQL> grant read, write on directory acquired_company to hemant;

Grant succeeded.

SQL>
SQL> connect hemant/hemant@ORCLPDB1
Connected.
SQL>
SQL> l
  1  alter table widgets_list
  2  add external partition attributes (
  3  type oracle_loader
  4  default directory acquired_company
  5  access parameters (
  6  fields terminated by ','
  7  (widget_class_id, widget_id, widget_name, widget_description)
  8  )
  9* )
SQL> /

Table altered.

SQL>
SQL> l
  1  alter table widgets_list
  2  add partition P_ACQ_CO values ('X')
  3* external location ('AC_Widgets_List.CSV')
SQL> /

Table altered.

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

PL/SQL procedure successfully completed.

SQL>
SQL> l
  1  select partition_name, high_value, num_rows
  2  from user_tab_partitions
  3  where table_name = 'WIDGETS_LIST'
  4* order by partition_position
SQL> /

PARTITION_NAME                   HIGH_VALUE         NUM_ROWS
-------------------------------- ---------------- ----------
P_A                              'A'                    1520
P_B                              'B'                     520
P_C                              'C'                     119
P_ACQ_CO                         'X'                       3

SQL>
SQL> l
  1* select * from widgets_list partition (P_ACQ_CO)
SQL> /

WIDGET_CLASS WIDGET_ID                        WIDGET_NAME
------------ -------------------------------- --------------------------------
WIDGET_DESCRIPTION
--------------------------------------------------------------------------------
'X'          'ABCXX2'                         'The1'
'cddfdaxx'

'X'          'XXD2'                           'The2'
'dda3'

'X'          'XRC34'                          'The3'
'ff33355312'


SQL>

The rows in the "AC_Widgets_List.CSV" file are now visible as rows in a *Partition* in our Oracle Table WIDGETS_LIST.
Of course, these being external, cannot be modified by INSERT/UPDATE/DELETE DML.

The External Attribute Type that I used is ORACLE_LOADER to use the SQL Loader libraries on a filesystem file.  Oracle 19c also supports ORACLE_DATAPUMP, ORACLE_HDFS and ORACLE_HIVE to reference files stored in other types of storage.

Hybrid Partitions are supported with single-level Range and List partitioning methods.  ALTER TABLE to ADD, DROP and RENAME Partitions is supported.

An External Partition can be Exchanged with an External Non-Partitioned Table only.
.
.
UPDATE :  Later, if I update the CSV file (using an external editor) to remove the quotation mark :

sh-4.2$ cat AC_Widgets_List.CSV
X,ABCXX2,The1,cddfdaxx
X,XXD2,The2,dda3
X,XRC34,The3,ff33355312
sh-4.2$

SQL> l
  1* select * from widgets_list partition (P_ACQ_CO)
SQL> /

WIDGET_CLASS_ID  WIDGET_ID                        WIDGET_NAME
---------------- -------------------------------- --------------------------------
WIDGET_DESCRIPTION
------------------------------------------------------------------------------------
X                ABCXX2                           The1
cddfdaxx

X                XXD2                             The2
dda3

X                XRC34                            The3
ff33355312


SQL>

So, it is possible to edit the External Partition using other methods (here I used "vi" on Linux)
.
.
.

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.