sql查询数据错误，直连两个observer查询竟然结果不一样

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

【使用环境】生产环境
【 OB or 其他组件】OB
【使用版本】4.1.0
【问题描述】sql查询数据错误，直连两个observer查询竟然结果不一样

同样一个sql 通过2881 连接两个两个server查询结果不一样！！
SELECT * from
(
SELECT DISTINCT
*
FROM
v_v_diag_obj_class2 a
WHERE
diag_obj_id IN (
SELECT
diag_obj_id
FROM
dw_diag_obj b
WHERE
b.school_code = ‘xmshczx’
AND b.school_term_id = ‘2321’
)
) as aa

并且在同一个observer中执行
SELECT * from
(
SELECT DISTINCT
*
FROM
v_v_diag_obj_class2 a
WHERE
diag_obj_id IN (
SELECT
diag_obj_id
FROM
dw_diag_obj b
WHERE
b.school_code = ‘xmshczx’
AND b.school_term_id = ‘2321’
)
) as aa
where aa.diag_obj_id=‘232108701175’
sql结果有值，但是去掉where条件查询的结果集中没有diag_obj_id=‘232108701175’ 的记录
求助。。。

序风 · 2023 年11 月 17 日 10:04

具体的小版本号能查一下吗
show parameters like ‘%version%’;

另外表结构和数据方便发吗？

秃蛙 · 2023 年11 月 17 日 10:04

直连2个节点，分别获取下执行计划看下。
explain EXTENDED sql语句

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

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

v_v_diag_obj_class2 是一个视图其中用到了union 。好像union后面的值没有生效


CREATE or replace VIEW v_v_diag_obj_class2 AS select DISTINCT diag_obj_id,class_id,class_name,class_show_seq from (
		(
		SELECT
			`dw_obj_teaching_classes_class`.`diag_obj_id` AS `diag_obj_id`,
			`dw_obj_teaching_classes_class`.`class_id` AS `class_id`,
			`dw_obj_teaching_classes_class`.`class_name` AS `class_name`,
			`dw_obj_teaching_classes_class`.`class_show_seq` AS `class_show_seq` 
		FROM
			`dw_obj_teaching_classes_class` 
		) 
		
	 UNION 
	
		(
		SELECT 
			`o`.`diag_obj_id` AS `diag_obj_id`,
			`o1`.`diag_obj_id` AS `class_id`,
			`o1`.`diag_obj_name` AS `class_name`,
			`o1`.`show_seq` AS `class_show_seq` 
		FROM
			(( `edu_dw`.`dw_diag_obj` `o` JOIN `edu_dw`.`dw_diag_obj_closure` `c` ON 1 = 1 ) JOIN `edu_dw`.`dw_diag_obj` `o1` ON 1 = 1 ) 
		WHERE
			( `o`.`diag_obj_id` = `c`.`diag_obj_id_descendants` ) 
		AND ( `c`.`diag_obj_id` = `o1`.`diag_obj_id` ) 
		AND ( `o1`.`diag_obj_type_id` = 'ZRB' ) 
		) 
	
	)

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

