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
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)