This page provides more detailed information about queries referencing both
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
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
SELECTlist contains an arbitrary combination of
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.