oracle sql效率

ryoqjall 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(321)

我有一个查询，得到了将近一千条记录。问题是数据的返回非常慢，大约一两分钟。
这是我的问题

SELECT POLNO, COLID, FORCEWRITE, INSURETAG, TO_CHAR(TO_DATE(TO_CHAR(STARTDT,'MM/DD/YYYY'),'MM/DD/YYYY'),'MM/DD/YYYY') AS STARTDT, TO_CHAR(TO_DATE(TO_CHAR(EXPIRYDT,'MM/DD/YYYY'),'MM/DD/YYYY'),'MM/DD/YYYY') AS EXPIRYDT, TO_CHAR(TO_DATE(TO_CHAR(DUEDT,'MM/DD/YYYY'),'MM/DD/YYYY'),'MM/DD/YYYY') AS DUEDT, CANTAG, PREMAMT, NETPREM FROM LPCINS WHERE CANTAG IS NULL AND COLID IN (SELECT COLID FROM LCCOLASS WHERE LOANNO IN (SELECT LOANNO FROM TMPPORT3)) ORDER BY EXPIRYDT DESC

有什么办法可以改进吗？我也有一个索引放置，但它仍然是缓慢的。我正在使用oracle数据库。

sql

来源：https://stackoverflow.com/questions/62525764/oracle-sql-efficiency

1条答案

按热度按时间

kb5ga3dv1#

我会用 exists :

SELECT POLNO, COLID, FORCEWRITE, INSURETAG, 
       TO_CHAR(TO_DATE(TO_CHAR(STARTDT,'MM/DD/YYYY'),'MM/DD/YYYY'),'MM/DD/YYYY') AS 
STARTDT,
       TO_CHAR(TO_DATE(TO_CHAR(EXPIRYDT,'MM/DD/YYYY'),'MM/DD/YYYY'),'MM/DD/YYYY') AS EXPIRYDT, TO_CHAR(TO_DATE(TO_CHAR(DUEDT,'MM/DD/YYYY'),'MM/DD/YYYY'),'MM/DD/YYYY') AS DUEDT,
       CANTAG, PREMAMT, NETPREM
FROM LPCINS L
WHERE CANTAG IS NULL AND
      EXISTS (SELECT 1
              FROM LCCOLASS LC JOIN
                   TMPPORT3 T
                   ON LC.LOANNO = T.LOANNO
              WHERE L.COLID = LC.COLID
             )
ORDER BY EXPIRYDT DESC

然后尝试索引 LPCINS(CANAG, COLID) 以及 LCCOLASS(COLID, LOANNO) 以及 TMMPORT3(LOANNO) .

赞(0）回复(0）举报 2021-07-24

我来回答

oracle sql效率

1条答案

相关问题

热门标签

最新问答