When to use a hash partition
Oracle EE 12.1.0.2
I have two existing non-partitioned tables. I'm just listing the relevant columns, there are more.
Table1 - 700 million rows
ID
START_DT_GMT
DEVICE_ID
CLIENT_ID
PK = ID
Table2 - 2 billion rows
ID
TABLE1_ID
PK = ID
FK = TABLE1_ID with cascade delete enabled
I plan on converting Table1 to an interval partitioned table based on START_DT_GMT with an interval of 1 day. We get currently get 500,000 rows a day and we keep no data older than 5 years.
We delete data based either CLIENT_ID+START_DT_GMT or by DEVICE_ID. (Client data expires after a set period of time and individual devices are deleted).