1、sql语句–执行34s

SELECT S_.* 
  FROM ( SELECT 
                T.*
           FROM (SELECT T."NSNAME1" AS "NSNAME1", 
                        T."MODELVERSION" AS "MODELVERSION", 
                        T."CONTACTSPROPERTY" AS "CONTACTSPROPERTY", 
                        ………… t 表 140列…………
                        T."FJID" AS "FJID" 
                   FROM "表T" T ) T
          WHERE ( ( "STATUS" = '4'
                               OR "STATUS" = '1' )
                     AND( "KHGYSZT" = '1' )
                     AND( "条件A" = 'DONE' )
                     AND( "DATASTATUS" <> '10' ) )
            AND ( 1 = 1 
                )
            AND NOT EXISTS (SELECT 1
                   FROM 表M F
                  WHERE F.DATAID = T.ID
                    AND (F.条件A LIKE 'DRAFT%' 
                             OR F.条件A LIKE 'APPROVE%'))
       ORDER BY T.ID DESC LIMIT 0, 
                50 ) S_

2、 执行计划

1   #NSET2: [14917, 50, 5699] 
2     #PIPE2: [14917, 50, 5699] 
3       #PRJT2: [11291, 50, 5699]; exp_num(145), is_atom(FALSE) 
4         #PRJT2: [11291, 50, 5699]; exp_num(145), is_atom(FALSE) 
5           #SORT3: [11291, 50, 5699]; key_num(1), partition_key_num(0), is_distinct(FALSE), top_flag(1), is_adaptive(0)
6             #HASH RIGHT SEMI JOIN2: [4383, 44784, 5699]; n_keys(1) (ANTI), KEY(colname=T.ROWID) KEY_NULL_EQU(1)
7               #PRJT2: [2922, 4248, 5767]; exp_num(1), is_atom(FALSE) 
8                 #UNION FOR OR2: [2922, 4248, 5767]; key_num(0), outer_join(-)
9                   #HASH2 INNER JOIN: [1459, 4046, 5767];  KEY_NUM(1); KEY(F.DATAID=T.ID) KEY_NULL_EQU(0)
10                    #BLKUP2: [4, 4050, 68]; IDX_表M_条件A(F)
11                      #SSEK2: [4, 4050, 68]; scan_type(ASC), IDX_表M_条件A(表M as F), scan_range['DRAFT','DRAFU'), is_global(0)
12                    #HEAP TABLE SCAN: [1416, 48831, 5699]; table_no(0) 
13                  #HASH2 INNER JOIN: [1455, 202, 5767];  KEY_NUM(1); KEY(F.DATAID=T.ID) KEY_NULL_EQU(0)
14                    #BLKUP2: [1, 202, 68]; IDX_表M_条件A(F)
15                      #SLCT2: [1, 202, 68]; exp11
16                        #SSEK2: [1, 202, 68]; scan_type(ASC), IDX_表M_条件A(表M as F), scan_range['APPROVE','APPROVF'), is_global(0)
17                    #HEAP TABLE SCAN: [1416, 48831, 5699]; table_no(0) 
18              #HEAP TABLE SCAN: [1416, 48831, 5699]; table_no(0) 
19      #HEAP TABLE: [3625, 48831, 5699]; table_no(0) full(0), mpp_full(0) autoid(0), sites(-)
20        #PRJT2: [3625, 48831, 5699]; exp_num(145), is_atom(FALSE) 
21          #HASH RIGHT SEMI JOIN2: [3625, 48831, 5699]; n_keys(1) KEY(DMTEMPVIEW_889751938.colname=T.STATUS) KEY_NULL_EQU(0)
22            #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1)
23            #SLCT2: [3586, 49230, 5699]; (T.KHGYSZT = '1' AND T.条件A = 'DONE' AND T.DATASTATUS <> '10') SLCT_PUSHDOWN(TRUE)
24              #CSCN2: [3586, 1969435, 5699]; INDEX33555915(表T as T) NEED_SLCT(TRUE); btr_scan(1)

3、优化思路

3.1、是否可以创建索引

