IM aggregation improves performance of queries that join relatively small tables to a relatively large fact table, and aggregate data in the fact table. This typically occurs in a star or snowflake query.

Both row-store tables and tables in the IM column store can benefit from IM aggregation.

Example 9-1 VECTOR GROUP BY

Consider the following query, which performs a join of the customers dimension table with the sales fact table:

SELECT c.customer_id, s.quantity_sold, s.amount_sold 
FROM   customers c, sales s
WHERE  c.customer_id = s.customer_id 
AND    c.country_id = 'FR';

When both tables are populated in the IM column store, the database can use SIMD vector processing to scan the row sets and apply filters. The following figure shows how the query uses vector joins. The optimizer converts the predicate on the customers table, c.country_id='FR' into a filter on the sales fact table. The filter is country_id='FR'. Because sales is stored in columnar format, the query only needs to scan one column to determine the result.

Figure 9-1 Vector Joins Using In-Memory Column Store

Description of "Figure 9-1 Vector Joins Using In-Memory Column Store"

IM aggregation benefits certain star queries when sufficient system resources exist. Other queries may receive little or no benefit.

The optimizer decides whether to use vector transformation based on the size of the key vector (that is, the distinct join keys), the number of distinct grouping keys, and other factors. The optimizer tends to choose this transformation when dimension join keys have low cardinality.

Oracle Database uses VECTOR GROUP BY aggregation to perform data aggregation when the following conditions are met:

• The queries or subqueries aggregate data from a fact table and join the fact table to one or more dimensions.

  Multiple fact tables joined to the same dimensions are also supported assuming that these fact tables are connected only through joins to the dimension. In this case, VECTOR GROUP BY aggregates fact table separately and then joins the results on the grouping keys.

• The dimensions and fact table are connected to each other only through join columns.

  Specifically, the query must not have any other predicates that refer to columns across multiple dimensions or from both a dimension and the fact table. If a query performs a join between two or more tables and then joins the result to the fact, then VECTOR GROUP BY aggregation treats the multiple dimensions as a single dimension.

VECTOR GROUP BY aggregation does not support the following:

• Semi-joins and anti-joins across multiple dimensions or between a dimension and the fact table

• Equijoins across multiple dimensions

• Aggregations performed using the DISTINCT function

A key vector is a data structure that maps between dense join keys and dense grouping keys.

A dense key is a numeric key that is stored as a native integer and has a range of values. A dense join key represents all join keys whose join columns come from a particular fact table or dimension. A dense grouping key represents all grouping keys whose grouping columns come from a particular fact table or dimension. A key vector enables fast lookups.

SQL> SELECT country_id FROM locations;
 
CO
--
IT
IT
JP
JP
US
US
US
US
CA
CA
CN

0
0
0
0
1
1
1
1
0
0
0

0
0
2
2
1
1
1
1
0
0
0

Typically, VECTOR GROUP BY aggregation processes each dimension in sequence, and then processes the fact table.

When performing IM aggregation, the database proceeds as follows:

1. Process each dimension sequentially as follows:

   1. Find the unique dense grouping keys.

   2. Create a key vector.

   3. Create a temporary table (CURSOR DURATION MEMORY).

   The following figure illustrates the steps in this phase, beginning with the scan of the dimension table in DFO 0, and ending with the creation of a temporary table. In the simplest form of parallel GROUP BY or join processing, the database processes each join or GROUP BY in its own DFO.

   Figure 9-2 Phase 1 of In-Memory Aggregation

   
   Description of "Figure 9-2 Phase 1 of In-Memory Aggregation"

2. Process the fact table.

   1. Process all the joins and aggregations using the key vectors created in the preceding phase.

   2. Join back the results to each temporary table.

   Figure 9-3 illustrates phase 2 in a join of the fact table with two dimensions. In DFO 0, the database performs a full scan of the fact table, and then uses the key vectors for each dimension to filter out nonmatching rows. DFO 2 joins the results of DFO 0 with DFO 1. DFO 4 joins the result of DFO 2 with DFO 3.

   Figure 9-3 Phase 2 of In-Memory Aggregation

   
   Description of "Figure 9-3 Phase 2 of In-Memory Aggregation"

This section gives a conceptual example of how VECTOR GROUP BY aggregation works.

This section contains the following topics:

• Sample Analytic Query of a Star Schema
  This sample star schema in this scenario contains the sales_online fact table and two dimension tables: geography and products.
• Step 1: Key Vector and Temporary Table Creation for geography Dimension
  In the first phase of VECTOR GROUP BY aggregation for this query, the database creates a dense grouping key for each city/state combination for cities in the states of Washington or California.
• Step 2: Key Vector and Temporary Table Creation for products Dimension
  The database creates a dense grouping key for each distinct category/subcategory combination of an Acme product.
• Step 3: Key Vector Query Transformation
  In this phase, the database processes the fact table.
• Step 4: Row Filtering from Fact Table
  This phase obtains the amount sold for each combination of grouping keys.
• Step 5: Aggregation Using an Array
  The database uses a multidimensional array to perform the aggregation.
• Step 6: Join Back to Temporary Tables
  In the final stage of processing, the database uses the dense grouping keys to join back the rows to the temporary tables to obtain the names of the regions and categories.