Oracle复杂SQL查询优化问题咨询

[zheng8498]

通过查询Oracle视图获取到以下SQL查询时间耗时大概在 4S以上，请问是否有优化的空间和方法。
相关的表都有聚集索引和关联字段的索引

SELECT *  FROM ROBXDJ  ROBXDJ
       JOIN LSZGZD LSZGZD ON ROBXDJ.ROBXDJ_YGNM = LSZGZD.LSZGZD_ID 
       JOIN LSBZDW LSBZDW ON LSZGZD.LSZGZD_DWBH = LSBZDW.LSBZDW_DWBH
       LEFT OUTER JOIN CODEITEMS CODEITEMS           ON     LSBZDW.COUNTRYORAREA = CODEITEMS.NM              AND CODEITEMS.SETID = 'A019'
       LEFT OUTER JOIN HROrgDuty HRORGDUTY           ON LSZGZD.HRZGZD_XRZW = HRORGDUTY.NM
       LEFT OUTER JOIN HRORGRANK HRORGRANK           ON LSZGZD.LSZGZD_ZGZJ = HRORGRANK.NM
       JOIN LSBZDW LSBZDW1
           ON     ROBXDJ.ROBXDJ_DWBH = LSBZDW1.LSBZDW_DWBH
              AND (    (LSBZDW1.LSBZDW_GSLB <> '3')                   AND (   LSBZDW1.LSBZDW_GXDW <> '1'                        OR LSBZDW1.LSBZDW_GXDW IS NULL                        OR LSBZDW1.LSBZDW_GXDW IS NULL)                   AND (LSBZDW1.LSBZDW_GSXZ <> '3'))
       LEFT OUTER JOIN CODEITEMS CODEITEMS1
           ON     LSBZDW1.COUNTRYORAREA = CODEITEMS1.NM
              AND CODEITEMS1.SETID = 'A019'
       JOIN ROBXLX ROBXLX ON ROBXDJ.ROBXDJ_BXLX = ROBXLX.ROBXLX_NM
       JOIN LSBMZD LSBMZD
           ON     ROBXDJ.ROBXDJ_DWBH = LSBMZD.LSBMZD_DWBH AND ROBXDJ.ROBXDJ_BMBH = LSBMZD.LSBMZD_BMBH
       LEFT OUTER JOIN LSBMSX LSBMSX ON LSBMZD.LSBMZD_SX = LSBMSX.LSBMSX_SXBH
       LEFT OUTER JOIN GSPUser GSPUSER ON LSBMZD.LSBMZD_BMFZR = GSPUSER.ID
       JOIN LSWBZD LSWBZD ON ROBXDJ.ROBXDJ_BXHB = LSWBZD.LSWBZD_BZBH
       LEFT OUTER JOIN ZWJSFS ZWJSFS ON ROBXDJ.ROBXDJ_YQFS = ZWJSFS.ZWJSFS_BH
       LEFT OUTER JOIN ROFYXM ROFYXM ON ROBXDJ.ROBXDJ_FYXM = ROFYXM.ROFYXM_NM
       LEFT OUTER JOIN ROZFFS ZFFS ON ROBXDJ.ROBXDJ_FKFS = ZFFS.ROZFFS_NM
       LEFT OUTER JOIN ZWJSFS ZWJSFS1 ON ZFFS.ROZFFS_JSFS = ZWJSFS1.ZWJSFS_BH
       JOIN ROYWZT ROYWZT          ON ROBXDJ.ROBXDJ_ZT = ROYWZT.ROYWZT_ZT AND ROYWZT_YWLX = 'BX'
       LEFT OUTER JOIN GSPUser GSPUSER1 ON ROBXDJ.ROBXDJ_USERID = GSPUSER1.ID
       LEFT OUTER JOIN PROCESSINSTANCE PROCESSINSTANCE
           ON ROBXDJ.ROBXDJ_LCSL = PROCESSINSTANCE.PROCESSINSTANCEID
       LEFT OUTER JOIN LSBZDW LSBZDW2
           ON ROBXDJ.ROBXDJ_JZZZ = LSBZDW2.LSBZDW_DWBH
       LEFT OUTER JOIN ROHBYG ROHBYG
           ON ROBXDJ.ROBXDJ_HBYGNM = ROHBYG.ROHBYG_NM
       LEFT OUTER JOIN ROHBGX ROHBGX ON ROHBYG.ROHBYG_GXNM = ROHBGX.ROHBGX_NM
       LEFT OUTER JOIN LSZGZD LSZGZD1
           ON ROHBYG.ROHBYG_YGNM = LSZGZD1.LSZGZD_ID
       LEFT OUTER JOIN FSYWLX FSYWLX ON ROBXDJ.ROBXDJ_YWLX = FSYWLX.FSYWLX_NM
       LEFT OUTER JOIN GSPSECLEVEL GSPSECLEVEL
           ON ROBXDJ.ROBXDJ_MJ = GSPSECLEVEL.ID
       LEFT OUTER JOIN LSBMZD LSBMZD1
           ON ROBXDJ.ROBXDJ_JZBMID = LSBMZD1.LSBMZD_ID
       LEFT OUTER JOIN LSBMSX LSBMSX1
           ON LSBMZD1.LSBMZD_SX = LSBMSX1.LSBMSX_SXBH
       LEFT OUTER JOIN GSPUser GSPUSER2 ON LSBMZD1.LSBMZD_BMFZR = GSPUSER2.ID
       LEFT OUTER JOIN ROBXDD ROBXDD
           ON ROBXDJ.ROBXDJ_BXDDID = ROBXDD.ROBXDD_NM
       LEFT OUTER JOIN VW_BZZXGCXM VW_BZZXGCXM
           ON ROBXDJ.ROBXDJ_GXM1 = VW_BZZXGCXM.ZJGCXM_NM
       LEFT OUTER JOIN VW_BZZXHTB VW_BZZXHTB
           ON ROBXDJ.ROBXDJ_GXM2 = VW_BZZXHTB.ZJXMHTB_NM
       LEFT OUTER JOIN CODEITEMS CODEITEMS2
           ON     ROBXDJ.ROBXDJ_GXM4 = CODEITEMS2.CODE
              AND CODEITEMS2.SETID = 'M005'
       LEFT OUTER JOIN PMMDCYBK PMMDCYBK
           ON ROBXDJ.ROBXDJ_GNM5 = PMMDCYBK.CYBKID
       LEFT OUTER JOIN ROFYXM ROFYXM1
           ON ROBXDJ.ROBXDJ_XM1 = ROFYXM1.ROFYXM_NM
       LEFT OUTER JOIN PMMDGCZT PMMDGCZT
           ON ROBXDJ.ROBXDJ_XM2 = PMMDGCZT.GCZTID
       LEFT OUTER JOIN LSZGZD LSZGZD2
           ON ROBXDJ.ROBXDJ_XM5 = LSZGZD2.LSZGZD_ID
       LEFT OUTER JOIN ROFYXM ROFYXM2
           ON ROBXDJ.ROBXDJ_XM7 = ROFYXM2.ROFYXM_NM
       LEFT OUTER JOIN LSZGZD LSZGZD3
           ON ROBXDJ.ROBXDJ_XM8 = LSZGZD3.LSZGZD_ID
       LEFT OUTER JOIN CODEITEMS CODEITEMS3
           ON     ROBXDJ.ROBXDJ_XM9 = CODEITEMS3.CODE
              AND CODEITEMS3.SETID = 'M003'
       LEFT OUTER JOIN GSPUser GSPUSER3 ON ROBXDJ.ROBXDJ_XM10 = GSPUSER3.ID
       LEFT OUTER JOIN LSBMZD LSBMZD2
           ON ROBXDJ.ROBXDJ_XM11 = LSBMZD2.LSBMZD_ID
       LEFT OUTER JOIN CODEITEMS CODEITEMS4
           ON     ROBXDJ.ROBXDJ_XM13 = CODEITEMS4.CODE
              AND CODEITEMS4.SETID = 'M017'
       LEFT OUTER JOIN LSHSXM2021 LSHSXM
           ON ROBXDJ.ROBXDJ_XM14 = LSHSXM.LSHSXM_ID
       LEFT OUTER JOIN ROFYXM ROFYXM3
           ON ROBXDJ.ROBXDJ_XM15 = ROFYXM3.ROFYXM_NM
       LEFT OUTER JOIN ROFYXM ROFYXM4
           ON ROBXDJ.ROBXDJ_NM2 = ROFYXM4.ROFYXM_NM
       LEFT OUTER JOIN LSBMZD LSBMZD3
           ON ROBXDJ.ROBXDJ_NM3 = LSBMZD3.LSBMZD_ID
       LEFT OUTER JOIN VW_EINVOICE_BZD DO_BZZX_DZDZK
           ON ROBXDJ.ROBXDJ_NM20 = DO_BZZX_DZDZK.ID
       LEFT OUTER JOIN ROFYXM ROFYXM5
           ON ROBXDJ.ROBXDJ_NM4 = ROFYXM5.ROFYXM_NM
 WHERE ROBXDJ.ROBXDJ_NM = '5c4970a9-6f78-480d-88b6-7eba7d8e5baf'

