I don't remember exactly - it was well over a year ago.
A guess, the latest_response_p = t probably was the cause of the full table scan:
15171456 TABLE ACCESS FULL SURVEY_RESPONSES
This is because latest_response_p had lots of values that were "t" and doesn't have differing values to really make it and effectively indexed column. So Oracle scans at the whole table.
You'll notice that I replaced the lastest_response_p = t with
(select max(response_id) as response_id
from survey_responses
group by nvl(initial_response_id, response_id)) latest
that worked more effectively.
