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

Franck Pachot
Jan 7, 2019 · 3 min read

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:

Image for post
Image for post

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:

Image for post
Image for post

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.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store