oracle using index even though there is no filter criteeria specified

Question

In this query:

 SELECT WTTEMPLATE.TEMPLATEuID,
                    MAX (WTTRX.VALUEDATE) AS template_last_use_date
               FROM wttemplate, wttrx
              WHERE WTTEMPLATE.TEMPLATEID = WTTRX.TEMPLATEID(+)
                    AND WTTEMPLATE.CUSTID = WTTRX.CUSTID
           GROUP BY WTTEMPLATE.TEMPLATEuID

The explain plan shows:index fast full scan using indexes on WTTEMPLATE.TEMPLATEID and (WTTRX.TEMPLATEID,WTTRX.CUSTID). My question is this: I have not specified any filter criteria , so how can it use indexes? It should do full scan...right?

score 1 · Accepted Answer · answered Oct 19 '12 at 23:16

You are using TEMPLATEID in the where clause. That's why Oracle uses an index to speed up the lookup for the join operation.

Btw: but you should really get used to using standard JOIN syntax instead of the implicit joining in the WHERE clause:

SELECT wttemplate.templateuid,
       max (wttrx.valuedate) as template_last_use_date
FROM wttemplate 
   LEFT JOIN wttrx 
      ON wttemplate.templateid = wttrx.templateid
     AND wttemplate.custid = wttrx.custid
GROUP BY wttemplate.templateuid

Changing the join syntax won't change the execution plan. It's just a matter of better clarity and it's less error prone to unwanted cartesian joins.

oracle using index even though there is no filter criteeria specified

1 Answers1