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?
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.