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.
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 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 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.
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.
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 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.
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.
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.
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.
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.
Andy Rivenes is a Senior Principal Product Manager at Oracle Corporation and is a Product Manager for Database In-Memory. Andy has been working with Oracle products since 1992 and spent a large part of his career managing Oracle databases for both custom and E-Business Suite applications. Andy now spends his time helping customers implement Database In-Memory and providing information about Database In-Memory through blogs, conferences, and customer visits.