【 使用环境 】测试环境
【 OB or 其他组件 】ob4.0-ce
【 使用版本 】ob4.0-ce
【问题描述】有6张表使用order_id进行关联，order_id都是主键，ob默认使用的nested loop连接方式，查询需要19秒，使用hint强制让ob走hash join后，查询9.8秒出结果，为什么ob没有选择最优的hash join执行计划？
【复现路径】下面是6张表的行数：
search_collection 11443340
customer_idcard_info 1408869
order_time_info 1389190
order_status_info 1551894
sale_personnel_info 13897647
order_property_info 6338163
（1）、不使用hint，查询sql是：
select count() from search_collection_or sc,customer_idcard_info_or cii,order_time_info_or oti,order_status_info_or osi,sale_personnel_info_or spi,order_property_info_or opi
WHERE oti.order_id =sc.order_id and sc.order_id =cii.order_id and sc.order_id = spi.order_id and sc.order_id=osi.order_id
and sc.order_id = opi.order_id and opi.is_speak = ‘1’ and oti.in_zsapp_time is not null;
执行计划是：
explain extended select count() from search_collection sc,customer_idcard_info cii,order_time_info oti,order_status_info osi,sale_personnel_info spi,order_property_info opi WHERE oti.order_id =sc.order_id and sc.order_id =cii.order_id and sc.order_id = spi.order_id and sc.order_id=osi.order_id and sc.order_id = opi.order_id and opi.is_speak = ‘1’ and oti.in_zsapp_time is not null\G
*************************** 1. row ***************************
Query Plan: Plan signature: 9790605396299211230
|ID|OPERATOR |NAME|EST. ROWS|COST |
|0 |EXCHANGE IN REMOTE | |1 |1392081|
|1 | EXCHANGE OUT REMOTE | |1 |1392081|
|2 | SCALAR GROUP BY | |1 |1392081|
|3 | NESTED-LOOP JOIN | |299 |1392075|
|4 | MERGE JOIN | |2204 |1356528|
|5 | NESTED-LOOP JOIN | |17899 |1293384|
|6 | MERGE JOIN | |17899 |1004657|
|7 | MERGE JOIN | |358062 |600733 |
|8 | TABLE SCAN |oti |356959 |90984 |
|9 | TABLE SCAN |spi |13897647 |412582 |
|10| TABLE SCAN |opi |692551 |358374 |
|11| DISTRIBUTED TABLE GET|sc |1 |16 |
|12| TABLE SCAN |cii |1408869 |55141 |
|13| DISTRIBUTED TABLE GET |osi |1 |16 |
Outputs & filters:
0 - output([T_FUN_COUNT()(0x7f5388a488a0)]), filter(nil)
1 - output([T_FUN_COUNT()(0x7f5388a488a0)]), filter(nil)
2 - output([T_FUN_COUNT()(0x7f5388a488a0)]), filter(nil), rowset=256,
group(nil), agg_func([T_FUN_COUNT()(0x7f5388a488a0)])
3 - output(nil), filter(nil), rowset=256,
conds(nil), nl_params_([spi.order_id(0x7f5388a43750)]), batch_join=true
4 - output([spi.order_id(0x7f5388a43750)]), filter(nil), rowset=256,
equal_conds([cii.order_id(0x7f5388a424f0) = spi.order_id(0x7f5388a43750)(0x7f5388a4c4f0)]), other_conds(nil),
merge_directions([ASC])
5 - output([spi.order_id(0x7f5388a43750)]), filter(nil), rowset=256,
conds(nil), nl_params_([spi.order_id(0x7f5388a43750)]), batch_join=true
6 - output([spi.order_id(0x7f5388a43750)]), filter(nil), rowset=256,
equal_conds([spi.order_id(0x7f5388a43750) = opi.order_id(0x7f5388a45c10)(0x7f5388a4e670)]), other_conds(nil),
merge_directions([ASC])
7 - output([spi.order_id(0x7f5388a43750)]), filter(nil), rowset=256,
equal_conds([oti.order_id(0x7f5388a40fb0) = spi.order_id(0x7f5388a43750)(0x7f5388a4abd0)]), other_conds(nil),
merge_directions([ASC])
8 - output([oti.order_id(0x7f5388a40fb0)]), filter([(T_OP_IS_NOT, oti.in_zsapp_time(0x7f5388a482a0), NULL, 0)(0x7f5388a47910)]), rowset=256,
access([oti.order_id(0x7f5388a40fb0)], [oti.in_zsapp_time(0x7f5388a482a0)]), partitions(p0),
is_index_back=false, filter_before_indexback[false],
range_key([oti.order_id(0x7f5388a40fb0)]), range(MIN ; MAX)always true
9 - output([spi.order_id(0x7f5388a43750)]), filter(nil), rowset=256,
access([spi.order_id(0x7f5388a43750)]), partitions(p0),
is_index_back=false,
range_key([spi.order_id(0x7f5388a43750)]), range(MIN ; MAX)always true
10 - output([opi.order_id(0x7f5388a45c10)]), filter([opi.is_speak(0x7f5388a46e00) = ‘1’(0x7f5388a46720)]), rowset=256,
access([opi.order_id(0x7f5388a45c10)], [opi.is_speak(0x7f5388a46e00)]), partitions(p0),
is_index_back=false, filter_before_indexback[false],
range_key([opi.order_id(0x7f5388a45c10)]), range(MIN ; MAX)always true
11 - output(nil), filter(nil), rowset=256,
access([GROUP_ID(0x7f5440134970)]), partitions(p0),
is_index_back=false,
range_key([sc.order_id(0x7f5388a41290)]), range(MIN ; MAX),
range_cond([sc.order_id(0x7f5388a41290) = ?(0x7f5388b6f330)])
12 - output([cii.order_id(0x7f5388a424f0)]), filter(nil), rowset=256,
access([cii.order_id(0x7f5388a424f0)]), partitions(p0),
is_index_back=false,
range_key([cii.order_id(0x7f5388a424f0)]), range(MIN ; MAX)always true
13 - output(nil), filter(nil), rowset=256,
access([GROUP_ID(0x7f5440136b60)]), partitions(p0),
is_index_back=false,
range_key([osi.order_id(0x7f5388a449b0)]), range(MIN ; MAX),
range_cond([? = osi.order_id(0x7f5388a449b0)(0x7f5319e2ac50)])
Used Hint:
/*+
*/
Qb name trace:
stmt_id:0, stmt_type:T_EXPLAIN
stmt_id:1, SEL$1 > SEL$0208448F
Outline Data:
/*+
BEGIN_OUTLINE_DATA
LEADING(@“SEL$0208448F” (((((“order_center”.“oti”@“SEL$1” “order_center”.“spi”@“SEL$1”) “order_center”.“opi”@“SEL$1”) “order_center”.“sc”@“SEL$1”) “order_center”.“cii”@“SEL$1”) “order_center”.“osi”@“SEL$1”))
USE_NL(@“SEL$0208448F” “order_center”.“osi”@“SEL$1”)
USE_MERGE(@“SEL$0208448F” “order_center”.“cii”@“SEL$1”)
USE_NL(@“SEL$0208448F” “order_center”.“sc”@“SEL$1”)
USE_MERGE(@“SEL$0208448F” “order_center”.“opi”@“SEL$1”)
USE_MERGE(@“SEL$0208448F” “order_center”.“spi”@“SEL$1”)
FULL(@“SEL$0208448F” “order_center”.“oti”@“SEL$1”)
FULL(@“SEL$0208448F” “order_center”.“spi”@“SEL$1”)
FULL(@“SEL$0208448F” “order_center”.“opi”@“SEL$1”)
FULL(@“SEL$0208448F” “order_center”.“sc”@“SEL$1”)
USE_DAS(@“SEL$0208448F” “order_center”.“sc”@“SEL$1”)
FULL(@“SEL$0208448F” “order_center”.“cii”@“SEL$1”)
FULL(@“SEL$0208448F” “order_center”.“osi”@“SEL$1”)
USE_DAS(@“SEL$0208448F” “order_center”.“osi”@“SEL$1”)
PRED_DEDUCE(@“SEL$1”)
OPTIMIZER_FEATURES_ENABLE(‘4.0.0.0’)
END_OUTLINE_DATA
*/
Plan Type:
REMOTE
Optimization Info:
oti:table_rows:1346149, physical_range_rows:1389190, logical_range_rows:1389190, index_back_rows:0, output_rows:356958, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[order_time_info], estimation info[table_id:500068, (table_type:10, version:-1–1–1, logical_rc:1389190, physical_rc:1389190)]
spi:table_rows:13854821, physical_range_rows:13897647, logical_range_rows:13897647, index_back_rows:0, output_rows:13897647, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[sale_personnel_info], pruned_index_name[sale_id_idx], estimation info[table_id:500107, (table_type:10, version:-1–1–1, logical_rc:13897647, physical_rc:13897647)]
opi:table_rows:6307933, physical_range_rows:6338163, logical_range_rows:6338163, index_back_rows:0, output_rows:692550, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[order_property_info], estimation info[table_id:500093, (table_type:10, version:-1–1–1, logical_rc:6338163, physical_rc:6338163)]
sc:table_rows:11443340, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:local_storage, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback
cii:table_rows:1378636, physical_range_rows:1408869, logical_range_rows:1408869, index_back_rows:0, output_rows:1408869, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[customer_idcard_info], pruned_index_name[customer_idcard_info_customer_unique_id_IDX,customer_idcard_info_id_no_IDX], estimation info[table_id:500065, (table_type:10, version:-1–1–1, logical_rc:1408869, physical_rc:1408869)]
osi:table_rows:1509058, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:local_storage, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback
（2）、使用hint /+USE_HASH(sc, cii, oti, osi, spi, opi)/让sql走hash join：
走hash join的话，查询时间为9秒多
执行计划是：
explain extended select /+USE_HASH(sc, cii, oti, osi, spi, opi)/ count(*) from search_collection sc,customer_idcard_info cii,order_time_info oti,order_status_info osi,sale_personnel_info spi,order_property_info opi WHERE oti.order_id =sc.order_id and sc.order_id =cii.order_id and sc.order_id = spi.order_id and sc.order_id=osi.order_id and sc.order_id = opi.order_id and opi.is_speak = ‘1’ and oti.in_zsapp_time is not null\G
*************************** 1. row ***************************
Query Plan: Plan signature: 5477511262647619875
|ID|OPERATOR |NAME |EST. ROWS|COST |
|0 |SCALAR GROUP BY | |1 |4271320|
|1 | MERGE JOIN | |299 |4271315|
|2 | TABLE SCAN |sc |11443340 |402279 |
|3 | SORT | |18810 |3121236|
|4 | HASH JOIN | |18810 |3105013|
|5 | PX COORDINATOR | |18291 |2642510|
|6 | EXCHANGE OUT DISTR|:EX10000|18291 |2622576|
|7 | MERGE JOIN | |18291 |2577754|
|8 | TABLE SCAN |cii |1408869 |55141 |
|9 | MERGE JOIN | |17899 |2430511|
|10| TABLE SCAN |spi |13897647 |412582 |
|11| SORT | |356959 |1088565|
|12| HASH JOIN | |356959 |780765 |
|13| TABLE SCAN |oti |356959 |90984 |
|14| TABLE SCAN |opi |692551 |358374 |
|15| TABLE SCAN |osi |1551894 |44219 |
Outputs & filters:
0 - output([T_FUN_COUNT()(0x7f5401649e60)]), filter(nil), rowset=256,
group(nil), agg_func([T_FUN_COUNT()(0x7f5401649e60)])
1 - output(nil), filter(nil), rowset=256,
equal_conds([sc.order_id(0x7f5401642850) = spi.order_id(0x7f5401644d10)(0x7f5401644630)]), other_conds(nil),
merge_directions([ASC])
2 - output([sc.order_id(0x7f5401642850)]), filter(nil), rowset=256,
access([sc.order_id(0x7f5401642850)]), partitions(p0),
is_index_back=false,
range_key([sc.order_id(0x7f5401642850)]), range(MIN ; MAX)always true
3 - output([spi.order_id(0x7f5401644d10)]), filter(nil), rowset=256, sort_keys([spi.order_id(0x7f5401644d10), ASC])
4 - output([spi.order_id(0x7f5401644d10)]), filter(nil), rowset=256,
equal_conds([spi.order_id(0x7f5401644d10) = osi.order_id(0x7f5401645f70)(0x7f540164fcd0)]), other_conds(nil)
5 - output([spi.order_id(0x7f5401644d10)]), filter(nil), rowset=256
6 - output([spi.order_id(0x7f5401644d10)]), filter(nil), rowset=256, is_single, dop=1
7 - output([spi.order_id(0x7f5401644d10)]), filter(nil), rowset=256,
equal_conds([cii.order_id(0x7f5401643ab0) = spi.order_id(0x7f5401644d10)(0x7f540164e3b0)]), other_conds(nil),
merge_directions([ASC])
8 - output([cii.order_id(0x7f5401643ab0)]), filter(nil), rowset=256,
access([cii.order_id(0x7f5401643ab0)]), partitions(p0),
is_index_back=false,
range_key([cii.order_id(0x7f5401643ab0)]), range(MIN ; MAX)always true
9 - output([spi.order_id(0x7f5401644d10)]), filter(nil), rowset=256,
equal_conds([oti.order_id(0x7f5401642570) = spi.order_id(0x7f5401644d10)(0x7f540164ca90)]), other_conds(nil),
merge_directions([ASC])
10 - output([spi.order_id(0x7f5401644d10)]), filter(nil), rowset=256,
access([spi.order_id(0x7f5401644d10)]), partitions(p0),
is_index_back=false,
range_key([spi.order_id(0x7f5401644d10)]), range(MIN ; MAX)always true
11 - output([oti.order_id(0x7f5401642570)]), filter(nil), rowset=256, sort_keys([oti.order_id(0x7f5401642570), ASC])
12 - output([oti.order_id(0x7f5401642570)]), filter(nil), rowset=256,
equal_conds([oti.order_id(0x7f5401642570) = opi.order_id(0x7f54016471d0)(0x7f540164db50)]), other_conds(nil)
13 - output([oti.order_id(0x7f5401642570)]), filter([(T_OP_IS_NOT, oti.in_zsapp_time(0x7f5401649860), NULL, 0)(0x7f5401648ed0)]), rowset=256,
access([oti.order_id(0x7f5401642570)], [oti.in_zsapp_time(0x7f5401649860)]), partitions(p0),
is_index_back=false, filter_before_indexback[false],
range_key([oti.order_id(0x7f5401642570)]), range(MIN ; MAX)always true
14 - output([opi.order_id(0x7f54016471d0)]), filter([opi.is_speak(0x7f54016483c0) = ‘1’(0x7f5401647ce0)]), rowset=256,
access([opi.order_id(0x7f54016471d0)], [opi.is_speak(0x7f54016483c0)]), partitions(p0),
is_index_back=false, filter_before_indexback[false],
range_key([opi.order_id(0x7f54016471d0)]), range(MIN ; MAX)always true
15 - output([osi.order_id(0x7f5401645f70)]), filter(nil), rowset=256,
access([osi.order_id(0x7f5401645f70)]), partitions(p0),
is_index_back=false,
range_key([osi.order_id(0x7f5401645f70)]), range(MIN ; MAX)always true
Used Hint:
/*+
USE_HASH(“osi”)
USE_HASH(“opi”)
*/
Qb name trace:
stmt_id:0, stmt_type:T_EXPLAIN
stmt_id:1, SEL$1 > SEL$0208448F
Outline Data:
/*+
BEGIN_OUTLINE_DATA
LEADING(@“SEL$0208448F” (“order_center”.“sc”@“SEL$1” ((“order_center”.“cii”@“SEL$1” (“order_center”.“spi”@“SEL$1” (“order_center”.“oti”@“SEL$1” “order_center”.“opi”@“SEL$1”))) “order_center”.“osi”@“SEL$1”)))
USE_MERGE(@“SEL$0208448F” (“order_center”.“cii”@“SEL$1” “order_center”.“oti”@“SEL$1” “order_center”.“osi”@“SEL$1” “order_center”.“spi”@“SEL$1” “order_center”.“opi”@“SEL$1”))
FULL(@“SEL$0208448F” “order_center”.“sc”@“SEL$1”)
USE_HASH(@“SEL$0208448F” “order_center”.“osi”@“SEL$1”)
PQ_DISTRIBUTE(@“SEL$0208448F” “order_center”.“osi”@“SEL$1” LOCAL LOCAL)
USE_MERGE(@“SEL$0208448F” (“order_center”.“oti”@“SEL$1” “order_center”.“spi”@“SEL$1” “order_center”.“opi”@“SEL$1”))
FULL(@“SEL$0208448F” “order_center”.“cii”@“SEL$1”)
USE_MERGE(@“SEL$0208448F” (“order_center”.“oti”@“SEL$1” “order_center”.“opi”@“SEL$1”))
FULL(@“SEL$0208448F” “order_center”.“spi”@“SEL$1”)
USE_HASH(@“SEL$0208448F” “order_center”.“opi”@“SEL$1”)
FULL(@“SEL$0208448F” “order_center”.“oti”@“SEL$1”)
FULL(@“SEL$0208448F” “order_center”.“opi”@“SEL$1”)
FULL(@“SEL$0208448F” “order_center”.“osi”@“SEL$1”)
PRED_DEDUCE(@“SEL$1”)
OPTIMIZER_FEATURES_ENABLE(‘4.0.0.0’)
END_OUTLINE_DATA
*/
Plan Type:
DISTRIBUTED
Optimization Info:
sc:table_rows:11443340, physical_range_rows:11443340, logical_range_rows:11443340, index_back_rows:0, output_rows:11443340, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[search_collection], pruned_index_name[search_collection_appl_date_IDX,search_collection_appl_store_IDX,search_collection_app_entrance_flag_IDX,search_collection_belong_branch_IDX,search_collection_belong_city_branch_IDX,search_collection_belong_store_IDX,search_collection_bel_type_IDX,search_collection_customer_id_IDX,search_collection_director_id_IDX,search_collection_first_channel_IDX,search_collection_id_no_IDX,search_collection_maintainer_id_IDX,search_collection_manage_store_IDX,search_collection_mobile_IDX,search_collection_name_IDX,search_collection_policy_no_IDX,search_collection_property_number_IDX,search_collection_recorder_id_IDX,search_collection_sale_belong_team_id_IDX,search_collection_sale_id_IDX,search_collection_second_channel_IDX,search_collection_third_channel_IDX,search_collection_vin_IDX,search_time], estimation info[table_id:502749, (table_type:10, version:-1–1–1, logical_rc:11443340, physical_rc:11443340)]
cii:table_rows:1378636, physical_range_rows:1408869, logical_range_rows:1408869, index_back_rows:0, output_rows:1408869, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[customer_idcard_info], pruned_index_name[customer_idcard_info_customer_unique_id_IDX,customer_idcard_info_id_no_IDX], estimation info[table_id:500065, (table_type:10, version:-1–1–1, logical_rc:1408869, physical_rc:1408869)]
spi:table_rows:13854821, physical_range_rows:13897647, logical_range_rows:13897647, index_back_rows:0, output_rows:13897647, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[sale_personnel_info], pruned_index_name[sale_id_idx], estimation info[table_id:500107, (table_type:10, version:-1–1–1, logical_rc:13897647, physical_rc:13897647)]
oti:table_rows:1346149, physical_range_rows:1389190, logical_range_rows:1389190, index_back_rows:0, output_rows:356958, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[order_time_info], estimation info[table_id:500068, (table_type:10, version:-1–1–1, logical_rc:1389190, physical_rc:1389190)]
opi:table_rows:6307933, physical_range_rows:6338163, logical_range_rows:6338163, index_back_rows:0, output_rows:692550, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[order_property_info], estimation info[table_id:500093, (table_type:10, version:-1–1–1, logical_rc:6338163, physical_rc:6338163)]
osi:table_rows:1509058, physical_range_rows:1551894, logical_range_rows:1551894, index_back_rows:0, output_rows:1551894, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[order_status_info], estimation info[table_id:500052, (table_type:10, version:-1–1–1, logical_rc:1551894, physical_rc:1551894)]
【问题现象及影响】
【附件】