Skip to Main Content
  • Questions

  • What indexes and partitions are best for manage insert and update of 100 cr records in a table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Adinarayana.

Asked: March 04, 2024 - 5:18 am UTC

Last updated: March 21, 2024 - 12:32 am UTC

Version: 12c

Viewed 1000+ times

You Asked

In our project we have to create a table that should contain around 100 cr records and 60% records will be update in later time..date is the key factor on this table.. To manage this table what type of indexes and partitions are best suitable to improve the performance of the table when querying from the table. Thanks in Advance.

and Connor said...

This is sort of impossible to answer because of the lack of information.

For example, if a common demand is the need to query one month of data ... then maybe monthly partitions are fine.
But if you need to query a week of data, then maybe weekly is better. Perhaps daily or even hourly are important requirements ... we don't know.

But partitioning and indexes both fall into the same category namely:

1) identify the usage of the table both in terms of how you need to store the data, how long, do you archive it, what are query patterns and on what predicates
2) what are the response time/resource constraints for the things in (1). For example, if you need to query a month of data, *but* you run that query once per month over night, then maybe you dont need it to be fast. Work out the parts of the app that have the most sensitive requirements.
3) index/partition to meet the needs of (2)

Rating

  (1 rating)

Comments

A reader, March 20, 2024 - 12:52 pm UTC

If we have the table partitioned by date, and i am going to access other column along with date . in this case do we need to create a composite partition (Month,ColA) as local or global. OR since the Month is partitioned, Should i go with indexing ColA alone as local?

Which is the best assuming the volume of the record count for each partition is more than 15 Million records
Connor McDonald
March 21, 2024 - 12:32 am UTC

The answer is "maybe".

Seriously - I'm not trying to be a 'smart ass' here. It comes back to my initial answer

Partitioning and indexes both fall into the same category namely:

1) identify the usage of the table both in terms of how you need to store the data, how long, do you archive it, what are query patterns and on what predicates
2) what are the response time/resource constraints for the things in (1). For example, if you need to query a month of data, *but* you run that query once per month over night, then maybe you dont need it to be fast. Work out the parts of the app that have the most sensitive requirements.
3) index/partition to meet the needs of (2)
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

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.