Thanks for the question, GP.
Asked: November 29, 2014 - 7:11 pm UTC
Answered by: Tom Kyte - Last updated: November 30, 2014 - 4:26 pm UTC
Category: Database - Version: 188.8.131.52.0
Viewed 1000+ times
We have a NV pair table (I know you hate this design) in our app and there is a corresponding history version of this table. The current & history tables are currently having 166321841 and 875505705 row rspectively. The tables are only going to grow faster since we are integrating with more systems as we speak.
We are not in a position to do away with this table altogether since we act as an aggregator of data from several sources and this design provides an easy way to store the data. The search performance obviously is not great when the user has to query on 2 or more NV pairs but the design still serves to provide for a simplified search UI - just select the attributes of interest and put in the values to look for.
So I had a few questions:
1. At what point would the number of rows start becoming an issue for Oracle? What i mean by that i any type of issue - Oracle can't sore any more rows, the search performance degrades drastically etc.
2. Would partitioning the table help in any way? FYI, all searches are always going against our current table.
3. What be your approach in this situation if
a. you are tasked with maintaining this application
b. you are building this from the ground up
As always, we are very much thankful to you for all your inputs.
Thank you very much.
and we said...
1) no practical limit, we have customers with trillions of rows in tables.
Is this answer out of date? If it is, please let us know via a Review
2) absolutely. think about maintenance. You might have to rebuild an index some day. Do you want to rebuild a 1,000,000,000 row index (think time, redo - if it fails, how much time did you just lose) or ten 100,000,000 row indexes, one after the other?
3) researching other possible implementations... As things grow and change over time - engineers need to use different approaches. The planes I fly in have wings that go straight out from the plane. That is because I fly at about 500 mph. A fighter jet at mach 2 would be going 1,500 mph. The wings on a fighter jet are significantly different in their architecture - because if they weren't it wouldn't work. We have different types of bridges in order to cross different types of chasms. We have different building styles for different types of builds (stick and brick for a 2 story house, steel girders in a frame for 30 story buildings).
As you scale up, the solutions that worked for trivial amounts of data will not necessarily work for large amounts of data.
If you are in a place that says "make it work but do not change code" - expect to have a bad day.