使用儒略日期列优化此SELECT查询 [英] Optimizing this SELECT query with Julian date column

问题描述

下面的查询需要很长时间1693秒.

This query below takes a long time 1693 seconds to be exact.

SELECT MAX(cov_julian(t.tatime)) 
  FROM Table1 t,Table2 a,Table3 c
 WHERE  (t.PERSONID = a.EMPID or t.PERSONID2 = c.Col5) 
   AND cov_julian(tatime) <=sysdate   

cov_julian是用户定义的函数，可将朱利安日期转换为日期.

cov_julian is an user defined function that converts julian date to date.

如何优化此查询?请帮忙.

how do I optimize this query? please help.

推荐答案

根据您的SELECT子句，您真正关心的唯一列是tatime.

According to your SELECT clause, the only column you really care about here is tatime.

如果tatime是Table1上的一列，那么我将检查是否存在于Table2或Table3中，而不是加入到其中.

If tatime is a column on Table1, then I would check for existence in, and not join to, Table2 or Table3.

结合Ollie的建议，查询变为

Combined with Ollie's suggestion, the query becomes

SELECT MAX(cov_julian(t.tatime)) 
FROM   table1   t
WHERE  t.tatime <= TO_NUMBER(TO_CHAR(SYSDATE,'J'))
AND   (EXISTS (SELECT NULL
               FROM   table2        a
               WHERE  a.empid = t.personid)
    OR EXISTS (SELECT NULL
               FROM   table3        c
               WHERE  c.col5  = t.personid2))
;

这篇关于使用儒略日期列优化此SELECT查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！