Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Let me start with: I am not a DBA; I am a backend developer.

It has been suggested that, for a report schema, I implement a star schema where-in the fact table can have ~2B rows before being archived. ~1M rows a day will be added to said table. The information in the report can be no more than 15 minutes stale and page load time must be < 7s

The report to be offered will be a sort of "summary-and-drill-down" where in when a selected summary portion is selected a subset of the rows that made up said summary will be displayed in another section Example: enter image description here

I am inclined to move to a NoSQL store like Cassandra because I am really concerned about a fact table (and perhaps attribute tables) stretching to 2 Billion rows. Hoping someone here can shed some light on both the use of a star schema for this and how well I can expect Oracle (or any RDBMS, for that matter) to handle selecting and joining through a table with ~2B records.


share|improve this question

closed as off-topic by Mark Storey-Smith, RolandoMySQLDBA, Marian, Paul White, ypercubeᵀᴹ Dec 27 '13 at 13:25

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." – Mark Storey-Smith, RolandoMySQLDBA, Marian, ypercubeᵀᴹ
If this question can be reworded to fit the rules in the help center, please edit the question.

Oracle was dealing with 100s of billions of rows before Cassandra even existed. – Phil Dec 26 '13 at 22:46
"dealing with" isn't entirely what I am looking for. If I need to select 50 rows from somewhere in the center of a 2B records will it occur in < 10ms or will it takes several seconds? The difference is a matter of meeting my requirements or not. The table will be actively recording data. It's also not in my power to dictate hardware at all to where this will be deployed nor is it in my power to dictate license upgrades etc. If me/we deploy something and it suddenly requires our customer to drop mountains of oracle cash 6 months they will not be happy. – Christian Bongiorno Dec 27 '13 at 18:31

Row count isn't a great indicator of database size. I would not be worried about Oracle scaling to 2 billion rows. Relational databases can practically scale up into the many terabyte range. The ability to scale comes down to data model, hardware, and developer skill, user requirements, and budget. It's feasible to scale an Oracle (or SQL Server) data warehouse up into the 10s to 100s of terabytes.

Using a star schema is the right starting point for a relational data warehouse. There are a number of well thought out techniques for data warehousing outlined in The Data Warehouse Tookit. This will help you design a data warehouse that can scale.

At some point, you'll want to look into features that your vendor offers like partitioning, materialized views, and dimensional hierarchies. You should be able to put that off in your first attempt, though.

share|improve this answer
I am not concerned about on-disk size. It's the time it would take me to select a handful of records. For example, select f.* from attributes att, facts f where att.factId = f.factId and att.quarter = 4' limit 50; AKA: Give me all results for 4th quarter. I am going to talk to our in-house dba today and see what she says. Thanks all for the insight – Christian Bongiorno Dec 27 '13 at 18:35
Time to select data is related to the volume of data on disk, the pathway between CPU and disk, and the speed of the CPUs themselves. Designing large data warehouses is as much hardware design as it is database design. – Jeremiah Peschka Dec 27 '13 at 21:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.