A join group is a group of between 1 and 255 columns that are frequently joined.

The table set for the join group includes one or more internal tables. External tables are not supported. When the IM column store is enabled, the database can use join groups to optimize joins of populated tables.

The columns in the join group can be in the same or different tables. For example, if the sales and times tables frequently join on the time_id column, then you might create a join group for (times(time_id), sales(time_id)). If the employees table often joins to itself on the employee_id column, then you could create the join group (employees(employee_id)).

When you create a join group, the database invalidates the current In-Memory contents of the tables referenced in the join group. Subsequent repopulation causes the database to re-encode the IMCUs of the tables with the common dictionary. For this reason, Oracle recommends that you first create the join group, and then populate the tables.

Create join groups using the CREATE INMEMORY JOIN GROUP statement. To add columns to or drop columns from a join group, use an ALTER INMEMORY JOIN GROUP statement. Drop a join group using the DROP INMEMORY JOIN GROUP statement.

CREATE INMEMORY JOIN GROUP deptid_jg (hr.employees(department_id),hr.departments(department_id));

In certain queries, join groups eliminate the performance overhead of decompressing and hashing column values.

Without join groups, if the optimizer uses a hash join but cannot use a Bloom filter, or if the Bloom filter does not filter rows effectively, then the database must decompress IMCUs and use an expensive hash join. To illustrate the problem, assume a star schema has a sales fact table and a vehicles dimension table. The following query joins these tables, but does not filter the output, which means that the database cannot use a Bloom filter:

SELECT v.year, v.name, s.sales_price
FROM   vehicles v, sales s
WHERE  v.name = s.name;

The following figure illustrates how the database joins the two data sets.

Figure 8-1 Hash Join without Join Group

Description of "Figure 8-1 Hash Join without Join Group"

The database performs a hash join as follows:

1. Scans the vehicles table, decompresses the rows that satisfy the predicate (in this case, all rows satisfy the predicate because no filters exist), and sends the rows to the hash join

2. Builds a hash table in the PGA based on the decompressed rows

3. Scans the sales table and applies any filters (in this case, the query does not specify filters)

4. Processes matching rows from the IMCUs and then sends the rows to the join

   When the hash join can consume row sets from the probe side (in this case, the sales table), the row sets sent by the table scan are in compressed form. Depending on whether the local dictionary or join group is leveraged to find matching rows from the build side, the hash join either decompresses the rows or leaves them uncompressed.

5. Probes the hash table using the join column, which in this case is the vehicle name

If a join group exists on the v.name and s.name columns, then the database can make the preceding steps more efficient, eliminating the decompression and filtering overhead. The benefits of join groups are:

• The database operates on compressed data.

• The database avoids hashing on the join key and probing the hash table, which requires comparing the join keys of the probe rows and hashed rows.

  When a join group exists, the database stores codes for each join column value in a common dictionary. The database builds a join group array using dictionary codes. Every array element points to a build-side row stored in the hash area (typically, PGA memory). During the probe, each probe row has a code associated with the join key. The database uses this code to search the array to determine whether a pointer exists in the array element. If a pointer exists, then there is a match; otherwise, there is no match.

• The dictionary codes are dense and have a fixed length, which makes them space efficient.

• Optimizing a query with a join group is sometimes possible when it is not possible to use a Bloom filter.

In a join group, the database compresses all columns in the join group using the same common dictionary.

Joins on columns in a join group typically see a performance benefit.

At join group creation, the database does the following:

• Caches the hash of the dictionary values for the join key columns

• Caches the binary representation of the NUMBER data for the join key columns

• Encodes columns with the same common dictionary

A join on columns in a join group always uses the first two optimizations to improve performance. For example, if the optimizer chooses a hash join, then the query uses the cached hash values to probe the bloom filter. If the query uses an IM aggregation join, then the query uses the cached binary number to index into the key vector.

A hash join may or may not use dictionary encodings. When dictionary encodings are present in at least one column of the hash join, the query can leverage the encodings in the following ways:

• Join group-aware hash join

  Both columns in the hash join carry common dictionary encoding data during runtime. The execution plan must show either a parallel hash join plan without any distribution involved from both sides of the hash join, or a serial hash join plan.

• Encoding-aware hash join

  One fact table column in the hash join carries dictionary encoding data during runtime. The execution plan must show either a parallel hash join without any distribution from the right side of the hash join, or a serial hash join plan. In some cases, if the common dictionary has good compression ratio, and if a parallel hash join plan cannot leverage a join group-aware hash join (for example, in a parallel broadcast-none plan), then the query can use an encoding-aware hash join for the common dictionary.