#CSCN2: [3586, 1969435, 5699]; INDEX33555915(表T as T) NEED_SLCT(TRUE); btr_scan(1)

看到全表扫，尝试创建索引。查询发现where 的条件只从196w降到194w，这要是走回表消耗会更大。

select count(*) from 表T t --1969446行
select count(*) --1948228行
  from 表T t 
 WHERE ( ( "STATUS" = '4'
                  OR "STATUS" = '1' )
          AND( "KHGYSZT" = '1' )
          AND( "条件A" = 'DONE' )
          AND( "DATASTATUS" <> '10' ) )
select count(*) --1947839行
  from 表T t 
 WHERE ( ( "STATUS" = '4'
                  OR "STATUS" = '1' )
          AND( "KHGYSZT" = '1' )
          AND( "条件A" = 'DONE' )
          AND( "DATASTATUS" <> '10' ) )
   AND NOT EXISTS (SELECT 1
          FROM 表M F
         WHERE F.DATAID = T.ID
           AND (F.条件A LIKE 'DRAFT%'
                    OR F.条件A LIKE 'APPROVE%'))

3.2 参数 添加hint：ENABLE_RQ_TO_INV(1)

执行计划

1   #NSET2: [3, 50, 5699] 
2     #PRJT2: [3, 50, 5699]; exp_num(145), is_atom(FALSE) 
3       #PRJT2: [3, 50, 5699]; exp_num(145), is_atom(FALSE) 
4         #TOPN2: [3, 50, 5699]; top_num(50), top_off(0)
5           #PRJT2: [3, 300, 5699]; exp_num(145), is_atom(FALSE) 
6             #HASH RIGHT SEMI JOIN2: [3, 300, 5699]; n_keys(1) KEY(DMTEMPVIEW_889752629.colname=T.STATUS) KEY_NULL_EQU(0)
7               #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1)
8               #SLCT2: [3, 300, 5699]; (T.KHGYSZT = '1' AND T.条件A = 'DONE' AND T.DATASTATUS <> '10' AND exp11 = 1)
9                 #BLKUP2: [3, 300, 5699]; IDX_表T_ID(T)
10                  #SSCN: [3, 300, 5699]; IDX_表T_ID(表T as T); btr_scan(1); is_global(0)

METHOD: PHAF_9_RN_84446257
1   #SELECT INTO2: [0, 0, 0] 
2     #PIPE2: [4, 1, 1] 
3       #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 
4         #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL(SYSDUAL); btr_scan(1)
5       #SPL2: [4, 106, 68]; key_num(1), spool_num(0), is_atom(FALSE), has_var(0), sites(-)
6         #PRJT2: [4, 106, 68]; exp_num(1), is_atom(FALSE) 
7           #UNION FOR OR2: [4, 106, 68]; key_num(0), outer_join(-)
8             #SLCT2: [2, 101, 68]; (F.条件A >= 'DRAFT' AND F.条件A < 'DRAFU')
9               #BLKUP2: [2, 2700, 68]; IDX_表M_DATAID(F)
10                #SSEK2: [2, 2700, 68]; scan_type(ASC), IDX_表M_DATAID(表M as F), scan_range[exp45,exp45], is_global(0)
11            #SLCT2: [1, 5, 68]; F.DATAID = exp45
12              #BLKUP2: [1, 202, 68]; IDX_表M_条件A(F)
13                #SLCT2: [1, 202, 68]; exp11
14                  #SSEK2: [1, 202, 68]; scan_type(ASC), IDX_表M_条件A(表M as F), scan_range['APPROVE','APPROVF'), is_global(0)

结果分析

为什么优化后执行时间0.029s？
通过对比执行计划发现，优化后的执行计划变成了2段。它针对NOT EXISTS 子查询部分当成了整体EXP11函数处理。先将 NOT EXISTS 表M 表查询出来，跟表 表T T.ID 关联，sql也是根据id排序 ，就可以使用 IDX_表T_ID 索引。他取出300行数据 和其他的 where 条件匹配，最后再取top50。将190w的全表扫变成了300行回表。