D’oh! I misread the 1,000 Actual rows as being input to the hash join anti rather than the output. 4 million rows multiplied by the projected size of the keys is a lot more than 1,000 rows.
Jonathan Lewis wrote:
Done and published - the big question now is why is Oracle making such an idiotic choice of index to access t_iot.
I wonder if it's this bug Doc ID 2334876.1 , we hit it on 12.1 and the workaround did the trick. It's the kind of bug that might flip-flop between patches
That bug might explain the idiotic choice of index - and if the more appropriate index were used the performance should change dramatically; but the bug doesn't seem to be related to the strange way that Oracle uses the "universal rowid" of the second index on the IOT, which is the immediate cause of the I/O overhead.