New syntax for so called “Top N” queries was introduced in Oracle 12c.
It can be used to return either fixed number of rows or specified percent of rows in a table.
Obviously latter requires to know the total number of rows to return the result so let’s check what is happening under the hood in this case.
Bloom filter may help to dramatically reduce amount of data to be read from partitioned table by eliminating unneeded partitions.
Here is a nice introduction into bloom filters.
Unfortunately bloom filter doesn’t work with DML which causes a lot of challenges in 11gR2.
This bug is documented on metalink Bug 13801198 : BLOOM PRUNING/FILTER NOT USED IN DML STATEMENTS however that note doesn’t suggest any workarounds.
There are many cases when executing some statement (either SQL or PL/SQL) may invoke execution of another SQL or PL/SQL statement.
However it’s not at all obvious how execution statistics for cursors are aggregated.