Home » Developer & Programmer » Designer » Date Partitioning and Index
Date Partitioning and Index [message #240722] Fri, 25 May 2007 10:13 Go to next message
amitsarna
Messages: 6
Registered: August 2006
Junior Member
Hi Guys,
I have a big audit database with a timestamp column (AUDIT_TIMESTAMP) containing dates of the form: 26/01/2007 12:51:07.123

If i range partition this by date e.g.

PARTITION BY RANGE(AUDIT_TIMESTAMP)
(PARTITION <TABLE> VALUES LESS THAN (to_date('12/05/2007', 'DD/MM/YYYY')) ...

I want to create a local prefixed index which only accounts for the date part of the partition key (audit_timestamp).

if i just use "audit_timestamp, col_a, col_b" as the index then it won't work i dont think because it'll take into account the time part of audit timestamp and since these are unique this will just be the data sorted by time. What i want is the data sorted by the date part of audit_timestamp and then further by col_a and col_b.

Is what i'm saying possible and if so is it efficient?

Cheers
Amit
Re: Date Partitioning and Index [message #240726 is a reply to message #240722] Fri, 25 May 2007 10:22 Go to previous message
amitsarna
Messages: 6
Registered: August 2006
Junior Member
(and also the field has to be a timestamp field, i know this would be easier if i just had a date field but i'd rather not change the table schema if possible ... )
Previous Topic: Table Design issue
Next Topic: Data type
Goto Forum:
  


Current Time: Thu Mar 28 03:10:30 CDT 2024