SQL & PL/SQL

Hi everyone

There's a situation on a project I'm working on whereby we have a large-ish table that contains ~16 million records. The data in this table is date-based from approx 2003 up until today. The number of records for each year varies quite a lot with the earlier years containing tens of thousands of records right up until 2014 which contains about 5 million records. The data in this one large table needs to be processed and then migrated to another table, but in test runs the performance of the processing drops almost exponentially once the record count goes above ~100,000 (i.e. to start with it processes at around 400 records/second but after 100,000 records it drops down and down until it's running at ~40 records per second which causes big problems due to there only being a limited time window available to do the processing).

My personal preference would be to sort out the processing so it didn't slow down as there's obviously some issue with it!! However, it has been decreed from on high that there isn't enough time to do this and so the "solution" is to break the large table down into multiple smaller tables with 100K rows in each which will each then get processed in turn...so I guess ~160 tables. (I fully appreciate that this is a terrible solution and I have voiced my concerns but my hands are tied and there's nothing I can do to change it). The tables should contain the records in date order, so if the number of records for each year are along the lines of:

• 2003: 40K
• 2004: 50K
• 2005: 80K
• 2006: 300K
• 2007: 500K
• etc...

...then the tables would be created/populated as follows:

• table 1: 100,000 records from:
  • 2003 (1-40K)
  • 2004 (1-50K)
  • 2005 (1-10K)
• table 2: 100,000 records from:
  • 2005 (10-80K)
  • 2006 (1-30K)
• table 3: 100,000 records from:
  • 2006 (30-130K)
• table 4: 100,000 records from:
  • 2006 (130-230K)
• table 5: 100,000 records from:
  • 2006 (230-300K)
  • 2007 (1-30K)
• etc...
  

What I'm wondering is what would be the best way of creating this set of smaller tables? Again, I realise just how dreadful this approach is, but I can't change it I'm afraid.

Oracle version is 11GR2 and the platform is Windows Server 2012.

Thanks...