Oracle Database 21c and Database In-Memory

Oracle Database 21c is now available on-premises and I thought it would be nice to review all of the new features that are part of Database In-Memory in Oracle Database 21c. Oracle Database 21c has been available in Oracle Cloud for quite a while but now it is available for on-premises customers as well. Here's a short summary of the new and enhanced features.

Enhanced Automatic In-Memory

The In-Memory (IM) column store has become the self-managing IM column store. Automatic In-Memory (AIM) in Oracle Database 21c adds a new ability to automatically manage all application objects without having to worry about how to determine which objects to populate. AIM will now consider all application objects as candidates for the IM column store and will populate the most active objects using access tracking and column statistics. No more having to figure out how to maximize the benefit of Database In-Memory for your application. AIM will take care of it for you.

In-Memory Vectorized Joins

In Oracle Database 21c a new In-Memory vectorization framework has been created. The new In-Memory vectorized joins feature uses this framework to accelerate hash join and group by aggregation operations on columns populated in the IM column store. With In-Memory vectorized joins a hash join is broken down into smaller operations that can be passed to the SIMD (Single Instruction Multiple Data) vector processor. This optimization can improve the performance of join processing by 100% or more. The In-Memory vectorized joins feature is transparent to the user, requiring no plan changes.

In-Memory Hybrid Columnar Scans

In order to save space it is possible to exclude columns of a table from being populated in the IM column store. In most instances this isn't an issue but every once in a while users may encounter a query which needs columns populated in the IM column store and columns that have not been populated, and are only available in the row store. In previous releases of Database In-Memory, such queries would run entirely against the row store. In Oracle Database 21c users can now use both! The optimizer can now elect to scan the In-Memory Column Store and only fetch projected column values from the row store if needed. This can result in significant improvements in performance.

Enhanced In-Memory External Table Support

In-Memory External Table support has been enhanced to fully support all external tables. This now includes support for partitioned external tables and hybrid partitioned tables.

In-Memory Spatial Support

Database In-Memory now supports Oracle Spatial in the IM column store. A spatial table can be populated in the IM column store using a new INMEMORY SPATIAL clause. This means that you no longer have to create and maintain conventional disk-based spatial indexes when spatial tables are stored using Database In-Memory. The in-memory spatial index  provides much faster query performance for SDO_FILTER() operations.

In-Memory Full Text Columns

In-Memory Full Text Columns supports the ability to populate non-scalar document objects such as text, XML and JSON in the In-Memory column store. This enables fast predicate evaluations without having to create separate domain indexes such as Oracle full text index, XML Search Index or JSON Search Index. This also allows an in-memory scan to evaluate both scalar and non-scalar data which means queries that access text, XML and JSON data can avoid accessing the row store, thereby further improving performance.

In-Memory Support for the new JSON data type

With the introduction of a native JSON data type in Oracle Database 21c, Database In-Memory provides the ability to populate JSON data type columns in the IM column store. Like previous JSON support, JSON data defined with the JSON data type is populated in a proprietary binary format that is optimized for query processing and can significantly improve JSON processing performance. The new JSON data type support is in addition to previous JSON support for VARCHAR2, BLOB and CLOB data using an IS JSON check constraint.

Base-Level Feature

Database In-Memory now supports a "Base Level" feature. There is a new BASE_LEVEL value for the INMEMORY_FORCE parameter. When this new value is set the INMEMORY_SIZE parameter can be set up to a value of 16GB without having to license the Database In-Memory option. In fact, when the BASE_LEVEL value is set you cannot set the INMEMORY_SIZE parameter larger than 16GB. If you do then Oracle will generate an error message. The 16GB limit applies at the container database (CDB) level. This means that all pluggable databases (PDBs) share the 16GB limit of the CDB. However, on RAC databases the Base Level feature allows a 16GB column store to be allocated on each RAC instance.

Cell Memory

For on-premises Oracle Exadata systems a new CELLMEMORY_LEVEL value has been added to the INMEMORY_FORCE parameter. This allows customers that have licensed Database In-Memory to take advantage of Database In-Memory columnar formats in Exadata Smart Flash Cache without having to allocate an IM column store in the SGA.

Changes to Automatic In-Memory, In-Memory External Tables and Memoptimize Rowstore licensing

These features are no longer restricted to Exadata. Non-Exadata environments can now make use of these features. See details in the Licensing Guide.

That is a lot of new features and enhancements. The Database In-Memory team has been busy! There are several ways to get more information about all of these new features. You can visit the documentation for Oracle Database 21c and we have already blogged about Automatic In-Memory and we've reviewed 21c new features for Database In-Memory in our Ask TOM Office Hours series.