难复现的复杂sql

最新推荐文章于 2024-06-05 08:30:00 发布

librasama

最新推荐文章于 2024-06-05 08:30:00 发布

阅读量613

点赞数

本文链接：https://blog.csdn.net/librasama/article/details/8880622

版权

这是一段多表联查，表自关联且嵌套子查询中还有此表聚集函数(max)的sql。查询结果后需要排序，之后进行分页。运行于oracle10。鉴于对oracle执行计划了解的尚不深入，所以不保证能够清楚的剖析问题的来龙去脉。只是记录下这个奇怪的问题……

原sql

SELECT   con1.*, ROWNUM rn
  FROM   (  SELECT   psn.did,
                     (SELECT   deptname
                        FROM   t_dept
                       WHERE   did = psn.did)
                        deptname,
                     code.descitem,
                     code.longdesc,
                     (CASE
                         WHEN groupno IS NULL AND departmentno IS NULL
                         THEN
                            majorno
                         WHEN groupno IS NULL
                         THEN
                            majorno || ',' || departmentno
                         ELSE
                            majorno || ',' || departmentno || ',' || groupno
                      END)
                        newatid,
                     (SELECT   teamname
                        FROM   t_team
                       WHERE   enddate = (SELECT   MAX (tt.enddate)
                                            FROM   t_team tt
                                           WHERE   tt.tid = tm.tid)
                               AND tid = tm.tid)
                        zu,
                     (SELECT   teamname
                        FROM   t_team
                       WHERE   enddate = (SELECT   MAX (tt.enddate)
                                            FROM   t_team tt
                                           WHERE   tt.tid = t2t.relatid)
                               AND tid = t2t.relatid)
                        bu,
                     (SELECT   teamname
                        FROM   t_team
                       WHERE   enddate = (SELECT   MAX (tt.enddate)
                                            FROM   t_team tt
                                           WHERE   tt.tid = t2t1.relatid)
                               AND tid = t2t1.relatid)
                        qu,
                     (SELECT   DISTINCT rankname
                        FROM   t_rankdef
                       WHERE   rankid = cr.chgbakrankid)
                        rankname,
                     TO_CHAR (cr.startdate, 'YYYY-MM-DD') rankstartdate,
                     TO_CHAR (cr.enddate, 'YYYY-MM-DD') rankenddate,
                     psn.pid,
                     psn.psnname,
                     NVL (nt.peid, '-') peid,
                     (SELECT   codename
                        FROM   t_codedef
                       WHERE   codetypeno = 'P007' AND codevalue = psn.status)
                        status,
                     (SELECT   codename
                        FROM   t_codedef
                       WHERE   codetypeno = 'C002' AND codevalue = nt.series)
                        series,
                     nt.assstartdate
              FROM   t_person psn,
                     t_naturegx nt,
                     t_memberset tm,
                     t_teamno tn,
                     t_chgrank cr,
                     t_team2team t2t,
                     t_team2team t2t1,
                     t_psn2dept_gx p2d,
                     la_codeitem code
             WHERE       code.descitem = p2d.aracde
                     AND p2d.pid = psn.pid
                     AND cr.enddate BETWEEN p2d.startdate AND p2d.enddate
                     AND p2d.aracde IN ('006')
                     AND psn.pid = tm.pid
                     AND cr.enddate BETWEEN tm.startdate AND tm.enddate
                     AND tm.tid = t2t.signtid
                     AND t2t.relano = 'TT01'
                     AND t2t.enddate = (SELECT   MAX (enddate)
                                          FROM   t_team2team
                                         WHERE   signtid = t2t.signtid)
                     AND t2t.relatid = t2t1.signtid
                     AND t2t1.relano = 'TT02'
                     AND t2t1.enddate = (SELECT   MAX (enddate)
                                           FROM   t_team2team
                                          WHERE   signtid = t2t1.signtid)
                     AND tm.tid = tn.tid
                     AND cr.enddate BETWEEN tn.startdate AND tn.enddate
                     AND psn.pid = nt.pid
                     AND cr.enddate BETWEEN nt.startdate AND nt.enddate
                     AND psn.pid = cr.pid
                     AND cr.enddate =
                           (SELECT   MIN (enddate)
                              FROM   t_chgrank
                             WHERE   pid = cr.pid
                                     AND enddate BETWEEN psn.startdate
                                                     AND  psn.enddate)
                     AND psn.psnname LIKE '%张一%'
                     AND psn.status IN ('0')
          ORDER BY   pid) con1
 WHERE   ROWNUM > 0

