The In-Memory Area is an optional system global area (SGA) component within the database instance. It contains the In-Memory column store (IM column store), which stores tables and partitions in memory by using a columnar format that is optimized for rapid scans. The IM column store enables data to be simultaneously populated in the SGA in both the traditional row format (in the buffer cache) and a columnar format. The database transparently sends online transactional processing (OLTP) queries, such as primary key lookups, to the buffer cache and analytic and reporting queries to the IM column store. When fetching data, Oracle AI Database can also read data from both memory areas within the same query. The dual-format architecture does not double memory requirements. The buffer cache is optimized to run with a much smaller size than the size of the database.

You should populate only the most performance-critical data in the IM column store. To add an object to the IM column store, turn on the INMEMORY attribute for an object when you create or alter it. You can specify this attribute on a tablespace (for all new tables and views in the tablespace), table, partition, subpartition, materialized view, or subset of columns within an object.

The IM column store manages both data and metadata in optimized storage units, not in traditional Oracle data blocks. An In-Memory Compression Unit (IMCU) is a compressed, read-only storage unit that contains data for one or more columns. A Snapshot Metadata Unit (SMU) contains metadata and transactional information for an associated IMCU. Every IMCU maps to a separate SMU.

The Expression Statistics Store (ESS) is a repository that stores statistics about expression evaluation. The ESS resides in the SGA and also persists on disk. When an IM column store is enabled, the database leverages the ESS for its In-Memory Expressions (IM expressions) feature. An In-Memory Expression Unit (IMEU) is a storage container for materialized IM expressions and user-defined virtual columns. Note that the ESS is independent of the IM column store. The ESS is a permanent component of the database and cannot be disabled.

Conceptually, an IMEU is a logical extension of its parent IMCU. Just as an IMCU can contain multiple columns, an IMEU can contain multiple virtual columns. Every IMEU maps to exactly one IMCU, mapping to the same row set. The IMEU contains expression results for the data that is contained in its associated IMCU. When the IMCU is populated, the associated IMEU is also populated.

A typical IM expression involves one or more columns, possibly with constants, and has a one-to-one mapping with the rows in the table. For example, an IMCU for an EMPLOYEES table might contain rows 1-1000 for the column weekly_salary. For the rows that are stored in this IMCU, the IMEU calculates the automatically detected IM expression weekly_salary*52, and the user-defined virtual column quarterly_salary defined as weekly_salary*12. The third row down in the IMCU maps to the third row down in the IMEU.

The In-Memory Area is subdivided into two pools: 

The relative size of the two pools is determined by internal heuristics. The majority of the In-Memory area memory is allocated to the 1MB pool. The size of the In-Memory Area is controlled by the initialization parameter INMEMORY_SIZE (default 0) and must have a minimum size of 100MB. Starting in Oracle Database 12.2, you can increase the size of the In-Memory Area on the fly by increasing the INMEMORY_SIZE parameter via an ALTER SYSTEM command by at least 128MB. Note that it is not possible to shrink the size of the In-Memory Area on the fly.

Oracle AI Database In-Memory has a Base Level feature that allows you to use up to a 16GB column store without triggering any license tracking.

An in-memory table gets IMCUs allocated in the IM column store when the table data is first accessed or at database startup. An in-memory copy of the table is made by doing a conversion from the on-disk format to the new in-memory columnar format. This conversion is done each time the instance restarts because the IM column store copy resides only in memory. When this conversion is done, the in-memory version of the table gradually becomes available for queries. If a table is partially converted, queries can use the partial in-memory version and go to disk for the rest, rather than waiting for the entire table to be converted.

In-memory hybrid scans can access some data from the IM column store and some data from the row store when not all columns in a table have been populated into the IM column store. This improves performance by orders of magnitude over pure row store queries.

Automatic In-Memory enables, populates, evicts, and recompresses segments without user intervention. When INMEMORY_AUTOMATIC_LEVEL is set to HIGH, the database automatically enables and populates segments based on their usage patterns. This automation helps maximize the number of objects that can be populated into the IM column store at one time.

In response to queries and data manipulation language (DML), server processes scan columnar data and update SMU metadata. Background processes populate row data from disk into the IM column store. The in-memory coordinator process (IMCO) initiates the background population and repopulation of columnar data. The space management coordinator process (SMCO) and space management worker processes (Wnnn) do the actual populating and repopulating of data on behalf of IMCO. DML block changes are written to the buffer cache and then to disk. Background processes then repopulate row data from disk into the IM column store based on the metadata invalidations and query requests.

You can enable the In-Memory FastStart feature to write the columnar data in the IM column store back to a tablespace in the database in compressed columnar format. This feature makes database startup faster. Note that this feature does not apply to IMEUs. They are always populated dynamically from the IMCUs.

In-Memory deep vectorization can optimize complex SQL operators by pipelining the physical operators inside each SQL operator and vectorizing them using single instruction, multiple data ( SIMD) techniques. This feature is enabled by default but you can disable it by setting the INMEMORY_DEEP_VECTORIZATION initialization parameter to false.