1

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.

Thanks

Improve this question
2
  • 3
    Oracle was dealing with 100s of billions of rows before Cassandra even existed.
    – Philᵀᴹ
    Dec 26, 2013 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, 2013 at 18:31

1 Answer 1

Reset to default
7

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.

Improve this answer
2
  • 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, 2013 at 18:35
  • 1
    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.
    – anon
    Dec 27, 2013 at 21:24

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