暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片
首页 / 哈希连接特点测试

哈希连接特点测试

原创 唐祖亮 2020-03-19
626

哈希连接（HASH JOIN）是一种两个表在做表连接时主要依靠哈希运算来得到连接结果集的表连接方法。

对于排序合并连接，如果两个表在施加了目标SQL中指定的谓词条件后得到的结果集很大而且需要排序，则排序合并连接的执行效率一定不高；而对于嵌套循环连接，如果驱动表所对应的驱动结果集的记录数很大，即便在被驱动表的连接列上存在索引，此时使用嵌套循环连接的执行效率也会同样不高。为了解决这个问题，于是ORACLE引进了哈希连接。在ORACLE 10g及其以后的版本中，优化器 （实际上是CBO，因为哈希连接仅适用于CBO）在解析目标SQL的时候是否考虑哈希连接受限于隐含参数_HASH_JOIN_ENABLED,默认值是TRUE。

oracle表之间的连接之哈希连接(Hash Join)，其特点如下：

1，驱动表和被驱动表都是最多只被访问一次。

2，哈希连接的表有驱动顺序。

3，哈希表连接的表无需要排序，但是他在做连接之前做哈希运算的时候，会用到HASH_AREA_SIZE来创建哈希表。

4，哈希连接不适用于非等值连接条件：不等于<>，大于>，小于<，小于等于<=，大于等于>=，like。

下面用实验来证实如上的结论：
首先创建测试表T1,T2：

Table created.
SQL> create table t2(id number(10),
                name varchar2(10),
                telnum number(11),
                address varchar2(15));  2    3    4  
Table created.


复制

向T2表中插入30万条数据

  for i in 1..100000
    loop
      insert into T2 values(i,'tzl',13246587653,'beijing');
      end loop;
      commit;
      end;  2    3    4    5    6    7  
PL/SQL procedure successfully completed

SQL> begin
  for i in 2..200001
    loop
      insert into T2 values(i,'tzl',13246587653,'beijing');
      end loop;
      commit;
      end;  2    3    4    5    6    7  
PL/SQL procedure successfully completed

复制

查看两张表的数据：

SQL> select count(*) from t1;
  COUNT(*)
----------
     71973
SQL> select count(*) from t2;
  COUNT(*)
----------
    1000000

复制

1、驱动表和被驱动表的访问次数

为了查看到更详细的执行计划统计信息需要进行参数设置

SQL> alter session set statistics_level=all;
Session altered.

复制

测试T2表仅被访问1次

SQL> select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.object_id;
此处省略执行的结果记录集

复制

查看执行计划

SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	bq5c59uu0b447, child number 0
-------------------------------------
select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where
t1.object_id=t2.id

Plan hash value: 1838229974

----------------------------------------------------------------------------------------------------------------
| Id  | Operation	   | Name | Starts | E-Rows | A-Rows |	 A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	 1 |	    |  71973 |00:00:00.28 |    7066 |	    |	    |	       |
|*  1 |  HASH JOIN	   |	  |	 1 |  74621 |  71973 |00:00:00.28 |    7066 |	 10M|  2018K|	14M (0)|
|   2 |   TABLE ACCESS FULL| T1   |	 1 |  74621 |  71973 |00:00:00.01 |    1030 |	    |	    |	       |
|   3 |   TABLE ACCESS FULL| T2   |	 1 |	255K|	 300K|00:00:00.06 |    6036 |	    |	    |	       |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."ID")

Note
-----
   - dynamic sampling used for this statement (level=2)
25 rows selected.

复制

从上面的执行计划可以看出，HASH连接中，驱动表和被驱动表都只会被访问1次。
接下来测试被访问0次的情况。
首先让T1作为被驱动表并加入条件使T1表没有符合条件的结果：

SQL> select /*+ leading(t2) use_hash(t1)*/ * from t1,t2 where t1.object_id=t2.id and t1.object_id<0;
no rows selected
查看执行计划：
SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	dawu7kwrxd1ng, child number 0
-------------------------------------
select /*+ leading(t2) use_hash(t1)*/ * from t1,t2 where
t1.object_id=t2.id and t1.object_id<0

Plan hash value: 2959412835

