【 使用环境 】生产环境
【 OB or 其他组件 】
【 使用版本 】4.2.1
【问题描述】请问，sql语句中加入limit 导致查询结果出不来，去掉就可以。limit这么大影响？
sql:
SELECT * from (
SELECT
dt0.diagnostician_type_id ,
dt0.diagnostician_type_name ,
dtg.tician_tag_name 标签,
o.diag_obj_id 对象ID,
o.diag_obj_name 名称,
o.show_seq 对象显示顺序,
r.resource_id 资源ID,
r.resource_name 资源名称,
r.resource_show_seq 资源显示顺序
FROM
dw_actual_answer_naire_question a
JOIN dw_diag_Obj o ON a.diag_obj_id = o.diag_obj_id
JOIN v_diag_obj_resource r ON o.diag_obj_id = r.diag_obj_id
JOIN dw_diagnostician d ON a.diagnostician_id = d.diagnostician_id
JOIN dw_tician_type dt ON d.diagnostician_id = dt.diagnostician_id
JOIN dw_diagnostician_type dt0 ON dt.diagnostician_type_id = dt0.diagnostician_type_id
JOIN dw_tician_tag dtg ON d.diagnostician_id = dtg.diagnostician_id
JOIN dw_diag_obj_rpt_tag rtag ON rtag.diag_obj_id = o.diag_obj_id
AND rtag.rpt_tag = dtg.tician_tag_name
JOIN dw_diag_obj_rpt_tag rtag1 ON rtag1.diag_obj_id = o.diag_obj_id
WHERE
a.school_code IN ( 'fsswgy' )
AND a.school_term_id IN ( '2411' )
AND o.diag_obj_type_id IN ( 'ZY', 'ZYFL', 'ZYX' )
AND rtag1.rpt_tag IN ( '全部' )
AND dt0.diagnostician_type_id IN ( 'XS' )
GROUP BY
dt0.diagnostician_type_id,
dtg.tician_tag_name,
o.diag_obj_id,
o.diag_obj_name,
o.show_seq
)
LIMIT 11
解释：
=====================================================================================================
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
-----------------------------------------------------------------------------------------------------
|0 |LIMIT | |11 |2023547 |
|1 |└─HASH GROUP BY | |11 |2023547 |
|2 | └─NESTED-LOOP JOIN CARTESIAN | |401 |2023505 |
|3 | ├─PX COORDINATOR | |1 |6 |
|4 | │ └─EXCHANGE OUT DISTR |:EX10000 |1 |5 |
|5 | │ └─TABLE GET |dt0 |1 |5 |
|6 | └─NESTED-LOOP JOIN | |401 |2023489 |
|7 | ├─NESTED-LOOP JOIN | |360 |2016934 |
|8 | │ ├─HASH JOIN | |343 |2010684 |
|9 | │ │ ├─TABLE RANGE SCAN |a(diag_obj_id_diagnostician_id)|890075 |129033 |
|10| │ │ └─NESTED-LOOP JOIN | |2020103 |368679 |
|11| │ │ ├─HASH JOIN | |8773 |93002 |
|12| │ │ │ ├─TABLE FULL SCAN |r |5751 |389 |
|13| │ │ │ └─MERGE JOIN | |8202 |87103 |
|14| │ │ │ ├─MERGE JOIN | |5998 |48050 |
|15| │ │ │ │ ├─SORT | |5049 |19946 |
|16| │ │ │ │ │ └─TABLE RANGE SCAN |o(t_d) |5049 |16461 |
|17| │ │ │ │ └─TABLE RANGE SCAN |rtag1(tag_obj_1) |753383 |24758 |
|18| │ │ │ └─TABLE FULL SCAN |rtag |1060072 |34779 |
|19| │ │ └─DISTRIBUTED TABLE RANGE SCAN|dtg(tician_tag_name) |231 |25 |
|20| │ └─DISTRIBUTED TABLE RANGE SCAN |d(diagnostician_id) |1 |18 |
|21| └─DISTRIBUTED TABLE RANGE SCAN |dt(d_t) |1 |18 |
=====================================================================================================
Outputs & filters:
-------------------------------------
0 - output([dt0.diagnostician_type_id], [dt0.diagnostician_type_name], [dtg.tician_tag_name], [o.diag_obj_id], [o.diag_obj_name], [o.show_seq], [r.resource_id],
[r.resource_name], [r.resource_show_seq]), filter(nil), rowset=256
limit(11), offset(nil)
1 - output([dt0.diagnostician_type_id], [dt0.diagnostician_type_name], [dtg.tician_tag_name], [o.diag_obj_id], [o.diag_obj_name], [o.show_seq], [r.resource_id],
[r.resource_name], [r.resource_show_seq]), filter(nil), rowset=256
group([o.diag_obj_id], [dtg.tician_tag_name]), agg_func(nil)
2 - output([dt0.diagnostician_type_id], [dt0.diagnostician_type_name], [dtg.tician_tag_name], [o.diag_obj_id], [o.diag_obj_name], [o.show_seq], [r.resource_id],
[r.resource_name], [r.resource_show_seq]), filter(nil), rowset=256
conds(nil), nl_params_(nil), use_batch=false
3 - output([dt0.diagnostician_type_id], [dt0.diagnostician_type_name]), filter(nil), rowset=256
4 - output([dt0.diagnostician_type_id], [dt0.diagnostician_type_name]), filter(nil), rowset=256
is_single, dop=1
5 - output([dt0.diagnostician_type_id], [dt0.diagnostician_type_name]), filter(nil), rowset=256
access([dt0.diagnostician_type_id], [dt0.diagnostician_type_name]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([dt0.diagnostician_type_id]), range[XS ; XS],
range_cond([dt0.diagnostician_type_id = 'XS'])
6 - output([dtg.tician_tag_name], [o.diag_obj_id], [o.diag_obj_name], [o.show_seq], [r.resource_id], [r.resource_name], [r.resource_show_seq]), filter(nil), rowset=256
conds(nil), nl_params_([a.diagnostician_id(:6)]), use_batch=true
7 - output([dtg.tician_tag_name], [o.diag_obj_id], [o.diag_obj_name], [o.show_seq], [r.resource_id], [r.resource_name], [r.resource_show_seq], [a.diagnostician_id]), filter(nil), rowset=256
conds(nil), nl_params_([a.diagnostician_id(:4)]), use_batch=true
8 - output([dtg.tician_tag_name], [o.diag_obj_id], [o.diag_obj_name], [o.show_seq], [r.resource_id], [r.resource_name], [r.resource_show_seq], [a.diagnostician_id]), filter(nil), rowset=256
equal_conds([a.diag_obj_id = o.diag_obj_id], [dtg.diagnostician_id = a.diagnostician_id]), other_conds(nil)
9 - output([a.diag_obj_id], [a.diagnostician_id]), filter(nil), rowset=256
access([a.diag_obj_id], [a.diagnostician_id]), partitions(p63)
is_index_back=false, is_global_index=false,
range_key([a.school_code], [a.school_term_id], [a.diag_obj_id], [a.diagnostician_id], [a.ID]), range(fsswgyxx,2411,MIN,MIN,MIN ; fsswgyxx,2411,MAX,
MAX,MAX),
range_cond([a.school_code = 'fsswgyxx'], [cast(a.school_term_id, DECIMAL(11, 0)) = cast('2411', DECIMAL(1, -1))])
10 - output([dtg.tician_tag_name], [o.diag_obj_id], [o.diag_obj_name], [o.show_seq], [r.resource_id], [r.resource_name], [r.resource_show_seq], [dtg.diagnostician_id]), filter(nil), rowset=256
conds(nil), nl_params_([rtag.rpt_tag(:31)]), use_batch=true
11 - output([o.diag_obj_id], [o.diag_obj_name], [o.show_seq], [r.resource_id], [r.resource_name], [r.resource_show_seq], [rtag.rpt_tag]), filter(nil), rowset=256
equal_conds([o.diag_obj_id = r.diag_obj_id]), other_conds(nil)
12 - output([r.diag_obj_id], [r.resource_id], [r.resource_name], [r.resource_show_seq]), filter(nil), rowset=256
access([r.diag_obj_id], [r.resource_id], [r.resource_name], [r.resource_show_seq]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([r.__pk_increment]), range(MIN ; MAX)always true
13 - output([o.diag_obj_id], [o.diag_obj_name], [o.show_seq], [rtag.rpt_tag]), filter(nil), rowset=256
equal_conds([rtag.diag_obj_id = o.diag_obj_id]), other_conds(nil)
merge_directions([ASC])
14 - output([o.diag_obj_id], [o.diag_obj_name], [o.show_seq]), filter(nil), rowset=256
equal_conds([rtag1.diag_obj_id = o.diag_obj_id]), other_conds(nil)
merge_directions([ASC])
15 - output([o.diag_obj_id], [o.diag_obj_name], [o.show_seq]), filter(nil), rowset=256
sort_keys([o.diag_obj_id, ASC])
16 - output([o.diag_obj_id], [o.diag_obj_name], [o.show_seq]), filter(nil), rowset=256
access([o.diag_obj_id], [o.diag_obj_name], [o.show_seq]), partitions(p0)
is_index_back=true, is_global_index=false,
range_key([o.diag_obj_type_id], [o.diag_obj_id]), range(ZY,MIN ; ZY,MAX), (ZYFL,MIN ; ZYFL,MAX), (ZYX,MIN ; ZYX,MAX),
range_cond([o.diag_obj_type_id IN ('ZY', 'ZYFL', 'ZYX')])
17 - output([rtag1.diag_obj_id]), filter(nil), rowset=256
access([rtag1.diag_obj_id]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([rtag1.rpt_tag], [rtag1.diag_obj_id]), range(全部,MIN ; 全部,MAX),
range_cond([rtag1.rpt_tag = '全部'])
18 - output([rtag.diag_obj_id], [rtag.rpt_tag]), filter(nil), rowset=256
access([rtag.diag_obj_id], [rtag.rpt_tag]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([rtag.diag_obj_id], [rtag.rpt_tag]), range(MIN,MIN ; MAX,MAX)always true
19 - output([dtg.diagnostician_id], [dtg.tician_tag_name]), filter(nil), rowset=256
access([GROUP_ID], [dtg.diagnostician_id], [dtg.tician_tag_name]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([dtg.tician_tag_name], [dtg.school_code], [dtg.school_term_id], [dtg.diagnostician_id], [dtg.school_seq], [dtg.tician_tag]), range(MIN ;
MAX),
range_cond([:31 = dtg.tician_tag_name])
20 - output(nil), filter(nil), rowset=256
access([GROUP_ID]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([d.diagnostician_id], [d.shadow_pk_0], [d.shadow_pk_1], [d.shadow_pk_2], [d.shadow_pk_3]), range(MIN ; MAX),
range_cond([:4 = d.diagnostician_id])
21 - output(nil), filter(nil), rowset=256
access([GROUP_ID]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([dt.diagnostician_type_id], [dt.diagnostician_id], [dt.school_code], [dt.school_term_id], [dt.school_seq]), range(MIN ; MAX),
range_cond([dt.diagnostician_type_id = 'XS'], [dt.diagnostician_id = :6])
