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;

```
The the sub query on table c, an index on `effdt` is not enough.

An index on `(id, effdt)` would do better. The key is to cover the `where` clause as well as the `max(effdt)` function in a single index. This is very similar to indexing `order by`: <http://use-the-index-luke.com/de/sql/sortieren-gruppieren/indexed-order-by>