执行相同sql，有没有排序执行时间相差40倍

大白熊 · 2023 年11 月 24 日 16:01

【使用环境】生产环境
【 OB or 其他组件】OB
【使用版本】4.2.1
【问题描述】执行同一个sql，结果集也就11条，不加排序执行时间0.5s，加上排序执行时间20s

SELECT DISTINCT
	bb.diag_obj_id AS param_value,
	bb.diag_obj_name AS param_value_dispaly
FROM
	dw_actual_obj_q_stat aa
join dw_question cc ON aa.q_id = cc.q_id AND cc.diag_module IN ('教学-专项诊断')
join v_diag_ojb_diagnostician_stat bb on aa.diag_obj_id=bb.diag_obj_id_child AND bb.diag_obj_type_id_child IN ('JXBYS','JXBYB','JXBGDSY','JXBTY','JXBJS','JXBJINGSAI','JXBDXXX','JXBXYZ','JXBZZ','JXBGJTY','JXBRWYSH','JXBJJ') and bb.diag_obj_type_id in ('XK')
join v_rpt_diag_obj dd on bb.diag_obj_id_child=dd.diag_obj_id and dd.rpt_diag_obj_tag in ('本部')
join v_diag_obj_grade gg on aa.diag_obj_id=gg.diag_obj_id and gg.grade_id='232100001686'

where aa.school_code = 'bnds' AND aa.school_term_id = '2321'AND aa.school_term_id = '2321'

加上排序

SELECT DISTINCT
	bb.diag_obj_id AS param_value,
	bb.diag_obj_name AS param_value_dispaly
FROM
	dw_actual_obj_q_stat aa
join dw_question cc ON aa.q_id = cc.q_id AND cc.diag_module IN ('教学-专项诊断')
join v_diag_ojb_diagnostician_stat bb on aa.diag_obj_id=bb.diag_obj_id_child AND bb.diag_obj_type_id_child IN ('JXBYS','JXBYB','JXBGDSY','JXBTY','JXBJS','JXBJINGSAI','JXBDXXX','JXBXYZ','JXBZZ','JXBGJTY','JXBRWYSH','JXBJJ') and bb.diag_obj_type_id in ('XK')
join v_rpt_diag_obj dd on bb.diag_obj_id_child=dd.diag_obj_id and dd.rpt_diag_obj_tag in ('本部')
join v_diag_obj_grade gg on aa.diag_obj_id=gg.diag_obj_id and gg.grade_id='232100001686'

where aa.school_code = 'bnds' AND aa.school_term_id = '2321'AND aa.school_term_id = '2321'
ORDER BY bb.show_seq desc

v_diag_ojb_diagnostician_stat 是一个视图

不加排序的explain：

加排序的explain：

大白熊 · 2023 年11 月 24 日 16:07

完整的explain（没有排序）：

