内容介绍
_optimizer_outer_join_to_inner 是一个Oracle数据库的隐藏优化器参数，用于控制是否允许将外连接（OUTER JOIN）转换为内连接（INNER JOIN）。使用yashandb数据库对比测试，总结如下： 测试场景一：谓词不带函数且过滤被驱动表(性能相当) 1. 性能对比： Oracle使用 HASH JOIN(OUTER_JOIN_TO_INNER),耗时：1.81秒 yashandb使用 HASH JOIN INNER,耗时：1.24秒 2. oracle与yashandb都可以进行OUTER_JOIN_TO_INNER转换，且性能相当。 场景二、谓词带内置函数(Built-in Functions) 且过滤被驱动表(yashandb性能较优) 1. 性能对比： Oracle使用 HASH JOIN RIGHT OUTER,耗时：4.33秒 yashandb使用 HASH JOIN INNER,耗时：0.98秒 2. 谓词带函数，Oracle不进行查询转换 3. 谓词带内置函数(Built-in Functions)，Yashandb可以正常转换，性能更优 4. Oracle将谓词放在hash join外的FILTER 场景三、谓词带内置函数(Built-in Functions) 且过滤被驱动表，且驱动表短路(yashandb性能较优) 1. 性能对比： Oracle使用 HASH JOIN RIGHT OUTER,耗时：10.71秒 yashandb使用 HASH JOIN INNER,耗时：0.018秒 2. Oracle将谓词放在hash join外的FILTER,所以hash join后才能使用filter中的谓词过滤 3. yashandb 由于谓词在驱动表上，且谓词造成驱动表短路，被驱动表不需要执行 场景四、谓词带用户定义函数 (User-Defined Functions, UDF)且过滤被驱动表(yashandb性能较优) 1. 性能对比： Oracle使用 HASH JOIN RIGHT OUTER,耗时：61.80秒 yashandb使用 HASH JOIN RIGHT OUTER,耗时：28.76秒 2. yashandb对用户定义函数 (User-Defined Functions, UDF)，也不支持查询转换 3. 执行计划相同，yashandb性能比oracle快2.15倍，说明yashandb的hash join的性能更好。
场景测试
场景一、谓词不带函数且过滤被驱动表
测试语句
set linesize 300 pagesize 9999 col object_name for a30 col object_type for a20 col c2 for a30 SELECT t1.object_name ,t3.object_type ,t3.object_name c2 FROM t1 left JOIN t3 ON t1.object_name = t3.object_name WHERE t3.object_type = 'RULE';
Oracle 测试结果
SQL>
set linesize 300 pagesize 9999
col object_name for a30
col object_type for a20
col c2 for a30
SELECT t1.object_name
,t3.object_type
,t3.object_name c2
FROM t1
left JOIN t3 ON t1.object_name = t3.object_name
6 WHERE t3.object_type = 'RULE';
OBJECT_NAME OBJECT_TYPE C2
------------------------------ -------------------- ------------------------------
ALERT_QUE$1 RULE ALERT_QUE$1
ALERT_QUE$1 RULE ALERT_QUE$1
Elapsed: 00:00:01.81
SQL> @xp
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cbzd92x8kk2y2, child number 0
-------------------------------------
SELECT t1.object_name ,t3.object_type ,t3.object_name c2 FROM t1 left
JOIN t3 ON t1.object_name = t3.object_name WHERE t3.object_type = 'RULE'
Plan hash value: 740309051
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 49143 (100)| | 2 |00:00:01.81 | 174K|
|* 1 | HASH JOIN | | 1 | 253K| 49143 (1)| 00:00:02 | 2 |00:00:01.81 | 174K|
|* 2 | TABLE ACCESS FULL| T3 | 1 | 1546 | 394 (1)| 00:00:01 | 1 |00:00:00.01 | 1436 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 10M| 48722 (1)| 00:00:02 | 10M|00:00:01.57 | 172K|
-------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$29B07E9F
2 - SEL$29B07E9F / T3@SEL$1
3 - SEL$29B07E9F / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$29B07E9F")
OUTER_JOIN_TO_INNER(@"SEL$2BFA4EE4" "T3"@"SEL$1") <--转换
OUTLINE(@"SEL$2BFA4EE4")
MERGE(@"SEL$8812AA4E" >"SEL$948754D7")
OUTLINE(@"SEL$948754D7")
ANSI_REARCH(@"SEL$2")
OUTLINE(@"SEL$8812AA4E")
ANSI_REARCH(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
FULL(@"SEL$29B07E9F" "T3"@"SEL$1") <--访问方式，访问顺序
FULL(@"SEL$29B07E9F" "T1"@"SEL$1") <--访问方式，访问顺序
LEADING(@"SEL$29B07E9F" "T3"@"SEL$1" "T1"@"SEL$1") <--驱动表，访问顺序
USE_HASH(@"SEL$29B07E9F" "T1"@"SEL$1") <--连接方式
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_NAME"="T3"."OBJECT_NAME")
2 - filter("T3"."OBJECT_TYPE"='RULE')
Query Block Registry:
---------------------
<q o="68" h="y"><n><![CDATA[SEL$8812AA4E]]></n><p><![CDATA[SEL$1]]></p><f><h><t><![CDATA[T1]]></t><s>
<![CDATA[SEL$1]]></s></h><h><t><![CDATA[T3]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
<q o="2"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[T1]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDAT
A[T3]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
<q o="2"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[from$_subquery$_003]]></t><s><![CDATA[SEL$2]]></s>
</h></f></q>
<q o="18" h="y"><n><![CDATA[SEL$2BFA4EE4]]></n><p><![CDATA[SEL$948754D7]]></p><i><o><t>VW</t><v><![CD
ATA[SEL$8812AA4E]]></v></o></i><f><h><t><![CDATA[T1]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[
T3]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
<q o="67" h="y"><n><![CDATA[SEL$948754D7]]></n><p><![CDATA[SEL$2]]></p><i><o><t>VW</t><v><![CDATA[SEL
$8812AA4E]]></v></o></i><f><h><t><![CDATA[from$_subquery$_003]]></t><s><![CDATA[SEL$2]]></s></h></f><
/q>
<q o="35" f="y" h="y"><n><![CDATA[SEL$29B07E9F]]></n><p><![CDATA[SEL$2BFA4EE4]]></p><i><o><t>TA</t><v
><![CDATA[T3@SEL$1]]></v></o></i><f><h><t><![CDATA[T1]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDAT
A[T3]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
78 rows selected.
Elapsed: 00:00:00.03
SQL>
SQL优化我关注的三要素：访问方式、访问顺序、连接方式
OUTER_JOIN_TO_INNER(@"SEL$2BFA4EE4" "T3"@"SEL$1") <--转换
FULL(@"SEL$29B07E9F" "T3"@"SEL$1") <--访问方式，访问顺序
FULL(@"SEL$29B07E9F" "T1"@"SEL$1") <--访问方式，访问顺序
LEADING(@"SEL$29B07E9F" "T3"@"SEL$1" "T1"@"SEL$1") <--驱动表，访问顺序
USE_HASH(@"SEL$29B07E9F" "T1"@"SEL$1") <--连接方式
yashandb测试结果
SQL> @st
Succeed.
set linesize 300 pagesize 9999
YASQL-00008 unknown SET option
col object_name for a30
col object_type for a20
col c2 for a30
SELECT t1.object_name
,t3.object_type
,t3.object_name c2
FROM t1
left JOIN t3 ON t1.object_name = t3.object_name
6 WHERE t3.object_type = 'RULE';
OBJECT_NAME OBJECT_TYPE C2
------------------------------ -------------------- ------------------------------
ALERT_QUE$1 RULE ALERT_QUE$1
ALERT_QUE$1 RULE ALERT_QUE$1
Execution Plan
----------------------------------------------------------------
SQL hash value: 3593047075
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | 2| | 1226427| 3| 0| 0| |
|* 1 | HASH JOIN INNER | | | 332| 2| 281570( 0)| 1226417| 3| 0| 0| |
| 2 | JOIN FILTER USE | | | 10000000| 2| 280979( 0)| 1217431| 3| 0| 0| |
|* 3 | TABLE ACCESS FULL | T1 | YASTEST | 10000000| 2| 280979( 0)| 1217430| 3| 0| 0| |
|* 4 | JOIN FILTER CREATE | | | 2| 1| 226( 0)| 7812| 2| 0| 0| |
|* 5 | TABLE ACCESS FULL | T3 | YASTEST | 2| 1| 226( 0)| 7812| 2| 0| 0| |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
1 - Execution : Algorithm : 0 PartCount : 1
Predicate : access("T1"."OBJECT_NAME" = "T3"."OBJECT_NAME")
3 - Predicate : RUNTIME FILTER(RUNTIME USE(0): "T1"."OBJECT_NAME")
4 - Predicate : RUNTIME FILTER(RUNTIME CREATE(0): "T3"."OBJECT_NAME")
5 - Predicate : filter("T3"."OBJECT_TYPE" = 'RULE')
Statistics
----------------------------------------------------------------------------------------------------
0 physical reads
183067 db block gets
0 consistent gets
0 redo size
0 recursive calls
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
0 bytes sent via PX
0 block received
37 rows fetched.
Elapsed: 00:00:01.243
SQL>
小结
测试场景一：谓词不带函数且过滤被驱动表(性能相当) 1. 性能对比： Oracle使用 HASH JOIN(OUTER_JOIN_TO_INNER),耗时：1.81秒 yashandb使用 HASH JOIN INNER,耗时：1.24秒 2. oracle与yashandb都可以进行OUTER_JOIN_TO_INNER转换，且性能相当。
场景二、谓词带内置函数(Built-in Functions) 且过滤被驱动表
测试语句
set linesize 300 pagesize 9999 col object_name for a30 col object_type for a20 col c2 for a30 SELECT t1.object_name ,t3.object_type ,t3.object_name c2 FROM t1 left JOIN t3 ON t1.object_name = t3.object_name WHERE substr(t3.object_type, 1, 4) = 'RULE';
Oracle 测试结果
SQL> @xp
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID aj6pkt5gtrdjx, child number 0
-------------------------------------
SELECT t1.object_name ,t3.object_type ,t3.object_name c2 FROM t1 left
JOIN t3 ON t1.object_name = t3.object_name WHERE substr(t3.object_type,
1, 4) = 'RULE'
Plan hash value: 2389609966
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 64024 (100)| | 24 |00:00:04.32 | 174K|
|* 1 | FILTER | | 1 | | | | 24 |00:00:04.32 | 174K|
|* 2 | HASH JOIN RIGHT OUTER| | 1 | 11M| 64024 (1)| 00:00:03 | 23M|00:00:02.98 | 174K|
| 3 | TABLE ACCESS FULL | T3 | 1 | 72655 | 394 (1)| 00:00:01 | 72656 |00:00:00.01 | 1436 |
| 4 | TABLE ACCESS FULL | T1 | 1 | 10M| 48722 (1)| 00:00:02 | 10M|00:00:00.52 | 172K|
-----------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2BFA4EE4
3 - SEL$2BFA4EE4 / T3@SEL$1
4 - SEL$2BFA4EE4 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2BFA4EE4")
MERGE(@"SEL$8812AA4E" >"SEL$948754D7")
OUTLINE(@"SEL$948754D7")
ANSI_REARCH(@"SEL$2")
OUTLINE(@"SEL$8812AA4E")
ANSI_REARCH(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
FULL(@"SEL$2BFA4EE4" "T1"@"SEL$1")
FULL(@"SEL$2BFA4EE4" "T3"@"SEL$1")
LEADING(@"SEL$2BFA4EE4" "T1"@"SEL$1" "T3"@"SEL$1")
USE_HASH(@"SEL$2BFA4EE4" "T3"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$2BFA4EE4" "T3"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUBSTR("T3"."OBJECT_TYPE",1,4)='RULE')
2 - access("T1"."OBJECT_NAME"="T3"."OBJECT_NAME")
Query Block Registry:
---------------------
<q o="68" h="y"><n><![CDATA[SEL$8812AA4E]]></n><p><![CDATA[SEL$1]]></p><f><h><t><![CDATA[T1]]></t><s><![C
DATA[SEL$1]]></s></h><h><t><![CDATA[T3]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
<q o="2"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[T1]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[T3
]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
<q o="2"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[from$_subquery$_003]]></t><s><![CDATA[SEL$2]]></s></h>
</f></q>
<q o="18" f="y" h="y"><n><![CDATA[SEL$2BFA4EE4]]></n><p><![CDATA[SEL$948754D7]]></p><i><o><t>VW</t><v><![
CDATA[SEL$8812AA4E]]></v></o></i><f><h><t><![CDATA[T1]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[T3
]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
<q o="67" h="y"><n><![CDATA[SEL$948754D7]]></n><p><![CDATA[SEL$2]]></p><i><o><t>VW</t><v><![CDATA[SEL$881
2AA4E]]></v></o></i><f><h><t><![CDATA[from$_subquery$_003]]></t><s><![CDATA[SEL$2]]></s></h></f></q>
75 rows selected.
Elapsed: 00:00:00.03
SQL>
YashanDB测试结果
Execution Plan
----------------------------------------------------------------
SQL hash value: 3603826884
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | 24| | 978424| 25| 0| 0| |
|* 1 | HASH JOIN INNER | | | 254439| 24| 263362( 0)| 978408| 25| 0| 0| |
| 2 | JOIN FILTER USE | | | 10000000| 12049| 262762( 0)| 959772| 12050| 0| 0| |
|* 3 | TABLE ACCESS FULL | T1 | YASTEST | 10000000| 12049| 262762( 0)| 958628| 12050| 0| 0| |
|* 4 | JOIN FILTER CREATE | | | 1546| 22| 230( 0)| 13257| 23| 0| 0| |
|* 5 | TABLE ACCESS FULL | T3 | YASTEST | 1546| 22| 230( 0)| 13254| 23| 0| 0| |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
1 - Execution : Algorithm : 0 PartCount : 1
Predicate : access("T1"."OBJECT_NAME" = "T3"."OBJECT_NAME")
3 - Predicate : RUNTIME FILTER(RUNTIME USE(0): "T1"."OBJECT_NAME")
4 - Predicate : RUNTIME FILTER(RUNTIME CREATE(0): "T3"."OBJECT_NAME")
5 - Predicate : filter(SUBSTR("T3"."OBJECT_TYPE", 1, 4) = 'RULE')
Statistics
----------------------------------------------------------------------------------------------------
0 physical reads
183067 db block gets
0 consistent gets
0 redo size
0 recursive calls
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
24 rows processed
0 bytes sent via PX
0 block received
59 rows fetched.
Elapsed: 00:00:00.984
SQL>
小结
场景二、谓词带内置函数(Built-in Functions) 且过滤被驱动表(yashandb性能较优) 1. 性能对比： Oracle使用 HASH JOIN RIGHT OUTER,耗时：4.33秒 yashandb使用 HASH JOIN INNER,耗时：0.98秒 2. 谓词带函数，Oracle不进行查询转换 3. 谓词带内置函数(Built-in Functions)，Yashandb可以正常转换，性能更优 4. Oracle将谓词放在hash join外的FILTER
场景三、谓词带内置函数(Built-in Functions) 且过滤被驱动表，且驱动表短路
测试语句
set linesize 300 pagesize 9999 col object_name for a30 col object_type for a20 col c2 for a30 SELECT t1.object_name ,t3.object_type ,t3.object_name c2 FROM t1 left JOIN t3 ON t1.object_name = t3.object_name WHERE substr(t3.object_type, 1, 4) = 'RULES';
Oracle 测试结果
SQL>
set linesize 300 pagesize 9999
col object_name for a30
col object_type for a20
col c2 for a30
SELECT t1.object_name
,t3.object_type
,t3.object_name c2
FROM t1
left JOIN t3 ON t1.object_name = t3.object_name
6 WHERE substr(t3.object_type, 1, 4) = 'RULES';
no rows selected
Elapsed: 00:00:10.71
SQL> @xp
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 256ky19rzuykx, child number 0
-------------------------------------
SELECT t1.object_name ,t3.object_type ,t3.object_name c2 FROM t1 left
JOIN t3 ON t1.object_name = t3.object_name WHERE substr(t3.object_type,
1, 4) = 'RULES'
Plan hash value: 2389609966
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 64024 (100)| | 0 |00:00:10.71 | 174K|
|* 1 | FILTER | | 1 | | | | 0 |00:00:10.71 | 174K|
|* 2 | HASH JOIN RIGHT OUTER| | 1 | 11M| 64024 (1)| 00:00:03 | 23M|00:00:06.90 | 174K|
| 3 | TABLE ACCESS FULL | T3 | 1 | 72655 | 394 (1)| 00:00:01 | 72656 |00:00:00.01 | 1436 |
| 4 | TABLE ACCESS FULL | T1 | 1 | 10M| 48722 (1)| 00:00:02 | 10M|00:00:01.01 | 172K|
-----------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2BFA4EE4
3 - SEL$2BFA4EE4 / T3@SEL$1
4 - SEL$2BFA4EE4 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2BFA4EE4")
MERGE(@"SEL$8812AA4E" >"SEL$948754D7")
OUTLINE(@"SEL$948754D7")
ANSI_REARCH(@"SEL$2")
OUTLINE(@"SEL$8812AA4E")
ANSI_REARCH(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
FULL(@"SEL$2BFA4EE4" "T1"@"SEL$1")
FULL(@"SEL$2BFA4EE4" "T3"@"SEL$1")
LEADING(@"SEL$2BFA4EE4" "T1"@"SEL$1" "T3"@"SEL$1")
USE_HASH(@"SEL$2BFA4EE4" "T3"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$2BFA4EE4" "T3"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUBSTR("T3"."OBJECT_TYPE",1,4)='RULES')
2 - access("T1"."OBJECT_NAME"="T3"."OBJECT_NAME")
Query Block Registry:
---------------------
<q o="68" h="y"><n><![CDATA[SEL$8812AA4E]]></n><p><![CDATA[SEL$1]]></p><f><h><t><![CDATA[T1]]></t><s><![C
DATA[SEL$1]]></s></h><h><t><![CDATA[T3]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
<q o="2"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[T1]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[T3
]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
<q o="2"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[from$_subquery$_003]]></t><s><![CDATA[SEL$2]]></s></h>
</f></q>
<q o="18" f="y" h="y"><n><![CDATA[SEL$2BFA4EE4]]></n><p><![CDATA[SEL$948754D7]]></p><i><o><t>VW</t><v><![
CDATA[SEL$8812AA4E]]></v></o></i><f><h><t><![CDATA[T1]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[T3
]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
<q o="67" h="y"><n><![CDATA[SEL$948754D7]]></n><p><![CDATA[SEL$2]]></p><i><o><t>VW</t><v><![CDATA[SEL$881
2AA4E]]></v></o></i><f><h><t><![CDATA[from$_subquery$_003]]></t><s><![CDATA[SEL$2]]></s></h></f></q>
75 rows selected.
Elapsed: 00:00:00.03
SQL>
YashanDB测试结果
SQL>
set linesize 300 pagesize 9999
YASQL-00008 unknown SET option
col object_name for a30
col object_type for a20
col c2 for a30
SELECT t1.object_name
,t3.object_type
,t3.object_name c2
FROM t1
left JOIN t3 ON t1.object_name = t3.object_name
6 WHERE substr(t3.object_type, 1, 4) = 'RULES';
OBJECT_NAME OBJECT_TYPE C2
------------------------------ -------------------- ------------------------------
Execution Plan
----------------------------------------------------------------
SQL hash value: 240635051
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | 0| | 12474| 1| 0| 0| |
|* 1 | HASH JOIN INNER | | | 254439| 0| 263362( 0)| 12468| 1| 0| 0| |
| 2 | JOIN FILTER USE | | | 10000000| 0| 262762( 0)| 0| 0| 0| 0| |
|* 3 | TABLE ACCESS FULL | T1 | YASTEST | 10000000| 0| 262762( 0)| 0| 0| 0| 0| |
|* 4 | JOIN FILTER CREATE | | | 1546| 0| 230( 0)| 12406| 1| 0| 0| |
|* 5 | TABLE ACCESS FULL | T3 | YASTEST | 1546| 0| 230( 0)| 12405| 1| 0| 0| |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
1 - Execution : Algorithm : 0 PartCount : 1
Predicate : access("T1"."OBJECT_NAME" = "T3"."OBJECT_NAME")
3 - Predicate : RUNTIME FILTER(RUNTIME USE(0): "T1"."OBJECT_NAME")
4 - Predicate : RUNTIME FILTER(RUNTIME CREATE(0): "T3"."OBJECT_NAME")
5 - Predicate : filter(SUBSTR("T3"."OBJECT_TYPE", 1, 4) = 'RULES')
Statistics
----------------------------------------------------------------------------------------------------
0 physical reads
1504 db block gets
0 consistent gets
0 redo size
0 recursive calls
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
0 bytes sent via PX
0 block received
35 rows fetched.
Elapsed: 00:00:00.015
SQL>
小结
场景三、谓词带内置函数(Built-in Functions) 且过滤被驱动表，且驱动表短路(yashandb性能较优) 1. 性能对比： Oracle使用 HASH JOIN RIGHT OUTER,耗时：10.71秒 yashandb使用 HASH JOIN INNER,耗时：0.018秒 2. Oracle将谓词放在hash join外的FILTER,所以hash join后才能使用filter中的谓词过滤 3. yashandb 由于谓词在驱动表上，且谓词造成驱动表短路，被驱动表不需要执行
场景四、谓词带用户定义函数 (User-Defined Functions, UDF)且过滤被驱动表
测试语句
CREATE OR REPLACE FUNCTION "YASTEST"."F_SUBSTR" ( p_string IN VARCHAR2, p_start IN NUMBER DEFAULT 1, p_length IN NUMBER DEFAULT NULL ) RETURN VARCHAR2 IS v_result VARCHAR2(4000); BEGIN -- 调用内置SUBSTR函数 v_result := SUBSTR(p_string, p_start, p_length); RETURN v_result; EXCEPTION WHEN OTHERS THEN -- 错误处理：返回NULL或原始字符串 RETURN NULL; END f_substr; / set linesize 300 pagesize 9999 col object_name for a30 col object_type for a20 col c2 for a30 SELECT t1.object_name ,t3.object_type ,t3.object_name c2 FROM t1 left JOIN t3 ON t1.object_name = t3.object_name WHERE f_substr(t3.object_type, 1, 4) = 'RULE';
Oracle 测试结果
Elapsed: 00:01:01.80
SQL> @xp
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8fn0n0y41qap3, child number 0
-------------------------------------
SELECT t1.object_name ,t3.object_type ,t3.object_name c2 FROM t1 LEFT
JOIN t3 ON t1.object_name = t3.object_name WHERE
f_substr(t3.object_type, 1, 4) = 'RULE'
Plan hash value: 2389609966
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 64024 (100)| | 24 |00:01:01.80 | 174K|
|* 1 | FILTER | | 1 | | | | 24 |00:01:01.80 | 174K|
|* 2 | HASH JOIN RIGHT OUTER| | 1 | 11M| 64024 (1)| 00:00:03 | 23M|00:00:06.46 | 174K|
| 3 | TABLE ACCESS FULL | T3 | 1 | 72655 | 394 (1)| 00:00:01 | 72656 |00:00:00.01 | 1436 |
| 4 | TABLE ACCESS FULL | T1 | 1 | 10M| 48722 (1)| 00:00:02 | 10M|00:00:01.68 | 172K|
-----------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2BFA4EE4
3 - SEL$2BFA4EE4 / T3@SEL$1
4 - SEL$2BFA4EE4 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2BFA4EE4")
MERGE(@"SEL$8812AA4E" >"SEL$948754D7")
OUTLINE(@"SEL$948754D7")
ANSI_REARCH(@"SEL$2")
OUTLINE(@"SEL$8812AA4E")
ANSI_REARCH(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
FULL(@"SEL$2BFA4EE4" "T1"@"SEL$1")
FULL(@"SEL$2BFA4EE4" "T3"@"SEL$1")
LEADING(@"SEL$2BFA4EE4" "T1"@"SEL$1" "T3"@"SEL$1")
USE_HASH(@"SEL$2BFA4EE4" "T3"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$2BFA4EE4" "T3"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("F_SUBSTR"("T3"."OBJECT_TYPE",1,4)='RULE')
2 - access("T1"."OBJECT_NAME"="T3"."OBJECT_NAME")
Query Block Registry:
---------------------
<q o="68" h="y"><n><![CDATA[SEL$8812AA4E]]></n><p><![CDATA[SEL$1]]></p><f><h><t><![CDATA[T1]]></t><s><![C
DATA[SEL$1]]></s></h><h><t><![CDATA[T3]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
<q o="2"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[T1]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[T3
]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
<q o="2"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[from$_subquery$_003]]></t><s><![CDATA[SEL$2]]></s></h>
</f></q>
<q o="18" f="y" h="y"><n><![CDATA[SEL$2BFA4EE4]]></n><p><![CDATA[SEL$948754D7]]></p><i><o><t>VW</t><v><![
CDATA[SEL$8812AA4E]]></v></o></i><f><h><t><![CDATA[T1]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[T3
]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
<q o="67" h="y"><n><![CDATA[SEL$948754D7]]></n><p><![CDATA[SEL$2]]></p><i><o><t>VW</t><v><![CDATA[SEL$881
2AA4E]]></v></o></i><f><h><t><![CDATA[from$_subquery$_003]]></t><s><![CDATA[SEL$2]]></s></h></f></q>
75 rows selected.
Elapsed: 00:00:00.03
SQL>
YashanDB测试结果
Execution Plan
----------------------------------------------------------------
SQL hash value: 1353645138
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | 24| | 28747783| 25| 0| 0| |
|* 1 | RESULT | | | 253105| 24| 282928( 0)| 28747774| 25| 0| 0| |
|* 2 | HASH JOIN LEFT OUTER | | | 11895909| 14521269| 281802( 0)| 4103866| 14521270| 0| 0| |
| 3 | TABLE ACCESS FULL | T1 | YASTEST | 10000000| 10000000| 280979( 0)| 1085559| 10000001| 0| 0| |
| 4 | TABLE ACCESS FULL | T3 | YASTEST | 72656| 72656| 223( 0)| 6199| 72657| 0| 0| |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
1 - Predicate : filter(F_SUBSTR("T3"."OBJECT_TYPE"[OPTMZ-0], 1[OPTMZ-0], 4[OPTMZ-0])[OPTMZ-0] = 'RULE'[OPTMZ-0])
2 - Execution : Algorithm : 1 PartCount : 1 BuildCount : 72656 BuildDistinct : 60636 BuildSize : 4947997 IsCacheProbe : 0
Predicate : access("T1"."OBJECT_NAME"[OPTMZ-3] = "T3"."OBJECT_NAME"[OPTMZ-3])
Statistics
----------------------------------------------------------------------------------------------------
0 physical reads
183067 db block gets
0 consistent gets
0 redo size
0 recursive calls
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
24 rows processed
0 bytes sent via PX
0 block received
56 rows fetched.
Elapsed: 00:00:28.761
SQL>
小结
场景四、谓词带用户定义函数 (User-Defined Functions, UDF)且过滤被驱动表(yashandb性能较优) 1. 性能对比： Oracle使用 HASH JOIN RIGHT OUTER,耗时：61.80秒 yashandb使用 HASH JOIN RIGHT OUTER,耗时：28.76秒 2. yashandb对用户定义函数 (User-Defined Functions, UDF)，也不支持查询转换 3. 执行计划相同，yashandb性能比oracle快2.15倍，说明yashandb的hash join的性能更好。
参考资料
http://www.minniebaby.tech/2025/09/08/oracle%e4%bc%98%e5%8c%96%e5%99%a8%e7%bc%ba%e9%99%b7%e4%b8%89/ https://www.anbob.com/archives/9362.html
最后修改时间：2026-01-19 09:38:41
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
