Oracle Database In-Memory & the Optimizer
By Maria Colgan-Oracle on Jul 06, 2014
As we continue our series of Q&A style blogs on Oracle Database In-Memory, I've got a chance to go back to my old life as the optimizer lady as I've been inundated with questions about the In-Memory option and the Optimizer.
Below are the answers to the questions I gotten so far.
If there is something else you want to know about how these two fascinating pieces of the Oracle technology work together just email me and I will include it in an upcoming post.
Is there a new Optimizer to deal with queries against the In-Memory column store?
No, there is no new Optimizer in Oracle Database 18.104.22.168. But the existing cost-based Optimizer has been enhanced to make it aware of the In-Memory column store (IM column store) and the objects that reside in it. The cost model has been enhances to consider in-memory scans, joins and aggregations.
For example, If a query is looking up a small number of records (rows), and an index on the WHERE clause column exists, the Optimizer will select an index access path (via the buffer cache), as this execution plan will be both more efficient (lowest cost) and more scalable in a multi-user environment.
Alternately, for an analytic query that accesses a small number of columns from a large number of rows, the Optimizer will select a full table or partition scan via the In-Memory column store.
Are there new Optimizer hints for In-Memory?
Yes, the different aspects of the In-Memory option- in-memory scans, joins and aggregations - can be control at a statement or a statement block level via the use of optimizer hints. As with most optimizer hints, the corresponding negative hint for each of these is preceded by word ‘NO_’.
For example, the INMEMORY hint allows the scan of in-memory objects to be enabled at the statement block level.
Will the same optimizer trace event work with In-Memory objects?
Yes, the same Optimizer trace event (10053) will work, even if some or all of the tables accessed in a query reside in the In-Memory column store (IM column store).
Should I replace all of indexes with the In-Memory column store to make it easier for the Optimizer?
No, we do not recommend replacing all existing indexes with the In-Memory column store. Indexes provide an extremely efficient and scalable access path to table data when a small number of rows is requested. The In-Memory column store does not improve upon these types of queries. To ensure your applications perform as well as they do today, we recommend you leave the indexes that support referential integrity and OLTP accesses intact.