----------------------------------------------------------------------------------------------------------------
| Id  | Operation	   | Name | Starts | E-Rows | A-Rows |	 A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	 1 |	    |	   0 |00:00:00.01 |    1257 |	    |	    |	       |
|*  1 |  HASH JOIN	   |	  |	 1 |	 11 |	   0 |00:00:00.01 |    1257 |	835K|	835K|  160K (0)|
|*  2 |   TABLE ACCESS FULL| T2   |	 1 |	 14 |	   0 |00:00:00.01 |    1257 |	    |	    |	       |
|*  3 |   TABLE ACCESS FULL| T1   |	 0 |	 12 |	   0 |00:00:00.01 |	  0 |	    |	    |	       |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."ID")
   2 - filter("T2"."ID"<0)
   3 - filter("T1"."OBJECT_ID"<0)

Note
-----
   - dynamic sampling used for this statement (level=2)


27 rows selected.

复制

这时发现T1表被访问的次数为0次。
接着让T2表作为被驱动表加入条件让T2表中满足条件的结果为0。

SQL> select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.object_id=t2.id and t2.id<0;
no rows selected

复制

查看执行计划：

SQL> select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.object_id=t2.id and t2.id<0;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	95jt9785rmvbw, child number 0
-------------------------------------
select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where
t1.object_id=t2.id and t2.id<0

Plan hash value: 1838229974

----------------------------------------------------------------------------------------------------------------
| Id  | Operation	   | Name | Starts | E-Rows | A-Rows |	 A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	 1 |	    |	   0 |00:00:00.01 |    1030 |	    |	    |	       |
|*  1 |  HASH JOIN	   |	  |	 1 |	 11 |	   0 |00:00:00.01 |    1030 |	714K|	714K|  185K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |	 1 |	 12 |	   0 |00:00:00.01 |    1030 |	    |	    |	       |
|*  3 |   TABLE ACCESS FULL| T2   |	 0 |	 14 |	   0 |00:00:00.01 |	  0 |	    |	    |	       |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."ID")
   2 - filter("T1"."OBJECT_ID"<0)
   3 - filter("T2"."ID"<0)

Note
-----
   - dynamic sampling used for this statement (level=2)

27 rows selected.

复制

通过上面两个SQL的测试会误认为当被驱动表没有满足的结果时，对被驱动表的访问次数就为0，下面接着测试其他情况。
下面测试当驱动表没有满足条件的数据时，被驱动表的被访问次数：

SQL> select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.object_id=t2.id and t1.object_id<0;

no rows selected

复制

执行计划：

SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	08ks9kvdtr6pg, child number 0
-------------------------------------
select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where
t1.object_id=t2.id and t1.object_id<0

Plan hash value: 1838229974

----------------------------------------------------------------------------------------------------------------
| Id  | Operation	   | Name | Starts | E-Rows | A-Rows |	 A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	 1 |	    |	   0 |00:00:00.01 |    1030 |	    |	    |	       |
|*  1 |  HASH JOIN	   |	  |	 1 |	 11 |	   0 |00:00:00.01 |    1030 |	714K|	714K|  171K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |	 1 |	 12 |	   0 |00:00:00.01 |    1030 |	    |	    |	       |
|*  3 |   TABLE ACCESS FULL| T2   |	 0 |	 14 |	   0 |00:00:00.01 |	  0 |	    |	    |	       |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."ID")
   2 - filter("T1"."OBJECT_ID"<0)
   3 - filter("T2"."ID"<0)

Note
-----
   - dynamic sampling used for this statement (level=2)


27 rows selected.

复制

这时候被驱动表的被访问次数还是为0，驱动表的被访问次数还是1。
那么两个表都设置条件让其没有数据返回呢？

SQL> select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.object_id=t2.id and t1.object_id<0 and t2.id<0;
no rows selected
执行计划：
SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	cf2sgjjg7cy8s, child number 0
-------------------------------------
select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where
t1.object_id=t2.id and t1.object_id<0 and t2.id<0

Plan hash value: 1838229974

