Thanks for the question, Akash.

Asked: January 06, 2020 - 11:12 pm UTC

Last updated: January 08, 2020 - 1:00 am UTC

Version: Oracle Database 12.1.0.2

Viewed 1000+ times

You Asked

Hi Tom,

We have multiple history tables in database, having columns to record the creation_time or last_update_time in epoch format. The data type for all those columns is number. Because we are getting a lot of history records in those tables, want to make range partition on monthly interval.
SQL> desc service_history
Name Null? Type
----------------------------------------- -------- ----------------------------
SERVICE_INSTANCE_ID NOT NULL VARCHAR2(100)
SUBSCRIPTION_EXPIRATION_TIME NOT NULL NUMBER(22)
PUBLIC_IDENTITY VARCHAR2(160)
PROPERTIES VARCHAR2(2000)
LAST_UPDATE_TIME NOT NULL NUMBER(22)
PCSCF_ADDRESS VARCHAR2(160)
REMOVE_REASON NOT NULL NUMBER(38)
REQUESTOR_ID VARCHAR2(128)
REQUESTOR_IP VARCHAR2(256)
ADDITIONAL_INFO VARCHAR2(512)
SUBSCRIPTION_LAST_UPDATE_TIME NOT NULL NUMBER(22)
APPLICATION_CATEGORY VARCHAR2(512)
SUBSCRIPTION_CREATION_TIME NUMBER(22)

is there any way i can create range interval partition based on number(epoch) data type.

and Connor said...

Yes it is possible.

You didn't define what epoch regime you are using, but lets assume (say) "seconds since 1970". That would mean for a month you are at:

SQL> select 30 * 86400 from dual;

  30*86400
----------
   2592000

seconds. Thus you could define your partitions as:

partition by range ( LAST_UPDATE_TIME )
interval (2592000)

But the name worries me - that looks like you are expecting that value to change regularly (ie, every time someone updates it). That means relocating the rows potentially (ie, we'll do a delete-insert behing the scenes) because rows might need to move from older to newer partitions. I'd have a long think about that and be doing some rigorous testing before proceeding.

Of course, ideally the best course of action is to use dates for dates, but I understand this might not be in your control. If the partition is *just* for maintenance reasons, ie, you don't expect to be *querying* based on the last_update_time etc, then you could look at adding a virtual column to resolve that number to a true date, and then partition on that column instead.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, January 07, 2020 - 7:00 pm UTC

Thanks Connor for your timely reply.

Its not actual table name, but columns are. Table have multiple columns, for illustration only showing the relevant ones.
Right i am using epoch time "seconds since 1970" in time columns. 1578421055 which is equivalent to Tuesday, January 7, 2020 10:17:35 AM GMT-08:00.
This table is used for Operational purpose i.e., "select" only to find the historical data. Records are inserted in those history tables after delete on the base tables, though there is no FK, all managed by application.
And luckily there would not be any row movements because of reasons explained above.

There are months have 31 days. So "interval(2592000)" will not work perfectly.

yes we will do "select" based on last_update_time.

January 08, 2020 - 1:00 am UTC

There are months have 31 days. So "interval(2592000)" will not work perfectly.

Neither does using a number for a date :-)

If you want accuracy to the month level, then you need to explicitly nominate each partition boundary....that is the cost of not using the right datatype.

You *could* get the precise months using the virtual column approach, but since you are selecting on last_update_time, you lose the benefit of partition elimination in this case.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

Administration

Range Partition on epoch(number) data type.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

Administration