Oracle数据库优化器经过数十年的发展，已经具备了相当成熟的自动查询转换能力。无论是面对编写“欠佳”的SQL语句，还是原本规范但在执行时存在更优路径的SQL，优化器通常都能将其等价转换为较优或最优的执行方案。正因如此，在将Oracle迁移至其他异构数据库时，由于优化器在这方面的能力差异，相同的SQL语句执行效率可能会下降。
然而最近遇到一个场景却恰恰相反：Oracle优化器在该情况下存在一定的缺陷，而国产数据库YashanDB的表现却超出了预期。接下来我将具体展示这一情况。
Oralce 测试
测试案例
$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 9 08:47:15 2026
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> create table t2 as select * from t1;
Table created.
SQL> SELECT t1.object_name
,t2.object_type
,t2.object_name n1
FROM t1
LEFT JOIN t2 ON t1.object_name = t2.object_name AND t2.object_id = 100
WHERE t2.object_type = 'VIEW';
SQL> @xall
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID drhqht77m9891, child number 0
-------------------------------------
SELECT t1.object_name ,t2.object_type ,t2.object_name
n1 FROM t1 LEFT JOIN t2 ON t1.object_name = t2.object_name AND
t2.object_id = 100 WHERE t2.object_type = 'VIEW'
Plan hash value: 3467165580
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 726 (100)| | | | |
|* 1 | HASH JOIN | | 1 | 116 | 726 (1)| 00:00:01 | 1014K| 1014K| 221K (0)|
|* 2 | TABLE ACCESS FULL| T2 | 1 | 50 | 363 (1)| 00:00:01 | | | |
| 3 | TABLE ACCESS FULL| T1 | 65092 | 4195K| 363 (1)| 00:00:01 | | | |
-------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$683367AA
2 - SEL$683367AA / T2@SEL$1
3 - SEL$683367AA / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$683367AA")
OUTER_JOIN_TO_INNER(@"SEL$2BFA4EE4" "T2"@"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$683367AA" "T2"@"SEL$1")
FULL(@"SEL$683367AA" "T1"@"SEL$1")
LEADING(@"SEL$683367AA" "T2"@"SEL$1" "T1"@"SEL$1")
USE_HASH(@"SEL$683367AA" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME")
2 - filter(("T2"."OBJECT_ID"=100 AND "T2"."OBJECT_TYPE"='VIEW'))Note: 虽然sql写的是left [out] join, 但是oracle 优化器转换成了inner hash join 提前过滤了view 谓词，效率更加，减少了不必要的外层filter。
另外注意这里hash join 使用了小表t2做为build table（驱动表）,在内存构建hash table， 大表t2为 probe table（探测表或被驱动表），准确的说驱动表与被驱动表是nestloop join中的叫法.
上面的执行计划还有一个要注意的地方是outline部分，OUTER_JOIN_TO_INNER 提示有转换outer join to inner join; 同时 leading +use_hash 指定了join 驱动顺序。 outer to inner join该特性在oracle 11g就存在，众所周知在oracle一些特性除了sql hint还有隐藏参数对应该参数是_optimizer_outer_join_to_inner defult true. 查看参数
SQL> @pd outer_join
Show all parameters and session values from x$ksppi/x$ksppcv...
NUM N_HEX NAME VALUE DESCRIPTION
---------- ---------- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
3839 EFF _improved_outerjoin_card TRUE improved outer-join cardinality calculation
4073 FE9 _optimizer_outer_to_anti_enabled TRUE Enable transformation of outer-join to anti-join if possible
4122 101A _optimizer_native_full_outer_join FORCE execute full outer join using native implementaion
4126 101E _optimizer_ansi_rearchitecture TRUE re-architecture of ANSI left, right, and full outer joins
4162 1042 _mv_generalized_oj_refresh_opt TRUE enable/disable new algorithm for MJV with generalized outer joins
4230 1086 _optimizer_outer_join_to_inner TRUE enable/disable outer to inner join conversion
4244 1094 _optimizer_full_outer_join_to_outer TRUE enable/disable full outer to left outer join conversion
4462 116E _px_join_skew_null_handling TRUE enables null skew handling improvement for parallel outer joins
8 rows selected.
oracle增加函数
下面对维持条件增加个简单的过滤substr，再看是否可能把谓词提升并且outer join转换为inner join？
SQL> SELECT t1.object_name ,t2.object_type,t2.object_name n1
FROM t1
LEFT JOIN t2 ON t1.object_name =t2.object_name AND t2.object_id = 100
WHERE substr(t2.object_type, 1,4) = 'VIEW'
SQL> @xall
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID b9pdhmkg68f1y, child number 0
-------------------------------------
SELECT t1.object_name ,t2.object_type ,t2.object_name n1 FROM
t1 LEFT JOIN t2 ON t1.object_name = t2.object_name AND t2.object_id =
100 WHERE substr(t2.object_type, 1, 4) = 'VIEW'
Plan hash value: 721054564
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 726 (100)| | | | |
|* 1 | FILTER | | | | | | | | |
|* 2 | HASH JOIN RIGHT OUTER| | 65092 | 7373K| 726 (1)| 00:00:01 | 1744K| 1744K| 458K (0)|
|* 3 | TABLE ACCESS FULL | T2 | 1 | 50 | 363 (1)| 00:00:01 | | | |
| 4 | TABLE ACCESS FULL | T1 | 65092 | 4195K| 363 (1)| 00:00:01 | | | |
-----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2BFA4EE4
3 - SEL$2BFA4EE4 / T2@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')
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" "T2"@"SEL$1")
LEADING(@"SEL$2BFA4EE4" "T1"@"SEL$1" "T2"@"SEL$1")
USE_HASH(@"SEL$2BFA4EE4" "T2"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$2BFA4EE4" "T2"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUBSTR("T2"."OBJECT_TYPE",1,4)='VIEW')
2 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME")
3 - filter("T2"."OBJECT_ID"=100)Note: 增加函数后，Oracle选择了不再转换，而是“HASH JOIN RIGHT OUTER”， 在#1最外层做了filter view的条件， 这SQL 语义上也是可以等价转换像上面的一样的，结果没有。 另外注意outline部分 leading+use_hash hint是t1 驱动表，及SWAP_JOIN_INPUTS 指定t2是被驱动表，结果与执行计划相反，说明oracle 优化器在这个场景出现了缺陷或bug.
YashanDB（崖山） 测试
相同的测试场景，只是表数据差异。查看yashan是否学习了oracle的缺陷？
$ sh conn.sh
YashanDB SQL Enterprise Edition Release 23.5.1.100 x86_64
Connected to:
YashanDB Server Enterprise Edition Release 23.5.1.100 x86_64 - Linux
SQL> create table t1 as select * from dba_objects;
Succeed.
SQL> create table t2 as select * from t1;
Succeed.
SQL> explain SELECT t1.object_name
,t2.object_type
,t2.object_name n1
FROM t1
LEFT JOIN t2 ON t1.object_name = t2.object_name AND t2.object_id = 100
WHERE t2.object_type = 'VIEW';
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 2656247616
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN INNER | | | 2| 52( 0)| |
| 2 | JOIN FILTER USE | | | 2312| 25( 0)| |
|* 3 | TABLE ACCESS FULL | T1 | SYS | 2312| 25( 0)| |
|* 4 | JOIN FILTER CREATE | | | 1| 26( 0)| |
|* 5 | TABLE ACCESS FULL | T2 | SYS | 1| 26( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
1 - Predicate : access("T1"."OBJECT_NAME" = "T2"."OBJECT_NAME")
3 - Predicate : RUNTIME FILTER(RUNTIME USE(0): "T1"."OBJECT_NAME")
4 - Predicate : RUNTIME FILTER(RUNTIME CREATE(0): "T2"."OBJECT_NAME")
5 - Predicate : filter("T2"."OBJECT_ID" = 100 AND "T2"."OBJECT_TYPE" = 'VIEW')Note : 不错，yashanDB 也做到了outer join to inner, 执行计划显示了”HASH JOIN INNER”.
yashandb增加函数
SQL> explain plan for
SELECT t1.object_name
,t2.object_type
,t2.object_name n1
FROM t1
LEFT JOIN t2 ON t1.object_name = t2.object_name AND t2.object_id = 100
WHERE substr(t2.object_type, 1, 4) = 'VIEW';
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 1531094591
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN INNER | | | 2| 53( 0)| |
| 2 | JOIN FILTER USE | | | 2312| 25( 0)| |
|* 3 | TABLE ACCESS FULL | T1 | SYS | 2312| 25( 0)| |
|* 4 | JOIN FILTER CREATE | | | 1| 26( 0)| |
|* 5 | TABLE ACCESS FULL | T2 | SYS | 1| 26( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
1 - Predicate : access("T1"."OBJECT_NAME" = "T2"."OBJECT_NAME")
3 - Predicate : RUNTIME FILTER(RUNTIME USE(0): "T1"."OBJECT_NAME")
4 - Predicate : RUNTIME FILTER(RUNTIME CREATE(0): "T2"."OBJECT_NAME")
5 - Predicate : filter("T2"."OBJECT_ID" = 100 AND SUBSTR("T2"."OBJECT_TYPE", 1, 4) = 'VIEW')Note: 不错，yashanDB在where条件增加了函数时，同样可以做到转换为inner join. 不存在oracle的缺陷。
细心的朋友可能发现oracle 是 hash join t2（小表）做为驱动表，崖山这里使用了t1，对于hash join 不是说应该把小表做为驱动表吗？ 难道崖山这里不对？不是的， 看”join filter create/use”关键字，崖山居然还支持Bloom filter. 布隆过滤可以做到查询的提前过滤,#3#4有运行时runtime字样，关于bloom filter可以自行了解原因，这里不再描述。 其实YashanDB也可以指定hash join顺序，因为它也兼容了oracle的SQL hint，如下
SQL> set autot on
SQL> SELECT /*+leading(t2,t1) use_hash(t1)*/ t1.object_name
,t2.object_type
,t2.object_name n1
FROM t1
LEFT JOIN t2 ON t1.object_name = t2.object_name AND t2.object_id = 100
WHERE substr(t2.object_type, 1, 4) = 'VIEW';
OBJECT_NAME OBJECT_TYPE N1
---------------------------------------------------------------- --------------------- ----------------------------------------------------------------
Execution Plan
----------------------------------------------------------------
SQL hash value: 3507426962
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | | | | | | |
|* 1 | HASH JOIN INNER | | | 2| | 293( 0)| | | | | |
|* 2 | TABLE ACCESS FULL | T2 | SYS | 1| | 26( 0)| | | | | |
| 3 | TABLE ACCESS FULL | T1 | SYS | 2312| | 25( 0)| | | | | |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
1 - Predicate : access("T2"."OBJECT_NAME" = "T1"."OBJECT_NAME")
2 - Predicate : filter("T2"."OBJECT_ID" = 100 AND SUBSTR("T2"."OBJECT_TYPE", 1, 4) = 'VIEW')
Statistics
----------------------------------------------------------------------------------------------------
17 rows fetched.Note: 没错，这里在yashanDB执行的，连autotrace也支持，并且输出的执行计划和oracle几乎一样，这里不确认为什么statistics没有输出，可能存在小缺陷。 总之对国内数万名Oracle DBA非常友善，YashanDB对oracle兼容不仅照顾了开发，同时还兼顾了运维，这点点赞。
其他国产数据库
都到这里了，我习惯性再其他几个国产库试一下，这里不做评价，仅描述事实。如发现错误之处可以联系我。
GausDB (及opengauss系)
--GaussDB Kernel 505.2.0
gauss=# create table t1 as select oid,t.* from pg_class t;
INSERT 0 1429
gauss=# create table t2 as select * from t1;
INSERT 0 1429
gauss=# explain SELECT t1.relname
,t2.relkind
,t2.relname n1
FROM t1
LEFT JOIN t2 ON t1.relname = t2.relname AND t2.oid = 100
WHERE substr(t2.relkind, 1,1) = 'v';
QUERY PLAN
------------------------------------------------------------------------------------------
Hash Join (cost=70.43..133.32 rows=12 width=129)
Hash Cond: (t1.relname = t2.relname)
-> Seq Scan on t1 (cost=0.00..58.20 rows=1220 width=64)
-> Hash (cost=70.40..70.40 rows=2 width=65)
-> Seq Scan on t2 (cost=0.00..70.40 rows=2 width=65)
Filter: ((oid = 100::oid) AND (substr((relkind)::text, 1, 1) = 'v'::text))
Kingbase(及Postgresql系)
-- v8
test=# create table t1 as select * from pg_class;
SELECT 521
test=# create table t2 as select * from t1;
SELECT 521
test=# explain SELECT t1.relname
test-# ,t2.relkind
test-# ,t2.relname n1
test-# FROM t1
test-# LEFT JOIN t2 ON t1.relname = t2.relname AND t2.oid = 100
test-# WHERE substr(t2.relkind, 1,1) = 'v'
test-# ;
QUERY PLAN
--------------------------------------------------------------------------------------------
Hash Join (cost=24.43..45.61 rows=1 width=129)
Hash Cond: (t1.relname = t2.relname)
-> Seq Scan on t1 (cost=0.00..19.21 rows=521 width=64)
-> Hash (cost=24.42..24.42 rows=1 width=65)
-> Seq Scan on t2 (cost=0.00..24.42 rows=1 width=65)
Filter: ((oid = '100'::oid) AND (substr((relkind)::text, 1, 1) = 'v'::text))
(6 rows)
Oceanbase
-- OceanBase 4.2.5.3
obclient(SYS@orcl)[SYS]> create table t1 as select * from dba_objects;
Query OK, 2792 rows affected (1.915 sec)
obclient(SYS@orcl)[SYS]> create table t2 as select * from t1;
Query OK, 2792 rows affected (1.312 sec)
obclient(SYS@orcl)[SYS]> explain SELECT t1.object_name ,t2.object_type,t2.object_name n1
-> FROM t1
-> LEFT JOIN t2 ON t1.object_name =t2.object_name AND t2.object_id = 100
-> WHERE substr(t2.object_type, 1,4) = 'VIEW';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| ========================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ---------------------------------------------------------- |
| |0 |HASH RIGHT OUTER JOIN | |2758 |961 | |
| |1 |├─PX COORDINATOR | |2 |142 | |
| |2 |│ └─EXCHANGE OUT DISTR|:EX10000|2 |141 | |
| |3 |│ └─TABLE FULL SCAN |T2 |2 |139 | |
| |4 |└─TABLE FULL SCAN |T1 |2792 |85 | |
| ========================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T1.OBJECT_NAME], [T2.OBJECT_TYPE], [T2.OBJECT_NAME]), filter([SUBSTR(T2.OBJECT_TYPE, 1, 4) = cast('VIEW', VARCHAR2(1048576 ))]), rowset=256 |
| equal_conds([T1.OBJECT_NAME = T2.OBJECT_NAME]), other_conds(nil) |
| 1 - output([T2.OBJECT_TYPE], [T2.OBJECT_NAME]), filter(nil), rowset=256 |
| 2 - output([T2.OBJECT_TYPE], [T2.OBJECT_NAME]), filter(nil), rowset=256 |
| is_single, dop=1 |
| 3 - output([T2.OBJECT_NAME], [T2.OBJECT_TYPE]), filter([T2.OBJECT_ID = 100]), rowset=256 |
| access([T2.OBJECT_NAME], [T2.OBJECT_ID], [T2.OBJECT_TYPE]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([T2.__pk_increment]), range(MIN ; MAX)always true |
| 4 - output([T1.OBJECT_NAME]), filter(nil), rowset=256 |
| access([T1.OBJECT_NAME]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T1.__pk_increment]), range(MIN ; MAX)always true |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
24 rows in set (0.207 sec)
obclient(SYS@orcl)[SYS]> explain SELECT t1.object_name
-> ,t2.object_type
-> ,t2.object_name n1
-> FROM t1
-> LEFT JOIN t2 ON t1.object_name = t2.object_name AND t2.object_id = 100
-> WHERE t2.object_type = 'VIEW';
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| ========================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ---------------------------------------------------------- |
| |0 |HASH JOIN | |1 |1046 | |
| |1 |├─PX COORDINATOR | |1 |227 | |
| |2 |│ └─EXCHANGE OUT DISTR|:EX10000|1 |226 | |
| |3 |│ └─TABLE FULL SCAN |T2 |1 |226 | |
| |4 |└─TABLE FULL SCAN |T1 |2792 |85 | |
| ========================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T1.OBJECT_NAME], [T2.OBJECT_TYPE], [T2.OBJECT_NAME]), filter(nil), rowset=256 |
| equal_conds([T1.OBJECT_NAME = T2.OBJECT_NAME]), other_conds(nil) |
| 1 - output([T2.OBJECT_TYPE], [T2.OBJECT_NAME]), filter(nil), rowset=256 |
| 2 - output([T2.OBJECT_TYPE], [T2.OBJECT_NAME]), filter(nil), rowset=256 |
| is_single, dop=1 |
| 3 - output([T2.OBJECT_NAME], [T2.OBJECT_TYPE]), filter([T2.OBJECT_ID = 100], [T2.OBJECT_TYPE = cast('VIEW', VARCHAR2(1048576 ))]), rowset=256 |
| access([T2.OBJECT_NAME], [T2.OBJECT_ID], [T2.OBJECT_TYPE]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false,false], |
| range_key([T2.__pk_increment]), range(MIN ; MAX)always true |
| 4 - output([T1.OBJECT_NAME]), filter(nil), rowset=256 |
| access([T1.OBJECT_NAME]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T1.__pk_increment]), range(MIN ; MAX)always true |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
24 rows in set (0.012 sec)
— enjoy —