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