去掉一些无用的select字段。

SELECT   con1.*, ROWNUM rn
  FROM   (  SELECT   
                     (SELECT   teamname
                        FROM   t_team
                       WHERE   enddate = (SELECT   MAX (tt.enddate)
                                            FROM   t_team tt
                                           WHERE   tt.tid = t2t.relatid)
                               AND tid = t2t.relatid)
                        bu,
                     (SELECT   teamname
                        FROM   t_team
                       WHERE   enddate = (SELECT   MAX (tt.enddate)
                                            FROM   t_team tt
                                           WHERE   tt.tid = t2t1.relatid)
                               AND tid = t2t1.relatid)
                        qu
                     
              FROM   t_person psn,
                     t_naturegx nt,
                     t_memberset tm,
                     t_teamno tn,
                     t_chgrank cr,
                     t_team2team t2t,
                     t_team2team t2t1,
                     t_psn2dept_gx p2d,
                     la_codeitem code
             WHERE       code.descitem = p2d.aracde
                     AND p2d.pid = psn.pid
                     AND cr.enddate BETWEEN p2d.startdate AND p2d.enddate
                     AND p2d.aracde IN ('006')
                     AND psn.pid = tm.pid
                     AND cr.enddate BETWEEN tm.startdate AND tm.enddate
                     AND tm.tid = t2t.signtid
                     AND t2t.relano = 'TT01'
                     AND t2t.enddate = (SELECT   MAX (enddate)
                                          FROM   t_team2team
                                         WHERE   signtid = t2t.signtid)
                     AND t2t.relatid = t2t1.signtid
                     AND t2t1.relano = 'TT02'
                     AND t2t1.enddate = (SELECT   MAX (enddate)
                                           FROM   t_team2team
                                          WHERE   signtid = t2t1.signtid)
                     AND tm.tid = tn.tid
                     AND cr.enddate BETWEEN tn.startdate AND tn.enddate
                     AND psn.pid = nt.pid
                     AND cr.enddate BETWEEN nt.startdate AND nt.enddate
                     AND psn.pid = cr.pid
                     AND cr.enddate =
                           (SELECT   MIN (enddate)
                              FROM   t_chgrank
                             WHERE   pid = cr.pid
                                     AND enddate BETWEEN psn.startdate
                                                     AND  psn.enddate)
                     AND psn.psnname LIKE '%张一%'
                     AND psn.status IN ('0')
          ORDER BY   psn.pid) con1
 WHERE   ROWNUM > 0

执行结果

执行计划

SELECT STATEMENT, GOAL = ALL_ROWS			254	1	90
 COUNT					
  FILTER					
   VIEW	SMS422		246	1	90
    SORT ORDER BY			246	1	253
     FILTER					
      NESTED LOOPS			243	1	253
       NESTED LOOPS			242	1	214
        NESTED LOOPS			240	1	175
         NESTED LOOPS			238	1	138
          NESTED LOOPS			216	1	109
           HASH JOIN			214	1	84
            HASH JOIN			139	177	11859
             TABLE ACCESS FULL	SMS422	T_PERSON	86	177	6018
             HASH JOIN			52	2328	76824
              INDEX FULL SCAN	SMS422	PK_LA_CODEITEM	1	1	4
              TABLE ACCESS FULL	SMS422	T_PSN2DEPT_GX	51	2328	67512
            INDEX FAST FULL SCAN	SMS422	PK_T_CHGRANK	74	56027	952459
           TABLE ACCESS BY INDEX ROWID	SMS422	T_NATUREGX	2	1	25
            INDEX RANGE SCAN	SMS422	PK_T_NATUREGX	1	1	
          TABLE ACCESS FULL	SMS422	T_TEAMNO	22	26	754
         TABLE ACCESS BY INDEX ROWID	SMS422	T_MEMBERSET	2	1	37
          INDEX RANGE SCAN	SMS422	PK_T_MEMBERSET	1	1	
        TABLE ACCESS BY INDEX ROWID	SMS422	T_TEAM2TEAM	2	1	39
         INDEX RANGE SCAN	SMS422	PK_T_TEAM2TEAM	1	1	
       INDEX RANGE SCAN	SMS422	PK_T_TEAM2TEAM	1	1	39
      SORT AGGREGATE				1	17
       FILTER					
        INDEX RANGE SCAN	SMS422	PK_T_CHGRANK	2	1	17
   SORT AGGREGATE				1	21
    TABLE ACCESS BY INDEX ROWID	SMS422	T_TEAM2TEAM	4	3	63
     INDEX RANGE SCAN	SMS422	PK_T_TEAM2TEAM	2	3	
   SORT AGGREGATE				1	21
    TABLE ACCESS BY INDEX ROWID	SMS422	T_TEAM2TEAM	4	3	63
     INDEX RANGE SCAN	SMS422	PK_T_TEAM2TEAM	2	3

