TABLE ACCESS BY INDEX ROWID BATCHED

This looks like a pretty cool new way to use an index.  From the documentation it say that this will access the index and get 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.”

That sure seems like a good idea and from my testing it looks like it does just that.  I have a table called APHYS1 and it has an index on a column that has a really great clustering factor (these stats are identical on the 11.2 and the 12.1 database):

Clust. Factor : 152
Table Rows    : 10000
Table Blocks  : 164

You can’t get much better then that the clustering factor is just less than the number of blocks in the table.  When I run a query on this table in 11.2 that stats of the run looks like this for LIOs and Pins:

buffer is pinned count : 738
consistent gets: 63

In 12.1 the stats are:

buffer is pinned count: 784
consistent gets: 17

It appears that for about 6% more pins we cut the LIOs (consistent gets) by 73%, not bad.  I’m liking this. 

There is one thing about this that is still a bit of a mystery for me.  There is a parameter to turn this feature off:

_optimizer_batch_table_access_by_rowid

However I don’t think it does anything.  My test case (which is very simple) has the same stats with this set to TRUE or FALSE.  What does change is the word BATCHED is dropped from the plan when this is set to FALSE but otherwise it’s exactly the same plan, cost and everything else.  Even the 10053 Trace looks the same again except for the word BATCHED appearing in the plan.  The part of the 10053 trace where it costs out the step is identical. The word BATCHED doesn’t appear in that section at all. 

Here is what is in the 10053 trace for costing the access path, note nothing about BATCHED:

SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for APHYS1[APHYS1]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Column (#1): OBJECT_ID(NUMBER)
    AvgLen: 3 NDV: 100 Nulls: 0 Density: 0.010000 Min: 0.000000 Max: 99.000000
  Table: APHYS1  Alias: APHYS1
    Card: Original: 10000.000000  Rounded: 503  Computed: 503.03  Non Adjusted: 503.03
 ***** Logdef predicate Adjustment ******
 Final IO cst 0.00 , CPU cst 50.00
 ***** End Logdef Adjustment ******
 ***** Logdef predicate Adjustment ******
 Final IO cst 0.00 , CPU cst 52.52
 ***** End Logdef Adjustment ******
  Access Path: TableScan
    Cost:  46.08  Resp: 46.08  Degree: 0
      Cost_io: 46.00  Cost_cpu: 3203178
      Resp_io: 46.00  Resp_cpu: 3203178
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
  Access Path: index (RangeScan)
    Index: APHYS1_N1
    resc_io: 11.00  resc_cpu: 264816
    ix_sel: 0.050303  ix_sel_with_filters: 0.050303
    Cost: 11.01  Resp: 11.01  Degree: 1
  Best:: AccessPath: IndexRange
  Index: APHYS1_N1
         Cost: 11.01  Degree: 1  Resp: 11.01  Card: 503.03  Bytes: 0

Here is the plan in the 10053 trace when the parameter is set to TURE, the only difference when set to FALSE is the work BATCHED disappears (underlined here for emphasis, it’s not underlined in the trace file). 

============
Plan Table
============
--------------------------------------------------------+-----------------------------------+
| Id  | Operation                            | Name     | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                     |          |       |       |    11 |           |
| 1   |  TABLE ACCESS BY INDEX ROWID BATCHED | APHYS1   |   503 |   51K |    11 |  00:00:01 |
| 2   |   INDEX RANGE SCAN                   | APHYS1_N1|   503 |       |     3 |  00:00:01 |
--------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("OBJECT_ID">=1 AND "OBJECT_ID"<=4)

So in 12.1 is there really a difference between these two?  I’m not seeing it. 

If you’d like to try this, please do and let me know if you get the same results.  I’ve run this test many times and get the same each time.  I’ve been doing this testing on two Amazon Cloud Linux based systems. This is the code to create the table:

create table aphys1 as
select
trunc((rownum-1)/100) object_id,
rpad(rownum,100) col2
from dba_source
where rownum < 10001;

create index aphys1_n1 on aphys1(object_id);

exec dbms_stats.gather_table_stats(user,'aphys1',method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>TRUE);

This is the query used:

select * from aphys1 where object_id between 1 and 4;