1

I have a huge table with data and i need to partition the table based on the the below requirement.

The records will be inserted every half an hour for each id.we have around 5000 id's.

I have a id(datatype as number) and row_crt_dtm(datatype as date) columns and i always want to have the latest record .

for example,if i have id's 1,2 and for id 1 the latest date is this month and for id 2 the latest date is last month ,then i should have these records in one partition

I tried to create a virtual column partition by having the rank function but got the below error

ora-30483-window-functions-are-not-allowed-here

how can i partition the table to have the latest records in one partition.

I am using oracle 11g.

Thanks

MT0
  • 143,790
  • 11
  • 59
  • 117
user2899615
  • 31
  • 1
  • 1
  • 10
  • Make partition by ID (range or list) and subpartition by date (or vice versa) – Wernfried Domscheit Apr 24 '18 at 11:15
  • what do you want to happen when you insert a new record(a new latest) for an id? You need to move the previous latest in another partition. You want to have only two partitions in this table? – Florin Ghita Apr 24 '18 at 11:24
  • yes,i want to have the latest in one partition and also have subsequent partition based on the date ,so i can truncate the partition once it become aged. – user2899615 Apr 24 '18 at 14:03
  • Hi Wernfried - Thanks for the reply.If i subparittion by date.then i will not get the latest record in the latest partition,if the Id is not present for the current month – user2899615 Apr 24 '18 at 14:06

2 Answers2

1

I would partition by id and then sub-partition by date. Then you will always have the latest data for a given id in a partition (the most recent sub-partition for that id), but you will not have the latest values for all ids in the same partition. You can drop old sub-partitions easily.

If you really must have all the latest records in the same partition you could partition by date manually and have a job that checks the latest partition. When the job finds that there are multiple values for all ids it could compute a cut-off date and split the active partition. That can be done as an online operation, but even so there may be hickups. And what happens if you stop to get data for an id? Still, I can't see a better way. Perhaps it fits your bill?

ewramner
  • 5,810
  • 2
  • 17
  • 33
  • hi,There are 5k unique ID and if i create partition based on the ID will not be overhead in maintaining the partition .what is the maximum partition i can create – user2899615 May 07 '18 at 10:28
  • Way more than you need, https://docs.oracle.com/cloud/latest/db112/REFRN/limits003.htm#REFRN0043 says 1024k-1 (1048575). – ewramner May 07 '18 at 10:32
0

As a response to what you ask you can do something like this:

Have a flag as latest id. Have its date_time. Create two partitions: one with flag Y, another with flag N. Enable row movement for these partitions.

When you need insert an id, you have two options:

One is to update the flag to N for the current latest id and then insert the new row with flag Y(all these in a single transaction: update, insert, then commit),

Another is to have a trigger to update the existing latest row transparently when you insert the new row(but this idea with trigger is more complex and no so fast because you'll get a mutating table error which need to be treated with a special kind of construction - a collection with all ids... you can find examples here on SO).

Florin Ghita
  • 17,525
  • 6
  • 57
  • 76