All about partitions

Automatic Partitioning

For those DBAs who had continually to remember adding new partitions in given intervals – or to always split the "overflow" which has "blown up" meanwhile, these are very good news: With Oracle 11g, there is a new feature called Automatic Partitioning! Define it once and lay back!

So where's the hook? It won't apply on existing objects. So even with your pretty large 27 terrabyte table, to use this you'd have to re-create (which requires another up-to 27 terrabytes temporarily if you cannot bring compression in). One must be able to define the automatic interval to be used (which pretty much restricts it to RANGE). Oh, and the "automatically created" partitions get interesting names (Guess what? 'SYS…'). But beside that? I don't know any further hooks – yet...

So here is how it works, giving an easy example:

CREATE TABLE sales (
 sales_date DATE,
 prod_id    NUMBER,
 …
) PARTITION BY RANGE (sales_date)
  INTERVAL (INTERVAL '1' MONTH)
  (PARTITION p1 VALUES LESS THAN ( to_date('2001-01-01','YYYY-MM-DD') )
;

An initial partition must be defined with the CREATE statement, to make the "break-points" clear (in the given example, this specifies to switch to a new partition at midnight to the new year). All other partitions will be automatically created as required.

Need this with an Oracle version before 11g? Yeah, that's possible. One just needs the appropriate triggers and stuff, to give you the idea …

Partition by Reference

What the heck … Right: Now we can advice Oracle to take the partition criteria for table X from table Y – if they are related, i.e. there's a foreign key pointing from X to Y:

CREATE TABLE sales (
  …
  CONSTRAINT fk_sales_01
     FOREIGN KEY (…) REFERENCES customers
)
PARTITION BY REFERENCE ( fk_sales_01 );
…

Pretty nice and convenient. But I did not yet check what happens if customers has to be re-partitioned …

SELECT from partition

Want to select from a specific partition only? Of course one could figure out the partition criteria and include them with the WHERE clause. But why complicate things unnecessarily? Starting with Oracle 11g, it's as easy as this:

SELECT *
  FROM partitioned_table
  PARTITION FOR ( to_date('2001-05-27','YYYY-MM-DD') );

2020-11-18

IzzySoft