Why a SPILL SORT on *smaller* matchset and not on larger one?

[Dave Green]

I have a large table (600 Million rows) and I’ve been analysing explain plans for queries using this table and noticed that I’m getting a SORT(SPILL) and RIDSCN after the Index Scan nearly all the time. I thought this indicated that reading the RIDs was taking up more than the HEAPSIZE memory and so DB2 was spilling the RIDs onto disk. Here is the SQL….

EXPLAIN ALL SET QUERYNO=1 FOR
SELECT
Q1.INTERFACE_REC_ID
FROM MyBigTable Q1
WHERE 1=1
AND Q1.INTERFACE_ID = 57

Both INTERFACE_REC_ID and INTERFACE_ID are in the same index which looks like this:-

+INTERFACE_ID+IND_SUCCESS+INTERFACE_REC_ID

There are 76 potential values INTERFACE_ID can be. Depending on the value of INTERFACE_ID matching rows can be anywhere from 5 to 170 million.

Nothing confusing about that so far I hope. What IS confusing me is that when I change the value of INTERFACE_ID to return 170 million rows (filter factor 4.5%) the SORT(SPILL) and RIDSCN go away, but return for values that return smaller numbers of matching rows. After testing with different INTERFACE_ID values it appears that under 25 million rows returned creates a SORT(SPILL) and RIDSCN, but over that it’s just an index scan and fetch.

Why a spill on smaller cardinality matches instead of bigger ones? I would have thought less matches found would fit into a memory and bigger ones would spill onto disk, not the other way around!