Skip to content

Database In-Memory (IM) Column Store Join Groups

APPLIES TO:

Oracle Database – Enterprise Edition – Version 12.2.0.1 and later
Information in this document applies to any platform.

GOAL

Create join groups to optimize equijoins in the IM column store when no filter predicates are used.

SOLUTION

Prerequisites

  • COMPATIBLE >= 12.2.0
  • INMEMORY_SIZE >= 100MB
  • Tables for which you wish to create the join group have the INMEMORY attribute.

Documentation

Database In-Memory Guide: Optimizing Joins with Join Groups

Example

1. Create a join group. Select from the tables to repopulate them with the join-group information.

— CREATE INMEMORY JOIN GROUP join_group_name ( <table1(col1)>, <(table2(col2)> );
CREATE INMEMORY JOIN GROUP sales_products_jg (sh.sales(prod_id), sh.products(prod_id));
— repopulate
select count(*) from sales;
select count(*) from products;

2. View the join group. (You can also view v$im_segdict, but it shows only the first table in the join group.).  Available views are USER_JOINGROUPS, DBA_JOINGROUPS, and CDB_JOINGROUPS.

— Preferred query because it tells you the join_group name, as well as the tables
COL JOIN_GROUP_NAME FORMAT a15
COL OBJECT_NAME FORMAT a12

SELECT j.NAME AS JOIN_GROUP_NAME, o.OBJECT_NAME, d.COL#, d.DOMAIN#
FROM SYS.IM_DOMAIN$ d, DBA_OBJECTS o, SYS.IM_JOINGROUP$ j
WHERE d.OBJN=o.OBJECT_ID
AND j.DOMAIN# = d.DOMAIN#;

JOIN_GROUP_NAME OBJECT_NAME COL# DOMAIN#
————— ———— ———- ———-
SALES_PRODUCTS SALES 1 1
SALES_PRODUCTS PRODUCTS 1 1

— Query from v$im_segdict

COL Table_Name FORMAT a15
COL Column_Name FORMAT a10
SELECT o.object_name Table_Name, c.column_name Column_Name,
gd.head_address AS “GD Address”
FROM user_objects o,
user_tab_columns c,
v$im_segdict gd
WHERE gd.objn = o.object_id
AND o.object_name = c.table_name
AND gd.column_number = c.column_id;

TABLE_NAME COLUMN_NAM GD Address
————— ———- —————-
SALES PROD_ID 00000000A102AE50

3. Drop the join group.  Select from the tables to repopulate them without the join-group information.

— DROP INMEMORY JOIN GROUP <join_group_name>;
DROP INMEMORY JOIN GROUP sales_products_jg;
— repopulate
select count(*) from sales;
select count(*) from products;

 

Important: You must repopulate the associated objects in the IM column store after a join group is created and after one is dropped to start/stop their usage.