I’m starting a new series called “What’s New in 12.2” and I will highlight the new Database In-Memory features in Oracle Database 12c Release 2 as outlined in this blog post here and in the Database In-Memory technical white paper available here.
To further improve join performance in 12.2 a new feature called Join Groups was introduced. A Join Group is created explicitly with a DDL statement and tells Oracle that the named columns will be used in joins. When a Join Group is created the compression dictionary entries for the specified columns will be shared and this allows the join to be performed directly on the dictionary entries rather than the data values themselves. This saves CPU by not having to decompress and hash the data to perform the join. This use of a common dictionary requires that the segments be re-populated in the IM column store after the Join Group has been created.
When a segment is populated into the IM column store a common dictionary is created for all distinct values for the column in the table. The following illustrates a simple example.
Notice that all CUs share the same dictionary entries.
With a Join Group defined the dictionary entries for the specified columns are shared between the segments specified. The following illustrates an example of a Join Group and the synchronization of the common dictionary between the two segments.
Let’s see how we can use Join Groups in a real example. A Join Group is created with a new JOIN GROUP DDL command. The following commands will create Join Groups for our SSB schema:
CREATE INMEMORY JOIN GROUP lineorder_jg1 ( lineorder(lo_orderdate), date_dim(d_datekey));
CREATE INMEMORY JOIN GROUP lineorder_jg2 ( lineorder(lo_partkey), part(p_partkey));
CREATE INMEMORY JOIN GROUP lineorder_jg3 ( lineorder(lo_suppkey), supplier(s_suppkey));
Once created with the above SQL the segments then need to be re-populated.
You can query the user_joingroups view to see if the Join Group definitions have been created:
Now we will run a query to see if we can make use of the Join Groups that we have defined. We’ll use the following SQL:
SELECT d.d_year, p.p_brand1, sum(lo_revenue) rev
FROM lineorder l,
WHERE l.lo_orderdate = d.d_datekey
AND l.lo_partkey = p.p_partkey
AND l.lo_suppkey = s.s_suppkey
AND p.p_category = ‘MFGR#12’
AND s.s_region = ‘AMERICA’
AND d.d_year = 1997
GROUP BY d.d_year, p.p_brand1;
In order to determine if we actually used the Join Group(s) we have to check a SQL Monitor Active Report for the query. Using SQL Monitor Active Reports is currently the only way to verify that you used Join Groups in your query. Notice that I have created a SQL Monitor Active Report and clicked on the eye glasses icon for one of the hash joins (the third ⁞one on Line 7 in this case) and we see that “Columnar Encodings Leveraged” is set to 1. This means we used a Join Group. If it was any other value then we would not have used a Join Group. It is also possible to see “Columnar Encodings Observed” and other non-zero values for “Columnar Encodings Leveraged” and these are used for a new type of hash join that was introduced and is not associated with Join Groups.
And there you have it. You can create Join Groups for segments that you know you will be joined often. For example, in Data Warehouse environments where you have star schemas and know which columns are used to join dimensions tables with fact tables. Just like our example SSB star schema.