达梦数据库判断表或视图是否存在高效写法

最近有项目碰到，判断当前模式下表或视图是否存在的SQL并发存在性能问题，写法如下：

select 1 from (select object_name table_name,created create_time from 
               user_objects where object_type='TABLE' or object_type='VIEW') 
         WHERE  TABLE_NAME='XXX';

  执行计划如下： 

1   #NSET2: [82, 14, 565] 
2     #PIPE2: [82, 14, 565] 
3       #PIPE2: [61, 14, 565] 
4         #PIPE2: [57, 14, 565] 
5           #PIPE2: [57, 14, 565] 
6             #PRJT2: [30, 14, 565]; exp_num(1), is_atom(FALSE) 
7               #PRJT2: [30, 14, 565]; exp_num(0), is_atom(FALSE) 
8                 #PRJT2: [30, 14, 565]; exp_num(0), is_atom(FALSE) 
9                   #UNION ALL: [30, 14, 565]
10                    #PRJT2: [26, 13, 565]; exp_num(0), is_atom(FALSE) 
11                      #HASH LEFT SEMI JOIN2: [26, 13, 565]; KEY_NUM(1);  KEY(O.SCHID=DMTEMPVIEW_889193638.colname) KEY_NULL_EQU(0)
12                        #HASH RIGHT SEMI JOIN2: [24, 25, 565]; n_keys(1) KEY(DMTEMPVIEW_889193664.colname=exp_bool_case) KEY_NULL_EQU(0)
13                          #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 
14                          #HASH LEFT JOIN2: [24, 512, 565]; key_num(1), partition_keys_num(0), ret_null(0), mix(0)join condition(O.SUBTYPE$ = 'PKG') KEY(O.ID=PKG.ID)
15                            #HASH RIGHT JOIN2: [23, 512, 545]; key_num(1), ret_null(0), KEY(INFO.ID=O.ID)
16                              #HEAP TABLE SCAN: [1, 5, 148]; table_no(1) 
17                              #SLCT2: [21, 512, 397]; O.NAME = 'BAK_DMINI_230113'
18                                #HEAP TABLE SCAN: [20, 20493, 397]; table_no(0) 
19                            #PRJT2: [1, 679, 20]; exp_num(2), is_atom(FALSE) 
20                              #CSCN2: [1, 679, 20]; SYSINDEXSYSTEXTS(SYSTEXTS as TEXT)
21                        #HEAP TABLE SCAN: [1, 13, 56]; table_no(2) 
22                    #PRJT2: [3, 1, 212]; exp_num(0), is_atom(FALSE) 
23                      #INDEX JOIN SEMI JOIN2: [3, 1, 212];  join condition((SYSOBJECTS.TYPE$ = 'SCH' AND SYSOBJECTS.PID = exp11))
24                        #NEST LOOP LEFT JOIN2: [3, 1, 212]; join condition(INFO.ID = O_PART.ID)[with var] partition_keys_num(0) ret_null(0)
25                          #INDEX JOIN LEFT JOIN2: [1, 1, 112]  ret_null(0)
26                            #SLCT2: [1, 1, 112]; O_ROOT.NAME = 'BAK_DMINI_230113'
27                              #NEST LOOP INDEX JOIN2: [1, 1, 112] 
28                                #PRJT2: [1, 1, 56]; exp_num(2), is_atom(FALSE) 
29                                  #HASH RIGHT SEMI JOIN2: [1, 1, 56]; n_keys(1) KEY(DMTEMPVIEW_889193665.colname=exp_bool_case) KEY_NULL_EQU(0)
30                                    #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 
31                                    #SLCT2: [1, 1, 56]; DMTEMPVIEW_889193644.TMPCOL3 > 1
32                                      #PRJT2: [1, 1, 56]; exp_num(3), is_atom(FALSE) 
33                                        #HIERARCHICAL QUERY: [1, 1, 56]; KEY_NUM(0);
34                                          #INDEX JOIN SEMI JOIN2: [1, 1, 56];  (ANTI), 
35                                            #CSCN2: [1, 1, 56]; SYSINDEXSYSHPARTTABLEINFO(SYSHPARTTABLEINFO)
36                                            #SSEK2: [1, 1, 4]; scan_type(ASC), SYSINDEXPARTTIDSYSHPARTTABLEINFO(SYSHPARTTABLEINFO), scan_range[SYSHPARTTABLEINFO.BASE_TABLE_ID,SYSHPARTTABLEINFO.BASE_TABLE_ID]
37                                          #CSEK2: [1, 1, 56]; scan_type(ASC), SYSINDEXSYSHPARTTABLEINFO(SYSHPARTTABLEINFO), scan_range[(var12,min),(var12,max))
38                                #SSEK2: [1, 1, 4]; scan_type(ASC), SYSINDEXIDSYSOBJECTS(SYSOBJECTS as O_ROOT), scan_range[TABPART.ROOT_TABLE_ID,TABPART.ROOT_TABLE_ID]
39                            #SSEK2: [1, 1, 4]; scan_type(ASC), SYSINDEXIDSYSOBJECTS(SYSOBJECTS as O_PART), scan_range[TABPART.PART_TABLE_ID,TABPART.PART_TABLE_ID]
40                          #PRJT2: [1, 1, 100]; exp_num(1), is_atom(FALSE) 
41                            #SAGR2: [1, 1, 100]; grp_num(1), sfun_num(0); slave_empty(0) keys(DMTEMPVIEW_889193645.TMPCOL0) 
42                              #PRJT2: [1, 13, 100]; exp_num(1), is_atom(FALSE) 
43                                #NEST LOOP INDEX JOIN2: [1, 13, 100] 
44                                  #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 
45                                  #CSEK2: [1, 6, 100]; scan_type(ASC), SYSINDEXSYSOBJINFOS(SYSOBJINFOS), scan_range[(var15,DMTEMPVIEW_889193653.colname,min),(var15,DMTEMPVIEW_889193653.colname,max))
46                        #BLKUP2: [1, 1, 52]; SYSINDEXIDSYSOBJECTS(SYSOBJECTS)
47                          #SSEK2: [1, 1, 52]; scan_type(ASC), SYSINDEXIDSYSOBJECTS(SYSOBJECTS), scan_range[O_ROOT.SCHID,O_ROOT.SCHID]
48            #SPL2: [26, 13, 573]; key_num(4), spool_num(0), is_atom(FALSE), has_var(0), sites(-)
49              #PRJT2: [26, 13, 573]; exp_num(4), is_atom(FALSE) 
50                #SLCT2: [26, 13, 573]; O.NAME = 'BAK_DMINI_230113'
51                  #NEST LOOP INDEX JOIN2: [26, 13, 573] 
52                    #HASH LEFT SEMI JOIN2: [26, 13, 565]; KEY_NUM(1);  KEY(O.SCHID=DMTEMPVIEW_889193638.colname) KEY_NULL_EQU(0)
53                      #HASH RIGHT SEMI JOIN2: [24, 25, 565]; n_keys(1) KEY(DMTEMPVIEW_889193666.colname=exp_bool_case) KEY_NULL_EQU(0)
54                        #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 
55                        #HASH LEFT JOIN2: [24, 512, 565]; key_num(1), partition_keys_num(0), ret_null(0), mix(0)join condition(O.SUBTYPE$ = 'PKG') KEY(O.ID=PKG.ID)
56                          #HASH RIGHT JOIN2: [23, 512, 545]; key_num(1), ret_null(0), KEY(INFO.ID=O.ID)
57                            #HEAP TABLE SCAN: [1, 5, 148]; table_no(1) 
58                            #SLCT2: [21, 512, 397]; O.NAME = 'BAK_DMINI_230113'
59                              #HEAP TABLE SCAN: [20, 20493, 397]; table_no(0) 
60                          #PRJT2: [1, 679, 20]; exp_num(3), is_atom(FALSE) 
61                            #CSCN2: [1, 679, 20]; SYSINDEXSYSTEXTS(SYSTEXTS as TEXT)
62                      #HEAP TABLE SCAN: [1, 13, 56]; table_no(2) 
63                    #CSEK2: [1, 1, 4]; scan_type(UNIQUE), SYSINDEXINDEXES(SYSINDEXES), scan_range[O.ID,O.ID]
64          #HEAP TABLE: [1, 13, 56]; table_no(2) full(0), mpp_full(0) autoid(0), sites(-)
65            #PRJT2: [1, 13, 56]; exp_num(1), is_atom(FALSE) 
66              #SLCT2: [1, 13, 56]; SYSOBJECTS.TYPE$ = 'SCH'
67                #SSEK2: [1, 538, 56]; scan_type(ASC), SYSINDEXPIDIDSYSOBJECTS(SYSOBJECTS), scan_range[(exp11,min),(exp11,max))
68        #HEAP TABLE: [4, 5, 148]; table_no(1) full(0), mpp_full(0) autoid(1), sites(-)
69          #PRJT2: [4, 5, 148]; exp_num(1), is_atom(FALSE) 
70            #SAGR2: [4, 5, 148]; grp_num(1), sfun_num(0); slave_empty(0) keys(DMTEMPVIEW_889193637.TMPCOL0) 
71              #PRJT2: [3, 544, 148]; exp_num(1), is_atom(FALSE) 
72                #HASH2 INNER JOIN: [3, 544, 148];  KEY_NUM(1); KEY(DMTEMPVIEW_889193648.colname=SYSOBJINFOS.TYPE$) KEY_NULL_EQU(0)
73                  #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 
74                  #CSCN2: [1, 10884, 100]; SYSINDEXSYSOBJINFOS(SYSOBJINFOS)
75      #HEAP TABLE: [20, 20493, 397]; table_no(0) full(0), mpp_full(0) autoid(1), sites(-)
76        #PRJT2: [20, 20493, 397]; exp_num(6), is_atom(FALSE) 
77          #UNION ALL: [20, 20493, 397]
78            #PRJT2: [10, 20480, 397]; exp_num(17), is_atom(FALSE) 
79              #UNION ALL: [10, 20480, 397]
80                #PRJT2: [4, 20479, 397]; exp_num(17), is_atom(FALSE) 
81                  #HASH RIGHT SEMI JOIN2: [4, 20479, 397]; n_keys(1) (ANTI), KEY(DMTEMPVIEW_889193667.colname=SYSOBJECTS.SUBTYPE$) KEY_NULL_EQU(0)
82                    #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 
83                    #CSCN2: [4, 21557, 397]; SYSINDEXSYSOBJECTS(SYSOBJECTS)
84                #PRJT2: [1, 1, 397]; exp_num(17), is_atom(FALSE) 
85                  #SLCT2: [1, 1, 397]; (SYSOBJECTS.SUBTYPE$ = 'UTAB' AND SYSOBJECTS.INFO3&var22 = var23)
86                    #BLKUP2: [1, 538, 397]; SYSINDEXPIDIDSYSOBJECTS(SYSOBJECTS)
87                      #SSEK2: [1, 538, 397]; scan_type(ASC), SYSINDEXPIDIDSYSOBJECTS(SYSOBJECTS), scan_range[(-1,min),(-1,max))
88            #PRJT2: [4, 13, 397]; exp_num(17), is_atom(FALSE) 
89              #SLCT2: [4, 13, 397]; (SYSOBJECTS.SUBTYPE$ = 'TRIG' AND SYSOBJECTS.INFO1&var24 = 0)
90                #CSCN2: [4, 21557, 397]; SYSINDEXSYSOBJECTS(SYSOBJECTS)

