Does BATCHED in an execution plan still guarantee that records are returned in strict index order?
We are porting applications from using Oracle database 11 to using Oracle database 12. These applications access the database through SQL statements generated by a middleware. To read records from an index range, the middleware uses a hint instead of order by, to avoid FULL TABLE SCANs.
We found some differences in execution plans. One is:
11.1.0.6:
TABLE ACCESS BY INDEX ROWID
|
+-- INDEX RANGE SCAN
12.1.0.2:
TABLE ACCESS BY INDEX ROWID BATCHED
|
+-- INDEX RANGE SCAN
In the documentation (Optimizer Access Paths), I found: "The database retrieves a few rowids from the index, and then attempts to access rows in block order to improve the clustering and reduce the number of times that the database must access a block."
I have tested...
declare
n number;
begin
n := 0;
for cur in (select /*+index (wepsb key_wepsb_0)*/ id from wepsb) loop
if cur.id <= n then raise_application_error (-20000, 'wrong order'); end if;
n := cur.id;
end loop;
end;
... for many tables, all without error. I have exported tables in non-ID order, truncated, and re-imported. Still no error.
Until then, I guessed that either BATCHED was a fake or the original order of the index was explicitly maintained. But have a look at this:
select * from wepsb where id between :1 and :2;
SELECT STATEMENT ALL_ROWS Cost : 1315 Cardinality : 1390 Bytes : 79230
|
+--FILTER
|
+--TABLE ACCESS BY INDEX ROWID BATCHED LVS_ADMIN.WEPSB TABLE ANALYZED Cost : 1315 Cardinality : 1390 Bytes : 79230
|
+--INDEX RANGE SCAN LVS_ADMIN.KEY_WEPSB_0 INDEX (UNIQUE) ANALYZED Cost : 8 Cardinality : 2501
select * from wepsb where id between :1 and :2 order by id;
SELECT STATEMENT ALL_ROWS Cost : 1315 Cardinality : 1390 Bytes : 79230
|
+--FILTER
|
+--TABLE ACCESS BY INDEX ROWID LVS_ADMIN.WEPSB TABLE ANALYZED Cost : 1315 Cardinality : 1390 Bytes : 79230
|
+--INDEX RANGE SCAN LVS_ADMIN.KEY_WEPSB_0 INDEX (UNIQUE) ANALYZED Cost : 8 Cardinality : 2501
It looks like the optimizer does not rely on an index, if the corresponding records are read BATCHED. But does someone know? Does someone have additional information about BATCHED?