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.


  • 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;