waiting waiting - 6 months ago 6
SQL Question

make max() function faster when used as inline query join condition

I got a query where iam trying to get the max(date) value of from another table to be used as join condition.

SELECT a.col1, a.col2
FROM tablea a,
tableb b
WHERE a.pk_id = b.fk_id
AND a.effdt = (SELECT MAX(effdt)
FROM tablea c
where c.id= a.id
and c.effdt <= sysdate
)


Here a index is already created on the tablea for effdt column still the query is taking long time to return values. any help in joining them better would be great.

MT0 MT0
Answer

Use the RANK() analytic function to eliminate the correlated sub-query:

SELECT *
FROM   (
  SELECT a.*,
         RANK() OVER ( PARTITION BY a.id ORDER BY a.effdt DESC ) AS rnk
  FROM   tablea a
         INNER JOIN
         tableb b
         ON ( a.pk_id = b.fk_id )
  WHERE  a.effdt <= SYSDATE
)
WHERE  rnk = 1;
Comments