Home » SQL & PL/SQL » SQL & PL/SQL » Unix Timestamps/Partitioning
Unix Timestamps/Partitioning [message #221400] Mon, 26 February 2007 10:17 Go to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Guys,

I have an issue and would appreciate your input please. There is a application running against my Oracle 10.2.0.3.0 databases (on Solaris 10) which scrapes the RADIUS logs (containing authentication data) and inserts them into a production database - schema called RADACCT. The logs contain timestamps in them and these timestamps are UNIX timestamps (e.g. 1172502634). I have written functions to convert this timestamp into an Oracle date and vice versa so no issue there - I can provide these if needed.

The timestamps are being inserted into a table called SESSIONS_ARCHIVE and here is the create DDL:-

SQL> desc sessions_archive
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(60)
SESSIONID VARCHAR2(12)
START_TIME NUMBER(10)
END_TIME NUMBER(10)
CLIENT_IP NUMBER(10)
NAS_IP NUMBER(10)
STATE NUMBER(38)
FIRST_PACKET NUMBER(10)
NAS_PORT VARCHAR2(20)
ACCTUNIQUE VARCHAR2(16)
DISCONNECT_CAUSE VARCHAR2(30)
UP_SPEED NUMBER(10)
DOWN_SPEED NUMBER(10)
CONNECT_INFO VARCHAR2(20)
TUNNEL_END NUMBER(10)

The two columns I am interested in are START_TIME and END_TIME - they are both NUMBER(10). This table has over 50 million rows and will grow by 1 million a day and is not partitioned. This table really needs partitioning and the queries which run against this table are date reliant queries and therefore I would like to partition via a range for the START_TIME and/or DATE_TIME. The application cannot be changed at all and therefore I have to find a way to achieve this within the Oracle database itself.

Maybe an after-insert trigger to convert the NUMBER(10) strings into Oracle DATA datatypes and partition on this new column?

Am I missing something simple? Any ideas? All help gladly received.

Thanks,

Ken.
Re: Unix Timestamps/Partitioning [message #221429 is a reply to message #221400] Mon, 26 February 2007 13:35 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
take a look at hash partitioning. If you want to be able to prune off / swap out old partitions based on time then hashing probably wouldn't be suitable. Function based indexes may be worth a look at.
Re: Unix Timestamps/Partitioning [message #221450 is a reply to message #221429] Mon, 26 February 2007 20:41 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You dont have to RANGE partition on a DATE column. What's wrong with range partitioning on the UTC dates?

Ross Leishman
Re: Unix Timestamps/Partitioning [message #221487 is a reply to message #221400] Tue, 27 February 2007 02:54 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Thanks for the ideas Chaps. I am going to go for Ross's idea - simple but neat.
THanks again,
Ken.
Re: Unix Timestamps/Partitioning [message #221848 is a reply to message #221400] Wed, 28 February 2007 12:32 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Just to add, since you will be adding partitioning, you may want to look at adding table compression as well. The two options are wonderfully effective together.
Previous Topic: Packages
Next Topic: 10G Migration (Code Changes)
Goto Forum:
  


Current Time: Thu Dec 08 12:43:11 CST 2016

Total time taken to generate the page: 0.26769 seconds