« dramatic differences of in memory scanning performance on range queries | Main | Why the In-Memory Column Store is not used (I) »
Monday
Oct062014

Why the In-Memory Column Store is not used (II)

Now after some research - I detected one simple rule for provoking In-Memory scans :

Every full table scan on a table which is populated completely into the in-memory column store is done in-Memory;

For example you can disable index access on primary key columns using another data type for joining. in this example an FTS was caused using a VARCHAR2 based join instead of the RAW data type of the primary key column.

before : data types correct/aligned - Indexes used

 WHERE (Ae.Customerid) = Hextoraw(Uqj.Objectid)
   AND Uqj.Context = Hextoraw(p_Contextid)
   AND Ae.Executor = Hextoraw(p_Employeeid)

after: an Index could not be used anymore as RawToHex() on both sides of comparison violate the type compatibility of the PK column.

 WHERE (Ae.Customerid) = Rawtohex(Uqj.Objectid)
   AND Rawtohex(Uqj.Context) = Rawtohex(p_Contextid)
   AND Rawtohex(Ae.Executor) = Rawtohex(p_Employeeid)

Conclusion

Dependent on the CBOs estimations there is still an index affinity and and an in-memory affinity on tables which have indexes and are populated to the column store. Filtering an indexed column may result in an ordinary index scan discriminating the in-memory full table access. A root cause for this is that neither IMCUs achieve indexed like access nor there are in-memory indexes available.

So it seems Oracle In-Memory approach is balanced between read and transaction performance. A well sorted and indexed (over the complete column store) in-memory column store would generate a lot more overhead on DML operations.

Another aspect are hints. With introducing the In-Memory column store Oracle silently behaves different. A FULL hint does not scan the tables segment till it's high watermark but does a scan In-Memory if the table is populated in memory - also think about the PARALLEL hint. Will it be implemented with direct reads on the data file if the table is opted to be in-memory?

/Karl

References (2)

References allow you to track sources for this article, as well as articles that were written in response to this article.

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>