t_team2team存放的是团队与团队关系，存放着两级团队，区(TT01)部(TT02)。结果集重复出于此处，同时也是最可能出现问题的表。（自关联，查询条件中两次自身聚集操作）。可以看到多层嵌套关联中此表使用索引关联。

去掉任意一个max查询条件，结果正确

AND t2t.enddate = to_date('9999-09-09', 'yyyy-mm-dd')

执行计划

SELECT STATEMENT, GOAL = ALL_ROWS			265	1	326
 COUNT					
  FILTER					
   VIEW	SMS422		261	1	326
    SORT ORDER BY			261	1	316
     FILTER					
      TABLE ACCESS BY INDEX ROWID	SMS422	T_TEAM2TEAM	2	1	39
       NESTED LOOPS			258	1	316
        NESTED LOOPS			256	1	277
         NESTED LOOPS			254	1	238
          NESTED LOOPS			252	1	201
           NESTED LOOPS			230	1	161
            HASH JOIN			228	1	121
             HASH JOIN			140	177	15930
              TABLE ACCESS FULL	SMS422	T_PERSON	86	177	7257
              HASH JOIN			53	2328	114072
               TABLE ACCESS FULL	SMS422	LA_CODEITEM	2	1	20
               TABLE ACCESS FULL	SMS422	T_PSN2DEPT_GX	51	2328	67512
             TABLE ACCESS FULL	SMS422	T_CHGRANK	87	56027	1736837
            TABLE ACCESS BY INDEX ROWID	SMS422	T_NATUREGX	2	1	40
             INDEX RANGE SCAN	SMS422	PK_T_NATUREGX	1	1	
           TABLE ACCESS FULL	SMS422	T_TEAMNO	22	26	1040
          TABLE ACCESS BY INDEX ROWID	SMS422	T_MEMBERSET	2	1	37
           INDEX RANGE SCAN	SMS422	PK_T_MEMBERSET	1	1	
         TABLE ACCESS BY INDEX ROWID	SMS422	T_TEAM2TEAM	2	1	39
          INDEX RANGE SCAN	SMS422	PK_T_TEAM2TEAM	1	1	
        INDEX RANGE SCAN	SMS422	PK_T_TEAM2TEAM	1	1	
      SORT AGGREGATE				1	17
       FILTER					
        INDEX RANGE SCAN	SMS422	PK_T_CHGRANK	2	1	17
   SORT AGGREGATE				1	21
    TABLE ACCESS BY INDEX ROWID	SMS422	T_TEAM2TEAM	4	3	63
     INDEX RANGE SCAN	SMS422	PK_T_TEAM2TEAM	2	3

交换t_team2team两处过滤条件，改为下面这样

AND t2t1.enddate = (SELECT   MAX (enddate) FROM   t_team2team WHERE   signtid = t2t1.signtid)
AND t2t.enddate = (SELECT MAX (enddate) FROM t_team2team WHERE signtid = t2t.signtid)

结果居然不一样。。。

好吧，使用group by过滤来看看

AND t2t.enddate = (SELECT   MAX (enddate) FROM   t_team2team WHERE   signtid = t2t.signtid group by signtid having count(1)>4)
AND t2t1.enddate = (SELECT   MAX (enddate) FROM   t_team2team WHERE   signtid = t2t1.signtid group by signtid having count(1)>1)

AND t2t.enddate = (SELECT   MAX (enddate) FROM   t_team2team WHERE   signtid = t2t.signtid group by signtid having count(1)>5)
AND t2t1.enddate = (SELECT   MAX (enddate) FROM   t_team2team WHERE   signtid = t2t1.signtid group by signtid having count(1)>1)

