Details: In-Memory Hybrid Scans

This page provides more detailed information about queries referencing both INMEMORY and NO INMEMORY columns behaving differently in Oracle Database 21c.

Before Oracle Database 21c, if a query referenced any column with the NO INMEMORY attribute, then the query accessed all data from the row store (buffer cache). Therefore, the table scan could not take advantage of columnar formats, predicate pushdown, and other In-Memory features.

Starting in Oracle Database 21c, queries that reference both INMEMORY and NO INMEMORY columns can access columnar data.

In some cases, an IM hybrid scan can improve performance by orders of magnitude. The greatest performance benefits occur when a query has selective filters. In this case, the IM column store can quickly filter out most rows so that the row store projects only a small number of rows.

To achieve optimal performance, the optimizer compares different access methods. If the optimizer chooses a table scan, then the storage engine automatically determines whether an IM hybrid scan performs better than a regular row store scan. The optimizer considers hybrid scans when the following conditions are met:

  • The predicate contains only INMEMORY columns.
  • The SELECT list contains an arbitrary combination of INMEMORY and NO INMEMORY columns.

An IM hybrid scan logically divides the work into two: one part processes the query on the IM column store, and the other part processes the query on the row store. In the execution plan, the operation named TABLE ACCESS INMEMORY FULL (HYBRID) indicates a hybrid scan. Note that if runtime statistics indicate that performance will be faster by accessing the row store only, then the database can disable the IM hybrid scan at runtime.