The IM column store maintains copies of tables, partitions, and individual columns in a compressed columnar format that is optimized for rapid scans.

The IM column store resides in the In-Memory Area, which is an optional portion of the system global area (SGA). The IM column store does not replace row-based storage or the database buffer cache, but supplements it. The database enables data to be in memory in both a row-based and columnar format, providing the best of both worlds. The IM column store provides an additional transaction-consistent copy of table data that is independent of the disk format.

Use the INMEMORY clause in DDL statements to enable the IM column store at any of the following levels:

• Column (nonvirtual or virtual)

• Table (internal or external), materialized view, or partition

  Note:

  If you apply the INMEMORY attribute to a hybrid partitioned table, then the attribute applies only to the internal partitions.

• Tablespace

If the INMEMORY attribute is specified at the tablespace level, then all new tables and materialized views in the tablespace are enabled for the IM column store by default. In the context of Database In-Memory, population is the automatic transformation of row-based data on disk into columnar data in the IM column store. You can configure all or a subset of a database object's columns for population in the IM column store. Similarly, for a partitioned table or materialized view, you can configure all or a subset of the partitions for population.

For example, you might configure three tables from the sh schema for population into the IM column store: customers, products, and sales. The IM column store stores the data for each table by column rather than by row, and divides each column into separate row subsets. A special container called an In-Memory Compression Unit (IMCU) stores all columns for a subset of rows in a table segment.

Database In-Memory includes several performance optimizations for analytic queries.

Optimizations include:

• An expression is a combination of one or more values, operators, and SQL functions (DETERMINISTIC only) that resolve to a value. By default, the In-Memory Expression (IM expression) optimization enables the DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS procedure to identify and populate “hot” expressions in the IM column store. An IM expression is materialized as a hidden virtual column, but is accessed in the same way as a non-virtual column.

• A join group is a user-defined object that specifies a set of columns frequently used to join a set of tables. In certain queries, join groups enable the database to eliminate the performance overhead of decompressing and hashing column values.

• For aggregation queries that join small dimension tables to a large fact table, In-Memory Aggregation (IM aggregation) uses the VECTOR GROUP BY operation to enhance performance. This optimization aggregates data during the scan of the fact table rather than afterward.

• In the IM column store, repopulation is the automatic update of IMCUs after the data within them has been significantly modified. If an IMCU has stale entries but does not meet the staleness threshold, then background processes may instigate trickle repopulation, which is the gradual repopulation of the IM column store.

Availability is the degree to which an application, service, or function is accessible on demand.

Database In-Memory supports the following availability features:

• In-Memory FastStart (IM FastStart) reduces the time to populate data into the IM column store when a database instance restarts. IM FastStart achieves this by periodically saving a copy of the data currently populated in the IM column store on the disk in its compressed columnar format.

• Each node in an Oracle Real Application Clusters (Oracle RAC) environment has its own IM column store. It is possible to have completely different objects populated on every node, or to have larger objects distributed across all IM column stores in the cluster. In Engineered Systems, it is also possible to have the same objects appear in the IM column store on every node.

• Starting in Oracle Database 12c Release 2 (12.2), an IM column store is supported on a standby database in an Active Data Guard environment.

The columnar format provides fast throughput for scanning large amounts of data.

The IM column store enables you to analyze data in real time, enabling you to explore different possibilities and perform iterations. Specifically, the IM column store can drastically improve performance for queries that do the following:

• Scan many rows and applies filters that use operators such as <, >, =, and IN

• Select few columns from a table or a materialized view that has many columns, such as a query that accesses 5 out of 100 columns

• Select LOB columns using SQL operators

Columnar format uses fixed-width columns for most numeric and short string data types. This optimization enables rapid vector processing, which enables the database to answer queries faster.

Scans of the IM column store are faster than scans of row-based data for the following reasons:

• Elimination of buffer cache overhead

  The IM column store stores data in a pure, in-memory columnar format. The data does not persist in the data files (or generate redo), so the database avoids the overhead of reading data from disk into the buffer cache.

• Data pruning

  The database scans only the columns necessary for the query rather than entire rows of data. Furthermore, the database uses storage indexes and an internal dictionary to read only the necessary IMCUs for a specific query. For example, if a query requests all sales for a store with a store ID less than 8, then the database can use IMCU pruning to eliminate IMCUs that do not contain this value.

• Compression

  Traditionally, the goal of compression is to save space. In the IM column store, the goal of compression is to accelerate scans. The database automatically compresses columnar data using algorithms that allow WHERE clause predicates to be applied against the compressed formats. Depending on the type of compression applied, Oracle Database can scan data in its compressed format without decompressing it first. Therefore, the volume of data that the database must scan in the IM column store is less than the corresponding volume in the database buffer cache.

• Vector processing

  Each CPU core scans local in-memory columns. To process data as an array (set), the scans use SIMD vector instructions. For example, a query can read a set of values in a single CPU instruction rather than read the values one by one. Vector scans by a CPU core are orders of magnitude faster than row scans.

For example, suppose a user executes the following ad hoc query:

SELECT cust_id, time_id, channel_id
FROM   sales
WHERE  prod_id BETWEEN 14 and 29

When using the buffer cache, the database would typically scan an index to find the product IDs, use the rowids to fetch the rows from disk into the buffer cache, and then discard the unwanted column values. Scanning data in row format in the buffer cache requires many CPU instructions, and can result in suboptimal CPU efficiency.

When using the IM column store, the database can scan only the requested sales columns, avoiding disk altogether. Scanning data in columnar format pipelines only necessary columns to the CPU, increasing efficiency. Each CPU core scans local in-memory columns using SIMD vector instructions.

A Bloom filter is a low-memory data structure that tests membership in a set. The IM column store takes advantage of Bloom filters to improve the performance of joins.

Bloom filters speed up joins by converting predicates on small dimension tables to filters on large fact tables. This optimization is useful when performing a join of multiple dimensions with one large fact table. The dimension keys on fact tables have many repeat values. The scan performance and repeat value optimization speeds up joins by orders of magnitude.

An important aspect of analytics is to determine patterns and trends by aggregating data. Aggregations and complex SQL queries run faster when data is stored in the IM column store.

In Oracle Database, aggregation typically involves a GROUP BY clause. Traditionally, the database used SORT and HASH operators. Starting in Oracle Database 12c Release 1 (12.1), the database offered VECTOR GROUP BY transformations to enable efficient in-memory, array-based aggregation.

During a fact table scan, the database accumulates aggregate values into in-memory arrays, and uses efficient algorithms to perform aggregation. Joins based on the primary key and foreign key relationships are optimized for both star schemas and snowflake schemas.