结果为空。

看来

SELECT   MAX (enddate) FROM   t_team2team WHERE   signtid = t2t.signtid

查询结果集大小是5喽？再颠倒证明了。。。No

AND t2t1.enddate = (SELECT   MAX (enddate)  FROM   t_team2team WHERE   signtid = t2t1.signtid group by signtid having count(1)>1)
AND t2t.enddate = (SELECT   MAX (enddate) FROM   t_team2team WHERE   signtid = t2t.signtid group by signtid having count(1)>2)

AND t2t1.enddate = (SELECT   MAX (enddate)   FROM   t_team2team WHERE   signtid = t2t1.signtid group by signtid having count(1)>1)
AND t2t.enddate = (SELECT   MAX (enddate) FROM   t_team2team WHERE   signtid = t2t.signtid group by signtid having count(1)>3)

结果为空。

跪了。换个位置结果集大小就变了。看来是自关联+嵌套条件+聚集函数的问题？总之造成了中间结果集的不确定。同时，中间结果集还有其他排序参与着

去掉order by条件，结果正确

--ORDER BY   psn.pid

去掉外层的rownum，执行内侧子查询，结果正确

执行计划

SELECT STATEMENT, GOAL = ALL_ROWS            254    1    253
 FILTER                    
  TABLE ACCESS BY INDEX ROWID    SMS422    T_TEAM    3    1    31
   INDEX RANGE SCAN    SMS422    PK_T_TEAM    2    1    
  SORT AGGREGATE                1    21
   TABLE ACCESS BY INDEX ROWID    SMS422    T_TEAM    3    1    21
    INDEX RANGE SCAN    SMS422    PK_T_TEAM    2    1    
 FILTER                    
  TABLE ACCESS BY INDEX ROWID    SMS422    T_TEAM    3    1    31
   INDEX RANGE SCAN    SMS422    PK_T_TEAM    2    1    
  SORT AGGREGATE                1    21
   TABLE ACCESS BY INDEX ROWID    SMS422    T_TEAM    3    1    21
    INDEX RANGE SCAN    SMS422    PK_T_TEAM    2    1    
 COUNT                    
  FILTER                    
   TABLE ACCESS BY INDEX ROWID    SMS422    T_TEAM2TEAM    2    1    39
    NESTED LOOPS            244    1    253
     NESTED LOOPS            242    1    214
      NESTED LOOPS            240    1    175
       NESTED LOOPS            238    1    138
        NESTED LOOPS            216    1    109
         HASH JOIN            214    1    84
          HASH JOIN            139    177    11859
           TABLE ACCESS FULL    SMS422    T_PERSON    86    177    6018
           HASH JOIN            52    2328    76824
            INDEX FULL SCAN    SMS422    PK_LA_CODEITEM    1    1    4
            TABLE ACCESS FULL    SMS422    T_PSN2DEPT_GX    51    2328    67512
          INDEX FAST FULL SCAN    SMS422    PK_T_CHGRANK    74    56027    952459
         TABLE ACCESS BY INDEX ROWID    SMS422    T_NATUREGX    2    1    25
          INDEX RANGE SCAN    SMS422    PK_T_NATUREGX    1    1    
        TABLE ACCESS FULL    SMS422    T_TEAMNO    22    26    754
       TABLE ACCESS BY INDEX ROWID    SMS422    T_MEMBERSET    2    1    37
        INDEX RANGE SCAN    SMS422    PK_T_MEMBERSET    1    1    
      TABLE ACCESS BY INDEX ROWID    SMS422    T_TEAM2TEAM    2    1    39
       INDEX RANGE SCAN    SMS422    PK_T_TEAM2TEAM    1    1    
     INDEX RANGE SCAN    SMS422    PK_T_TEAM2TEAM    1    1    
   SORT AGGREGATE                1    21
    TABLE ACCESS BY INDEX ROWID    SMS422    T_TEAM2TEAM    4    3    63
     INDEX RANGE SCAN    SMS422    PK_T_TEAM2TEAM    2    3    
   SORT AGGREGATE                1    21
    TABLE ACCESS BY INDEX ROWID    SMS422    T_TEAM2TEAM    4    3    63
     INDEX RANGE SCAN    SMS422    PK_T_TEAM2TEAM    2    3    
   SORT AGGREGATE                1    17
    FILTER                    
     INDEX RANGE SCAN    SMS422    PK_T_CHGRANK    2    1    17

