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

Collapse
11: Re: .LRN v2 Performance (response to 1)
Posted by Janine Ohmer on
The second one is how it has been all along; the first one (the one from our old system) was optimized at Sloan a while back, probably for the same reasons we are seeing now.  Tracy did the original work, so I'll let her explain what she did and why.  Clearly her change, or something similar, needs to go into the toolkit as well.
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.