====================================================================================================
|ID|OPERATOR                                      |NAME                      |EST.ROWS|EST.TIME(us)|
----------------------------------------------------------------------------------------------------
|0 |EXCHANGE IN REMOTE                            |                          |1       |3694        |
|1 |└─EXCHANGE OUT REMOTE                         |                          |1       |3693        |
|2 |  └─HASH DISTINCT                             |                          |1       |3693        |
|3 |    └─NESTED-LOOP JOIN                        |                          |1       |3693        |
|4 |      ├─NESTED-LOOP JOIN                      |                          |1       |3692        |
|5 |      │ ├─NESTED-LOOP JOIN                    |                          |1       |3690        |
|6 |      │ │ ├─NESTED-LOOP JOIN                  |                          |1       |3689        |
|7 |      │ │ │ ├─NESTED-LOOP JOIN                |                          |5       |3606        |
|8 |      │ │ │ │ ├─NESTED-LOOP JOIN              |                          |2       |3588        |
|9 |      │ │ │ │ │ ├─TABLE RANGE SCAN            |gg(grade_id)              |161     |649         |
|10|      │ │ │ │ │ └─DISTRIBUTED TABLE RANGE SCAN|aa(diag_obj_id_q_id)      |1       |18          |
|11|      │ │ │ │ └─DISTRIBUTED TABLE RANGE SCAN  |b(diag_obj_id_descendants)|5       |18          |
|12|      │ │ │ └─DISTRIBUTED TABLE GET           |a                         |1       |18          |
|13|      │ │ └─DISTRIBUTED TABLE GET             |cc                        |1       |18          |
|14|      │ └─DISTRIBUTED TABLE GET               |c                         |1       |18          |
|15|      └─DISTRIBUTED TABLE GET                 |b                         |1       |18          |
====================================================================================================
Outputs & filters:
-------------------------------------
  0 - output([a.diag_obj_id], [a.diag_obj_name]), filter(nil)
  1 - output([a.diag_obj_id], [a.diag_obj_name]), filter(nil)
  2 - output([a.diag_obj_id], [a.diag_obj_name]), filter(nil), rowset=256
      distinct([a.diag_obj_id])
  3 - output([a.diag_obj_id], [a.diag_obj_name]), filter(nil), rowset=256
      conds(nil), nl_params_([aa.diag_obj_id(:6)]), use_batch=true
  4 - output([a.diag_obj_id], [a.diag_obj_name], [aa.diag_obj_id]), filter(nil), rowset=256
      conds(nil), nl_params_([aa.diag_obj_id(:4)]), use_batch=true
  5 - output([a.diag_obj_id], [a.diag_obj_name], [aa.diag_obj_id]), filter(nil), rowset=256
      conds(nil), nl_params_([aa.q_id(:0)]), use_batch=true
  6 - output([a.diag_obj_id], [a.diag_obj_name], [aa.diag_obj_id], [aa.q_id]), filter(nil), rowset=256
      conds(nil), nl_params_([b.diag_obj_id(:11)]), use_batch=true
  7 - output([aa.diag_obj_id], [aa.q_id], [b.diag_obj_id]), filter(nil), rowset=256
      conds(nil), nl_params_([aa.diag_obj_id(:2)]), use_batch=true
  8 - output([aa.diag_obj_id], [aa.q_id]), filter(nil), rowset=256
      conds(nil), nl_params_([gg.diag_obj_id(:9)]), use_batch=true
  9 - output([gg.diag_obj_id]), filter(nil), rowset=256
      access([gg.__pk_increment], [gg.diag_obj_id]), partitions(p0)
      is_index_back=true, is_global_index=false, 
      range_key([gg.grade_id], [gg.__pk_increment]), range(232100001686,MIN ; 232100001686,MAX), 
      range_cond([gg.grade_id = '232100001686'])
 10 - output([aa.q_id], [aa.diag_obj_id]), filter(nil), rowset=256
      access([GROUP_ID], [aa.q_id], [aa.diag_obj_id]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([aa.school_code], [aa.school_term_id], [aa.diag_obj_id], [aa.q_id], [aa.__pk_increment]), range(MIN ; MAX), 
      range_cond([aa.school_code = 'bnds'], [cast(aa.school_term_id, DECIMAL(11, 0)) = cast('2321', DECIMAL(1, -1))], [aa.diag_obj_id = :9])
 11 - output([b.diag_obj_id]), filter(nil), rowset=256
      access([GROUP_ID], [b.diag_obj_id]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([b.diag_obj_id_descendants], [b.diag_obj_id]), range(MIN ; MAX), 
      range_cond([:2 = b.diag_obj_id_descendants])
 12 - output([a.diag_obj_id], [a.diag_obj_name]), filter([a.diag_obj_type_id = 'XK']), rowset=256
      access([GROUP_ID], [a.diag_obj_id], [a.diag_obj_name], [a.diag_obj_type_id]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([a.diag_obj_id]), range(MIN ; MAX), 
      range_cond([a.diag_obj_id = :11])
 13 - output(nil), filter([cc.diag_module = '教学-专项诊断']), rowset=256
      access([GROUP_ID], [cc.diag_module]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([cc.q_id]), range(MIN ; MAX), 
      range_cond([:0 = cc.q_id])
 14 - output(nil), filter([c.diag_obj_type_id IN ('JXBYS', 'JXBYB', 'JXBGDSY', 'JXBTY', 'JXBJS', 'JXBJINGSAI', 'JXBDXXX', 'JXBXYZ', 'JXBZZ', 'JXBGJTY', 
      'JXBRWYSH', 'JXBJJ')]), rowset=256
      access([GROUP_ID], [c.diag_obj_type_id]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([c.diag_obj_id]), range(MIN ; MAX), 
      range_cond([:4 = c.diag_obj_id])
 15 - output(nil), filter(nil), rowset=256
      access([GROUP_ID]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([b.diag_obj_id], [b.rpt_tag]), range(MIN ; MAX), 
      range_cond([b.rpt_tag = '本部'], [:6 = b.diag_obj_id])

完整的explain（有排序）：

========================================================================================================
|ID|OPERATOR                                          |NAME                      |EST.ROWS|EST.TIME(us)|
--------------------------------------------------------------------------------------------------------
|0 |SORT                                              |                          |5       |3852        |
|1 |└─HASH DISTINCT                                   |                          |5       |3851        |
|2 |  └─NESTED-LOOP OUTER JOIN                        |                          |5       |3850        |
|3 |    ├─NESTED-LOOP JOIN                            |                          |1       |3694        |
|4 |    │ ├─PX COORDINATOR                            |                          |1       |3692        |
|5 |    │ │ └─EXCHANGE OUT DISTR                      |:EX10000                  |1       |3692        |
|6 |    │ │   └─NESTED-LOOP JOIN                      |                          |1       |3692        |
|7 |    │ │     ├─NESTED-LOOP JOIN                    |                          |1       |3690        |
|8 |    │ │     │ ├─NESTED-LOOP JOIN                  |                          |1       |3689        |
|9 |    │ │     │ │ ├─NESTED-LOOP JOIN                |                          |5       |3606        |
|10|    │ │     │ │ │ ├─NESTED-LOOP JOIN              |                          |2       |3588        |
|11|    │ │     │ │ │ │ ├─TABLE RANGE SCAN            |gg(grade_id)              |161     |649         |
|12|    │ │     │ │ │ │ └─DISTRIBUTED TABLE RANGE SCAN|aa(diag_obj_id_q_id)      |1       |18          |
|13|    │ │     │ │ │ └─DISTRIBUTED TABLE RANGE SCAN  |b(diag_obj_id_descendants)|5       |18          |
|14|    │ │     │ │ └─DISTRIBUTED TABLE GET           |a                         |1       |18          |
|15|    │ │     │ └─DISTRIBUTED TABLE GET             |cc                        |1       |18          |
|16|    │ │     └─DISTRIBUTED TABLE GET               |c                         |1       |18          |
|17|    │ └─TABLE GET                                 |b                         |1       |18          |
|18|    └─DISTRIBUTED TABLE RANGE SCAN                |d(diag_obj_id)            |56      |1743        |
========================================================================================================
Outputs & filters:
-------------------------------------
  0 - output([a.diag_obj_id], [a.diag_obj_name]), filter(nil), rowset=256
      sort_keys([a.show_seq, DESC])
  1 - output([a.show_seq], [a.diag_obj_id], [a.diag_obj_name]), filter(nil), rowset=256
      distinct([a.diag_obj_id])
  2 - output([a.show_seq], [a.diag_obj_id], [a.diag_obj_name]), filter(nil), rowset=256
      conds(nil), nl_params_([c.diag_obj_id(:18)]), use_batch=false
  3 - output([a.show_seq], [a.diag_obj_id], [a.diag_obj_name], [c.diag_obj_id]), filter(nil), rowset=256
      conds(nil), nl_params_([aa.diag_obj_id(:6)]), use_batch=true
  4 - output([a.show_seq], [a.diag_obj_id], [a.diag_obj_name], [c.diag_obj_id], [aa.diag_obj_id]), filter(nil), rowset=256
  5 - output([a.show_seq], [a.diag_obj_id], [a.diag_obj_name], [c.diag_obj_id], [aa.diag_obj_id]), filter(nil), rowset=256
      is_single, dop=1
  6 - output([a.show_seq], [a.diag_obj_id], [a.diag_obj_name], [c.diag_obj_id], [aa.diag_obj_id]), filter(nil), rowset=256
      conds(nil), nl_params_([aa.diag_obj_id(:4)]), use_batch=true
  7 - output([a.show_seq], [a.diag_obj_id], [a.diag_obj_name], [aa.diag_obj_id]), filter(nil), rowset=256
      conds(nil), nl_params_([aa.q_id(:0)]), use_batch=true
  8 - output([a.show_seq], [a.diag_obj_id], [a.diag_obj_name], [aa.diag_obj_id], [aa.q_id]), filter(nil), rowset=256
      conds(nil), nl_params_([b.diag_obj_id(:11)]), use_batch=true
  9 - output([aa.diag_obj_id], [aa.q_id], [b.diag_obj_id]), filter(nil), rowset=256
      conds(nil), nl_params_([aa.diag_obj_id(:2)]), use_batch=true
 10 - output([aa.diag_obj_id], [aa.q_id]), filter(nil), rowset=256
      conds(nil), nl_params_([gg.diag_obj_id(:9)]), use_batch=true
 11 - output([gg.diag_obj_id]), filter(nil), rowset=256
      access([gg.__pk_increment], [gg.diag_obj_id]), partitions(p0)
      is_index_back=true, is_global_index=false, 
      range_key([gg.grade_id], [gg.__pk_increment]), range(232100001686,MIN ; 232100001686,MAX), 
      range_cond([gg.grade_id = '232100001686'])
 12 - output([aa.q_id], [aa.diag_obj_id]), filter(nil), rowset=256
      access([GROUP_ID], [aa.q_id], [aa.diag_obj_id]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([aa.school_code], [aa.school_term_id], [aa.diag_obj_id], [aa.q_id], [aa.__pk_increment]), range(MIN ; MAX), 
      range_cond([aa.school_code = 'bnds'], [cast(aa.school_term_id, DECIMAL(11, 0)) = cast('2321', DECIMAL(1, -1))], [aa.diag_obj_id = :9])
 13 - output([b.diag_obj_id]), filter(nil), rowset=256
      access([GROUP_ID], [b.diag_obj_id]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([b.diag_obj_id_descendants], [b.diag_obj_id]), range(MIN ; MAX), 
      range_cond([:2 = b.diag_obj_id_descendants])
 14 - output([a.diag_obj_id], [a.diag_obj_name], [a.show_seq]), filter([a.diag_obj_type_id = 'XK']), rowset=256
      access([GROUP_ID], [a.diag_obj_id], [a.diag_obj_name], [a.diag_obj_type_id], [a.show_seq]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([a.diag_obj_id]), range(MIN ; MAX), 
      range_cond([a.diag_obj_id = :11])
 15 - output(nil), filter([cc.diag_module = '教学-专项诊断']), rowset=256
      access([GROUP_ID], [cc.diag_module]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([cc.q_id]), range(MIN ; MAX), 
      range_cond([:0 = cc.q_id])
 16 - output([c.diag_obj_id]), filter([c.diag_obj_type_id IN ('JXBYS', 'JXBYB', 'JXBGDSY', 'JXBTY', 'JXBJS', 'JXBJINGSAI', 'JXBDXXX', 'JXBXYZ', 'JXBZZ',
       'JXBGJTY', 'JXBRWYSH', 'JXBJJ')]), rowset=256
      access([GROUP_ID], [c.diag_obj_id], [c.diag_obj_type_id]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([c.diag_obj_id]), range(MIN ; MAX), 
      range_cond([:4 = c.diag_obj_id])
 17 - output(nil), filter(nil), rowset=256
      access([GROUP_ID]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([b.diag_obj_id], [b.rpt_tag]), range(MIN ; MAX), 
      range_cond([b.rpt_tag = '本部'], [:6 = b.diag_obj_id])
 18 - output(nil), filter(nil), rowset=256
      access(nil), partitions(p[0-99])
      is_index_back=false, is_global_index=false, 
      range_key([d.diag_obj_id], [d.school_code], [d.school_term_id], [d.diagnostician_id], [d.school_seq]), range(MIN ; MAX), 
      range_cond([:18 = d.diag_obj_id])

靖顺 · 2023 年11 月 24 日 16:49

现在看起来sort算子耗时比较多一些，方便发一下涉及到的表的schema吗？敏感信息可以隐藏一下。

大白熊 · 2023 年11 月 24 日 17:10

不能私信吗？

靖顺 · 2023 年11 月 27 日 16:09

从执行计划和表的结构来看，没看出来具体问题在哪，在提供一下expain extended的执行计划。可能统计信息不准导致的

大白熊 · 2023 年11 月 28 日 10:52

无排序的expain extended：

======================================================================================================
|ID|OPERATOR                                        |NAME                      |EST.ROWS|EST.TIME(us)|
------------------------------------------------------------------------------------------------------
|0 |EXCHANGE IN REMOTE                              |                          |2       |5685        |
|1 |└─EXCHANGE OUT REMOTE                           |                          |2       |5684        |
|2 |  └─HASH DISTINCT                               |                          |2       |5682        |
|3 |    └─NESTED-LOOP JOIN                          |                          |2       |5682        |
|4 |      ├─NESTED-LOOP JOIN                        |                          |2       |5655        |
|5 |      │ ├─NESTED-LOOP JOIN                      |                          |2       |5628        |
|6 |      │ │ ├─NESTED-LOOP JOIN                    |                          |78      |4207        |
|7 |      │ │ │ ├─MERGE JOIN                        |                          |18      |3887        |
|8 |      │ │ │ │ ├─TABLE RANGE SCAN                |cc(idx_m_q)               |1529    |45          |
|9 |      │ │ │ │ └─SORT                            |                          |171     |3644        |
|10|      │ │ │ │   └─NESTED-LOOP JOIN              |                          |171     |3579        |
|11|      │ │ │ │     ├─TABLE RANGE SCAN            |gg(grade_id)              |161     |640         |
|12|      │ │ │ │     └─DISTRIBUTED TABLE RANGE SCAN|aa(diag_obj_id_q_id)      |1       |18          |
|13|      │ │ │ └─DISTRIBUTED TABLE RANGE SCAN      |b(diag_obj_id_descendants)|5       |18          |
|14|      │ │ └─DISTRIBUTED TABLE GET               |a                         |1       |18          |
|15|      │ └─DISTRIBUTED TABLE GET                 |c                         |1       |18          |
|16|      └─DISTRIBUTED TABLE GET                   |b                         |1       |18          |
======================================================================================================
Outputs & filters:
-------------------------------------
  0 - output([a.diag_obj_id(0x7f58d2e4c0f0)], [a.diag_obj_name(0x7f58d2e56ce0)]), filter(nil)
  1 - output([a.diag_obj_id(0x7f58d2e4c0f0)], [a.diag_obj_name(0x7f58d2e56ce0)]), filter(nil)
  2 - output([a.diag_obj_id(0x7f58d2e4c0f0)], [a.diag_obj_name(0x7f58d2e56ce0)]), filter(nil), rowset=256
      distinct([a.diag_obj_id(0x7f58d2e4c0f0)])
  3 - output([a.diag_obj_id(0x7f58d2e4c0f0)], [a.diag_obj_name(0x7f58d2e56ce0)]), filter(nil), rowset=256
      conds(nil), nl_params_([aa.diag_obj_id(0x7f58d2e72ad0)(:6)]), use_batch=true
  4 - output([a.diag_obj_id(0x7f58d2e4c0f0)], [a.diag_obj_name(0x7f58d2e56ce0)], [aa.diag_obj_id(0x7f58d2e72ad0)]), filter(nil), rowset=256
      conds(nil), nl_params_([aa.diag_obj_id(0x7f58d2e72ad0)(:4)]), use_batch=true
  5 - output([a.diag_obj_id(0x7f58d2e4c0f0)], [a.diag_obj_name(0x7f58d2e56ce0)], [aa.diag_obj_id(0x7f58d2e72ad0)]), filter(nil), rowset=256
      conds(nil), nl_params_([b.diag_obj_id(0x7f58d2e4c3e0)(:11)]), use_batch=true
  6 - output([aa.diag_obj_id(0x7f58d2e72ad0)], [b.diag_obj_id(0x7f58d2e4c3e0)]), filter(nil), rowset=256
      conds(nil), nl_params_([aa.diag_obj_id(0x7f58d2e72ad0)(:2)]), use_batch=true
  7 - output([aa.diag_obj_id(0x7f58d2e72ad0)]), filter(nil), rowset=256
      equal_conds([aa.q_id(0x7f58d2e29e20) = cc.q_id(0x7f58d2e2a110)(0x7f58d2e296d0)]), other_conds(nil)
      merge_directions([ASC])
  8 - output([cc.q_id(0x7f58d2e2a110)]), filter(nil), rowset=256
      access([cc.q_id(0x7f58d2e2a110)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([cc.diag_module(0x7f58d2e2ba70)], [cc.q_id(0x7f58d2e2a110)]), range(教学-专项诊断,MIN ; 教学-专项诊断,MAX), 
      range_cond([cc.diag_module(0x7f58d2e2ba70) = '教学-专项诊断'(0x7f58d2e2a6a0)])
  9 - output([aa.diag_obj_id(0x7f58d2e72ad0)], [aa.q_id(0x7f58d2e29e20)]), filter(nil), rowset=256
      sort_keys([aa.q_id(0x7f58d2e29e20), ASC])
 10 - output([aa.diag_obj_id(0x7f58d2e72ad0)], [aa.q_id(0x7f58d2e29e20)]), filter(nil), rowset=256
      conds(nil), nl_params_([gg.diag_obj_id(0x7f58d2eb83c0)(:9)]), use_batch=true
 11 - output([gg.diag_obj_id(0x7f58d2eb83c0)]), filter(nil), rowset=256
      access([gg.__pk_increment(0x7f58d2ec9cb0)], [gg.diag_obj_id(0x7f58d2eb83c0)]), partitions(p0)
      is_index_back=true, is_global_index=false, 
      range_key([gg.grade_id(0x7f58d2eb9620)], [gg.__pk_increment(0x7f58d2ec9cb0)]), range(232100001686,MIN ; 232100001686,MAX), 
      range_cond([gg.grade_id(0x7f58d2eb9620) = '232100001686'(0x7f58d2eb8ed0)])
 12 - output([aa.q_id(0x7f58d2e29e20)], [aa.diag_obj_id(0x7f58d2e72ad0)]), filter(nil), rowset=256
      access([GROUP_ID(0x7f567bebdbd0)], [aa.q_id(0x7f58d2e29e20)], [aa.diag_obj_id(0x7f58d2e72ad0)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([aa.school_code(0x7f58d2eba880)], [aa.school_term_id(0x7f58d2ebbae0)], [aa.diag_obj_id(0x7f58d2e72ad0)], [aa.q_id(0x7f58d2e29e20)], [aa.__pk_increment(0x7f58d2ec99d0)]),
       range(MIN ; MAX), 
      range_cond([aa.school_code(0x7f58d2eba880) = 'bnds'(0x7f58d2eba130)], [cast(aa.school_term_id(0x7f58d2ebbae0), DECIMAL(11, 0))(0x7f58d2ebc050) = cast('2321',
       DECIMAL(1, -1))(0x7f58d2ebcba0)(0x7f58d2ebb390)], [aa.diag_obj_id(0x7f58d2e72ad0) = :9(0x7f5b019ff400)])
 13 - output([b.diag_obj_id(0x7f58d2e4c3e0)]), filter(nil), rowset=256
      access([GROUP_ID(0x7f567bebfd30)], [b.diag_obj_id(0x7f58d2e4c3e0)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([b.diag_obj_id_descendants(0x7f58d2e4f890)], [b.diag_obj_id(0x7f58d2e4c3e0)]), range(MIN ; MAX), 
      range_cond([:2 = b.diag_obj_id_descendants(0x7f58d2e4f890)(0x7f5b018df880)])
 14 - output([a.diag_obj_id(0x7f58d2e4c0f0)], [a.diag_obj_name(0x7f58d2e56ce0)]), filter([a.diag_obj_type_id(0x7f58d2e57ee0) = 'XK'(0x7f58d2e7ce50)]), rowset=256
      access([GROUP_ID(0x7f567bebff80)], [a.diag_obj_id(0x7f58d2e4c0f0)], [a.diag_obj_name(0x7f58d2e56ce0)], [a.diag_obj_type_id(0x7f58d2e57ee0)]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([a.diag_obj_id(0x7f58d2e4c0f0)]), range(MIN ; MAX), 
      range_cond([a.diag_obj_id(0x7f58d2e4c0f0) = :11(0x7f5baa25bee0)])
 15 - output(nil), filter([c.diag_obj_type_id(0x7f58d2e6cb40) IN ('JXBYS', 'JXBYB', 'JXBGDSY', 'JXBTY', 'JXBJS', 'JXBJINGSAI', 'JXBDXXX', 'JXBXYZ', 'JXBZZ',
       'JXBGJTY', 'JXBRWYSH', 'JXBJJ')(0x7f58d2f5b460)(0x7f58d2e73350)]), rowset=256
      access([GROUP_ID(0x7f567bec20e0)], [c.diag_obj_type_id(0x7f58d2e6cb40)]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([c.diag_obj_id(0x7f58d2e4fb80)]), range(MIN ; MAX), 
      range_cond([:4 = c.diag_obj_id(0x7f58d2e4fb80)(0x7f5b01937690)])
 16 - output(nil), filter(nil), rowset=256
      access([GROUP_ID(0x7f567bec2330)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([b.diag_obj_id(0x7f58d2e9a630)], [b.rpt_tag(0x7f58d2ea2ab0)]), range(MIN ; MAX), 
      range_cond([b.rpt_tag(0x7f58d2ea2ab0) = '本部'(0x7f58d2eb3ae0)], [:6 = b.diag_obj_id(0x7f58d2e9a630)(0x7f5b01998b60)])
Used Hint:
-------------------------------------
  /*+
      
      INLINE
  */
Qb name trace:
-------------------------------------
  stmt_id:0, stmt_type:T_EXPLAIN 
  stmt_id:1, SEL$1 > SEL$BFD73BB5 > SEL$CF3BE71E > SEL$A4CF0106 > SEL$70ACD2D2 > SEL$3001438E > SEL$61ED67AD
  stmt_id:2, SEL$2 > SEL$2F8A4177
  stmt_id:3, SEL$3 > SEL$02A956E8
Outline Data: 
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_DISTINCT(@"SEL$61ED67AD")
      LEADING(@"SEL$61ED67AD" ((((("edu_dw"."cc"@"SEL$1" ("edu_dw"."gg"@"SEL$1" "edu_dw"."aa"@"SEL$1")) "edu_dw"."b"@"SEL$2") "edu_dw"."a"@"SEL$2") "edu_dw"."c"@"SEL$2") "edu_dw"."b"@"SEL$3"))
      USE_NL(@"SEL$61ED67AD" "edu_dw"."b"@"SEL$3")
      USE_NL(@"SEL$61ED67AD" "edu_dw"."c"@"SEL$2")
      USE_NL(@"SEL$61ED67AD" "edu_dw"."a"@"SEL$2")
      USE_NL(@"SEL$61ED67AD" "edu_dw"."b"@"SEL$2")
      USE_MERGE(@"SEL$61ED67AD" ("edu_dw"."gg"@"SEL$1" "edu_dw"."aa"@"SEL$1"))
      INDEX(@"SEL$61ED67AD" "cc"@"SEL$1" "idx_m_q")
      USE_NL(@"SEL$61ED67AD" "edu_dw"."aa"@"SEL$1")
      INDEX(@"SEL$61ED67AD" "gg"@"SEL$1" "grade_id")
      INDEX(@"SEL$61ED67AD" "aa"@"SEL$1" "diag_obj_id_q_id")
      USE_DAS(@"SEL$61ED67AD" "aa"@"SEL$1")
      INDEX(@"SEL$61ED67AD" "b"@"SEL$2" "diag_obj_id_descendants")
      USE_DAS(@"SEL$61ED67AD" "b"@"SEL$2")
      FULL(@"SEL$61ED67AD" "a"@"SEL$2")
      USE_DAS(@"SEL$61ED67AD" "a"@"SEL$2")
      FULL(@"SEL$61ED67AD" "c"@"SEL$2")
      USE_DAS(@"SEL$61ED67AD" "c"@"SEL$2")
      FULL(@"SEL$61ED67AD" "b"@"SEL$3")
      USE_DAS(@"SEL$61ED67AD" "b"@"SEL$3")
      PROJECT_PRUNE(@"SEL$2")
      PROJECT_PRUNE(@"SEL$3")
      MERGE(@"SEL$2F8A4177" > "SEL$1")
      MERGE(@"SEL$02A956E8" > "SEL$1")
      OUTER_TO_INNER(@"SEL$BFD73BB5")
      ELIMINATE_JOIN(@"SEL$CF3BE71E" "edu_dw"."a"@"SEL$3" "edu_dw"."e"@"SEL$2")
      PRED_DEDUCE(@"SEL$A4CF0106")
      ELIMINATE_JOIN(@"SEL$70ACD2D2" "edu_dw"."d"@"SEL$2")
      OUTER_TO_INNER(@"SEL$3001438E")
      OPTIMIZER_FEATURES_ENABLE('4.0.0.0')
      END_OUTLINE_DATA
  */
Optimization Info:
-------------------------------------
  cc:
      table_rows:18224
      physical_range_rows:1529
      logical_range_rows:1529
      index_back_rows:0
      output_rows:1529
      table_dop:1
      dop_method:Table DOP
      avaiable_index_name:[q_id, idx_m_q, idx_m_g_q, idx_y_q, dw_question]
      pruned_index_name:[q_id, idx_m_g_q, idx_y_q, dw_question]
      stats version:1700258621878630
      dynamic sampling level:0
  gg:
      table_rows:597629
      physical_range_rows:161
      logical_range_rows:161
      index_back_rows:161
      output_rows:161
      table_dop:1
      dop_method:Table DOP
      avaiable_index_name:[grade_id, diag_obj_id, v_diag_obj_grade]
      unstable_index_name:[v_diag_obj_grade]
      stats version:1700661644187339
      dynamic sampling level:0
  aa:
      table_rows:3753885
      physical_range_rows:1
      logical_range_rows:1
      index_back_rows:0
      output_rows:1
      table_dop:1
      dop_method:DAS DOP
      avaiable_index_name:[diag_obj_id, diag_obj_id_q_id, q_id, school_term_id, school_code, dw_actual_obj_q_stat]
      pruned_index_name:[diag_obj_id, school_term_id, school_code, dw_actual_obj_q_stat]
      stats version:1700834868529075
      dynamic sampling level:0
  b:
      table_rows:3431977
      physical_range_rows:4
      logical_range_rows:4
      index_back_rows:0
      output_rows:4
      table_dop:1
      dop_method:DAS DOP
      avaiable_index_name:[diag_obj_id_descendants, diag_obj_id, dw_diag_obj_closure]
      unstable_index_name:[dw_diag_obj_closure]
      stats version:1700489702736231
      dynamic sampling level:0
  a:
      table_rows:714072
      physical_range_rows:1
      logical_range_rows:1
      index_back_rows:0
      output_rows:0
      table_dop:1
      dop_method:DAS DOP
      avaiable_index_name:[diag_obj_id, school_code, s_s_t_d, d_t, t_d, dw_diag_obj]
      pruned_index_name:[diag_obj_id, school_code, s_s_t_d, d_t, t_d]
      stats version:1700489708744977
      dynamic sampling level:0
  c:
      table_rows:714072
      physical_range_rows:1
      logical_range_rows:1
      index_back_rows:0
      output_rows:0
      table_dop:1
      dop_method:DAS DOP
      avaiable_index_name:[diag_obj_id, school_code, s_s_t_d, d_t, t_d, dw_diag_obj]
      pruned_index_name:[diag_obj_id, school_code, s_s_t_d, d_t, t_d]
      stats version:1700489708744977
      dynamic sampling level:0
  b:
      table_rows:983353
      physical_range_rows:1
      logical_range_rows:1
      index_back_rows:0
      output_rows:1
      table_dop:1
      dop_method:DAS DOP
      avaiable_index_name:[rpt_tag, diag_obj_id, tag_obj, tag_obj_1, dw_diag_obj_rpt_tag]
      pruned_index_name:[rpt_tag, diag_obj_id, tag_obj, tag_obj_1]
      stats version:1700662329956275
      dynamic sampling level:0
  Plan Type:
      REMOTE
  Note:
      Degree of Parallelisim is 1 because of table property

有排序的expain extended：

==========================================================================================================
|ID|OPERATOR                                            |NAME                      |EST.ROWS|EST.TIME(us)|
----------------------------------------------------------------------------------------------------------
|0 |SORT                                                |                          |85      |8416        |
|1 |└─HASH DISTINCT                                     |                          |85      |8396        |
|2 |  └─NESTED-LOOP OUTER JOIN                          |                          |85      |8365        |
|3 |    ├─NESTED-LOOP JOIN                              |                          |2       |5687        |
|4 |    │ ├─PX COORDINATOR                              |                          |2       |5660        |
|5 |    │ │ └─EXCHANGE OUT DISTR                        |:EX10000                  |2       |5658        |
|6 |    │ │   └─NESTED-LOOP JOIN                        |                          |2       |5655        |
|7 |    │ │     ├─NESTED-LOOP JOIN                      |                          |2       |5628        |
|8 |    │ │     │ ├─NESTED-LOOP JOIN                    |                          |78      |4207        |
|9 |    │ │     │ │ ├─MERGE JOIN                        |                          |18      |3887        |
|10|    │ │     │ │ │ ├─TABLE RANGE SCAN                |cc(idx_m_q)               |1529    |45          |
|11|    │ │     │ │ │ └─SORT                            |                          |171     |3644        |
|12|    │ │     │ │ │   └─NESTED-LOOP JOIN              |                          |171     |3579        |
|13|    │ │     │ │ │     ├─TABLE RANGE SCAN            |gg(grade_id)              |161     |640         |
|14|    │ │     │ │ │     └─DISTRIBUTED TABLE RANGE SCAN|aa(diag_obj_id_q_id)      |1       |18          |
|15|    │ │     │ │ └─DISTRIBUTED TABLE RANGE SCAN      |b(diag_obj_id_descendants)|5       |18          |
|16|    │ │     │ └─DISTRIBUTED TABLE GET               |a                         |1       |18          |
|17|    │ │     └─DISTRIBUTED TABLE GET                 |c                         |1       |18          |
|18|    │ └─TABLE GET                                   |b                         |1       |18          |
|19|    └─DISTRIBUTED TABLE RANGE SCAN                  |d(diag_obj_id)            |56      |1743        |
==========================================================================================================
Outputs & filters:
-------------------------------------
  0 - output([a.diag_obj_id(0x7f58adc4c650)], [a.diag_obj_name(0x7f58adc57240)]), filter(nil), rowset=256
      sort_keys([a.show_seq(0x7f58adc59640), DESC])
  1 - output([a.show_seq(0x7f58adc59640)], [a.diag_obj_id(0x7f58adc4c650)], [a.diag_obj_name(0x7f58adc57240)]), filter(nil), rowset=256
      distinct([a.diag_obj_id(0x7f58adc4c650)])
  2 - output([a.show_seq(0x7f58adc59640)], [a.diag_obj_id(0x7f58adc4c650)], [a.diag_obj_name(0x7f58adc57240)]), filter(nil), rowset=256
      conds(nil), nl_params_([c.diag_obj_id(0x7f58adc500e0)(:18)]), use_batch=false
  3 - output([a.show_seq(0x7f58adc59640)], [a.diag_obj_id(0x7f58adc4c650)], [a.diag_obj_name(0x7f58adc57240)], [c.diag_obj_id(0x7f58adc500e0)]), filter(nil), rowset=256
      conds(nil), nl_params_([aa.diag_obj_id(0x7f58adc73030)(:6)]), use_batch=true
  4 - output([a.show_seq(0x7f58adc59640)], [a.diag_obj_id(0x7f58adc4c650)], [a.diag_obj_name(0x7f58adc57240)], [c.diag_obj_id(0x7f58adc500e0)], [aa.diag_obj_id(0x7f58adc73030)]), filter(nil), rowset=256
  5 - output([a.show_seq(0x7f58adc59640)], [a.diag_obj_id(0x7f58adc4c650)], [a.diag_obj_name(0x7f58adc57240)], [c.diag_obj_id(0x7f58adc500e0)], [aa.diag_obj_id(0x7f58adc73030)]), filter(nil), rowset=256
      is_single, dop=1
  6 - output([a.show_seq(0x7f58adc59640)], [a.diag_obj_id(0x7f58adc4c650)], [a.diag_obj_name(0x7f58adc57240)], [c.diag_obj_id(0x7f58adc500e0)], [aa.diag_obj_id(0x7f58adc73030)]), filter(nil), rowset=256
      conds(nil), nl_params_([aa.diag_obj_id(0x7f58adc73030)(:4)]), use_batch=true
  7 - output([a.show_seq(0x7f58adc59640)], [a.diag_obj_id(0x7f58adc4c650)], [a.diag_obj_name(0x7f58adc57240)], [aa.diag_obj_id(0x7f58adc73030)]), filter(nil), rowset=256
      conds(nil), nl_params_([b.diag_obj_id(0x7f58adc4c940)(:11)]), use_batch=true
  8 - output([aa.diag_obj_id(0x7f58adc73030)], [b.diag_obj_id(0x7f58adc4c940)]), filter(nil), rowset=256
      conds(nil), nl_params_([aa.diag_obj_id(0x7f58adc73030)(:2)]), use_batch=true
  9 - output([aa.diag_obj_id(0x7f58adc73030)]), filter(nil), rowset=256
      equal_conds([aa.q_id(0x7f58adc2a380) = cc.q_id(0x7f58adc2a670)(0x7f58adc29c30)]), other_conds(nil)
      merge_directions([ASC])
 10 - output([cc.q_id(0x7f58adc2a670)]), filter(nil), rowset=256
      access([cc.q_id(0x7f58adc2a670)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([cc.diag_module(0x7f58adc2bfd0)], [cc.q_id(0x7f58adc2a670)]), range(教学-专项诊断,MIN ; 教学-专项诊断,MAX), 
      range_cond([cc.diag_module(0x7f58adc2bfd0) = '教学-专项诊断'(0x7f58adc2ac00)])
 11 - output([aa.diag_obj_id(0x7f58adc73030)], [aa.q_id(0x7f58adc2a380)]), filter(nil), rowset=256
      sort_keys([aa.q_id(0x7f58adc2a380), ASC])
 12 - output([aa.diag_obj_id(0x7f58adc73030)], [aa.q_id(0x7f58adc2a380)]), filter(nil), rowset=256
      conds(nil), nl_params_([gg.diag_obj_id(0x7f58adcb8920)(:9)]), use_batch=true
 13 - output([gg.diag_obj_id(0x7f58adcb8920)]), filter(nil), rowset=256
      access([gg.__pk_increment(0x7f58adccc720)], [gg.diag_obj_id(0x7f58adcb8920)]), partitions(p0)
      is_index_back=true, is_global_index=false, 
      range_key([gg.grade_id(0x7f58adcb9b80)], [gg.__pk_increment(0x7f58adccc720)]), range(232100001686,MIN ; 232100001686,MAX), 
      range_cond([gg.grade_id(0x7f58adcb9b80) = '232100001686'(0x7f58adcb9430)])
 14 - output([aa.q_id(0x7f58adc2a380)], [aa.diag_obj_id(0x7f58adc73030)]), filter(nil), rowset=256
      access([GROUP_ID(0x7f5e689de020)], [aa.q_id(0x7f58adc2a380)], [aa.diag_obj_id(0x7f58adc73030)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([aa.school_code(0x7f58adcbade0)], [aa.school_term_id(0x7f58adcbc040)], [aa.diag_obj_id(0x7f58adc73030)], [aa.q_id(0x7f58adc2a380)], [aa.__pk_increment(0x7f58adcca530)]),
       range(MIN ; MAX), 
      range_cond([aa.school_code(0x7f58adcbade0) = 'bnds'(0x7f58adcba690)], [cast(aa.school_term_id(0x7f58adcbc040), DECIMAL(11, 0))(0x7f58adcbc5b0) = cast('2321',
       DECIMAL(1, -1))(0x7f58adcbd100)(0x7f58adcbb8f0)], [aa.diag_obj_id(0x7f58adc73030) = :9(0x7f5caea1cc90)])
 15 - output([b.diag_obj_id(0x7f58adc4c940)]), filter(nil), rowset=256
      access([GROUP_ID(0x7f5e689e0180)], [b.diag_obj_id(0x7f58adc4c940)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([b.diag_obj_id_descendants(0x7f58adc4fdf0)], [b.diag_obj_id(0x7f58adc4c940)]), range(MIN ; MAX), 
      range_cond([:2 = b.diag_obj_id_descendants(0x7f58adc4fdf0)(0x7f5ad66f8300)])
 16 - output([a.diag_obj_id(0x7f58adc4c650)], [a.diag_obj_name(0x7f58adc57240)], [a.show_seq(0x7f58adc59640)]), filter([a.diag_obj_type_id(0x7f58adc58440) 
      = 'XK'(0x7f58adc7d3b0)]), rowset=256
      access([GROUP_ID(0x7f5e689e22e0)], [a.diag_obj_id(0x7f58adc4c650)], [a.diag_obj_name(0x7f58adc57240)], [a.diag_obj_type_id(0x7f58adc58440)], [a.show_seq(0x7f58adc59640)]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([a.diag_obj_id(0x7f58adc4c650)]), range(MIN ; MAX), 
      range_cond([a.diag_obj_id(0x7f58adc4c650) = :11(0x7f5caea752a0)])
 17 - output([c.diag_obj_id(0x7f58adc500e0)]), filter([c.diag_obj_type_id(0x7f58adc6d0a0) IN ('JXBYS', 'JXBYB', 'JXBGDSY', 'JXBTY', 'JXBJS', 'JXBJINGSAI',
       'JXBDXXX', 'JXBXYZ', 'JXBZZ', 'JXBGJTY', 'JXBRWYSH', 'JXBJJ')(0x7f58add27970)(0x7f58adc738b0)]), rowset=256
      access([GROUP_ID(0x7f5e689e4440)], [c.diag_obj_id(0x7f58adc500e0)], [c.diag_obj_type_id(0x7f58adc6d0a0)]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([c.diag_obj_id(0x7f58adc500e0)]), range(MIN ; MAX), 
      range_cond([:4 = c.diag_obj_id(0x7f58adc500e0)(0x7f5ad6750110)])
 18 - output(nil), filter(nil), rowset=256
      access([GROUP_ID(0x7f5e689e4690)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([b.diag_obj_id(0x7f58adc9ab90)], [b.rpt_tag(0x7f58adca3010)]), range(MIN ; MAX), 
      range_cond([b.rpt_tag(0x7f58adca3010) = '本部'(0x7f58adcb4040)], [:6 = b.diag_obj_id(0x7f58adc9ab90)(0x7f5ad67b1660)])
 19 - output(nil), filter(nil), rowset=256
      access(nil), partitions(p[0-99])
      is_index_back=false, is_global_index=false, 
      range_key([d.diag_obj_id(0x7f58adc52f20)], [d.school_code(0x7f58adc51ea0)], [d.school_term_id(0x7f58adcc8f50)], [d.diagnostician_id(0x7f58adc563e0)],
       [d.school_seq(0x7f58adcc9230)]), range(MIN ; MAX), 
      range_cond([:18 = d.diag_obj_id(0x7f58adc52f20)(0x7f5caeb4dc50)])
Used Hint:
-------------------------------------
  /*+
      
      INLINE
  */
Qb name trace:
-------------------------------------
  stmt_id:0, stmt_type:T_EXPLAIN 
  stmt_id:1, SEL$1 > SEL$BFD73BB5 > SEL$CF3BE71E > SEL$A4CF0106 > SEL$70ACD2D2
  stmt_id:2, SEL$2 > SEL$2F8A4177
  stmt_id:3, SEL$3 > SEL$02A956E8
Outline Data: 
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_DISTINCT(@"SEL$70ACD2D2")
      LEADING(@"SEL$70ACD2D2" (((((("edu_dw"."cc"@"SEL$1" ("edu_dw"."gg"@"SEL$1" "edu_dw"."aa"@"SEL$1")) "edu_dw"."b"@"SEL$2") "edu_dw"."a"@"SEL$2") "edu_dw"."c"@"SEL$2") "edu_dw"."b"@"SEL$3") "edu_dw"."d"@"SEL$2"))
      USE_NL(@"SEL$70ACD2D2" "edu_dw"."d"@"SEL$2")
      USE_NL(@"SEL$70ACD2D2" "edu_dw"."b"@"SEL$3")
      PQ_DISTRIBUTE(@"SEL$70ACD2D2" "edu_dw"."b"@"SEL$3" LOCAL LOCAL)
      USE_NL(@"SEL$70ACD2D2" "edu_dw"."c"@"SEL$2")
      USE_NL(@"SEL$70ACD2D2" "edu_dw"."a"@"SEL$2")
      USE_NL(@"SEL$70ACD2D2" "edu_dw"."b"@"SEL$2")
      USE_MERGE(@"SEL$70ACD2D2" ("edu_dw"."gg"@"SEL$1" "edu_dw"."aa"@"SEL$1"))
      INDEX(@"SEL$70ACD2D2" "cc"@"SEL$1" "idx_m_q")
      USE_NL(@"SEL$70ACD2D2" "edu_dw"."aa"@"SEL$1")
      INDEX(@"SEL$70ACD2D2" "gg"@"SEL$1" "grade_id")
      INDEX(@"SEL$70ACD2D2" "aa"@"SEL$1" "diag_obj_id_q_id")
      USE_DAS(@"SEL$70ACD2D2" "aa"@"SEL$1")
      INDEX(@"SEL$70ACD2D2" "b"@"SEL$2" "diag_obj_id_descendants")
      USE_DAS(@"SEL$70ACD2D2" "b"@"SEL$2")
      FULL(@"SEL$70ACD2D2" "a"@"SEL$2")
      USE_DAS(@"SEL$70ACD2D2" "a"@"SEL$2")
      FULL(@"SEL$70ACD2D2" "c"@"SEL$2")
      USE_DAS(@"SEL$70ACD2D2" "c"@"SEL$2")
      FULL(@"SEL$70ACD2D2" "b"@"SEL$3")
      INDEX(@"SEL$70ACD2D2" "d"@"SEL$2" "diag_obj_id")
      USE_DAS(@"SEL$70ACD2D2" "d"@"SEL$2")
      PROJECT_PRUNE(@"SEL$2")
      PROJECT_PRUNE(@"SEL$3")
      MERGE(@"SEL$2F8A4177" > "SEL$1")
      MERGE(@"SEL$02A956E8" > "SEL$1")
      OUTER_TO_INNER(@"SEL$BFD73BB5")
      ELIMINATE_JOIN(@"SEL$CF3BE71E" "edu_dw"."a"@"SEL$3" "edu_dw"."e"@"SEL$2")
      PRED_DEDUCE(@"SEL$A4CF0106")
      OPTIMIZER_FEATURES_ENABLE('4.0.0.0')
      END_OUTLINE_DATA
  */
Optimization Info:
-------------------------------------
  cc:
      table_rows:18224
      physical_range_rows:1529
      logical_range_rows:1529
      index_back_rows:0
      output_rows:1529
      table_dop:1
      dop_method:Table DOP
      avaiable_index_name:[q_id, idx_m_q, idx_m_g_q, idx_y_q, dw_question]
      pruned_index_name:[q_id, idx_m_g_q, idx_y_q, dw_question]
      stats version:1700258621878630
      dynamic sampling level:0
  gg:
      table_rows:597629
      physical_range_rows:161
      logical_range_rows:161
      index_back_rows:161
      output_rows:161
      table_dop:1
      dop_method:Table DOP
      avaiable_index_name:[grade_id, diag_obj_id, v_diag_obj_grade]
      unstable_index_name:[v_diag_obj_grade]
      stats version:1700661644187339
      dynamic sampling level:0
  aa:
      table_rows:3753885
      physical_range_rows:1
      logical_range_rows:1
      index_back_rows:0
      output_rows:1
      table_dop:1
      dop_method:DAS DOP
      avaiable_index_name:[diag_obj_id, diag_obj_id_q_id, q_id, school_term_id, school_code, dw_actual_obj_q_stat]
      pruned_index_name:[diag_obj_id, school_term_id, school_code, dw_actual_obj_q_stat]
      stats version:1700834868529075
      dynamic sampling level:0
  b:
      table_rows:3431977
      physical_range_rows:4
      logical_range_rows:4
      index_back_rows:0
      output_rows:4
      table_dop:1
      dop_method:DAS DOP
      avaiable_index_name:[diag_obj_id_descendants, diag_obj_id, dw_diag_obj_closure]
      unstable_index_name:[dw_diag_obj_closure]
      stats version:1700489702736231
      dynamic sampling level:0
  a:
      table_rows:714072
      physical_range_rows:1
      logical_range_rows:1
      index_back_rows:0
      output_rows:0
      table_dop:1
      dop_method:DAS DOP
      avaiable_index_name:[diag_obj_id, school_code, s_s_t_d, d_t, t_d, dw_diag_obj]
      pruned_index_name:[diag_obj_id, school_code, s_s_t_d, d_t, t_d]
      stats version:1700489708744977
      dynamic sampling level:0
  c:
      table_rows:714072
      physical_range_rows:1
      logical_range_rows:1
      index_back_rows:0
      output_rows:0
      table_dop:1
      dop_method:DAS DOP
      avaiable_index_name:[diag_obj_id, school_code, s_s_t_d, d_t, t_d, dw_diag_obj]
      pruned_index_name:[diag_obj_id, school_code, s_s_t_d, d_t, t_d]
      stats version:1700489708744977
      dynamic sampling level:0
  b:
      table_rows:983353
      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:[rpt_tag, diag_obj_id, tag_obj, tag_obj_1, dw_diag_obj_rpt_tag]
      pruned_index_name:[rpt_tag, diag_obj_id, tag_obj, tag_obj_1]
      stats version:1700662329956275
      dynamic sampling level:0
  d:
      table_rows:35856035
      physical_range_rows:55
      logical_range_rows:55
      index_back_rows:0
      output_rows:55
      table_dop:1
      dop_method:DAS DOP
      avaiable_index_name:[diagnostician_id, diag_obj_id_diagnostician_id, diag_obj_id, school_code, school_seq, school_term_id, dw_diag_obj_tician_rtl]
      pruned_index_name:[diagnostician_id, diag_obj_id_diagnostician_id, school_code, school_seq, school_term_id, dw_diag_obj_tician_rtl]
      stats version:1701094246674841
      dynamic sampling level:0
  Plan Type:
      DISTRIBUTED
  Note:
      Degree of Parallelisim is 1 because of table property

甫瑜 · 2023 年11 月 28 日 12:48

像是走的较差的执行计划

xuyu · 2023 年11 月 28 日 13:46

大白熊:

SELECT DISTINCT
	bb.diag_obj_id AS param_value,
	bb.diag_obj_name AS param_value_dispaly
FROM
	dw_actual_obj_q_stat aa
join dw_question cc ON aa.q_id = cc.q_id AND cc.diag_module IN ('教学-专项诊断')
join v_diag_ojb_diagnostician_stat bb on aa.diag_obj_id=bb.diag_obj_id_child AND bb.diag_obj_type_id_child IN ('JXBYS','JXBYB','JXBGDSY','JXBTY','JXBJS','JXBJINGSAI','JXBDXXX','JXBXYZ','JXBZZ','JXBGJTY','JXBRWYSH','JXBJJ') and bb.diag_obj_type_id in ('XK')
join v_rpt_diag_obj dd on bb.diag_obj_id_child=dd.diag_obj_id and dd.rpt_diag_obj_tag in ('本部')
join v_diag_obj_grade gg on aa.diag_obj_id=gg.diag_obj_id and gg.grade_id='232100001686'

where aa.school_code = 'bnds' AND aa.school_term_id = '2321'AND aa.school_term_id = '2321'

请问d表是哪自来的，在原来的sql中并没有看到别名为d表的，只有dd表

任仲禹 · 2023 年11 月 28 日 13:48

select /*+ no_rewrite */ 加hint试下呢

大白熊 · 2023 年11 月 28 日 15:37

是视图 v_diag_ojb_diagnostician_stat 中的表

大白熊 · 2023 年11 月 28 日 15:39

加hint后，是否有排序都变慢了，二者时间差不多了

大白熊 · 2023 年11 月 28 日 15:40

视图结构如下：


CREATE VIEW `v_diag_ojb_diagnostician_stat` AS SELECT
/*+ INLINE */
`a`.`diag_obj_id` AS `diag_obj_id`,
`a`.`diag_obj_name` AS `diag_obj_name`,
`a`.`diag_obj_principal_id` AS `diag_obj_principal_id`,
`a`.`diag_obj_principal_name` AS `diag_obj_principal_name`,
`a`.`diag_obj_type_id` AS `diag_obj_type_id`,
`a`.`school_code` AS `school_code`,
`a`.`school_term_id` AS `school_term_id`,
`a`.`show_seq` AS `show_seq`,
`a`.`participate_in_diag` AS `participate_in_diag_obj`,
`c`.`participate_in_diag` AS `participate_in_diag_obj_child`,
`e`.`participate_in_diag` AS `participate_in_diag`,
`e`.`diagnostician_id` AS `diagnostician_id`,
`e`.`diagnostician_name` AS `diagnostician_name`,
`e`.`diagnostician_type_id` AS `diagnostician_type_id`,
`e`.`diag_code` AS `diag_code`,
`c`.`diag_obj_id` AS `diag_obj_id_child`,
`c`.`diag_obj_name` AS `diag_obj_name_child`,
`c`.`diag_obj_principal_id` AS `diag_obj_principal_id_child`,
`c`.`diag_obj_principal_name` AS `diag_obj_principal_name_child`,
`c`.`diag_obj_type_id` AS `diag_obj_type_id_child`,
`c`.`show_seq` AS `show_seq_child` 
FROM
	((((
					`edu_dw`.`dw_diag_obj` `a`
					JOIN `edu_dw`.`dw_diag_obj_closure` `b` ON ( `a`.`diag_obj_id` = `b`.`diag_obj_id` ))
				JOIN `edu_dw`.`dw_diag_obj` `c` ON ( `b`.`diag_obj_id_descendants` = `c`.`diag_obj_id` ))
		LEFT JOIN `edu_dw`.`dw_diag_obj_tician_rtl` `d` ON ( `c`.`diag_obj_id` = `d`.`diag_obj_id` ))
	LEFT JOIN `edu_dw`.`dw_diagnostician` `e` ON ( `d`.`diagnostician_id` = `e`.`diagnostician_id` ))

大白熊 · 2023 年11 月 28 日 15:43

怀疑使用了视图的sql，排序优化策略有问题，结果集也就11条

Lixz · 2023 年11 月 28 日 17:30

有排序的情况下，多扫描了视图中的d表，不知道优化器怎么算的