将此部分逻辑摘出单独运行，结果正确，因为随着表数量的减少，执行计划也随着变化了。真是丧心病狂。。。。。。

问题分析就现这样。
以目前水平来看，造成这个执行计划的原因有多个，破坏掉其中一个就可以。即是去掉最无关业务的实现，max改为排序取第一行问题即解决。

AND t2t1.enddate = (SELECT   enddate from( select enddate FROM   t_team2team WHERE   signtid = t2t1.signtid order by enddate desc) where rownum=1)  
AND t2t.enddate = (SELECT enddate from( select enddate FROM t_team2team WHERE signtid = t2t.signtid order by enddate desc) where rownum=1)

执行计划也随之改变。

SELECT STATEMENT, GOAL = ALL_ROWS			482	1	102
 COUNT					
  FILTER					
   VIEW	SMS422		480	1	102
    SORT ORDER BY			480	1	253
     TABLE ACCESS BY INDEX ROWID	SMS422	T_NATUREGX	2	1	25
      NESTED LOOPS			139	1	253
       NESTED LOOPS			137	1	228
        NESTED LOOPS			135	1	199
         NESTED LOOPS			134	1	182
          NESTED LOOPS			133	1	178
           NESTED LOOPS			131	1	149
            NESTED LOOPS			121	5	575
             HASH JOIN			113	1	78
              TABLE ACCESS FULL	SMS422	T_TEAM2TEAM	56	78	3042
               COUNT STOPKEY					
                VIEW	SMS422		170	16846	151614
                 SORT ORDER BY STOPKEY			170	16846	353766
                  TABLE ACCESS FULL	SMS422	T_TEAM2TEAM	56	16846	353766
              TABLE ACCESS FULL	SMS422	T_TEAM2TEAM	56	82	3198
               COUNT STOPKEY					
                VIEW	SMS422		170	16846	151614
                 SORT ORDER BY STOPKEY			170	16846	353766
                  TABLE ACCESS FULL	SMS422	T_TEAM2TEAM	56	16846	353766
             TABLE ACCESS BY INDEX ROWID	SMS422	T_MEMBERSET	8	8	296
              INDEX RANGE SCAN	SMS422	PK_T_MEMBERSET	1	8	
            TABLE ACCESS BY INDEX ROWID	SMS422	T_PERSON	2	1	34
             INDEX RANGE SCAN	SMS422	PK_T_PERSON	1	1	
           TABLE ACCESS BY INDEX ROWID	SMS422	T_PSN2DEPT_GX	2	1	29
            INDEX RANGE SCAN	SMS422	PK_T_PSN2DEPT_GX	1	1	
          INDEX FULL SCAN	SMS422	PK_LA_CODEITEM	1	1	4
         INDEX RANGE SCAN	SMS422	PK_T_CHGRANK	1	1	17
        TABLE ACCESS BY INDEX ROWID	SMS422	T_TEAMNO	2	1	29
         INDEX RANGE SCAN	SMS422	PK_T_TEAMNO	1	1	
       INDEX RANGE SCAN	SMS422	PK_T_NATUREGX	1	1	
   SORT AGGREGATE				1	17
    FILTER					
     INDEX RANGE SCAN	SMS422	PK_T_CHGRANK	2	1	17

同时结果正确。

但至于为什么这个执行计划会得到这个结果……清楚又不太清楚，是同一个表两次不同条件的max共用了同一个排序结果集吗，看起来又不像是一直如此？oracle似乎做了优化，但group by ...having的例子说明子查询的确已经出现了错误。

深入这个问题，感觉必须了解oracle内部对笛卡尔积，排序，聚集的同时存在时的处理策略，不过看太深入的实现机制又头痛……_(:з」∠)_

librasama

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
难复现的复杂sql

这是一段多表联查，表自关联且嵌套子查询中还有此表聚集函数(max)的sql。查询结果后需要排序，之后进行分页。运行于oracle10。鉴于对oracle执行计划了解的尚不深入，所以不保证能够清楚的剖析问题的来龙去脉。只是记录下这个奇怪的问题……原sqlSELECT con1.*, ROWNUM rn FROM ( SELECT psn.did,
复制链接

扫一扫