通過查詢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')
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/284388.html
標籤：開發