DBA Blogs

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)
.
.
.

Del.icio.us
Digg