Monday, June 9, 2014

Oracle In-Memory v/s SQLServer In-Memory. Are we talking about the same thing?

OLTP In-Memory is the new exciting RDBMS feature, one of those bringing lots of promises and that can probably be called a major new feature that you get at least once every 5 years in an RDBMS life.
Microsoft has release SQLServer 2014 with an In-Memory Feature and Oracle will be launching its In-Memory implementation in a new 12c patchset to be launched tomorrow 10 June 2014.

It's been a lot of talk around In-Memory since a couple of years. Though some In-Memory databases technologies have been there for a while, they are mostly dedicated for some type of processing, mainly for Analytics and less for OLTP or are proprietary to some environment leading to dangerous lock-in risks.
OLTP In-Memory options have been until now mainly high-end options such as Database Caching technologies available with Oracle Timesten.

So what do we have with the two main RDBMS players In-Memory offerings ? Are they comparable ?

Based on MSDN library on Microsoft MSDN Library on In-Memory limitations we see that some unsupported features with T-SQL are :

Contraints not supported, Filtered and UNIQUE indexes,  savepoints and a long list of natively compiled store procedures features that are not supported.

I have found a FAQs in this SQLmag article that give some answers on these limitations too.

Meaning that before trying the SQLServer In-Memory feature (an Enterprise, Developer, and Evaluation editions only feature), one must be prepared to review his code and even his model tables and indexes design for some cases !!

On Oracle side, based on what is being announced as per Larry Ellison own words:

"Everything runs. There are no changes to SQL. There are no changes to your applications. There are no functions that are restricted. Everything that works today, works with the in-memory option turned on."

"Queries run 100 times faster and updates double in speed, simply by throwing a switch."

It seems anyone using an Oracle Database (apparently available in the Standard version of the database but this has yet to be confirmed. <UPDATED Aug'14: Unfortunately, it's an EE option>), will benefit from this.
And if taken into account automatically by CBO (cost-based optimizer), DBA will be happy to have a new huge way to tune their Database and even avoid adding indexes as the In-Memory structure (dual Rows and Columns layouts compressed arrays of tables in memory) can turn some indexes useless, the In-Memory reads being much faster than any usual indexes.

Other and above what is claimed by each, what is important is the way the feature is implemented by-design in the Database and it is clear that Oracle has done a really good job here by augmenting the Database capabilities without any dependencies on the existing architecture. Feature rich to enrich and augment the whole database capabilities without compromise and not to just for the new feature alone to augment its commercial features list.

BUT Oracle In-Memory at time of writing is not available yet and only some hands-on and testing will confirm what is being announced by Larry !

UPDATE 23 JULY 2014 > 

More information on the new database release at This Oracle blog post
A Whitepaper on In-Memory is available on Oracle Technology Network site