In-Memory Optimized Number Format Speeds Up Arithmetic Operations

In-Memory Optimized Arithmetic is an in-memory optimized NUMBER format for fast calculations using SIMD vector processing.  For tables compressed with QUERY LOW (which is the default), NUMBER columns can be encoded using this optimized format. 

Computations are a large portion of analytical workloads and with SIMD vector processing, aggregations and other arithmetic operations can be a lot faster. With the new in-memory optimized NUMBER format you can expect a significant performance boost for arithmetic computations. We have seen up to 9X better performance.

The new in-memory optimized NUMBER format is enabled through the INMEMORY_OPTIMIZED_ARITHMETIC initialization parameter.

As an example, let’s run the following query without using In-Memory optimized NUMBER format.

SQL> select sum(l_extendedprice), sum(l_orderkey), sum(l_partkey) from lineitem_num;
 
SUM(L_EXTENDEDPRICE) SUM(L_ORDERKEY) SUM(L_PARTKEY)
-------------------- --------------- --------------
          2.2958E+11      1.8005E+13     6.0023E+11
 
Elapsed: 00:00:00.53

Now set the initialization parameter INMEMORY_OPTIMIZED_ARITHMETIC to ENABLE (DISABLE is the default) as a user with the necessary privileges:

ALTER SYSTEM SET INMEMORY_OPTIMIZED_ARITHMETIC = ‘ENABLE’ SCOPE=BOTH; 

Now let’s run the query again to benefit from the In-Memory optimized number format:

SQL> select sum(l_extendedprice), sum(l_orderkey), sum(l_partkey) from lineitem_num;
 
SUM(L_EXTENDEDPRICE) SUM(L_ORDERKEY) SUM(L_PARTKEY)
-------------------- --------------- --------------
          2.2958E+11      1.8005E+13     6.0023E+11
 
Elapsed: 00:00:00.06

As you can see, in this example the speedup is about 9X.

Note that there is a some space overhead when using the in-memory optimized NUMBER format, which is why it is not enabled by default. This is because we must store both the original Oracle Database NUMBER data type in the column store, as well as the new  In-Memory optimized NUMBER type as an additional In-Memory Expression column (In-Memory Expressions were introduced in Oracle Database 12.2).

This space overhead depends on how many NUMBER columns exist in the table and can typically be around 10-15%. However, this overhead may be an acceptable tradeoff in exchange for much faster arithmetic aggregations.

Original publish date: April 13, 2018