You Asked
My application has tables that are 30-40 GB (130M to 480M records) and indexes (indices) that are 10-25 GB.
There is potential for significant growth.
Oracle documentation recommends considering partitions when tables grow to 2GB,
so I am trying to determine whether or not partitioning the tables will help.
A simplified view of the application: It receives items with an expiration date and keeps track of them. Items are kept for long term and are rarely accessed.
There are 15+ static tables that hardly change and 3 dynamic tables which records are added to and deleted from.
ItemTable (two indexes Key36BytesString and insertdate)
Key36BytesString, insertdate, expiredate, ...
ItemStatusTable (three indexes Key36BytesString, SequenceNumberKey and inserteddate). Inserteddate is close to insertdate of ItemTable but not equal
Key36BytesString, SequenceNumberKey, inserteddate, ...
ItemAuditTable
SequenceNumberKey, SequenceNumberAuditKey, action
Daily, around 400,000 items are inserted into ItemTable with different expiration dates.
On average, items expire in two years or eight years. Only a small number of items are referenced daily for purposes other than maintenance, around 1,400.
For each item, one record is added to the ItemTable, one or two records are added to the ItemStatusTable, and three or more records are added to the ItemAuditTable.
When inserted, items are processed and remain in the system until they expire and are deleted (there are grace periods involved).
Records are inserted into the ItemAuditTable when an item is INSERTED, UPLOADED, EXPIRES, and DELETED. It is hardly ever accessed except when an item is deleted and the records are cleaned up.
- Is partitioning the tables beneficial?
- If so, what type of partitioning provides the most benefit? To me Interval partitioning by month seems to make the most sense.
- Should ItemTable and ItemStatusTable be partitioned separately or should Interval-Reference be considered?
- Since ItemAuditTable is not accessed much would partitioning it provide any benefit?
and Connor said...
"Oracle documentation recommends considering partitions when tables grow to 2GB"
I would ignore this. My view is simpler: "Consider partitioning when the benefits outweigh the drawbacks", and obviously you can replace the word "partitioning" with anything.
Some examples:
- If I only ever access as massive table with a primary key - then partitioning for query performance gives me nothing no matter how big that table is. But if I have to archive data out of that table based on (say) a date range, then partitioning might save me a huge delete cost each week/month/year etc.
- If I access large chunks of the historical data with queries, but that old data is read-only, I might partition say that I can compress the older partitions but the leave the current data uncompressed.
- If I query on ranges based on a logical partitioning key (eg "show me sales for this month") then partitioning will give me some good performace benefits on the query.
- If I *really* aggressively insert into the table, hash partitioning the index might reduced or remove contention issues.
- If my audit log is *huge* and I want to move the older data onto cheaper high density storage, then partitioning is a candidate.
- If I join several tables, and each have a common partitioning key, then I might get query benefits using partition-wise join
- If I have an existing index on a table, then partitioning that table *might* make access via that index slower depending on whether I make the index local or global.
So there are LOTS of potential benefits but also some potential drawbacks.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment