Geek asks and answers

Optimizing this SELECT query with Julian's date column

This query takes more than 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 is a user defined function that converts julian date.

how to optimize this query? please, help.

+1
source share
3 answers

According to your suggestion, the SELECTonly column that you really need is tatime.

If tatimeis a column on Table1, then I should check for existence in, and not join in Table2or Table3.

When combined with an Ollie offer, the request 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))
;
+1
source

If tatimethere is an index, then convert sysdate to julian and directly compare with tatime.

.

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

, , cov_julian(tatime).

+4

, UDF. , tatime MAX :

SELECT COV_JULIAN(MAX_TIME) FROM
  (SELECT MAX(t.tatime) AS MAX_TIME 
     FROM Table1 t,
          Table2 a,
          Table3 c 
     WHERE (t.PERSONID = a.EMPID or t.PERSONID2 = c.Col5)  
           AND tatime <= TO_NUMBER(TO_CHAR(sysdate, 'J'))) x

PL/SQL, SQL-, Bad News, , , . (10g +) "", , - .

.

+1

More articles:


All Articles