CREATE OUTLINE enforce_query_outline ON SELECT i.id, i.area_id, i.data_source_id, i.tag_id, i.refresh_answer_group, i.item_category, ( SELECT GROUP_CONCAT( CONCAT( g.answer_group_id, '-', g.bind_rule_flag, g.bind_rel_flag, g.bind_user_flag, g.black_flag ) ) FROM b_base_data_to_answer_group_bind g WHERE i.id = g.base_data_id AND g.type = ? ) AS answer_group_id_and_flag, ( SELECT GROUP_CONCAT(b_base_data_to_industry.industry_id) FROM b_base_data_to_industry WHERE b_base_data_to_industry.base_data_id = si.id AND b_base_data_to_industry.type = ? AND b_base_data_to_industry.industry_id IS NOT NULL ) AS industry_id FROM g_enforcement_item i INNER JOIN g_same_item_to_item ti ON ti.item_id = i.id LEFT JOIN g_same_item si ON ti.same_item_id = si.id WHERE i.use_flag = ? AND i.virtual_item_flag = ? AND i.id > ? AND i.id <= ? USING /*+ -- 并行执行相关，并行度为 1 PARALLEL(1), -- 表连接方式，使用嵌套循环连接 USE_NL(i ti si), USE_NL(i ti), USE_NL(ti si), -- 表 i 的扫描方式和索引使用 INDEX_RS(i, idx_use_Vitem_range), FILTER_PARTITION(i, 'p0sp[0-99],p1sp[0-99],p2sp[0-99],p3sp[0-99],p4sp[0-99],p5sp[0-99],p6sp[0-99],p7sp[0-99],p8sp[0-99],p9sp[0-99],p10sp[0-99],p11sp[0-99],p12sp[0-99],p13sp[0-99],p14sp[0-99],p15sp[0-99],p16sp[0-99],p17sp[0-99],p18sp[0-99],p19sp[0-99],p20sp[0-99],p21sp[0-99],p22sp[0-99],p23sp[0-99],p24sp[0-99],p25sp[0-99],p26sp[0-99],p27sp[0-99],p28sp[0-99],p29sp[0-99],p30sp[0-99],p31sp[0-99],p32sp[0-99],p33sp[0-99],p34sp[0-99],p35sp[0-99],p36sp[0-99],p37sp[0-99],p38sp[0-99],p39sp[0-99],p40sp[0-99],p41sp[0-99],p42sp[0-99],p43sp[0-99],p44sp[0-99],p45sp[0-99],p46sp[0-99],p47sp[0-99],p48sp[0-99],p49sp[0-99]'), RANGE_COND(i, 'i.use_flag = ? AND i.virtual_item_flag = ? AND i.id > ? AND i.id <= ?'), -- 表 ti 的扫描方式和索引使用 INDEX_RS(ti, item_id), FILTER_PARTITION(ti, 'p0'), RANGE_COND(ti, 'ti.item_id = ?'), -- 表 si 的扫描方式 TABLE_GET(si), FILTER_PARTITION(si, 'p0'), RANGE_COND(si, '? = si.id'), -- 表 g 的扫描方式和索引使用 INDEX_RS(g, index_bind_rule), FILTER_PARTITION(g, 'p[0-411]'), RANGE_COND(g, '? = g.base_data_id AND g.type = ? AND g.base_data_id > ? AND g.base_data_id <= ?'), -- 表 b_base_data_to_industry 的扫描方式和索引使用 INDEX_RS(b_base_data_to_industry, ALL), FILTER_PARTITION(b_base_data_to_industry, 'p0'), RANGE_COND(b_base_data_to_industry, 'b_base_data_to_industry.base_data_id = ? AND b_base_data_to_industry.type = ?') */; 根据参数生成的执行计划 =============================================================================================== |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| ----------------------------------------------------------------------------------------------- |0 |SUBPLAN FILTER | |2 |37235 | |1 |├─PX COORDINATOR | |2 |29431 | |2 |│ └─EXCHANGE OUT DISTR |:EX10002 |2 |29430 | |3 |│ └─NESTED-LOOP OUTER JOIN | |2 |29427 | |4 |│ ├─EXCHANGE IN DISTR | |2 |29411 | |5 |│ │ └─EXCHANGE OUT DISTR (BC2HOST) |:EX10001 |2 |29410 | |6 |│ │ └─NESTED-LOOP JOIN | |2 |29407 | |7 |│ │ ├─EXCHANGE IN DISTR | |1 |29380 | |8 |│ │ │ └─EXCHANGE OUT DISTR |:EX10000 |1 |29378 | |9 |│ │ │ └─PX PARTITION ITERATOR| |1 |29375 | |10|│ │ │ └─TABLE RANGE SCAN |i(idx_use_Vitem_range) |1 |29375 | |11|│ │ └─TABLE RANGE SCAN |ti(item_id) |1 |27 | |12|│ └─TABLE GET |si |1 |16 | |13|├─SCALAR GROUP BY | |1 |7388 | |14|│ └─PX COORDINATOR | |1 |7388 | |15|│ └─EXCHANGE OUT DISTR |:EX20000 |1 |7387 | |16|│ └─PX PARTITION ITERATOR | |1 |7384 | |17|│ └─TABLE RANGE SCAN |g(index_bind_rule) |1 |7384 | |18|└─SCALAR GROUP BY | |1 |18 | |19| └─TABLE RANGE SCAN |b_base_data_to_industry(ALL)|2 |18 | =============================================================================================== Outputs & filters: ------------------------------------- " 0 - output([i.id], [i.area_id], [i.data_source_id], [i.tag_id], [i.refresh_answer_group], [i.item_category], [subquery(1)], [subquery(2)]), filter(nil), rowset=16" " exec_params_([i.id(:0)], [si.id(:1)]), onetime_exprs_(nil), init_plan_idxs_(nil), use_batch=false" " 1 - output([i.id], [si.id], [i.area_id], [i.data_source_id], [i.tag_id], [i.refresh_answer_group], [i.item_category]), filter(nil), rowset=16" " 2 - output([i.id], [si.id], [i.area_id], [i.data_source_id], [i.tag_id], [i.refresh_answer_group], [i.item_category]), filter(nil), rowset=16" " is_single, dop=1" " 3 - output([i.id], [si.id], [i.area_id], [i.data_source_id], [i.tag_id], [i.refresh_answer_group], [i.item_category]), filter(nil), rowset=16" " conds(nil), nl_params_([ti.same_item_id(:4)]), use_batch=true" " 4 - output([i.id], [i.area_id], [i.data_source_id], [i.tag_id], [i.refresh_answer_group], [i.item_category], [ti.same_item_id]), filter(nil), rowset=16" " 5 - output([i.id], [i.area_id], [i.data_source_id], [i.tag_id], [i.refresh_answer_group], [i.item_category], [ti.same_item_id]), filter(nil), rowset=16" " is_single, dop=1" " 6 - output([i.id], [i.area_id], [i.data_source_id], [i.tag_id], [i.refresh_answer_group], [i.item_category], [ti.same_item_id]), filter(nil), rowset=16" " conds(nil), nl_params_([i.id(:2)]), use_batch=true" " 7 - output([i.id], [i.area_id], [i.data_source_id], [i.tag_id], [i.refresh_answer_group], [i.item_category]), filter(nil), rowset=16" " 8 - output([i.id], [i.area_id], [i.data_source_id], [i.tag_id], [i.refresh_answer_group], [i.item_category]), filter(nil), rowset=16" dop=1 " 9 - output([i.id], [i.data_source_id], [i.area_id], [i.tag_id], [i.refresh_answer_group], [i.item_category]), filter(nil), rowset=16" force partition granule " 10 - output([i.id], [i.data_source_id], [i.area_id], [i.tag_id], [i.refresh_answer_group], [i.item_category]), filter(nil), rowset=16" " access([i.id], [i.data_source_id], [i.area_id], [i.tag_id], [i.refresh_answer_group], [i.item_category]), partitions(p0sp[0-99], p1sp[0-99], p2sp[0-99]," " p3sp[0-99], p4sp[0-99], p5sp[0-99], p6sp[0-99], p7sp[0-99], p8sp[0-99], p9sp[0-99], p10sp[0-99], p11sp[0-99], p12sp[0-99], p13sp[0-99], p14sp[0-99], p15sp[0-99]," " p16sp[0-99], p17sp[0-99], p18sp[0-99], p19sp[0-99], p20sp[0-99], p21sp[0-99], p22sp[0-99], p23sp[0-99], p24sp[0-99], p25sp[0-99], p26sp[0-99], p27sp[0-99]," " p28sp[0-99], p29sp[0-99], p30sp[0-99], p31sp[0-99], p32sp[0-99], p33sp[0-99], p34sp[0-99], p35sp[0-99], p36sp[0-99], p37sp[0-99], p38sp[0-99], p39sp[0-99]," " p40sp[0-99], p41sp[0-99], p42sp[0-99], p43sp[0-99], p44sp[0-99], p45sp[0-99], p46sp[0-99], p47sp[0-99], p48sp[0-99], p49sp[0-99])" " is_index_back=true, is_global_index=false, " " range_key([i.use_flag], [i.virtual_item_flag], [i.id], [i.data_source_id], [i.area_id]), range(1,0,400000,MAX,MAX ; 1,0,500000,MAX,MAX), " " range_cond([i.use_flag = 1], [i.virtual_item_flag = 0], [i.id > 400000], [i.id <= 500000])" " 11 - output([ti.same_item_id]), filter(nil), rowset=16" " access([GROUP_ID], [ti.id], [ti.same_item_id]), partitions(p0)" " is_index_back=true, is_global_index=false, " " range_key([ti.item_id], [ti.id]), range(MIN ; MAX), " range_cond([ti.item_id = :2]) " 12 - output([si.id]), filter(nil), rowset=16" " access([GROUP_ID], [si.id]), partitions(p0)" " is_index_back=false, is_global_index=false, " " range_key([si.id]), range(MIN ; MAX), " range_cond([:4 = si.id]) " 13 - output([T_FUN_GROUP_CONCAT(CONCAT(cast(g.answer_group_id, VARCHAR(1048576)), '-', cast(g.bind_rule_flag, VARCHAR(1048576)), cast(g.bind_rel_flag, " " VARCHAR(1048576)), cast(g.bind_user_flag, VARCHAR(1048576)), cast(g.black_flag, VARCHAR(1048576))))]), filter(nil), rowset=16" " group(nil), agg_func([T_FUN_GROUP_CONCAT(CONCAT(cast(g.answer_group_id, VARCHAR(1048576)), '-', cast(g.bind_rule_flag, VARCHAR(1048576)), cast(g.bind_rel_flag," " VARCHAR(1048576)), cast(g.bind_user_flag, VARCHAR(1048576)), cast(g.black_flag, VARCHAR(1048576))))])" " 14 - output([g.answer_group_id], [g.bind_rule_flag], [g.bind_rel_flag], [g.bind_user_flag], [g.black_flag]), filter(nil), rowset=16" " 15 - output([g.answer_group_id], [g.bind_rule_flag], [g.bind_rel_flag], [g.bind_user_flag], [g.black_flag]), filter(nil), rowset=16" dop=1 " 16 - output([g.answer_group_id], [g.bind_rule_flag], [g.bind_rel_flag], [g.bind_user_flag], [g.black_flag]), filter(nil), rowset=16" force partition granule " 17 - output([g.answer_group_id], [g.bind_rule_flag], [g.bind_rel_flag], [g.bind_user_flag], [g.black_flag]), filter(nil), rowset=16" " access([g.answer_group_id], [g.bind_rule_flag], [g.bind_rel_flag], [g.bind_user_flag], [g.black_flag]), partitions(p[0-411])" " is_index_back=false, is_global_index=false, " " range_key([g.type], [g.base_data_id], [g.bind_rel_flag], [g.black_flag], [g.bind_rule_flag], [g.bind_user_flag], [g.id], [g.answer_group_id]), range(1," " 400000,MAX,MAX,MAX,MAX,MAX,MAX ; 1,500000,MAX,MAX,MAX,MAX,MAX,MAX), " " range_cond([:0 = g.base_data_id], [g.type = 1], [g.base_data_id > 400000], [g.base_data_id <= 500000])" " 18 - output([T_FUN_GROUP_CONCAT(cast(b_base_data_to_industry.industry_id, VARCHAR(65536)))]), filter(nil), rowset=16" " group(nil), agg_func([T_FUN_GROUP_CONCAT(cast(b_base_data_to_industry.industry_id, VARCHAR(65536)))])" " 19 - output([b_base_data_to_industry.industry_id]), filter([b_base_data_to_industry.industry_id IS NOT NULL]), rowset=16" " access([b_base_data_to_industry.industry_id]), partitions(p0)" " is_index_back=false, is_global_index=false, filter_before_indexback[false], " " range_key([b_base_data_to_industry.type], [b_base_data_to_industry.base_data_id], [b_base_data_to_industry.industry_id], [b_base_data_to_industry.id])," " range(1,MIN,MIN,MIN ; 1,MAX,MAX,MAX), " " range_cond([b_base_data_to_industry.base_data_id = :1], [b_base_data_to_industry.type = 1])"