HASH表的构造

多表关联的时候总是避不开要使用到hash join，那么对SQL语句中的hash join我们到底了解多少呢？
来看一个简单的例子

with t1 as (
select level id from dual connect by level <= 10000
),
t2 as (
select level id from dual connect by level <= 10000
)
select * from t1 left join t2
on t1.id = t2.id

这个SQL语句的执行计划如下：

1   #NSET2: [3, 29999, 2] 
2     #PRJT2: [3, 29999, 2]; exp_num(2), is_atom(FALSE) 
3       #HASH LEFT JOIN2: [3, 29999, 2]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(T1.ID=T2.ID)
4         #PRJT2: [1, 10000, 1]; exp_num(1), is_atom(FALSE) 
5           #PRJT2: [1, 10000, 1]; exp_num(1), is_atom(FALSE) 
6             #HIERARCHICAL QUERY: [1, 10000, 1]; KEY_NUM(0);
7               #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL)
8               #SLCT2: [1, 1, 0]; var1 <= 10000
9                 #CSCN2: [1, 1, 0]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL)
10        #PRJT2: [1, 10000, 1]; exp_num(1), is_atom(FALSE) 
11          #PRJT2: [1, 10000, 1]; exp_num(1), is_atom(FALSE) 
12            #HIERARCHICAL QUERY: [1, 10000, 1]; KEY_NUM(0);
13              #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL)
14              #SLCT2: [1, 1, 0]; var3 <= 10000
15                #CSCN2: [1, 1, 0]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL)

很简单的一个hash left join。
将T1表的所有行数据拿出来构造hash桶，再拿T2的关联key去匹配。最后获得结果。

对于执行计划的操作符我们关注的不外乎三点。
代价、估算行数、字节长度。
结合本文的主题，今天主要讲字节长度对SQL执行的影响。

资源消耗

首先我们来先看看案例语句的执行情况

SQL>SET AUTOTRACE TRACE
SQL>ALTER SESSION SET 'MONITO_SQL_EXEC'=1;

我们来看第三行的的执行计划：

3       #HASH LEFT JOIN2: [3, 29999->10000, 2]; key_num(1); col_num(2); partition_keys_num(0); mix(0); MEM_USED(20797KB), DISK_USED(0KB) KEY(T1.ID=T2.ID)

翻车案例

某现场遇到的SQL语句简化后的执行计划如下

计划的主要问题在于第8行，hash left join消耗的内存接近2GB。
构造hash表的代价太高了，在高并发情况下，会瞬间将整改hash内存使用超过阈值（HJ_BUF_GLOBAL_SIZE/HJ_BUF_SIZE）。
这里的行长主要来自于查询的SQL语句写了SELECT *导致。

SELECT * FROM(SELECT INNER_TABLE.*,ROWNUM OUTER_TABLE_ROWNUM FROM (
SELECT T1.*,t4.labelName FROM TT1 T1 LEFT JOIN (
SELECT B.PROJECT_ID, WMSYS.WM_CONCAT(DISTINCT C.LABEL_NAME) AS labelName
FROM TT2 B, TT3 C
WHERE B.DELETE_FLAG = 1AND C.DELETE_FLAG = 1AND B.LABEL_ID = C.ID
GROUP BY B.PROJECT_ID
) T4 ON T4.PROJECT_ID = T1.ID  where  T1.PROJECT_TYPE_NUMBER = ?AND T1.RELEASE_STATUS = 1
AND T1.PROVINCE_CODE =?
order by update_time desc )INNER_TABLE ) OUTER_TABLE WHERE OUTER_TABLE_ROWNUM<=25AND OUTER_TABLE_ROWNUM >0

而实际前台页面展示的数据并不需要那么多列，T1.*的行长估算已经达到了4060。实际更长。那么多列数据构造hash表对系统的负担非常大。

结语

目前市面上的数据库产品构造hash表的时候都是把整行数据拿进去的，算法优化的再好，但总做无用功却是有些不合理，写那么多无用的列让系统资源为偷懒买单实在不是明智之举。