Database DataWarehousing (MOSC)

MOSC Banner

When to use a hash partition

edited Nov 6, 2019 4:04AM in Database DataWarehousing (MOSC) 5 commentsAnswered

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center