By Andy Rivenes-Oracle on Jun 17, 2016
I was recently asked by a customer, "is it possible to force a query to use the In-Memory column store?" and I thought it would be a good idea to share the answer here on the blog.
The first thing that comes to mind when you starting thinking about forcing how the Oracle Database will execute a query is Optimizer hints. There is an INMEMORY hint available, but that hint just enables the use of the In-Memory column store (IM column store) if the INMEMORY_QUERY parameter has been set to DISABLE (default is ENABLE). If we look closer at the documentation for the hint, we see that it says "This hint does not instruct the optimizer to perform a full table scan. If a full table scan is desired, then also specify the "FULL hint".
So it appears that the answer is that you have to use the FULL hint to ensure that the access method is a full table scan which is a requirement for accessing the IM column store. If the INMEMORY_QUERY has been set to DISABLE then you would also need to use the INMEMORY hint. Of course this also assumes that the IM column store has been enabled and that the object has been enabled for in-memory.