In a SQL Monitor report, the following fields show dictionary usage: Columnar Encodings Observed, and Columnar Encodings Leveraged. The statistics are cumulative. In a parallel hash join, the fields summarize statistics collected from all child processes involved in executing a row source. In the context of the local dictionary in an IMCU, the statistics show the number of encoding IDs observed from the right child row source and the number of encodings leveraged by the join. If a hash join on a single process leverages the common dictionary, then Columnar Encodings Leveraged shows the number of encodings leveraged in the join.

The following table indicates the possible values for Columnar Encodings Observed and Columnar Encodings Leveraged, and what the combinations mean.

For example, if the report shows that the Columnar Encodings Leveraged field is 4 (for example, because the parallel degree is 4) but the Columnar Encodings Observed field is absent, then the query leveraged the join group for the hash join. If the Columnar Encodings Observed field is 4 but the Columnar Encodings Leveraged field is absent, then dictionary encodings existed, but the query did not use them.

Various factors can prevent a query from engaging an encoding-aware hash join. Factors include the following:

• The compression ratio of the common dictionary is suboptimal.

• The query observes too many row sets passed from the table scan without a common dictionary.

• The build-side row length is too large.

• The build-side rows cannot fit into PGA memory.

• The build side has duplicate join keys.

Define join groups using the CREATE INMEMORY JOIN GROUP statement.

CREATE INMEMORY JOIN GROUP 
  sh.sales_products_jg (sh.sales(prod_id), sh.products(prod_id));

ALTER TABLE sh.sales INMEMORY;
ALTER TABLE sh.products INMEMORY;

COL OWNER FORMAT a3
COL NAME FORMAT a10
COL STATUS FORMAT a20

SELECT OWNER, SEGMENT_NAME NAME,
       POPULATE_STATUS STATUS
FROM   V$IM_SEGMENTS;

no rows selected

SELECT /*+ FULL(s) NO_PARALLEL(s) */ COUNT(*) FROM sh.sales s;
SELECT /*+ FULL(p) NO_PARALLEL(p) */ COUNT(*) FROM sh.products p;

COL OWNER FORMAT a3
COL NAME FORMAT a10
COL PARTITION FORMAT a13
COL STATUS FORMAT a20

SELECT OWNER, SEGMENT_NAME NAME, PARTITION_NAME PARTITION,
       POPULATE_STATUS STATUS, BYTES_NOT_POPULATED
FROM   V$IM_SEGMENTS;

OWN NAME       PARTITION     STATUS               BYTES_NOT_POPULATED
--- ---------- ------------- -------------------- -------------------
SH  SALES      SALES_Q3_1998 COMPLETED            0
SH  SALES      SALES_Q4_2001 COMPLETED            0
SH  SALES      SALES_Q4_1999 COMPLETED            0
SH  PRODUCTS                 COMPLETED            0
SH  SALES      SALES_Q1_2001 COMPLETED            0
SH  SALES      SALES_Q1_1999 COMPLETED            0
SH  SALES      SALES_Q2_2000 COMPLETED            0
SH  SALES      SALES_Q2_1998 COMPLETED            0
SH  SALES      SALES_Q3_2001 COMPLETED            0
SH  SALES      SALES_Q3_1999 COMPLETED            0
SH  SALES      SALES_Q4_2000 COMPLETED            0
SH  SALES      SALES_Q4_1998 COMPLETED            0
SH  SALES      SALES_Q1_2000 COMPLETED            0
SH  SALES      SALES_Q1_1998 COMPLETED            0
SH  SALES      SALES_Q2_2001 COMPLETED            0
SH  SALES      SALES_Q2_1999 COMPLETED            0
SH  SALES      SALES_Q3_2000 COMPLETED            0

COL JOINGROUP_NAME FORMAT a18
COL TABLE_NAME FORMAT a8
COL COLUMN_NAME FORMAT a7

SELECT JOINGROUP_NAME, TABLE_NAME, COLUMN_NAME, GD_ADDRESS 
FROM   DBA_JOINGROUPS;

JOINGROUP_NAME     TABLE_NA COLUMN_ GD_ADDRESS
------------------ -------- ------- ----------------
SALES_PRODUCTS_JG  SALES    PROD_ID 00000000A142AE50
SALES_PRODUCTS_JG  PRODUCTS PROD_ID 00000000A142AE50

To determine whether queries are using the join group, you can use either a graphical SQL Monitor report (recommended) or a SQL query that uses the DBMS_SQLTUNE.REPORT_SQL_MONITOR_XML function.