----------------------------------------------------------------------------------------------------------------
| Id  | Operation	   | Name | Starts | E-Rows | A-Rows |	 A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	 1 |	    |	   0 |00:00:00.01 |    1030 |	    |	    |	       |
|*  1 |  HASH JOIN	   |	  |	 1 |	 11 |	   0 |00:00:00.01 |    1030 |	714K|	714K|  173K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |	 1 |	 12 |	   0 |00:00:00.01 |    1030 |	    |	    |	       |
|*  3 |   TABLE ACCESS FULL| T2   |	 0 |	 14 |	   0 |00:00:00.01 |	  0 |	    |	    |	       |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."ID")
   2 - filter("T1"."OBJECT_ID"<0)
   3 - filter("T2"."ID"<0)

Note
-----
   - dynamic sampling used for this statement (level=2)


27 rows selected.

复制

这时可以发现就算两个表都没有符合条件的数据被驱动表的访问次数还是0。那么当出现两个以上的表进行HASH连接的时候呢。
接着创建第三张测试表然后对三张表加入条件使其都没有返回结果。

SQL> create table t3 as select * from dba_objects;
Table created.
SQL> select /*+ leading(t1,t2,t3) use_hash(t2 t3)*/ * from t1,t2,t3 where t1.object_id=t2.id and t3.object_id=t1.object_id and t1.object_id<0 and t2.id<0 and t3.object_id<0;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	d7gt178w9cnqk, child number 0
-------------------------------------
select /*+ leading(t1,t2,t3) use_hash(t2 t3)*/ * from t1,t2,t3 where
t1.object_id=t2.id and t3.object_id=t1.object_id and t1.object_id<0 and
t2.id<0 and t3.object_id<0

Plan hash value: 261998084

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation	    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |	OMem |	1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |	   |	  1 |	     |	    0 |00:00:00.01 |	1030 |	     |	     |		|
|*  1 |  HASH JOIN	    |	   |	  1 |	  11 |	    0 |00:00:00.01 |	1030 |	 708K|	 708K|	183K (0)|
|*  2 |   HASH JOIN	    |	   |	  1 |	  11 |	    0 |00:00:00.01 |	1030 |	 714K|	 714K|	169K (0)|
|*  3 |    TABLE ACCESS FULL| T1   |	  1 |	  12 |	    0 |00:00:00.01 |	1030 |	     |	     |		|
|*  4 |    TABLE ACCESS FULL| T2   |	  0 |	  14 |	    0 |00:00:00.01 |	   0 |	     |	     |		|
|*  5 |   TABLE ACCESS FULL | T3   |	  0 |	  12 |	    0 |00:00:00.01 |	   0 |	     |	     |		|
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T3"."OBJECT_ID"="T1"."OBJECT_ID")
   2 - access("T1"."OBJECT_ID"="T2"."ID")
   3 - filter("T1"."OBJECT_ID"<0)
   4 - filter("T2"."ID"<0)
   5 - filter("T3"."OBJECT_ID"<0)

Note
-----
   - dynamic sampling used for this statement (level=2)


32 rows selected.

复制

通过上面的执行计划可以知道在多个表进行HASH连接的时候不管哪个表没有满足条件的数据返回都只会对最内层的驱动表访问一次，其他表访问0次。那什么时候驱动表和被驱动表都访问0次呢？
接着进行下面的测试：
当在SQL条件中加入恒不成立的条件时查看SQL的执行计划

SQL> select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.object_id=t2.id and 1>2;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	8xr00w1wfk88w, child number 0
-------------------------------------
select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where
t1.object_id=t2.id and 1>2

Plan hash value: 487071653

-------------------------------------------------------------------------------------------------------
| Id  | Operation	    | Name | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |	   |	  1 |	     |	    0 |00:00:00.01 |	   |	   |	      |
|*  1 |  FILTER 	    |	   |	  1 |	     |	    0 |00:00:00.01 |	   |	   |	      |
|*  2 |   HASH JOIN	    |	   |	  0 |  74621 |	    0 |00:00:00.01 |	21M|  3809K|	      |
|   3 |    TABLE ACCESS FULL| T1   |	  0 |  74621 |	    0 |00:00:00.01 |	   |	   |	      |
|   4 |    TABLE ACCESS FULL| T2   |	  0 |	 255K|	    0 |00:00:00.01 |	   |	   |	      |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)
   2 - access("T1"."OBJECT_ID"="T2"."ID")

