Explian Plan explanation

Do you have an index on effective_date col?

Change this line
AND TRUNC (flum.effective_date) = TO_DATE ('01/30/2006', 'MM/DD/YYYY')
to

AND flum.effective_date between
to_date('01/30/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and
to_date('01/30/2006 23:59:59', 'MM/DD/YYYY HH24:MI:SS')
Tamil

No there is no index on effective_date. Should I add one?

Try it out. You know your data very well and its distribution.

Tamil