Partitioning -- 9 : System Partitioning

System Partitioning, introduced in 11g, unlike all the traditional Partitioning methods, requires that all DML specify the Target Partition.  For a System Partitioned Table, the RDBMS does not use a "high value" rule to determine the Target Partition but leaves it to (actually requires) the application code (user) to specify the Partition.

In my opinion, this seems like the precursor to Oracle Database Sharding.

SQL> create table sys_part_table
  2  (id_column number,
  3  data_element_1 varchar2(50),
  4  data_element_2 varchar2(50),
  5  entry_date date)
  6  partition by SYSTEM
  7  (partition PART_A tablespace PART_TBS_A,
  8   partition PART_B tablespace PART_TBS_B,
  9   partition PART_C tablespace PART_TBS_C)
 10  /

Table created.

SQL> 

Notice that I did not specify a Partition Key (column).  The Partitions are not mapped to specific values / range of values in a Key column.

Any DML must specify the Target Partition.

SQL> insert into sys_part_table
  2  values (1, 'First Row','A New Beginning',sysdate)
  3  /
insert into sys_part_table
            *
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on
tables partitioned by the System method


SQL> 
SQL> !oerr ora 14701
14701, 00000, "partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method"
// *Cause:  User attempted not to use partition-extended syntax
//          for a table partitioned by the System method
// *Action: Must use of partition-extended syntax in contexts mentioned above.

SQL> 
SQL> insert into sys_part_table partition (PART_A)
  2  values (1, 'First Row','A New Beginning',sysdate)
  3  /

1 row created.

SQL> insert into sys_part_table partition (PART_B)
  2  values (2,'Second Row','And So It Continues',sysdate)
  3  /

1 row created.

SQL> 

I have to specify the Target Partition for my INSERT statement. This, obviously, also applies to DELETE and UPDATE statements.   However, I can run a SELECT statement without filtering (pruning) to any Target Partition(s) -- i.e. a SELECT statement that does not use the PARTITION clause will span across all the Partitions.

SQL> select * from sys_part_table;

 ID_COLUMN DATA_ELEMENT_1
---------- --------------------------------------------------
DATA_ELEMENT_2                                     ENTRY_DAT
-------------------------------------------------- ---------
         1 First Row
A New Beginning                                    13-NOV-18

         2 Second Row
And So It Continues                                13-NOV-18


SQL> 

With Tablespaces assigned to the Partitions (see the CREATE table statement above),  I  can have each Partition mapped to a different underlying Disk / Disk Group.
.
.
.