Database Partitioning

Partitioning

What is it?
Splitting storage of data based on a key into different tables. End users of data don't generally "see" the partitioning, but do receive performance benefits. The key should be a value that is always available, such as user id.

Oracle Partitioning
Oracle partitioning creates sub tables for a given table and is defined when the table is created. Records are put into sub tables based on a key defined when the table is created.

CREATE TABLE mytable(
my_id NUMBER( 20 ) NOT NULL,
...
)
PARTITION BY HASH (MY_ID) -- Hash on MY_ID
PARTITIONS ???
STORE IN (tablespace_name)
/

Oracle partitioning limits:
- cannot span across hosts
- can't do an update to a row that would change which sub table the row is stored in-- if you want to move the record to another subtable, you must deleted the row and insert it again

Partitioning schemes
hash(column_name) (must understand Oracle hash function well to debug)
integer_column mod 10 (easy to debug, but can only span 2 or 5 sub tables)
integer_column mod 12 (harder to debug, but can be spanned across 6,4,3 or 2 sub tables)

Logical Partitioning/Sharding
It is possible to implement partitioning at the data access layer. Hibernate calls this sharding.

Mixing of sharding and partitioning
Temporal data

Uses of partitioning
Partitioning by geographic location (so user data is stored near their physical location)
partition by date