总结说明
目前优化器很多策略偏向保守，NLJ 计划最差的场景会非常慢，比如左表估计扫的行数和实际扫的行数偏差非常大，这时 NLJ 就是差计划，只有明显有收益的场景（右表数据量比较大并且有合适的索引），我们才会选到 NLJ。 这个案例中右表只有 40 行数据，NLJ 意义不大。
详细说明
在 OceanBase 数据库中，优化器的目标是在大多数情况下提供可接受的执行计划，而非总是选择理论上最优的计划。当两张表进行连接操作且右表的数据量较小时，即使两边的表都有索引，优化器也可能选择 Hash join 作为连接算法。这是因为优化器的策略倾向于保守，避免在估行不准确的情况下选择可能导致性能显著下降的 NLJ。如果左表的扫描行数与实际行数存在较大偏差，或者连接字段的数据分布存在倾斜，NLJ 可能会成为较差的选择，因此，如果没有明显收益的场景，优化器更倾向于选择 hash join，以确保整体性能的稳定性。若用户希望在特定场景下使用 NLJ，可以通过在 SQL 语句中使用 Hint 来指定连接算法。
优化器生成 Hash join 时的 XPLAN
| ===================================================================================================
|ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)|
---------------------------------------------------------------------------------------------------
|0 |HASH JOIN | |1 |14 |0 |1056 |0 |68 |
|1 |├─TABLE RANGE SCAN|AR |1 |5 |0 |1056 |0 |103 |
|2 |└─TABLE FULL SCAN |R |1 |9 |0 |1056 |0 |0 |
===================================================================================================
Outputs & filters:
-------------------------------------
0 - output([R.id], [R.name], [R.valid], [R.description], [R.create_by], [R.create_timestamp], [R.update_by], [R.update_timestamp], [AR.admin_id], [AR.role_id]), filter(nil), rowset=16
equal_conds([R.id = AR.role_id]), other_conds(nil)
1 - output([AR.admin_id], [AR.role_id]), filter(nil), rowset=16
access([AR.admin_id], [AR.role_id]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([AR.admin_id], [AR.role_id]), range(5,MIN ; 5,MAX), (2,MIN ; 2,MAX),
range_cond([AR.admin_id IN (5, 2)])
2 - output([R.id], [R.name], [R.valid], [R.description], [R.create_by], [R.create_timestamp], [R.update_by], [R.update_timestamp]), filter(nil), rowset=16
access([R.id], [R.name], [R.valid], [R.description], [R.create_by], [R.create_timestamp], [R.update_by], [R.update_timestamp]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([R.id]), range(MIN ; MAX)always true
Used Hint:
-------------------------------------
/*+
*/
Qb name trace:
-------------------------------------
stmt_id:0, SEL$1 > SEL$354178DD > SEL$0A4D2E68
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
LEADING(@"SEL$0A4D2E68" ("AR"@"SEL$1" "R"@"SEL$1"))
USE_HASH(@"SEL$0A4D2E68" "R"@"SEL$1")
INDEX(@"SEL$0A4D2E68" "AR"@"SEL$1" "primary")
FULL(@"SEL$0A4D2E68" "R"@"SEL$1")
SIMPLIFY_DISTINCT(@"SEL$1")
OUTER_TO_INNER(@"SEL$354178DD")
OPTIMIZER_FEATURES_ENABLE('4.3.5.2')
END_OUTLINE_DATA
*/
Optimization Info:
-------------------------------------
AR:
table_rows:2000
physical_range_rows:1
logical_range_rows:1
index_back_rows:0
output_rows:1
table_dop:1
dop_method:Table DOP
avaiable_index_name:[sys_admin_role]
stats info:[version=2025-12-01 15:16:51.987757, is_locked=0, is_expired=0]
dynamic sampling level:0
estimation method:[OPTIMIZER STATISTICS, STORAGE]
R:
table_rows:50
physical_range_rows:1
logical_range_rows:1
index_back_rows:0
output_rows:1
table_dop:1
dop_method:Table DOP
avaiable_index_name:[uidx_name, sys_role]
pruned_index_name:[uidx_name]
stats info:[version=2025-12-01 15:16:51.827683, is_locked=0, is_expired=0]
dynamic sampling level:0
estimation method:[OPTIMIZER STATISTICS, STORAGE]
Plan Type:
LOCAL
Parameters:
:0 => 5
:1 => 2
Note:
Degree of Parallelisim is 1 because of table property
|
+---------------------------------------------------------------------------------------------------------------------------------------------
使用 Hint 指定 nlj 时的 xplan
| ===================================================================================================
|ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)|
---------------------------------------------------------------------------------------------------
|0 |NESTED-LOOP JOIN | |1 |21 |0 |0 |0 |4 |
|1 |├─TABLE RANGE SCAN|AR |1 |5 |0 |0 |0 |32 |
|2 |└─TABLE GET |R |1 |16 |0 |0 |0 |0 |
===================================================================================================
Outputs & filters:
-------------------------------------
0 - output([R.id], [R.name], [R.valid], [R.description], [R.create_by], [R.create_timestamp], [R.update_by], [R.update_timestamp], [AR.admin_id], [AR.role_id]), filter(nil), rowset=16
conds(nil), nl_params_([AR.role_id(:0)]), use_batch=true
1 - output([AR.admin_id], [AR.role_id]), filter(nil), rowset=16
access([AR.admin_id], [AR.role_id]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([AR.admin_id], [AR.role_id]), range(5,MIN ; 5,MAX), (2,MIN ; 2,MAX),
range_cond([AR.admin_id IN (5, 2)])
2 - output([R.id], [R.name], [R.valid], [R.description], [R.create_by], [R.create_timestamp], [R.update_by], [R.update_timestamp]), filter(nil), rowset=16
access([GROUP_ID], [R.id], [R.name], [R.valid], [R.description], [R.create_by], [R.create_timestamp], [R.update_by], [R.update_timestamp]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([R.id]), range(MIN ; MAX),
range_cond([R.id = :0])
Used Hint:
-------------------------------------
/*+
LEADING(("ar" "r"))
USE_NL("r")
*/
Qb name trace:
-------------------------------------
stmt_id:0, SEL$1 > SEL$354178DD > SEL$0A4D2E68
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
LEADING(@"SEL$0A4D2E68" ("AR"@"SEL$1" "R"@"SEL$1"))
USE_NL(@"SEL$0A4D2E68" "R"@"SEL$1")
INDEX(@"SEL$0A4D2E68" "AR"@"SEL$1" "primary")
INDEX(@"SEL$0A4D2E68" "R"@"SEL$1" "primary")
SIMPLIFY_DISTINCT(@"SEL$1")
OUTER_TO_INNER(@"SEL$354178DD")
OPTIMIZER_FEATURES_ENABLE('4.3.5.2')
END_OUTLINE_DATA
*/
Optimization Info:
-------------------------------------
AR:
table_rows:2000
physical_range_rows:1
logical_range_rows:1
index_back_rows:0
output_rows:1
table_dop:1
dop_method:Table DOP
avaiable_index_name:[sys_admin_role]
stats info:[version=2025-12-01 15:16:51.987757, is_locked=0, is_expired=0]
dynamic sampling level:0
estimation method:[OPTIMIZER STATISTICS, STORAGE]
R:
table_rows:50
physical_range_rows:1
logical_range_rows:1
index_back_rows:0
output_rows:1
table_dop:1
dop_method:Table DOP
avaiable_index_name:[uidx_name, sys_role]
pruned_index_name:[uidx_name]
stats info:[version=2025-12-01 15:16:51.827683, is_locked=0, is_expired=0]
dynamic sampling level:0
estimation method:[OPTIMIZER STATISTICS]
Plan Type:
LOCAL
Parameters:
:0 => 5
:1 => 2
Note:
Degree of Parallelisim is 1 because of table property
适用版本
OceanBase 数据库所有版本。
