Skip to Main Content
  • Questions

  • Guidance on table design for batch processing

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Chirayu.

Asked: May 29, 2020 - 10:13 am UTC

Last updated: May 29, 2020 - 1:38 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi Chris/Connor,

UseCase
---------
We need to design a table which will be used for batch processing for about 2.5M transactions at peak load.
This table will be used for processing 100k in a batch and once the processing is complete we will be deleting the records from this table. So there will be frequent insert and deletes.
Each 100K record will be identified a single entity called batch_id which will be the key attribute for that particular batch run.
So for 2.5M transactions there will be roughly 25 batches.

Question
--------
1. Considering frequent inserts and deletes do you think it will be a good idea to partition table based on batch_id columm and drop the partition once the processing is complete ?
2. Since there will be parallel processing, should this table be a Global Temporary table or the standard heap table ?
3. Any best practices for designing tables for batch processing

and Chris said...

1. Based on what you've said, partitioning on batch_id is worth looking into.

12.2 adds auto-list partitioning, which would make this easier to manage in your scenario. But as you're on 12.1, I'd start with interval-range partitioning.

2. I don't understand why you want to use a GTT?

As you've mentioned parallel processing, DIY parallelism may help you here:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4248554900346593542

3. Batch processing is a rather broad term; general advice may not apply (or make things worse!) in your scenario. If you have more specific questions regarding your process just ask.


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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

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