Forum .LRN Q&A: Re: .LRN v2 Performance

Collapse
13: Re: .LRN v2 Performance (response to 11)
Posted by Tracy Adams on
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.

Collapse
16: Re: .LRN v2 Performance (response to 13)
Posted by Tracy Adams on
I had old and new backwards.

In reality, I had added and maintained a column latest_response_p in order to speed up the survey section.