上述SQL涉及到的表的大小如下截图所示


执行计划的部分内容如下：
Predicate Information (identified by operation id):
---------------------------------------------------

50 - access("ROBXDJ"."ROBXDJ_NM"='5c4970a9-6f78-480d-88b6-7eba7d8e5baf')
52 - access("ROBXDJ"."ROBXDJ_NM4"="ROFYXM5"."ROFYXM_NM"(+))
54 - access("ROBXDJ"."ROBXDJ_NM3"="LSBMZD3"."LSBMZD_ID"(+))
56 - access("ROBXDJ"."ROBXDJ_NM2"="ROFYXM4"."ROFYXM_NM"(+))
58 - access("ROBXDJ"."ROBXDJ_XM15"="ROFYXM3"."ROFYXM_NM"(+))
60 - access("CODEITEMS4"."SETID"(+)='M017' AND "ROBXDJ"."ROBXDJ_XM13"="CODEITEMS4"."CODE"(+))
62 - access("ROBXDJ"."ROBXDJ_XM11"="LSBMZD2"."LSBMZD_ID"(+))
64 - access("ROBXDJ"."ROBXDJ_XM10"="GSPUSER3"."ID"(+))
66 - access("CODEITEMS3"."SETID"(+)='M003' AND "ROBXDJ"."ROBXDJ_XM9"="CODEITEMS3"."CODE"(+))
68 - access("ROBXDJ"."ROBXDJ_XM8"="LSZGZD3"."LSZGZD_ID"(+))
70 - access("ROBXDJ"."ROBXDJ_XM7"="ROFYXM2"."ROFYXM_NM"(+))
72 - access("ROBXDJ"."ROBXDJ_XM5"="LSZGZD2"."LSZGZD_ID"(+))
74 - access("ROBXDJ"."ROBXDJ_XM2"="PMMDGCZT"."GCZTID"(+))
76 - access("ROBXDJ"."ROBXDJ_XM1"="ROFYXM1"."ROFYXM_NM"(+))
78 - access("ROBXDJ"."ROBXDJ_GNM5"="PMMDCYBK"."CYBKID"(+))
80 - access("CODEITEMS2"."SETID"(+)='M005' AND "ROBXDJ"."ROBXDJ_GXM4"="CODEITEMS2"."CODE"(+))
82 - access("ROBXDJ"."ROBXDJ_BXDDID"="ROBXDD"."ROBXDD_NM"(+))
84 - access("ROBXDJ"."ROBXDJ_JZBMID"="LSBMZD1"."LSBMZD_ID"(+))
86 - access("ROBXDJ"."ROBXDJ_MJ"="GSPSECLEVEL"."ID"(+))
88 - access("ROBXDJ"."ROBXDJ_YWLX"="FSYWLX"."FSYWLX_NM"(+))
90 - access("ROBXDJ"."ROBXDJ_HBYGNM"="ROHBYG"."ROHBYG_NM"(+))
92 - access("ROBXDJ"."ROBXDJ_JZZZ"="LSBZDW2"."LSBZDW_DWBH"(+))
94 - access("ROBXDJ"."ROBXDJ_LCSL"="PROCESSINSTANCE"."PROCESSINSTANCEID"(+))
96 - access("ROBXDJ"."ROBXDJ_USERID"="GSPUSER1"."ID"(+))
98 - access("ROYWZT_YWLX"='BX' AND "ROBXDJ"."ROBXDJ_ZT"="ROYWZT"."ROYWZT_ZT")
100 - access("ROBXDJ"."ROBXDJ_FKFS"="ZFFS"."ROZFFS_NM"(+))
102 - access("ROBXDJ"."ROBXDJ_FYXM"="ROFYXM"."ROFYXM_NM"(+))
104 - access("ROBXDJ"."ROBXDJ_YQFS"="ZWJSFS"."ZWJSFS_BH"(+))
106 - access("ROBXDJ"."ROBXDJ_BXHB"="LSWBZD"."LSWBZD_BZBH")
108 - access("ROBXDJ"."ROBXDJ_BXLX"="ROBXLX"."ROBXLX_NM")
109 - filter("LSBZDW1"."LSBZDW_GSXZ"<>'3' AND "LSBZDW1"."LSBZDW_GSLB"<>'3' AND ("LSBZDW1"."LSBZDW_GXDW" IS NULL OR
"LSBZDW1"."LSBZDW_GXDW"<>'1'))
110 - access("ROBXDJ"."ROBXDJ_DWBH"="LSBZDW1"."LSBZDW_DWBH")
112 - access("ROBXDJ"."ROBXDJ_YGNM"="LSZGZD"."LSZGZD_ID")
114 - access("LSBMZD1"."LSBMZD_BMFZR"="GSPUSER2"."ID"(+))
116 - access("LSBMZD1"."LSBMZD_SX"="LSBMSX1"."LSBMSX_SXBH"(+))
118 - access("ROHBYG"."ROHBYG_YGNM"="LSZGZD1"."LSZGZD_ID"(+))
120 - access("ROHBYG"."ROHBYG_GXNM"="ROHBGX"."ROHBGX_NM"(+))
122 - access("ZFFS"."ROZFFS_JSFS"="ZWJSFS1"."ZWJSFS_BH"(+))
124 - access("LSZGZD"."LSZGZD_ZGZJ"="HRORGRANK"."NM"(+))
126 - access("LSZGZD"."HRZGZD_XRZW"="HRORGDUTY"."NM"(+))
128 - access("LSZGZD"."LSZGZD_DWBH"="LSBZDW"."LSBZDW_DWBH")
129 - filter("ROBXDJ"."ROBXDJ_BMBH"="LSBMZD"."LSBMZD_BMBH" AND "ROBXDJ"."ROBXDJ_DWBH"="LSBMZD"."LSBMZD_DWBH")
132 - filter("ISCERTIFICATION"='2' AND "INVOICESTATE"='0')
133 - access("A"."ID"="ROBXDJ"."ROBXDJ_NM20")
134 - filter("ISNULL"("SRCSYS",' ')<>'YDSM')
135 - access("C"."PARENTID"="ROBXDJ"."ROBXDJ_NM20")
filter("A"."ID"="C"."PARENTID")
141 - filter("A"."PMCTQTHT_SPZT"='2' OR "A"."PMCTQTHT_SPZT"='5')
142 - access("A"."PMCTQTHT_ID"="ROBXDJ"."ROBXDJ_GXM2")
144 - access("ZJDWJGDYB_DWNM"(+)="A"."PMCTQTHT_DWBH")
145 - access("LSWLDW_WLDWBH"(+)="A"."PMCTQTHT_YFBH")
147 - access("CODEITEMSM003"."SETID"(+)='M003' AND "CODEITEMSM003"."CODE"(+)="A"."STRING17")
149 - access("CODEITEMSM004"."SETID"(+)='M004' AND "CODEITEMSM004"."CODE"(+)="A"."STRING18")
162 - filter("XMZD"."XMXX_SPZT"='2' OR "XMZD"."XMXX_SPZT"='5')
163 - access("XMZD"."XMXX_XMID"="ROBXDJ"."ROBXDJ_GXM1")
165 - access("LSCSZD"."LSCSZD_NM"(+)="XMZD"."STRING13")
167 - access("XMFZR"."ID"(+)="XMZD"."STRING09")
169 - access("LSBMZD"."LSBMZD_ID"(+)="XMZD"."STRING07")
171 - access("LSWLDW"."LSWLDW_WLDWBH"(+)="XMZD"."XMXX_SWXMMC")
173 - access("CODEITEMSM003"."SETID"(+)='M003' AND "CODEITEMSM003"."CODE"(+)="XMZD"."STRING01")
175 - access("CODEITEMSM006"."SETID"(+)='M006' AND "CODEITEMSM006"."CODE"(+)="XMZD"."STRING11")
177 - access("CODEITEMSM005"."SETID"(+)='M005' AND "CODEITEMSM005"."CODE"(+)="XMZD"."STRING12")
178 - access("XMZD"."XMXX_JSDWID"="LSWLDW_WLDWBH"(+))
180 - access("XMZD"."XMXX_CYBK"="XMBK"."CYBKID"(+))
181 - filter("XMZD"."XMXX_GCZT"="XMJD"."GCZTMC"(+))
182 - filter("DQZD"."SETID"(+)='A018')
183 - access("DQZD"."NAME"(+)="XMZD"."XMXX_XZQY")
185 - access("LSBMZD"."LSBMZD_SX"="LSBMSX"."LSBMSX_SXBH"(+))
187 - access("LSBMZD"."LSBMZD_BMFZR"="GSPUSER"."ID"(+))
189 - access("ROBXDJ"."ROBXDJ_XM14"="LSHSXM"."LSHSXM_ID"(+))
190 - filter("LSBZDW1"."COUNTRYORAREA"="CODEITEMS1"."NM"(+))
191 - access("CODEITEMS1"."SETID"(+)='A019')
192 - filter("LSBZDW"."COUNTRYORAREA"="CODEITEMS"."NM"(+))
193 - access("CODEITEMS"."SETID"(+)='A019')

[js14982]

语句较为简单，只是涉及到的表较多，而且貌似没什么重复表，语句可优化空间不大。 执行计划上没有执行所用时长，看不出什么。 你这个语句顶多也就看看是否要加几个索引提提高查询速度了。

[zheng8498]

链接: https://pan.baidu.com/s/1IJyIGcbkeyZdZbqOo8kbOA 提取码: vmkg 补充执行计划，因为太大了无法放到帖子里面。

[zheng8498]

最终通过AWR报告确定问题原因是 硬解析 的原因。