Skip to Main Content
  • Questions

  • Oracle - Max Rows in a NV Pair Table

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Question and Answer

Tom Kyte

Thanks for the question, GP.

Asked: November 29, 2014 - 7:11 pm UTC

Last updated: November 30, 2014 - 4:26 pm UTC

Version: 11.2.0.3.0

Viewed 1000+ times

You Asked

Hello Tom

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.
GP

and Tom said...

1) no practical limit, we have customers with trillions of rows in tables.

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.



We're not taking comments currently, so please try again later if you want to add 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

VLDB

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

VLDB

Documentation set on VLDB and Partitioning.