从执行计划可以看出，只是判断表或视图是否存在，通过动态视图查询的SQL执行计划过于复杂，这是因为user_objects这个动态视图过于复杂，这样查单条SQL性能没问题，但是如果并发较多或系统中对象过多的时候就会出现性能问题。

那么是否有性能更好的方法呢？答案是肯定的，可以查询达梦提供的系统表sysobjects来进行查询，写法如下：

select 1 from sysobjects 
 where subtype$ IN ('UTAB','VIEW') AND NAME='BAK_DMINI_230113'
   AND SCHID= (SELECT ID FROM sysobjects WHERE NAME=USER() AND TYPE$='SCH');

 从执行计划可以看出，这个语句性能会好很多

1   #NSET2: [1, 1, 200] 
2     #PRJT2: [1, 1, 200]; exp_num(1), is_atom(FALSE) 
3       #SLCT2: [1, 1, 200]; DMTEMPVIEW_889193685.colname = SYSOBJECTS.SCHID
4         #NEST LOOP INNER JOIN2: [1, 1, 200]; 
5           #PRJT2: [1, 1, 100]; exp_num(1), is_atom(TRUE) 
6             #SLCT2: [1, 13, 100]; SYSOBJECTS.NAME = var2
7               #CSEK2: [1, 538, 100]; scan_type(ASC), SYSINDEXSYSOBJECTS(SYSOBJECTS as SYSOBJECTS), scan_range[('SCH',min,min),('SCH',max,max))
8           #HASH RIGHT SEMI JOIN2: [1, 26, 100]; n_keys(1) KEY(DMTEMPVIEW_889193688.colname=SYSOBJECTS.SUBTYPE$) KEY_NULL_EQU(0)
9             #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 
10            #BLKUP2: [1, 538, 100]; SYSINDEXNAMESYSOBJECTS(SYSOBJECTS)
11              #SSEK2: [1, 538, 100]; scan_type(ASC), SYSINDEXNAMESYSOBJECTS(SYSOBJECTS as SYSOBJECTS), scan_range['BAK_DMINI_230113','BAK_DMINI_230113']