Note
-----
   - dynamic sampling used for this statement (level=2)


27 rows selected.

复制

从上面的执行计划可以看出，当条件中出现恒不成立的条件的时候，驱动表和被驱动表的被访问次数都为0次。

2、哈希连接的表的驱动顺序

收集两张表的统计信息


PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size auto',no_invalidate=>false);

PL/SQL procedure successfully completed.

复制

让T1表作为驱动表查看执行计划

SQL> select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.object_id=t2.id and t1.object_id=1;
省略查询结果

100000 rows selected.

Elapsed: 00:00:17.33
SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	2d981q7w261m4, child number 0
-------------------------------------
select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where
t1.object_id=t2.id and t1.object_id=1

Plan hash value: 1838229974

----------------------------------------------------------------------------------------------------------------
| Id  | Operation	   | Name | Starts | E-Rows | A-Rows |	 A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	 1 |	    |	 100K|00:00:00.15 |    8928 |	    |	    |	       |
|*  1 |  HASH JOIN	   |	  |	 1 |  99213 |	 100K|00:00:00.15 |    8928 |	773K|	773K|  351K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |	 1 |	  1 |	   1 |00:00:00.01 |    1030 |	    |	    |	       |
|*  3 |   TABLE ACCESS FULL| T2   |	 1 |  99213 |	 100K|00:00:00.06 |    7898 |	    |	    |	       |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."ID")
   2 - filter("T1"."OBJECT_ID"=1)
   3 - filter("T2"."ID"=1)


23 rows selected.

复制

再查看以T2表为驱动表的执行计划

SQL> select /*+ leading(t2) use_hash(t1)*/ * from t1,t2 where t1.object_id=t2.id and t1.object_id=1;
省略查询结果

100000 rows selected.

Elapsed: 00:00:16.85
SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	fh4pqszpmdfzv, child number 0
-------------------------------------
select /*+ leading(t2) use_hash(t1)*/ * from t1,t2 where
t1.object_id=t2.id and t1.object_id=1

Plan hash value: 2959412835

----------------------------------------------------------------------------------------------------------------
| Id  | Operation	   | Name | Starts | E-Rows | A-Rows |	 A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	 1 |	    |	 100K|00:00:00.09 |    2288 |	    |	    |	       |
|*  1 |  HASH JOIN	   |	  |	 1 |  99213 |	 100K|00:00:00.09 |    2288 |  6203K|  2546K|	 9M (0)|
|*  2 |   TABLE ACCESS FULL| T2   |	 1 |  99213 |	 100K|00:00:00.01 |    1257 |	    |	    |	       |
|*  3 |   TABLE ACCESS FULL| T1   |	 1 |	  1 |	   1 |00:00:00.01 |    1031 |	    |	    |	       |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."ID")
   2 - filter("T2"."ID"=1)
   3 - filter("T1"."OBJECT_ID"=1)


23 rows selected.


复制

从上面的执行计划可以看到当T1、T2分别作为驱动表的时候消耗的时间差距并不大，但是在连接的时候占用的内存差距却非常大，也就是执行计划中的Used-Mem这个列的值，在T1表作为驱动表的时候Used-Mem为351K (0)，而当T2表作为驱动表时，Used-Mem却高达9M (0)。这说明HASH连接的时候是会区分连接顺序的，一般情况下结果集小的部分作为驱动表进行连接效果更好。

3、哈希连接不适用于非等值连接条件

在非等值连接的SQL中加入HASH连接的提示，查看其执行计划：

使用不等于连接

SQL>select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.object_id<>t2.id and t1.object_id=3;
省略查询结果
299999 rows selected.

Elapsed: 00:00:52.82
SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	3275jbnjsxadq, child number 0
-------------------------------------
select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where
t1.object_id<>t2.id and t1.object_id=3

Plan hash value: 1967407726

