Conventional DML processes one row or array of rows at a time, and inserts rows below the high water mark. Regardless of whether the IM column store is enabled, the database processes DML using the buffer cache.

IMCUs are read-only. When a statement modifies a row in an IMCU, the IM column store records the rowid in the associated SMU.

A Column Compression Unit (CU) entry becomes stale when its value differs from the value in its corresponding journal entry. For example, a transaction may change an employee’s weekly salary from 1000 to 1200, but the actual value in the IMCU is still 1000. The transaction journal records the rowid of the stale row and its SCN.

A direct path load is an INSERT /*+APPEND*/ statement or a SQL*Loader operation in which DIRECT=true .

In a direct path load, the database writes formatted data blocks directly to the data files, bypassing the database buffer cache. The database appends the data above the high water mark, which is the boundary between used and unused space in a segment. Direct path loads operate are “all or nothing” operations: the operation either inserts all data or no data.

Figure 10-2 Direct Path Loads and the High Water Mark

Description of "Figure 10-2 Direct Path Loads and the High Water Mark"

When the segment is populated in the IM column store, a direct path load works as follows:

1. You load data using a CREATE TABLE AS SELECT or INSERT /*+APPEND*/ statement. Only the current session is aware of the DML.

2. You commit the statement.

3. The high water mark moves to encompass the new data, which alerts the IMCU that data is missing. V$IM_SEGMENTS.BYTES_NOT_POPULATED now indicates the size of the newly inserted data.

4. The IM column store manages repopulation based on the following algorithm:

   • If the affected object has a PRIORITY set to a value other than NONE, then the database repopulates the data.

   • If the affected object has a PRIORITY set to NONE, then the database repopulates at the next full scan of the object.

A partition exchange load is a technique that exchanges a table for a partition. An exchange load is almost instantaneous because it modifies metadata instead of data.

To perform an exchange load, follow these steps:

1. Create a nonpartitoned table, called a source table.

2. Load rows into the source table.

3. Exchange an existing table partition, called the target partition, with the table.

For the target partition to be populated in the IM column store after the exchange, the source table must be populated in the IM column store before the exchange. The following scenarios are possible, depending on the whether the target partition is populated:

• Before the exchange, the target partition is not populated in the IM column store. For example, the partition is empty.

  After the exchange, the source table is no longer populated in the IM column store. The source IMCUs are now associated with the target partition.

• Before the exchange, the target partition is populated in the IM column store.

  After the exchange, the source table remains populated in the IM column store.

Example 10-1 INMEMORY Partition Exchange Load

In this example, the sales table, which is partitioned, has the INMEMORY attribute set at the table level. All non-empty partitions in this table are currently populated. The sales_p042616 partition is currently empty. Your goal is to populate the empty partition sales_p042616 with data contained in text files. The following figure illustrates the before and after scenarios.

Figure 10-3 Partition Exchange

Description of "Figure 10-3 Partition Exchange"

To perform the exchange, do the following:

1. Create an external table sales_tmp_ext using the CREATE TABLE ... ORGANIZATION EXTERNAL statement.

   The external table does not reside in the database, and can be in any format for which an access driver is provided. The table is read-only.

2. Create a nonpartitioned table named sales_tmp_ld using CREATE TABLE ... AS SELECT * FROM sales_tmp_ext.

   The sales_tmp_ld table is not external, which means it stores rows in the data files.

3. Set the INMEMORY attribute in sales_tmp_ld using an ALTER TABLE statement.

   The sales_tmp_ld table is now marked as INMEMORY, but it is not yet populated into the IM column store.

4. Populate sales_tmp_ld into the IM column store by forcing a full table scan.

   For example, the following query forces a full scan:

   SELECT /*+ FULL(s) NO_PARALLEL(s) */ COUNT(*) FROM sales_tmp_ld s;

5. Exchange the sales_p042616 partition with the sales_tmp_ld table.

   For example, alter the sales table as follows:

   ALTER TABLE sales EXCHANGE PARTITION sales_p042616 WITH TABLE sales_tmp_ld;

After the exchange completes, the sales_p042616 partition is populated in the IM column store, and the sales_tmp_ld is no longer populated.

See Also:

Oracle Database VLDB and Partitioning Guide to learn more about partition exchange loads

Automatic repopulation takes two forms: threshold-based repopulation and trickle repopulation.

Automatic repopulation always checks stale journal entries and uses double buffering. However, repopulation has different triggers:

• Threshold-based repopulation

  The database repopulates IMCUs when the number of changes recorded in the transaction journal reaches an internal staleness threshold. Threshold-based repopulation occurs automatically when INMEMORY_MAX_POPULATE_SERVERS initialization parameter is set to a value other than 0.

• Trickle repopulation

  The IMCO (In-Memory Coordinator) background process periodically checks whether stale rows exist, and then adds IMCUs to a repopulation queue. This mechanism does not depend on meeting the staleness threshold. The INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT initialization parameter limits the number of background processes used for trickle repopulation. Setting this parameter to 0 disables trickle repopulation.

Trickle repopulation is analogous to Java garbage collection. The mechanism works as follows:

1. IMCO wakes up.

2. IMCO determines whether any population tasks need to be performed, including whether any stale entries exist in the transaction journal associated with an IMCU.

3. If IMCO finds stale entries, then it triggers a Space Management Worker Process (Wnnn) to create a new version of the IMCU.

   During IMCU creation, the database records the rowids of modified rows in the transaction journal.

4. IMCO sleeps for two minutes, and then returns to Step 1.

Figure 10-4 Trickle Repopulation

Description of "Figure 10-4 Trickle Repopulation"

For example, a database may be busy for 8 hours per day. Most SMUs contain a small number of transaction journal entries (below the staleness threshold). When the database is quiet, IMCO wakes up, checks the journals to determine which IMCUs have stale entries, and then uses trickle repopulation to refresh the IMCUs.

The algorithm that triggers repopulation is internal, and depends on several factors.

The principal factors affecting repopulation are as follows:

• Rate of DML changes

  As the number of modified rows increases, the percentage of stale columnar data increases. The transaction journal grows, increasing the need to use the buffer cache to satisfy queries.

• Type of DML operations

  Typically, inserts have less performance overhead than deletes and updates because inserts often go into a new data block.

• Location of modified rows within a data block

  Changes grouped within the same database block or table partition have less effect then changes distributed across an entire table. Versioning every IMCU has a greater impact than versioning a small number of IMCUs.

• Compression level applied to INMEMORY objects

  Because of double buffering, tables with higher compression levels incur more query and DML overhead during repopulation. For example, MEMCOMPRESS FOR CAPACITY HIGH incurs more overhead than MEMCOMPRESS FOR DML.

• Number of active worker processes

  As the number of worker processes increases, more work occurs in parallel. Consequently, the rate of repopulation increases.