When the IM column store is enabled, the database can use join groups to optimize joins of tables populated in the IM column store.

A join group is a set of columns on which a set of tables is frequently joined. The column set contains one or more columns, with a maximum of 255 columns. The table set includes one or more internal tables. External tables are not supported.

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 a join group could be (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.

This section contains the following topics:

• How a Join Group Uses a Common Dictionary
  A common dictionary is a table-level, instance-specific set of dictionary codes.
• How a Join Group Optimizes Scans
  The key optimization is joining on common dictionary codes instead of column values, thereby avoiding the use of a hash table for the join.

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 slave 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.

• Monitoring Join Groups Using a SQL Monitor Report: Example
  Your goal is to use a graphical SQL Monitor report to determine whether a query leveraged a join group.
• Monitoring Join Groups from the Command Line: Example
  Your goal is to use command-line tools to determine whether a query leveraged a join group.