Oracle literal vs bind-variable in partition pruning and Top-N queries

Here is a query that retrieves the most recent value for a variable within a time range. The table has the variable ID, the timestamp and the value. All is properly indexed (it is actually an IOT) correctly: index on (VARIABLE_ID, TIMESTAMP, VALUE) locally partitioned on TIMESTAMP.

For whatever reason, the optimizer chooses an index fast full scan. With small partitions, this should not be a problem as we do not need to read all partitions: we want only the last value (rownum=1 after order by desc).

literals

Here is the execution plan with literals:

The interesting information is in the ‘Starts’ column: only two partitions have been read. That’s because we have found our row and there’s no need to continue. The line 5 is there for that with a STOPKEY on ROWNUM=1. This is possible because the partitions are read, by PARTITION RANGE ITERATOR, in the same order as we want from the ORDER BY: descending on TIMESTAMP. There’s a clue from Pstart/Pstop but the best way to see it is from partition pruning event 10128 level 2 (see https://support.oracle.com/epmos/faces/DocContentDisplay?id=166118.1)

The dump mentions a DESCENDING RANGE SCAN:

Partition Iterator Information:
partition level = PARTITION
call time = RUN
order = DESCENDING
Partition iterator for level 1:
iterator = RANGE [3954, 4266]
index = 4266
current partition: part# = 4266, subp# = 1048576, abs# = 4266

and the KKPAP_PRUNING table lists which one were actually read:

SQL> select ID,TYPE,LEVEL,ORDER,CALL,PARTNUM,APNUM 
from KKPAP_PRUNING;
ID TYPE  LEVEL     ORDER      CALL PARTNUM APNUM
-- ----- --------- ---------- ---- ------- -----
3 RANGE PARTITION DESCENDING RUN 4267 4266
3 RANGE PARTITION DESCENDING RUN 4266 4265

Those are the 2 ones counted by ‘Starts’.

bind variables

Here is the same query with bind variables for the timestamp range:

The big difference is that the STOPKEY is above the partition iterator here. This means that we cannot stop before having read all partitions. And that was the case: 313 ‘Starts’ — one for each partition — and 513 rows read before deciding which was the last one in the timestamp order.

It is different because the partitions are know known at parse time, because of the bind variables. This is why we see KEY/KEY rather than partition identifiers in Pstart/Pstop.

The event 10128 level 2 confirms that we have read all partitions:

SQL> select ID,TYPE,LEVEL,ORDER,CALL,PARTNUM,APNUM 
from KKPAP_PRUNING;
ID TYPE  LEVEL     ORDER      CALL PARTNUM APNUM
-- ----- --------- ---------- ---- ------- -----
6 RANGE PARTITION DESCENDING RUN 4267 4266
6 RANGE PARTITION DESCENDING RUN 4266 4265
6 RANGE PARTITION DESCENDING RUN 4265 4264
6 RANGE PARTITION DESCENDING RUN 4264 4263
...

However, as PARTITION DESCENDING is mentioned, I would have expected that the same optimization is possible as with the literals. That’s a big difference in execution time when the predicate covers many partitions, and we can see above that this is not considered by the optimizer: the cost is the same.

Note that Mauro Pagano has a note about the bind variables case where he mentions that the SORT step above the partition iterator happens even with RANGE SCAN:

In summary, whether it is necessary or not, the optimizer may be more clever with partition iterations when he knows the partitions at optimization time, which means when parameters are passed as literals rather than bind variable. Of course, you must also consider the execution frequency because of the parsing (CPU and latch) overhead.