ROWNUM is a pseudocolumn which is used to uniquely assign numbers (1,2,3,4,5…) to result rows as they are retrieved. In this blog post I will demonstrate a situation where using a ROWNUM filter on a table which has lot of empty blocks can negatively impact on the execution time. The root cause is not the ROWNUM itself, however lot of people are surprised when using a filter simple as ROWNUM <= 1 takes minutes to execute. Using ROWNUM <= N as a predicate triggers the optimizer to use COUNT STOPKEY operation, meaning it will stop the table scan once N rows are identified.
In the case of a full table scan all blocks below HWM are read. Oracle has no way to determine which blocks contain rows and which don’t before hand, all blocks will be read until N (ROWNUM) rows are identified. If there are empty blocks, it will result in many reads. That might be the case if you do lot of direct path inserts where the rows are placed after HWM in new unformatted blocks. Usually, Oracle will reuse empty blocks for regular inserts. However, if the data is loaded by direct path then Oracle won’t reuse any of the empty blocks.
Having T1 fully packed without any deletes ROWNUM <= 1 completes in less than a second. Once it finds one row which satisfies ROWNUM <= 1 it stops with execution, that’s the purpose of COUNT STOPKEY operation.
Having lot of empty blocks from the beginning of the segment will result in longer execution time due to the fact that Oracle will spend some time until it finds a block with a row. We can see that after we delete all rows except the latest which was inserted with APPEND hint.
It scanned all segment blocks, 1000002 physical reads is 7812mb (8k block size) and T1 segment is 7872mb in size (I previously flushed the buffer cache). Further we can confirm what’s the data distribution between the blocks with DBMS_SPACE.SPACE_USAGE procedure.
We have 7812mb free space (fs4_blocks – number of blocks fully empty), the same amount which was processed until a row was encountered.
This is expected behavior if lot of DELETE’s are performed followed by INSERT’s with APPEND hint, in which case empty blocks are not re-used. There are multiple approaches to release unused space, we can use online table redefinition, alter table move, data pump export/import, segment shrink, etc.
You can comment via Twitter.
Link for this post: comment