CREATE TABLE `dw_diag_obj` (
  `diag_obj_id` char(12) NOT NULL COMMENT '诊断对象ID',
  `diag_obj_type_id` varchar(32) DEFAULT NULL COMMENT '诊断对象类别ID',
  `school_code` varchar(20) NOT NULL DEFAULT 'bj12z' COMMENT '学校编码',
  `school_term_id` int(11) DEFAULT '152' COMMENT '学期编码',
  `subject_id` varchar(50) DEFAULT NULL COMMENT '学科编码',
  `parent_diag_obj_id` char(12) DEFAULT NULL COMMENT '诊断对象父对象',
  `diag_obj_code` varchar(200) DEFAULT NULL COMMENT '诊断对象编码',
  `diag_obj_name` varchar(100) DEFAULT NULL COMMENT '诊断对象名称',
  `diag_obj_photo` varchar(100) DEFAULT NULL COMMENT '诊断对象照片',
  `diag_obj_principal_id` char(13) DEFAULT NULL COMMENT '诊断对象负责人ID',
  `diag_obj_principal_name` varchar(100) DEFAULT NULL COMMENT '诊断对象负责人名称',
  `diag_obj_tag` varchar(100) DEFAULT NULL COMMENT '分类标签',
  `show_seq` int(11) DEFAULT NULL COMMENT '诊断对象的默认显示顺序',
  `participate_in_diag` tinyint(1) DEFAULT '1' COMMENT '是否参与诊断',
  `n_diag_obj_id` bigint(20) DEFAULT NULL COMMENT '诊断对象数值ID',
  `v_diag_obj_id` varchar(200) DEFAULT NULL COMMENT '诊断对象字符ID',
  PRIMARY KEY (`diag_obj_id`),
  CONSTRAINT `FK_诊断对象归属父对象` FOREIGN KEY (`parent_diag_obj_id`) REFERENCES `edu_dw`.`dw_diag_obj`(`diag_obj_id`) ON UPDATE CASCADE ON DELETE CASCADE ,
  CONSTRAINT `FK_诊断对象归属学科` FOREIGN KEY (`subject_id`) REFERENCES `edu_dw`.`dw_subject`(`subject_id`) ON UPDATE CASCADE ON DELETE CASCADE ,
  CONSTRAINT `FK_诊断对象归属学校` FOREIGN KEY (`school_code`) REFERENCES `edu_dw`.`dw_school`(`school_code`) ON UPDATE CASCADE ON DELETE CASCADE ,
  CONSTRAINT `FK_诊断对象归属学期` FOREIGN KEY (`school_term_id`) REFERENCES `edu_dw`.`dw_school_term`(`school_term_id`) ON UPDATE CASCADE ON DELETE CASCADE ,
  CONSTRAINT `FK_诊断对象属于诊断对象类别` FOREIGN KEY (`diag_obj_type_id`) REFERENCES `edu_dw`.`dw_diag_obj_type`(`diag_obj_type_id`) ON UPDATE CASCADE ON DELETE CASCADE ,
  KEY `diag_obj_id` (`diag_obj_id`) BLOCK_SIZE 16384 LOCAL,
  KEY `school_code` (`school_code`) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = COMPACT COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 COMMENT = '诊断对象'

秃蛙 · 2023 年11 月 17 日 10:08

最好提供一下表结构和可复现的数据，我们内部模拟下。

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

错误的节点

=====================================================================================
|ID|OPERATOR                    |NAME                         |EST.ROWS|EST.TIME(us)|
-------------------------------------------------------------------------------------
|0 |NESTED-LOOP JOIN            |                             |1       |5955        |
|1 | TABLE SCAN                 |b(school_code)               |1       |5955        |
|2 | SUBPLAN SCAN               |ANONYMOUS_VIEW1              |4       |158         |
|3 |  MERGE UNION DISTINCT      |                             |4       |158         |
|4 |   DISTRIBUTED TABLE SCAN   |dw_obj_teaching_classes_class|3       |19          |
|5 |   SORT                     |                             |1       |139         |
|6 |    NESTED-LOOP JOIN        |                             |1       |139         |
|7 |     NESTED-LOOP JOIN       |                             |5       |50          |
|8 |      DISTRIBUTED TABLE GET |o                            |1       |18          |
|9 |      DISTRIBUTED TABLE SCAN|c(diag_obj_id_descendants)   |5       |16          |
|10|     DISTRIBUTED TABLE GET  |o1                           |1       |16          |
=====================================================================================
Outputs & filters:
-------------------------------------
  0 - output([ANONYMOUS_VIEW1.diag_obj_id(0x7f093a2cac60)], [ANONYMOUS_VIEW1.class_id(0x7f093a2cb210)], [ANONYMOUS_VIEW1.class_name(0x7f093a2cb7c0)], [ANONYMOUS_VIEW1.class_show_seq(0x7f093a2cbd70)]),
filter(nil), rowset=256
      conds(nil), nl_params_([b.diag_obj_id(0x7f093a2f2940)]), batch_join=true
  1 - output([b.diag_obj_id(0x7f093a2f2940)]), filter([cast(b.school_term_id(0x7f093a2f0c30), DECIMAL(11, 0))(0x7f093a2f1170) = cast('2321', DECIMAL(1,
-1))(0x7f093a2f1be0)(0x7f093a2f0580)]), rowset=256
      access([b.diag_obj_id(0x7f093a2f2940)], [b.school_term_id(0x7f093a2f0c30)]), partitions(p0)
      is_index_back=true, is_global_index=false, filter_before_indexback[false], 
      range_key([b.school_code(0x7f093a2efae0)], [b.diag_obj_id(0x7f093a2f2940)]), range(xmshczx,MIN ; xmshczx,MAX), 
      range_cond([b.school_code(0x7f093a2efae0) = 'xmshczx'(0x7f093a2ef430)])
  2 - output([ANONYMOUS_VIEW1.diag_obj_id(0x7f093a2cac60)], [ANONYMOUS_VIEW1.class_id(0x7f093a2cb210)], [ANONYMOUS_VIEW1.class_name(0x7f093a2cb7c0)], [ANONYMOUS_VIEW1.class_show_seq(0x7f093a2cbd70)]),
filter(nil), rowset=256
      access([ANONYMOUS_VIEW1.diag_obj_id(0x7f093a2cac60)], [ANONYMOUS_VIEW1.class_id(0x7f093a2cb210)], [ANONYMOUS_VIEW1.class_name(0x7f093a2cb7c0)], [ANONYMOUS_VIEW1.class_show_seq(0x7f093a2cbd70)])
  3 - output([UNION([1])(0x7f093a2c9af0)], [UNION([2])(0x7f093a2c9d20)], [UNION([3])(0x7f093a2c9f50)], [UNION([4])(0x7f093a2ca180)]), filter(nil), rowset=256
  4 - output([dw_obj_teaching_classes_class.diag_obj_id(0x7f093a29fc70)], [dw_obj_teaching_classes_class.class_id(0x7f093a2a0220)], [dw_obj_teaching_classes_class.class_name(0x7f093a2a07d0)],
[dw_obj_teaching_classes_class.class_show_seq(0x7f093a2a0d80)]), filter(nil), rowset=256
      access([GROUP_ID(0x7f09db15fa90)], [dw_obj_teaching_classes_class.diag_obj_id(0x7f093a29fc70)], [dw_obj_teaching_classes_class.class_id(0x7f093a2a0220)],
[dw_obj_teaching_classes_class.class_name(0x7f093a2a07d0)], [dw_obj_teaching_classes_class.class_show_seq(0x7f093a2a0d80)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([dw_obj_teaching_classes_class.diag_obj_id(0x7f093a29fc70)], [dw_obj_teaching_classes_class.class_id(0x7f093a2a0220)]), range(MIN,MIN ;
MAX,MAX)always true, 
      range_cond([dw_obj_teaching_classes_class.diag_obj_id(0x7f093a29fc70) = :5(0x7f09717853a0)])
  5 - output([o.diag_obj_id(0x7f093a2c4f40)], [cast(o1.diag_obj_id(0x7f093a2c6650), VARCHAR(100))(0x7f093a2c90a0)], [o1.diag_obj_name(0x7f093a2c8530)],
[o1.show_seq(0x7f093a2c8ae0)]), filter(nil), rowset=256
      sort_keys([cast(o1.diag_obj_id(0x7f093a2c6650), VARCHAR(100))(0x7f093a2c90a0), ASC], [o1.diag_obj_name(0x7f093a2c8530), ASC], [o1.show_seq(0x7f093a2c8ae0),
ASC])
  6 - output([cast(o1.diag_obj_id(0x7f093a2c6650), VARCHAR(100))(0x7f093a2c90a0)], [o1.diag_obj_name(0x7f093a2c8530)], [o1.show_seq(0x7f093a2c8ae0)], [o.diag_obj_id(0x7f093a2c4f40)]),
filter(nil), rowset=256
      conds(nil), nl_params_([c.diag_obj_id(0x7f093a2c6390)]), batch_join=true
  7 - output([o.diag_obj_id(0x7f093a2c4f40)], [c.diag_obj_id(0x7f093a2c6390)]), filter(nil), rowset=256
      conds(nil), nl_params_([o.diag_obj_id(0x7f093a2c4f40)]), batch_join=true
  8 - output([o.diag_obj_id(0x7f093a2c4f40)]), filter(nil), rowset=256
      access([GROUP_ID(0x7f09db163be0)], [o.diag_obj_id(0x7f093a2c4f40)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([o.diag_obj_id(0x7f093a2c4f40)]), range(MIN ; MAX)always true, 
      range_cond([o.diag_obj_id(0x7f093a2c4f40) = :5(0x7f09717c4fe0)])
  9 - output([c.diag_obj_id(0x7f093a2c6390)]), filter(nil), rowset=256
      access([GROUP_ID(0x7f09db163e10)], [c.diag_obj_id(0x7f093a2c6390)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([c.diag_obj_id_descendants(0x7f093a2c5200)], [c.diag_obj_id(0x7f093a2c6390)]), range(MIN ; MAX), 
      range_cond([:6 = c.diag_obj_id_descendants(0x7f093a2c5200)(0x7f09db037640)])
 10 - output([o1.diag_obj_id(0x7f093a2c6650)], [o1.diag_obj_name(0x7f093a2c8530)], [o1.show_seq(0x7f093a2c8ae0)]), filter([o1.diag_obj_type_id(0x7f093a2c7760)
= 'ZRB'(0x7f093a2c70b0)]), rowset=256
      access([GROUP_ID(0x7f09db164040)], [o1.diag_obj_id(0x7f093a2c6650)], [o1.diag_obj_type_id(0x7f093a2c7760)], [o1.diag_obj_name(0x7f093a2c8530)], [o1.show_seq(0x7f093a2c8ae0)]),
partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([o1.diag_obj_id(0x7f093a2c6650)]), range(MIN ; MAX), 
      range_cond([:8 = o1.diag_obj_id(0x7f093a2c6650)(0x7f09db071580)])
Used Hint:
-------------------------------------
/*+
      
*/
Qb name trace:
-------------------------------------
  stmt_id:0, stmt_type:T_EXPLAIN 
  stmt_id:1, SEL$1 > SEL$354178DD > SEL$45BDDFFB > SEL$A82D6C9D > SEL$63F8D9AB > SEL$C84FE138
  stmt_id:2, SEL$2 > SEL$6E5640F5
  stmt_id:3, SET$1
  stmt_id:4, SEL$3
  stmt_id:5, SEL$4 > SEL$862290CF > SEL$BA81EF59
  stmt_id:6, SEL$5
Outline Data:
-------------------------------------
/*+
      BEGIN_OUTLINE_DATA
      LEADING(@"SEL$C84FE138" ("edu_dw"."b"@"SEL$5" "ANONYMOUS_VIEW1"@"SEL$2"))
      USE_NL(@"SEL$C84FE138" "ANONYMOUS_VIEW1"@"SEL$2")
      INDEX(@"SEL$C84FE138" "edu_dw"."b"@"SEL$5" "school_code")
      FULL(@"SEL$3" "edu_dw"."dw_obj_teaching_classes_class"@"SEL$3")
      USE_DAS(@"SEL$3" "edu_dw"."dw_obj_teaching_classes_class"@"SEL$3")
      LEADING(@"SEL$BA81EF59" (("edu_dw"."o"@"SEL$4" "edu_dw"."c"@"SEL$4") "edu_dw"."o1"@"SEL$4"))
      USE_NL(@"SEL$BA81EF59" "edu_dw"."o1"@"SEL$4")
      USE_NL(@"SEL$BA81EF59" "edu_dw"."c"@"SEL$4")
      FULL(@"SEL$BA81EF59" "edu_dw"."o"@"SEL$4")
      USE_DAS(@"SEL$BA81EF59" "edu_dw"."o"@"SEL$4")
      INDEX(@"SEL$BA81EF59" "edu_dw"."c"@"SEL$4" "diag_obj_id_descendants")
      USE_DAS(@"SEL$BA81EF59" "edu_dw"."c"@"SEL$4")
      FULL(@"SEL$BA81EF59" "edu_dw"."o1"@"SEL$4")
      USE_DAS(@"SEL$BA81EF59" "edu_dw"."o1"@"SEL$4")
      SIMPLIFY_EXPR(@"SEL$4")
      SIMPLIFY_DISTINCT(@"SEL$2")
      SIMPLIFY_DISTINCT(@"SEL$1")
      MERGE(@"SEL$6E5640F5" > "SEL$354178DD")
      UNNEST(@"SEL$5")
      SEMI_TO_INNER(@"SEL$A82D6C9D" "VIEW1")
      OUTER_TO_INNER(@"SEL$862290CF")
      MERGE(@"SEL$5" > "SEL$63F8D9AB")
      OPTIMIZER_FEATURES_ENABLE('4.0.0.0')
      END_OUTLINE_DATA
*/
Optimization Info:
-------------------------------------
b:
      table_rows:637044
      physical_range_rows:7083
      logical_range_rows:1571
      index_back_rows:1571
      output_rows:0
      est_method:local_storage
      optimization_method:cost_based
      avaiable_index_name:[school_code, dw_diag_obj]
      pruned_index_name:[diag_obj_id]
      table_id:509433:estimation info:(table_type:10, version:-1--1--1, logical_rc:1571, physical_rc:1571),
            (table_type:11, version:-1--1--1, logical_rc:-1378, physical_rc:4134),
            (table_type:12, version:-1--1--1, logical_rc:1378, physical_rc:1378)]            
      stats version:1690812050384966
dw_obj_teaching_classes_class:
      table_rows:716853
      physical_range_rows:2
      logical_range_rows:2
      index_back_rows:0
      output_rows:2
      est_method:local_storage
      optimization_method:cost_based
      avaiable_index_name:[class_id, dw_obj_teaching_classes_class]
      pruned_index_name:[diag_obj_id]
      table_id:500056:estimation info:(table_type:10, version:-1--1--1, logical_rc:717976, physical_rc:717976),
            (table_type:11, version:-1--1--1, logical_rc:3424, physical_rc:6793)]            
      stats version:1690812121205118
o:
      table_rows:637044
      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
      stats version:1690812050384966
c:
      table_rows:3056481
      physical_range_rows:4
      logical_range_rows:4
      index_back_rows:0
      output_rows:4
      est_method:local_storage
      optimization_method:cost_based
      avaiable_index_name:[diag_obj_id_descendants, diag_obj_id]
      unstable_index_name:[dw_diag_obj_closure]
      table_id:509434:estimation info:(table_type:10, version:-1--1--1, logical_rc:3083252, physical_rc:3083252),
            (table_type:11, version:-1--1--1, logical_rc:14634, physical_rc:34668)]            
      stats version:1690812053915354
o1:
      table_rows:637044
      physical_range_rows:1
      logical_range_rows:1
      index_back_rows:0
      output_rows:0
      est_method:local_storage
      optimization_method:rule_based
      heuristic_rule:unique_index_without_indexback
      stats version:1690812050384966
Plan Type:
      LOCAL
Expr Constraints:
      1 = 1 result is TRUE
      1 = 1 result is TRUE

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

正确的节点

====================================================================================
|ID|OPERATOR                   |NAME                         |EST.ROWS|EST.TIME(us)|
------------------------------------------------------------------------------------
|0 |NESTED-LOOP JOIN           |                             |1       |10253       |
|1 | PX COORDINATOR            |                             |1       |10253       |
|2 |  EXCHANGE OUT DISTR       |:EX10000                     |1       |10253       |
|3 |   TABLE SCAN              |b(school_code)               |1       |10253       |
|4 | SUBPLAN SCAN              |ANONYMOUS_VIEW1              |4       |127         |
|5 |  MERGE UNION DISTINCT     |                             |4       |127         |
|6 |   DISTRIBUTED TABLE SCAN  |dw_obj_teaching_classes_class|3       |19          |
|7 |   SORT                    |                             |1       |108         |
|8 |    NESTED-LOOP JOIN       |                             |1       |108         |
|9 |     NESTED-LOOP JOIN      |                             |5       |35          |
|10|      DISTRIBUTED TABLE GET|o                            |1       |18          |
|11|      TABLE SCAN           |c(diag_obj_id_descendants)   |5       |16          |
|12|     DISTRIBUTED TABLE GET |o1                           |1       |16          |
====================================================================================
Outputs & filters:
-------------------------------------
  0 - output([ANONYMOUS_VIEW1.diag_obj_id(0x7fcf198ca850)], [ANONYMOUS_VIEW1.class_id(0x7fcf198cae00)], [ANONYMOUS_VIEW1.class_name(0x7fcf198cb3b0)], [ANONYMOUS_VIEW1.class_show_seq(0x7fcf198cb960)]),
filter(nil), rowset=256
      conds(nil), nl_params_([b.diag_obj_id(0x7fcf198f2530)]), batch_join=false
  1 - output([b.diag_obj_id(0x7fcf198f2530)]), filter(nil), rowset=256
  2 - output([b.diag_obj_id(0x7fcf198f2530)]), filter(nil), rowset=256
      is_single, dop=1
  3 - output([b.diag_obj_id(0x7fcf198f2530)]), filter([cast(b.school_term_id(0x7fcf198f0820), DECIMAL(11, 0))(0x7fcf198f0d60) = cast('2321', DECIMAL(1,
-1))(0x7fcf198f17d0)(0x7fcf198f0170)]), rowset=256
      access([b.diag_obj_id(0x7fcf198f2530)], [b.school_term_id(0x7fcf198f0820)]), partitions(p0)
      is_index_back=true, is_global_index=false, filter_before_indexback[false], 
      range_key([b.school_code(0x7fcf198ef6d0)], [b.diag_obj_id(0x7fcf198f2530)]), range(xmshczx,MIN ; xmshczx,MAX), 
      range_cond([b.school_code(0x7fcf198ef6d0) = 'xmshczx'(0x7fcf198ef020)])
  4 - output([ANONYMOUS_VIEW1.diag_obj_id(0x7fcf198ca850)], [ANONYMOUS_VIEW1.class_id(0x7fcf198cae00)], [ANONYMOUS_VIEW1.class_name(0x7fcf198cb3b0)], [ANONYMOUS_VIEW1.class_show_seq(0x7fcf198cb960)]),
filter(nil), rowset=256
      access([ANONYMOUS_VIEW1.diag_obj_id(0x7fcf198ca850)], [ANONYMOUS_VIEW1.class_id(0x7fcf198cae00)], [ANONYMOUS_VIEW1.class_name(0x7fcf198cb3b0)], [ANONYMOUS_VIEW1.class_show_seq(0x7fcf198cb960)])
  5 - output([UNION([1])(0x7fcf198c96e0)], [UNION([2])(0x7fcf198c9910)], [UNION([3])(0x7fcf198c9b40)], [UNION([4])(0x7fcf198c9d70)]), filter(nil), rowset=256
  6 - output([dw_obj_teaching_classes_class.diag_obj_id(0x7fcf1989f860)], [dw_obj_teaching_classes_class.class_id(0x7fcf1989fe10)], [dw_obj_teaching_classes_class.class_name(0x7fcf198a03c0)],
[dw_obj_teaching_classes_class.class_show_seq(0x7fcf198a0970)]), filter(nil), rowset=256
      access([dw_obj_teaching_classes_class.diag_obj_id(0x7fcf1989f860)], [dw_obj_teaching_classes_class.class_id(0x7fcf1989fe10)], [dw_obj_teaching_classes_class.class_name(0x7fcf198a03c0)],
[dw_obj_teaching_classes_class.class_show_seq(0x7fcf198a0970)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([dw_obj_teaching_classes_class.diag_obj_id(0x7fcf1989f860)], [dw_obj_teaching_classes_class.class_id(0x7fcf1989fe10)]), range(MIN,MIN ;
MAX,MAX)always true, 
      range_cond([dw_obj_teaching_classes_class.diag_obj_id(0x7fcf1989f860) = :5(0x7fce61b5a250)])
  7 - output([o.diag_obj_id(0x7fcf198c4b30)], [cast(o1.diag_obj_id(0x7fcf198c6240), VARCHAR(100))(0x7fcf198c8c90)], [o1.diag_obj_name(0x7fcf198c8120)],
[o1.show_seq(0x7fcf198c86d0)]), filter(nil), rowset=256
      sort_keys([cast(o1.diag_obj_id(0x7fcf198c6240), VARCHAR(100))(0x7fcf198c8c90), ASC], [o1.diag_obj_name(0x7fcf198c8120), ASC], [o1.show_seq(0x7fcf198c86d0),
ASC])
  8 - output([cast(o1.diag_obj_id(0x7fcf198c6240), VARCHAR(100))(0x7fcf198c8c90)], [o1.diag_obj_name(0x7fcf198c8120)], [o1.show_seq(0x7fcf198c86d0)], [o.diag_obj_id(0x7fcf198c4b30)]),
filter(nil), rowset=256
      conds(nil), nl_params_([c.diag_obj_id(0x7fcf198c5f80)]), batch_join=true
  9 - output([o.diag_obj_id(0x7fcf198c4b30)], [c.diag_obj_id(0x7fcf198c5f80)]), filter(nil), rowset=256
      conds(nil), nl_params_([o.diag_obj_id(0x7fcf198c4b30)]), batch_join=true
 10 - output([o.diag_obj_id(0x7fcf198c4b30)]), filter(nil), rowset=256
      access([o.diag_obj_id(0x7fcf198c4b30)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([o.diag_obj_id(0x7fcf198c4b30)]), range(MIN ; MAX)always true, 
      range_cond([o.diag_obj_id(0x7fcf198c4b30) = :5(0x7fce61b99e90)])
 11 - output([c.diag_obj_id(0x7fcf198c5f80)]), filter(nil), rowset=256
      access([GROUP_ID(0x7fcfc7b30170)], [c.diag_obj_id(0x7fcf198c5f80)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([c.diag_obj_id_descendants(0x7fcf198c4df0)], [c.diag_obj_id(0x7fcf198c5f80)]), range(MIN ; MAX), 
      range_cond([:6 = c.diag_obj_id_descendants(0x7fcf198c4df0)(0x7fce61bf4da0)])
 12 - output([o1.diag_obj_id(0x7fcf198c6240)], [o1.diag_obj_name(0x7fcf198c8120)], [o1.show_seq(0x7fcf198c86d0)]), filter([o1.diag_obj_type_id(0x7fcf198c7350)
= 'ZRB'(0x7fcf198c6ca0)]), rowset=256
      access([GROUP_ID(0x7fcfc7b303a0)], [o1.diag_obj_id(0x7fcf198c6240)], [o1.diag_obj_type_id(0x7fcf198c7350)], [o1.diag_obj_name(0x7fcf198c8120)], [o1.show_seq(0x7fcf198c86d0)]),
partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([o1.diag_obj_id(0x7fcf198c6240)]), range(MIN ; MAX), 
      range_cond([:8 = o1.diag_obj_id(0x7fcf198c6240)(0x7fcfc7a280c0)])
Used Hint:
-------------------------------------
/*+
      
*/
Qb name trace:
-------------------------------------
  stmt_id:0, stmt_type:T_EXPLAIN 
  stmt_id:1, SEL$1 > SEL$354178DD > SEL$45BDDFFB > SEL$A82D6C9D > SEL$63F8D9AB > SEL$C84FE138
  stmt_id:2, SEL$2 > SEL$6E5640F5
  stmt_id:3, SET$1
  stmt_id:4, SEL$3
  stmt_id:5, SEL$4 > SEL$862290CF > SEL$BA81EF59
  stmt_id:6, SEL$5
Outline Data:
-------------------------------------
/*+
      BEGIN_OUTLINE_DATA
      LEADING(@"SEL$C84FE138" ("edu_dw"."b"@"SEL$5" "ANONYMOUS_VIEW1"@"SEL$2"))
      USE_NL(@"SEL$C84FE138" "ANONYMOUS_VIEW1"@"SEL$2")
      PQ_DISTRIBUTE(@"SEL$C84FE138" "ANONYMOUS_VIEW1"@"SEL$2" LOCAL LOCAL)
      INDEX(@"SEL$C84FE138" "edu_dw"."b"@"SEL$5" "school_code")
      FULL(@"SEL$3" "edu_dw"."dw_obj_teaching_classes_class"@"SEL$3")
      USE_DAS(@"SEL$3" "edu_dw"."dw_obj_teaching_classes_class"@"SEL$3")
      LEADING(@"SEL$BA81EF59" (("edu_dw"."o"@"SEL$4" "edu_dw"."c"@"SEL$4") "edu_dw"."o1"@"SEL$4"))
      USE_NL(@"SEL$BA81EF59" "edu_dw"."o1"@"SEL$4")
      USE_NL(@"SEL$BA81EF59" "edu_dw"."c"@"SEL$4")
      FULL(@"SEL$BA81EF59" "edu_dw"."o"@"SEL$4")
      USE_DAS(@"SEL$BA81EF59" "edu_dw"."o"@"SEL$4")
      INDEX(@"SEL$BA81EF59" "edu_dw"."c"@"SEL$4" "diag_obj_id_descendants")
      FULL(@"SEL$BA81EF59" "edu_dw"."o1"@"SEL$4")
      USE_DAS(@"SEL$BA81EF59" "edu_dw"."o1"@"SEL$4")
      SIMPLIFY_EXPR(@"SEL$4")
      SIMPLIFY_DISTINCT(@"SEL$2")
      SIMPLIFY_DISTINCT(@"SEL$1")
      MERGE(@"SEL$6E5640F5" > "SEL$354178DD")
      UNNEST(@"SEL$5")
      SEMI_TO_INNER(@"SEL$A82D6C9D" "VIEW1")
      OUTER_TO_INNER(@"SEL$862290CF")
      MERGE(@"SEL$5" > "SEL$63F8D9AB")
      OPTIMIZER_FEATURES_ENABLE('4.0.0.0')
      END_OUTLINE_DATA
*/
Optimization Info:
-------------------------------------
b:
      table_rows:637044
      physical_range_rows:7083
      logical_range_rows:2949
      index_back_rows:2949
      output_rows:0
      est_method:local_storage
      optimization_method:cost_based
      avaiable_index_name:[school_code, dw_diag_obj]
      pruned_index_name:[diag_obj_id]
      table_id:509433:estimation info:(table_type:10, version:-1--1--1, logical_rc:1571, physical_rc:1571),
            (table_type:11, version:-1--1--1, logical_rc:1378, physical_rc:5512)]            
      stats version:1690812050384966
dw_obj_teaching_classes_class:
      table_rows:716853
      physical_range_rows:2
      logical_range_rows:2
      index_back_rows:0
      output_rows:2
      est_method:local_storage
      optimization_method:cost_based
      avaiable_index_name:[class_id, dw_obj_teaching_classes_class]
      pruned_index_name:[diag_obj_id]
      table_id:500056:estimation info:(table_type:10, version:-1--1--1, logical_rc:717976, physical_rc:717976),
            (table_type:11, version:-1--1--1, logical_rc:3424, physical_rc:6793)]            
      stats version:1690812121205118
o:
      table_rows:637044
      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
      stats version:1690812050384966
c:
      table_rows:3056481
      physical_range_rows:4
      logical_range_rows:4
      index_back_rows:0
      output_rows:4
      est_method:local_storage
      optimization_method:cost_based
      avaiable_index_name:[diag_obj_id_descendants, diag_obj_id]
      unstable_index_name:[dw_diag_obj_closure]
      table_id:509434:estimation info:(table_type:10, version:-1--1--1, logical_rc:3083252, physical_rc:3083252),
            (table_type:11, version:-1--1--1, logical_rc:14634, physical_rc:34668)]            
      stats version:1690812053915354
o1:
      table_rows:637044
      physical_range_rows:1
      logical_range_rows:1
      index_back_rows:0
      output_rows:0
      est_method:local_storage
      optimization_method:rule_based
      heuristic_rule:unique_index_without_indexback
      stats version:1690812050384966
Plan Type:
      DISTRIBUTED
Expr Constraints:
      1 = 1 result is TRUE
      1 = 1 result is TRUE

大白熊 · 2023 年11 月 17 日 11:00

explain 看着有问题吗？

大白熊 · 2023 年11 月 17 日 11:02

我们会多次重导数据，会是同步有延迟导致的吗

序风 · 2023 年11 月 17 日 11:47

内部复现排查中，有进展会和您同步的

大白熊 · 2023 年11 月 17 日 13:08

好的,我们这是生产环境出问题了，希望尽快，谢谢！

序风 · 2023 年11 月 17 日 16:15

已知问题：
1.设置 global _nlj_batching_enabled = false 可以绕过；
2.最新发布的421bp1上已经修复了这个问题，建议尽快升级成421bp1；