Question: 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.

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;

```
Apart from adding the `index` as Markus suggested, try this. 
IMO joins work better than subquery (for the scenarios I tried)

```
SELECT a.*
FROM tablea a
INNER JOIN tableb b 
 ON a.pk_id = b.fk_id
INNER JOIN 
   (SELECT MAX(effdt) AS effdt
    FROM tablea
    WHERE effdt <= sysdate
    ) c ON c.id = a.id
    AND c.effdt = a.effdb

```