TABLE ACCESS BY INDEX ROWID BATCHED

I was writing an article for Allthings Oracle about Indexing strategy: discard and sort and testing the model supporting this article in different oracle database releases 10gR2, 11gR2 and 12cR1 until my attention has been kept by an interesting detail in 12cR1.

Observe the following execution plans taken from 12cR1 in response to the following query:

select * from t1 where id2 = 42 order by id1 desc;

------------------------------------------------------------------------
| Id  | Operation                   | Name  | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |      1 |        |   1000 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |      1 |    499K|   1000 |
|   2 |   INDEX FULL SCAN DESCENDING| T1_PK |      1 |    998K|   1000K|
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID2"=42)

---------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |      1 |        |   1000 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1             |      1 |    499K|   1000 |
|*  2 |   INDEX RANGE SCAN          | T1_IND_ID1_FBI |      1 |    499K|   1000 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID2"=42)

------------------------------------------------------------------------------
| Id | Operation                   | Name          | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------------
| 0  | SELECT STATEMENT            |               |      1 |        | 1000   |
| 1  | TABLE ACCESS BY INDEX ROWID | T1            |      1 |    499K| 1000   |
|* 2 | INDEX RANGE SCAN DESCENDING | T1_IND_ID1_NI |      1 |    499K| 1000   |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------
2 - access("ID2"=42)

Question: What have you already pointed out from the above execution plans?

Answer     : I have managed to design indexes so that Oracle succeeded to avoid the order by operation for each of the above query executions (there is no order by operation in the above execution plan).

But this is not the reason which paved the way to this article.  Wait a minute and will you know what motivated this article.

In my incessant desire to help the CBO doing good estimations, I created a virtual column(derived_id2), singularly indexed it with a b-tree index, collected statistics for this virtual column and executed a new but equivalent query:

SQL> select * from t1 where derived_id2 = 42 order by id1 desc;

Which has been honored via the following execution plan

--------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |      1 |        |   1000 |
|   1 |  SORT ORDER BY                       |                        |      1 |    511 |   1000 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1                     |      1 |    511 |   1000 |
|*  3 |    INDEX RANGE SCAN                  | T1_DERIVED_ID2_IND_BIS |      1 |    511 |   1000 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DERIVED_ID2"=42)

Question : Have you already noticed something?

Answer   : The appearance of  a SORT ORDER BY operation above TABLE ACCESS BY INDEX ROWID BATCHED

It seems that the new 12c TABLE ACCESS BY INDEX ROWID BATCHED cannot take place when Oracle uses the index access child operation to avoid the order by operation. In the first three execution plans above, Oracle uses an index access path to avoid the order by operation and in these cases the parent index table has been visited via the classical table access by index rowid. While when Oracle has been unable to eliminate the order by operation, the parent index child table has been accessed via the new 12c table access by index rowid batched followed by, what seems to be inevitable in this case, an order by operation.

Here below is a simple model you can play with to check this impossible to separate couple (order by, table access by index rowid batched)

 create table t1 as
 select rownum n1
      ,trunc((rownum-1)/3) n2
      ,rpad('x',100) v1
 from dual
connect by level <= 1e4; 

create index t1_ind1 on t1(n2, n1);

select * from t1 where n2 = 3329 order by n1 desc;

select * from table(dbms_xplan.display_cursor);

alter index t1_ind1 invisible;

create index t1_ind2 on t1(n2);

select * from t1 where n2 = 3329 order by n1 desc;

select * from table(dbms_xplan.display_cursor);

Index design I

Very often questions about the best position a column should be put in within a composite index come out into forums and Oracle discussions. The last question I have contributed to and tried to answer has been raised up in a French Forum. The original poster was wondering whether it is a good idea to place the very often repeated column (contains duplicates) at the leading edge of the index or not.

First of all, in contrast to my usual style of blogging I am not going to provide a SQL model on which I am going to expand my knowledge of index design. However, for those who want to learn and master indexes I would encourage them to read the world expert person in this field, Richard Foote. He has an excellent blog with several articles about almost all what one has to know about indexes and not only the widely used b-tree indexes but on all other types of indexes including bitmap indexes, function based indexes, partitioned indexes, exadata storage indexes etc..

The second reference is as always Jonathan Lewis blog in which you can find several articles about  index design, index efficiency and index maintenance. In addition, it is not sufficient to know how to design precise index; you need to know as well how your index will evolve with the delete, insert or update operations their underlined tables will undergo during the lifecycle of the application they participate to its normal functioning.

The third reference is the book Relational Database Index Design and the Optimizers which extends the index design to several databases including DB2, Oracle and SQL Server

I, from time to time, come to read very interesting articles about indexes in this web site that I am following via twitter. It contains valuable index design information which, according to what I have read up to now, is pertinent, correct and back up all what I have learned from Jonathan Lewis, Richard Foote and from my own professional experience.

That’s said, I will post here below few of my answers and articles(and Jonathan Lewis articles) about index design as a small answer to a lot of questions about index design

1. On the importance of the leading index columns that should be the ones on which an equality predicate is applied
2. Indexing Foreign keys
3. Redundant Indexes
4. Global or Local Partitioned Index
5. Compressing indexes basic part and cost of index compression

I am planning to write several other articles on indexes and I will be completing the above list as far as I will go with this publishing task

My answer to the original poster question about the importance of the number of distinct values property of an index candidate column is that the starting index column decision is not driven by its number of distinct values. It is instead driven by:

• The nature of the query where clause he has to cover
• The nature of the predicate (equality, inequality, etc..) applied on the starting column
• The constant desire to cover with the same index one or a couple of other queries
• The constant desire to cover with the same index a foreign key deadlock threat : sometime just by reversing the columns order we succeed to cover the current query and an existing foreign key
• The constant desire to avoid redundant indexes

And finally comes up the reason for which one has to consider placing the column with the small number of distinct values at the leading edge of the index: Compression. If you start your index with the more often duplicated column you will make a good index compression reducing, efficiently, the size of that index which means it will be very quickly put into the buffer cache and will be kept there more often due to its small size.