Partitioning -- 10 : Virtual Column Based Partitioning

Oracle 11g supports specifying a Virtual Column as the Partition Key.

A Virtual Column is a column where the value is derived on the basis of an expression on other columns or sql/plsql functions.  The actual value is not stored in the block holding the row but is computed when the row is retrieved.

For example :

create table my_sales_table
(invoice_id  number primary key,
 invoice_date date,
 sale_year number(4) generated always as ( to_number(to_char(invoice_date,'YYYY')) ) virtual,
 customer_id number,
 sale_value number
 )
/

insert into my_sales_table
 (invoice_id, invoice_date, customer_id, sale_value)
 values
 (1,sysdate,100,10200)
/

select invoice_id, invoice_date, sale_year
 from my_sales_table
/

INVOICE_ID  INVOICE_DATE  SALE_YEAR
         1  28-NOV-18          2018

The MY_SALES_TABLE can be List, Range or Hash Partitioned on SALE_YEAR.

drop table my_sales_table;

create table my_sales_table
(invoice_id  number primary key,
 invoice_date date,
 sale_year number(4) generated always as ( to_number(to_char(invoice_date,'YYYY')) ) virtual,
 customer_id number,
 sale_value number
 )
 partition by list(sale_year)
 (partition p_2018 values (2018),
  partition p_2019 values (2019),
  partition p_2020 values (2020)
  )
/

insert into my_sales_table
 (invoice_id, invoice_date, customer_id, sale_value)
 values
 (1,sysdate,100,10200)
/

select invoice_date, sale_year from my_sales_table partition (p_2018)
/

INVOICE_DATE  SALE_YEAR
28-NOV-18          2018

Thus, the SALE_YEAR value is not actually stored on disk, yet each partition has rows based on the SALE_YEAR (derived) value.

SQL Slowdown ? A short list of potential reasons

Jonathan Lewis has published a short list of potential reasons why you might see a slowdown in SQL execution.  With newer releases 12.2, 18c and 19c, the list may have to be expanded.

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

Partitioning -- 8 : Reference Partitioning

Like Interval Partitioning, another enhancement in 11g is Reference Partitioning.

Reference Partitioning allows you to use a Referential Integrity Constraint to equi-partition a "Child" Table with a "Parent" Table.

Here is a quick demonstration :

SQL> l
  1  create table orders
  2  (order_id  number primary key,
  3   order_date date not null,
  4   customer_id number)
  5  partition by range (order_date)
  6  (partition P_2017 values less than (to_date('01-JAN-2018','DD-MON-YYYY')),
  7   partition P_2018 values less than (to_date('01-JAN-2019','DD-MON-YYYY'))
  8* )
SQL> /

Table created.

SQL> 
SQL> l
  1  create table order_lines
  2  (line_unique_id  number primary key,
  3   order_id number not null,
  4   order_line_id  number,
  5   product_id  number,
  6   product_quantity number,
  7   constraint order_lines_fk foreign key (order_id)
  8    references orders(order_id)
  9  )
 10* partition by reference (order_lines_fk)
SQL> /

Table created.

SQL> 
SQL> col high_value format a28 trunc
SQL> col table_name format a16
SQL> col partition_name format a8
SQL> select table_name, partition_name, high_value
  2  from user_tab_partitions
  3  where table_name in ('ORDERS','ORDER_LINES')
  4  order by table_name, partition_position
  5  /

TABLE_NAME       PARTITIO HIGH_VALUE
---------------- -------- ----------------------------
ORDERS           P_2017   TO_DATE(' 2018-01-01 00:00:0
ORDERS           P_2018   TO_DATE(' 2019-01-01 00:00:0
ORDER_LINES      P_2017
ORDER_LINES      P_2018

SQL> 

Notice the "automatically" created Partitions for the ORDER_LINES ("Child") Table that match those for the ORDERS ("Parent") Table.

.
.
.