RAC now supports dictionaries that are global across the RAC cluster.

A common dictionary is instance-specific on single-instance Oracle databases and on Oracle RAC One Node databases. On these systems, join group aware hash joins are only enabled for joins that do not involve row distribution.

An Oracle RAC cluster supports a distributed, global dictionary that is shared across instances in a RAC cluster. This enables you to leverage join group aware hash joins for distributed joins on RAC. When you issue a CREATE INMEMORY JOIN GROUP statement on a RAC database, a distributed RAC-level global dictionary is created as part of the object population.

Consider the example below. A join group is created on the columns sales.dealer_key and dealership.dealership_key, which are used as join keys between the two tables. When you create an in-memory join group comprising these tables, a common RAC-level global dictionary is created for these columns. As you can see below, you do not need to do anything differently in order to benefit from this enhancement.

create inmemory join group jg1 (sales(dealer_key), dealership(deadlership_key));

Queries that access these columns can then take advantage of the join group.

select sum(revenue) from sales s, dealership d where s.dealership_key = d.dealership_key;

Distributed RAC-level global dictionaries can enhance query performance. Performance runs with join group aware hash joins leveraging RAC-level global dictionaries demonstrate significant gains on the SSB (Star Schema Benchmark).

In-Memory deep vectorization is a SIMD-based framework that supports vectorization for higher-level query operators in the query plan. The framework includes optimizations such as SIMD, hardware acceleration, and pipelined execution.

The In-Memory vectorized joins feature is key to the deep vectorization framework. Using SIMD vector processing, the framework optimizes aspects of hash joins such as hashing, building, probing, and gathering. 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. The optimization is enabled by default, but you can disable it by setting the INMEMORY_DEEP_VECTORIZATION initialization parameter to false.

The In-Memory deep vectorization framework deconstructs high-level, complex SQL operators such as hash joins into smaller kernel-sized units.

The deconstructed kernels are suitable for SIMD vectorization techniques. The database executes the kernels in a pipelined fashion to accelerate the overall operation.

The vectorized joins feature is a key aspect of the In-Memory deep vectorization framework.

An In-Memory vectorized join works as follows:

1. At run time, the database decides whether a hash join would benefit from In-Memory deep vectorization. If so, the database proceeds to the next step.

   Note:

   Because selection of the vectorized joins operation occurs at runtime, the execution plan does not show the optimization.

2. The database pushes down join processing to the scan operators for evaluation directly on In-Memory columnar data formats.

3. The operation uses a SIMD-optimized hash table data structure instead of a traditional hash table.

4. The database determines matched rows from the left and right side of the join, and sends them back to the parent SQL operator using vectorization techniques.

An In-Memory vectorized join may leverage In-Memory features such as the following:

• Join groups

  If a join group is declared, then join processing with deep vectorization may be significantly faster.

• IM dynamic scans

  Join processing is parallelized further in scan operators using lightweight threads.

• Aggregation pushdown

  Aggregations on top of joins are vectorized with the join operation.

• In-Memory columnar compression formats

  The vectorized joins feature heavily leverages columnar data formats.

The deep vectorization framework is enabled when the INMEMORY_DEEP_VECTORIZATION initialization parameter is true, which is the default value.

Queries containing the following are supported by the In-Memory Deep Vectorization Framework:

• Multiple join key columns
• Semi join and outer join
• These function-based aggregations:

Aggregations are also fully supported. This includes:

• Columns from the build side, or combination of build and probe sides.
• Simple expressions grouping columns or aggregates.

This example indicates how a hash join benefits from deep vectorization.

In-Memory Deep Vectorization enhancements now enable support for complex queries performing multi-table joins and aggregations.

As with other aspects of In-Memory Deep Vectorization , vectorization for multi-level joins is transparent to the user, requiring no plan changes.

Multiple join key columns in joins like the following are supported.

select * from build, probe 
where build.key1 = probe.key1
and build.key2 = probe.key2 
and …

select orders_order_id, avg(lineitem_extendedprice)
from orders, lineitem
where orders.zipcode = lineitem.zipcode
and orders.city = lineitem.city
group by orders.order_id;

Types of Multi-Level Joins Supported by the Optimizer

There are two types of multi-level joins generated by the optimizer:

• Left Deep Joins

  The result of each join node feeds the Build side of its parent join

• Right Deep Join

  The result of each join node feeds the Probe side of its parent join

Description of the illustration inmem_multileveljoins.eps