Thoughts on Integrating OLTP and Data Warehousing (Especially in Exadata 2)

Exadata is great for data warehousing. At this time, that's a claim much better supported by marketing and theory than by practice.

Exadata 2 is a suitable annual improvement over last year's Exadata 1. That's quite plausible. Oracle is outstanding for OLTP. That's borne out by vast amounts of experience, especially if by "outstanding" you mean "Gets the job done really, really well at a very high cost in terms of both licenses and labor." The Flash memory in Exadata 2 makes Oracle even better for OLTP.* That's plausible too. Worst-case is probably that Flash support doesn't really work well in those release, but will be cleaned up soon.** OLTP and data warehousing uses for Exadata don't interfere with each other. That one bears some discussion.*Oracle has repeatedly emphasized that the Flash memory in Exadata 2 is meant to speed up OLTP. By way of contrast, I've only noticed one vague claim that Flash memory helps data warehousing -- a reference to a doubling in "user scan rates", which perhaps was a slip of the marketing pen. **Oracle probably has been working on Flash memory support for a long time. But it's likely that Oracle didn't have a strategic commitment to Sun's specific technology until April of this year. After all, back in March it looked as if IBM would wind up owning Sun.

You might just want to run a separate brand of DBMS for your OLTP and data warehousing. Oracle thinks this is a terrible idea. (I disagree, as do a whole lot of analytic DBMS vendors -- Teradata, Netezza, Greenplum, Sybase, Vertica, Aster Data, Infobright, Kognitio, et al. -- and their customers.)

You may want to lay out or index your tables differently for OLTP and data warehousing. Materialized view capabilities as flexible as Oracle's should let you do that in a single database.

You may want to lay out your files differently for OLTP and data warehousing (e.g., in terms of block sizes). Oracle might claim that ASM (Automatic Storage Management) and, in particular, the "Stripe and Mirror Everything" option obviate that point. I'm far from convinced.

OLTP and analytic workloads step on each other's toes, Part 1. For example, analytic queries that call for table scans often don't mix well with OLTP operations that call for random reads and (especially) writes.* In principle, Flash memory could greatly reduce the problem, if the OLTP workload talks mainly to Flash, while Flash talks to disk mainly via microbatches. But I'll be quite surprised if Oracle has aced that challenge on the first try. More likely, a longish stretch of Bottleneck Whack-A-Mole lies ahead.

OLTP and analytic workloads step on each other's toes, Part 2. Even more fundamentally: If you don't have sufficiently good workload management tools, combining OLTP and analytic workloads is a ghastly performance idea, with OLTP slowing to a crawl while analytic queries rumble to completion. However, I'd think Oracle is in pretty good shape in that area.

*If this weren't a terribly difficult problem, Oracle, IBM, and/or Teradata -- all of which can do a reasonably decent job of mixing long and short queries in the same workload -- would probably have solved it years ago.