-------------------------------------------------------------------------------------
| Id  | Operation	   | Name | Starts | E-Rows | A-Rows |	 A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	 1 |	    |	 299K|00:00:00.26 |   22205 |
|   1 |  NESTED LOOPS	   |	  |	 1 |	299K|	 299K|00:00:00.26 |   22205 |
|*  2 |   TABLE ACCESS FULL| T1   |	 1 |	  1 |	   1 |00:00:00.01 |    1031 |
|*  3 |   TABLE ACCESS FULL| T2   |	 1 |	299K|	 299K|00:00:00.19 |   21174 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T1"."OBJECT_ID"=3)
   3 - filter(("T1"."OBJECT_ID"<>"T2"."ID" AND "T2"."ID"<>3))


22 rows selected.

复制

使用小于连接

SQL> select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.object_id<t2.id and t1.object_id=3;
199998 rows selected.
省略查询结果

Elapsed: 00:00:34.37
SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	8fr2bv6zukwmy, child number 0
-------------------------------------
select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where
t1.object_id<t2.id and t1.object_id=3

Plan hash value: 1967407726

-------------------------------------------------------------------------------------
| Id  | Operation	   | Name | Starts | E-Rows | A-Rows |	 A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	 1 |	    |	 199K|00:00:00.19 |   15566 |
|   1 |  NESTED LOOPS	   |	  |	 1 |	200K|	 199K|00:00:00.19 |   15566 |
|*  2 |   TABLE ACCESS FULL| T1   |	 1 |	  1 |	   1 |00:00:00.01 |    1031 |
|*  3 |   TABLE ACCESS FULL| T2   |	 1 |	200K|	 199K|00:00:00.14 |   14535 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T1"."OBJECT_ID"=3)
   3 - filter(("T2"."ID">3 AND "T1"."OBJECT_ID"<"T2"."ID"))


22 rows selected.

复制

使用like连接

SQL> select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.object_id like '%1'||T2.ID and t1.object_id=3;

no rows selected

Elapsed: 00:00:00.15
SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	a618f74sbmfn5, child number 0
-------------------------------------
select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.object_id
like '%1'||T2.ID and t1.object_id=3

Plan hash value: 1967407726

-------------------------------------------------------------------------------------
| Id  | Operation	   | Name | Starts | E-Rows | A-Rows |	 A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	 1 |	    |	   0 |00:00:00.14 |    2287 |
|   1 |  NESTED LOOPS	   |	  |	 1 |  15000 |	   0 |00:00:00.14 |    2287 |
|*  2 |   TABLE ACCESS FULL| T1   |	 1 |	  1 |	   1 |00:00:00.01 |    1030 |
|*  3 |   TABLE ACCESS FULL| T2   |	 1 |  15000 |	   0 |00:00:00.14 |    1257 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T1"."OBJECT_ID"=3)
   3 - filter(TO_CHAR("T1"."OBJECT_ID") LIKE '%1'||TO_CHAR("T2"."ID"))


22 rows selected.


复制

从上面可以看出执行计划走的是NESTED LOOPS JOIN。而不是HINT指定的HASH ，按照上面的方法可以证明哈希连接不支持不等值连接。

oracle数据库
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
1人已赞赏
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

相关阅读

【专家有话说第五期】在不同年龄段，DBA应该怎样规划自己的职业发展？
墨天轮编辑部
1394次阅读
2025-03-13 11:40:53
Oracle RAC ASM 磁盘组满了，无法扩容怎么在线处理？
Lucifer三思而后行
847次阅读
2025-03-17 11:33:53
RAC 19C 删除+新增节点
gh
528次阅读
2025-03-14 15:44:18
2025年3月国产数据库大事记
墨天轮编辑部
493次阅读
2025-04-03 15:21:16
2月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
487次阅读
2025-03-13 14:38:19
AI的优化能力，取决于你问问题的能力！
潇湘秦
467次阅读
2025-03-11 11:18:22
Oracle 如何修改 db_unique_name？强迫症福音！
Lucifer三思而后行
386次阅读
2025-03-12 21:27:56
优炫数据库成功应用于国家电投集团青海海南州新能源电厂！
优炫软件
356次阅读
2025-03-21 10:34:08
Oracle DataGuard高可用性解决方案详解
孙莹
340次阅读
2025-03-26 23:27:33
征文大赛 |「码」上数据库—— KWDB 2025 创作者计划启动
KaiwuDB
334次阅读
2025-04-01 20:42:12