first_rows_N again

If you run a query using first_rows_N optimisation you could run into a massive performance problem in cases where the optimizer thinks the complete result set is quite large when it is actually very small.

If both conditions are true the optimizer may choose a very resource-intensive execution path “expecting” to stop (or at least pause between fetches) after N rows – hoping to give the impression that it can respond very quickly – but find that the query has to run to completion because the N rows simply don’t exist.

As a simple thought experiment, consider the query:

select  /*+ first_rows(1) */
        *
from
        t1
where
        flag = 'X'
;

Assume t1 holds 1,000,000 rows at roughly 50 per block (for a total of 20,000 blocks). Now assume that the optimizer thinks, for whatever reason, that 100,000 rows (one in ten) have the value ‘X’ for coumn flag.

Based on this information the optimizer will estimate that there are five suitable rows in every block in the table and may therefore choose to do a tablescan because that will be the fastest way to return one row from the table. Unfortunately if there are no rows matching the predicate your query is going to have to scan the whole table to return no rows.

In a variation on the same theme – maybe there really are 100,000 rows that match the predicate, but if they are all located in the last 2,000 blocks of the table, Oracle will have to scan 18,000 blocks before it finds the first row.

Cases like this may be a little unusual – at least on paper – but they do happen. Remember, though, that the two basic optimisation questions will tell you what’s going on if you see a performance problem like this: “how much data”, “where is it”.

Further reading:

rownum effects and first_rows_n, and an example of the problem in this item on the OTN database forum.