Re: RRN in SQL and Full table scan...

On 28-Jul-2016 09:46 -0500, Charles Wilt wrote:

Back in May there was a thread where Birgitta mentioned that
"Sometime in release 6.1? / 7.1?. the table scan for RRN was changed
to an values list access, i.e. the list containing RRNs is searched
first and with the result an access to the record performed."

A test with WHERE RRN(A) = 750000

Showed the "VALUES LIST" in Visual Explain.

However, today I tried
WHERE RRN(A) between 750000 and 750010

And I see a full table scan in VE.

This however, shows a values list..
WHERE RRN(A) IN ( 750000, 750001, 750002, 750003, 750004, 750005
, 750006, 750007, 750008, 750009, 750010)

I'm running 7.1 TR 9.

TR11+C15317710 I also see arrival sequence access method implementations with *VALUES-list for both the equal (=) and IN predicates. Similarly, I do not see the *VALUES-list with the BETWEEN predicate [nor with the equivalent combined predicates of effective "*GE AND *LE"]; though for both effective BETWEEN predicates, I do not know if also a full table-scan implementation, per no mention in the query debug messages that I was using as a feedback mechanism.

However, since creating an INDEX on the RRN(), the availability of that AccPth avoided any arrival sequence access method implementations, for each of the queries, implementing instead, using the derived keyed access path defined over the RRN; chosen for the purpose of row selection.