使用连接组优化连接 (IM 6)
本章为IM系列第6章 使用连接组优化连接。
连接组是用户创建的字典对象，其中列出了可以有意义连接的两列。
本章包含以下主题：
- 关于In-Memory连接 连接是数据仓库工作负载的一个组成部分。当连接的表存储在内存中时，IM列存储能够增强连接的性能。
- 关于连接组 当启用IM列存储时，数据库可以使用连接组来优化在IM列存储中填充的表的连接。
- 使用连接组的目的 在某些查询中，连接组消除了解压缩和哈希列值的性能开销。
- 连接组如何工作 在连接组中，数据库使用相同的通用字典压缩连接组中的所有列。
- 创建连接组 使用CREATE INMEMORY JOIN GROUP语句定义连接组。 候选者是经常在连接谓词中配对的列，例如连接事实和维度表的列。
- 监控连接组的使用情况 要确定查询是否正在使用连接组，可以将SQL ID传递给DBMS_SQLTUNE.REPORT_SQL_MONITOR_XML函数。
01关于In-Memory连接
连接（Join）是数据仓库工作负载的一个组成部分。 当连接的表存储在内存中时，IM列存储增强了连接的性能。
由于更快的扫描和连接处理，使用Bloom过滤器的复杂多表连接和简单连连接将受益于IM列存储。 在数据仓库环境中，最常用的连接涉及事实表和一个或多个维度表。
在IM列存储中填充表时，以下连接运行速度更快：
- 适合使用Bloom过滤器的连接
- 将多个小维度表与一个事实表连接起来
- 两个具有主键 - 外键关系的表之间的连接
02关于连接组
当启用IM列存储时，数据库可以使用连接组来优化在IM列存储中填充的表的连接。
连接组是一组经常连接一组表的列。 列集包含一列或多列； 表集包含一个或多个表。 连接组中的列可以位于相同或不同的表中。 例如，如果sales 和times 频繁加入time_id列，则可以创建(times(time_id) , sales(time_id))的联合组。 连接组中的最大列数是255。
注:
一个列不能是多个连接组的成员。
创建连接组时，数据库使连接组中引用的表的当前内存中内容无效。 随后的重新填充会导致数据库使用通用字典重新编码表的IMCU。 因此，Oracle建议您首先创建连接组，然后填充表。
使用CREATE INMEMORY JOIN GROUP语句创建连接组。 要向连接组添加列或从连接组中删除列，请使用 ALTER INMEMORY JOIN GROUP语句。 使用DROP INMEMORY JOIN GROUP 语句删除连接组。
注:
在Oracle Active Data Guard中，备用数据库忽略连接组定义。 备用数据库不使用通用字典，并且像连接组不存在一样执行查询。
示例6-1创建连接组
本示例创建名为deptid_jg的连接组，其中包括hr.employees和hr.departments表中的 department_id列。
CREATE INMEMORY JOIN GROUP deptid_jg (hr.employees(department_id),hr.departments(department_id));
03连接组的目的
在某些查询中，连接组消除了解压缩和哈希列值的性能开销。
如果没有连接组，如果优化器使用Hash连接但不能使用Bloom过滤器，或者Bloom过滤器不能有效地过滤行，那么数据库必须解压缩IMCU并使用昂贵的Hash连接。 为了说明这个问题，假设星型模式有一个sales事实表和一个vehicles维度表。 以下查询连接这些表，但不会筛选输出，这意味着数据库不能使用Bloom筛选器：
SELECT v.year, v.name, s.sales_price
FROM vehicles v, sales s
WHERE v.name = s.name;
下图说明了数据库如何连接两个数据集。
图6-1无连接组的哈希连接
数据库执行哈希连接，如下所示：
- 扫描vehicles表，解压满足谓词的行（在这种情况下，由于不存在过滤器，所有行都满足谓词），并将行发送到Hash连接
- 根据解压缩的行在PGA中构建一个哈希表
- 扫描sales 表并应用任何过滤器（在这种情况下，查询不指定过滤器）
- 从IMCU解压缩匹配的行，将它们进行Hash，然后将它们发送到连接
- 使用连接列来探测Hash表，在这种情况下是连接列的名称
但是，如果v.name和s.name列上存在连接组，则数据库可以使前面的步骤更加高效，从而消除了解压缩和筛选开销。 连接组的好处是：
- 数据库对压缩数据进行操作。
- 在基于连接组的Hash连接中，数据库使用数组而不是构建Hash表。
数据库将每个连接列值的代码存储在通用字典（ common dictionary）中。 数据库连接代码而不是实际的列值。 这种技术避免了复制行源的开销。
- 字典代码密集且具有固定的长度，这使得它们具有空间效率。
- 如果不能使用Bloom过滤器，有时可以使用连接组优化查询。
03连接组如何工作
在连接组中，数据库使用相同的通用字典压缩连接组中的所有列。
本节包含以下主题：
主题:
- 连接组如何使用通用字典 一个通用字典是一个表级的，特定于实例的字典代码集合。
- 连接组如何优化扫描 关键优化是加入通用字典代码而不是列值，从而避免使用散列表进行连接。
连接组如何使用通用字典
一个通用字典是一个表级的，特定于实例的字典代码集合。
当在基础列上定义连接组时，数据库将自动在IM列存储中创建一个通用字典。 通用字典使连接列共享相同的字典代码。
一个通用字典提供了以下好处：
- 使用通用字典中的代码对本地字典中的值进行编码，从而提供压缩并提高IMCU的高速缓存效率
- 允许连接使用字典代码来构造和探测Hash连接期间使用的数据结构
- 使优化器能够获得基数，列值分布等统计信息
下图说明了与在sales.name和vehicles.name表上创建的连接组相对应的通用字典。
图6-2连接组的通用字典
当数据库使用通用字典时，每个CU的本地字典不存储原始值：Audi，BMW等。 相反，本地字典存储对通用字典中存储的值的引用。 例如，本地字典可以存储101 个Audi，220 个BMW等等。
连接组如何优化扫描
关键优化是连接通用字典代码而不是列值，从而避免使用Hash表进行连接。
考虑下面的查询，该查询使用连接组来在名称列上连接vehicles 和sales：
SELECT v.year, v.name, s.sales_price
FROM vehicles v, sales s
WHERE v.name = s.name
AND v.name IN ('Audi', 'BMW', 'Porsche', 'VW');
下图说明了连接在连接组上创建的通用字典的好处。
图6-3带连接组的哈希连接
如上图所示，数据库按如下方式对压缩数据执行Hash连接：
- 扫描vehicles 表，并将字典代码（不是原始列值）发送到Hash连接：0（Audi），1（BMW），2（Cadillac）等等
- 在PGA中构建一组不同的通用字典代码
- 扫描sales 表并应用任何过滤器（在这种情况下，过滤器仅适用于德国汽车）
- 将压缩格式的匹配行发送到连接
- 查找数组中相应的值而不是探测哈希表，从而避免在连接键列上计算哈希函数
在这个例子中，vehicles 表只有七行。 vehicles.name列具有以下值：
Audi
BMW
Cadillac
Ford
Porsche
Tesla
VW
通用字典为每个不同的值分配一个字典代码。 从概念上讲，通用字典如下所示：
Audi 0
BMW 1
Cadillac 2
Ford 3
Porsche 4
Tesla 5
VW 6
数据库扫描vehicles.name，从第一个IMCU中的第一个字典代码开始，到最后一个IMCU中的最后一个代码结束。 它为每个匹配过滤器的行（仅限德国汽车）存储1，每个与过滤器不匹配的行存储0。 从概念上讲，数组可能如下所示：
array[0]: 1
array[1]: 1
array[2]: 0
array[3]: 0
array[4]: 1
array[5]: 0
array[6]: 1
数据库现在扫描sales 事实表。 为了简化示例，假定sales 表只有6行。 数据库按以下方式扫描行（每个值的通用字典代码显示在括号中）：
Cadillac (2)
Cadillac (2)
BMW (1)
Ford (3)
Audi (0)
Tesla (5)
数据库然后继续通过vehicles.name数组，寻找匹配。 如果一行匹配，那么数据库将发送匹配行及其关联的通用字典代码，并从vehicles.name 和sales.name 的IMCUs中检索相应的列值：
2 -> array[2] is 0, so no join
2 -> array[2] is 0, so no join
1 -> array[1] is 1, so join
3 -> array[3] is 0, so no join
0 -> array[0] is 1, so join
5 -> array[5] is 0, so no join
04创建连接组
使用CREATE INMEMORY JOIN GROUP语句定义连接组。 候选者是经常在连接谓词中配对的列，例如连接事实和维度表的列。
CREATE INMEMORY JOIN GROUP语句立即定义一个连接组，这意味着它的元数据在数据字典中是可见的。 数据库不会立即构建通用字典。 相反，数据库将在下一次在连接组中引用的表被填充或重新填充到IM列存储中时生成通用字典。
参考
创建、修改或删除连接组通常会使连接组中引用的所有基础表无效。 因此，Oracle建议您在初始填充表之前创建连接组。
创建一个连接组：
- 在SQL * Plus或SQL Developer中，以具有必要权限的用户身份登录到数据库。
- 使用以下形式的语句创建一个连接组：
CREATE INMEMORY JOIN GROUP join_group_name (table1(col1),table2(col2) );
例如，以下语句创建名为sales_products_jg的连接组：
CREATE INMEMORY JOIN GROUP sales_products_jg (sales(prod_id), products(prod_id));
- （可选）通过查询数据字典（包括示例输出）查看连接组定义：
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
- 填充连接组中引用的表，或者填充当前填充的表。
示例6-2使用连接组优化查询
在本例中，您以SYSTEM的身份登录到数据库，然后在sales 和products的prod_id列上创建连接组，该列尚未填充到IM列存储中：
CREATE INMEMORY JOIN GROUP sh.sales_products_jg (sh.sales(prod_id), sh.products(prod_id));
您在IM列存储中启用sh.sales和sh.products表以进行填充：
ALTER TABLE sh.sales INMEMORY;
ALTER TABLE sh.products INMEMORY;
以下查询表示表格尚未填充到IM列存储（包含示例输出）中：
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
查询两个表以在IM列存储中填充它们：
SELECT /*+ FULL(s) NO_PARALLEL(s) */ COUNT(*) FROM sh.sales s;
SELECT /*+ FULL(p) NO_PARALLEL(p) */ COUNT(*) FROM sh.products p;
以下查询表示现在将这些表填充到IM列存储（包含示例输出）中：
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
查询DBA_JOINGROUPS以获取有关连接组的信息（包括示例输出）：
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
上面的输出显示连接组sales_products_jg加入同一个通用字典地址。
05监控连接组的使用情况
要确定查询是否正在使用连接组，可以将SQL ID传递给DBMS_SQLTUNE.REPORT_SQL_MONITOR_XML函数。
Oracle建议从命令行查询DBMS_SQLTUNE.REPORT_SQL_MONITOR_XML输出以获取SQL ID。 如果查询返回行，那么数据库将使用与此SQL ID关联的语句的连接组。 否则，数据库不会使用连接组。
先决条件
要监视连接组，您必须满足以下先决条件：
- 连接组必须存在。
- 连接组引用的列必须在连接组创建后填充。
- 您必须执行可能使用连接组的连接查询。
监控连接组的使用情况：
- 使用必要的权限登录到数据库。
- 获取要监视的查询的SQL ID。
例如，执行要监视的查询，然后查询V$SESSION.PREV_SQL_ID。
- 使用DBMS_SQLTUNE.REPORT_SQL_MONITOR_XML.EXTRACT函数确定数据库是否在Hash连接中使用了连接组。
如果查询DBMS_SQLTUNE.REPORT_SQL_MONITOR_XML.EXTRACT函数输出返回行，那么数据库使用连接组。
示例6-3监视连接组
在本例中，您将在sh.products和sh.sales表的 prod_id列上创建一个连接组，然后在该列上连接这些表。 您的目标是确定连接查询是否使用了连接组。 您授予sh帐户管理权限。 您以sh身份登录，然后按以下步骤操作：
- 为SQL ID创建一个SQL * Plus变量，如下所示：
VAR B_SQLID VARCHAR2(13)
- 将INMEMORY 属性应用于sh.products和sh.sales表，如下所示：
ALTER TABLE sales INMEMORY MEMCOMPRESS FOR QUERY;
ALTER TABLE products INMEMORY MEMCOMPRESS FOR QUERY;
- 在prod_id上创建一个连接组：
CREATE INMEMORY JOIN GROUP sh_jg (products(prod_id), sales(prod_id));
- 扫描表格以将其填充到IM列存储中：
SELECT /*+ FULL(s) */ COUNT(*) FROM sales s;
SELECT /*+ FULL(p) */ COUNT(*) FROM products p;
- 执行在 prod_id列上连接的查询，然后汇总产品销售：
SELECT /*+ USE_HASH(sales) LEADING(products sales) MONITOR */
products.prod_id, SUM(sales.amount_sold)
FROM products, sales
WHERE products.prod_id = sales.prod_id
GROUP BY products.prod_id;
- 获取上述聚合查询的SQL ID：
BEGIN
SELECT PREV_SQL_ID
INTO :B_SQLID
FROM V$SESSION
WHERE SID=USERENV('SID');
END;
- 确定数据库是否使用了连接组：
SET LONGCHUNKSIZE 10000000 LONG 10000000
COL JOIN_GROUP_USAGE FORMAT A50
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR_XML(sql_id=>:B_SQLID).
EXTRACT(q'#//operation[@name='HASH JOIN']/rwsstats/stat[@id='9']#').
GETCLOBVAL(2,2) join_group_usage
FROM DUAL;
JOIN_GROUP_USAGE
--------------------------------------------------
<stat id="9">1</stat>
该查询返回了行，所以数据库使用了与该SQL ID关联的语句的连接组。
以下是IM系列文章内容：
第一章 Oracle Database In-Memory 相关概念(IM-1.1)
第一章 Oracle Database In-Memory 相关概念（续）(IM-1.2)
第二章 Oracle Database In-Memory 体系结构(上) (IM-2.1)
第二章 IM 体系结构：CPU架构：SIMD向量处理（IM-2.3）
第四章 为In-Memory 启用填充对象（IM-4.1 第一部分）
第四章 为In-Memory 启用填充对象（IM-4.2 第二部分）
第四章 为IM 启用填充对象之启用和禁用列（IM-4.3 第三部分）
第四章 为IM 启用填充对象之在NO INMEMORY表上指定INMEMORY列属性：示例（IM-4.4 第四部分）
第四章 为IM 启用填充对象之启用和禁用表空间的IM列存储（IM 4.5）
第四章 为IM 启用填充对象之强制填充In-Memory对象：教程（IM 4.7）
第四章 为IM 启用填充对象之为IM列存储启用ADO（IM 4.8）
第五章 使用In-Memory表达式优化查询（IM 5.1）
山东Oracle用户组(Shandong Oracle User Group)，简称：SDOUG，是一个充满朝气、年轻的非营利性组织，旨在为济南及周边地区技术爱好者提供一个交流平台。SDOUG会不定期组织线下技术分享活动，促进本地区及周边IT技术的发展、帮助技术爱好者提高自己。分享技术、分享快乐，SDOUG在路上。