In-Memory Expressions

In-Memory Expressions (IM expressions) provide the ability to materialize simple expressions and store them in the In-Memory column store (IM column store) so that they only have to be calculated once, not each time they are accessed. They are also treated like any other column in the IM column store so the database can scan and filter those columns and take advantage of all Database In-Memory query optimizations like SIMD vector processing and IM storage indexes. The Database In-Memory Guide defines an In-Memory Expression as “… a combination of one or more values, operators, and SQL or PL/SQL functions (DETERMINISTIC only) that resolve to a value.” I often tell people when I’m presenting the feature that “the fastest way to do something is to not do it at all”[1], but the next fastest way to do something is to do it only once. I think that’s the easiest way to think about In-Memory Expressions.

There are actually two types of IM expressions, a user-defined In-Memory virtual column (IM virtual column) that meets the requirements of an IM expression, and automatically detected IM expressions which are stored as a hidden virtual column when captured. The automatically detected IM expressions are captured in the new Expression Statistics Store (ESS). IM expressions are fully documented in the In-Memory Guide so I’m not going to repeat that documentation here. Instead, let’s take a look at what an IM expression is, how to create them, and why IM expressions are one of the high-performance features of Database In-Memory. In this post, we will look at how to create user-defined IM virtual columns and how they can significantly improve query performance. In the next post, we will explore automatically detected IM expressions and the details of how they work.

Let’s start with an example. In our other blog posts, and in our Hands On Labs, we use a modified Star Schema Benchmark (SSB) schema. Many of the queries that we use have expressions in them. The following is an example:

SELECT
 lo_shipmode,
 SUM(lo_ordtotalprice),
 SUM(lo_ordtotalprice - (lo_ordtotalprice*(lo_discount/100)) + lo_tax) discount_price
FROM
 LINEORDER
GROUP BY
 lo_shipmode
ORDER BY
 lo_shipmode;

The following shows how the query ran on my test system:

(lo_ordtotalprice - (lo_ordtotalprice*(lo_discount/100)) + lo_tax)