Just a little follow-up on my earlier note on buffer sorts. The following is an extract from a a tkprof output showing the rowsource operation for a query. Note especially the rows information.
7854 MERGE JOIN CARTESIAN (cr=14 time=63688 us) 66 TABLE ACCESS FULL GTT_REB_GRP_HDLE_RATE (cr=7 time=460 us) 7854 BUFFER SORT (cr=7 time=24797 us) 119 TABLE ACCESS FULL VP_REBATE_CONTROL_DTL (cr=7 time=391 us)
The numbers help to explan what’s going on in the buffer sort. We see a merge join cartesian (which, in this case, looks like an accident – the optimizer may have expected just one row to come out of the GTT_REB_GRP_HDLE_RATE table – the name suggests it’s a global temporary table, and they often cause problems to the optimizer). To make this join more efficient, Oracle has scanned the VP_REBATE_CONTROL_DTL just once, producing 119 rows and stuffing them into a buffer.
We then see that the buffer sort operation has produced 7,854 rows – why ? Because Oracle has scanned the buffer once for every row in the driving table. (Quick check: 66 * 119 = 7,854).