I've been told that there's an oracle in-memory option for 12c and that it uses columnar compression to get some great query speeds. I'd like to leverage it for some business intelligence stuff. I've been searching google and the only thing I can find are announcements and news articles. Does anyone have any code snippets or any directions on where I should look to implement this or learn more about it?
It's still in beta. It won't be released till the 18.104.22.168 patchset. As such, unless you're in the beta program, not much information is available yet.
I think the NDA has been lifted, as I saw Tom Kyte speak about it at Collaborate 14 in Las Vegas a couple of weeks ago, but, you can't actually use it yet.
Hope that helps.
The Oracle Database In-Memory Option is not released yet and there is no official documentation yet. Yesterday I attended a presentation from an Oracle product manager about this new option. We were not allowed to record the session but there was no NDA or any request to keep the information secret. Below is everything I can remember from the presentation, with a few opinions.
- Dual format: Data is stored in two memory structures, the buffer cache and the in-memory column storage. Everything you know about Oracle stays the same, this is just an EXTRA option for some data. It's up to you to choose the "interesting" columns and tables.
- Hybrid columnar compression: 2x-10x compression ratio since column data is usually repetitive. (Although I always cringe when I see numbers surrounding compression. It's depends entirely on the data.) This feature does not require Exadata (but they would really appreciate it if you bought one anyway).
- Why is it faster? Only the column needs to be scanned, and the column data is compressed and kind of partitioned. It allows processors to use SIMD instructions. (But I'm a little skeptical that such a high-level feature neatly maps to a hardware instruction.) In benchmarks a single core can scan billions of rows per second with the column format, instead of about 25 million rows per second with the row format.
- When is it faster? It can speed up column scans/filters, group by, joins, and even OLTP by removing the need for many extra indexes.
- Why not just index? The in-memory data structures generate no extra redo or undo and are much quicker to update and access than a regular index. Undo information necessary for consistency is stored in the in-memory column store itself.
- Column Partitioning/Indexing: Column blocks(?) store the min and max value and can do something similar to partition pruning. A column scan does not necessarily need to scan the full column.
- How to manage: DBA specifies which tables or columns to put in memory, to load them at startup or when needed, and to distribute or duplicate the data across RAC nodes. No "aging out", you specify all the data and it will just stop caching when it runs out of memory. Data can be manually freed from memory. It sounds like there are very simple commands, like "alter table table_name in_memory", "alter table table_name free memory". (The ability to distribute across nodes is interesting; I wonder if this departure from a shared-everything architecture introduces some complexity.)
- Transparent: No changes required to code. Everything will still work; if there's not enough memory for column storage Oracle will just use the regular row mechanisms. There are some new execution plan operations.
- Parallelism: Works with multiple cores and the in-memory parallel query feature, although it can be so fast that the thread overhead is slower than the advantage of extra cores. Works across RAC, data can be distributed or duplicated.
- Start up time: It takes a long time to load all the data in memory, but that process is asynchronous. The database starts up just as fast, and is usable before the in-memory data structures are loaded.
- Licensing: Not specified yet, but it will very likely costs extra money per core.
- Available: With 22.214.171.124, which may be out in June/July/August.
- Not from Times Ten: This product was built over the last 4 years for the core Oracle database, it is not related to Times Ten. This option will also be available for Times Ten, using the same code. This is "Database In-Memory Option", not an "In-Memory Database".
- Disclaimers: Since the product is not released all of this information could change. And of course I may have misunderstood something or remembered something incorrectly.
I was skeptical of this feature at first but now I see how it's a cool idea. But it's going to require extra memory, configuration, a specific workload, and a lot of money.
Larry Ellison is hosting a Live Webcast on June 10 @ 10:00 a.m. PT – 11:30 a.m. PT to present the introduction of the Oracle Database In-Memory Option. I m sure you will find out more info during this launch and afterwards.