TABLE ACCESS BY INDEX ROWID BATCHED | Mohamed Houri’s
https://hourim.wordpress.com/2014/09/23/table-access-by-index-rowid-batched/
September 23, 2014 September 23, 2014 TABLE ACCESS BY INDEX ROWID BATCHED Filed under: — hourim @ 7:05 pm I was writing an article for 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
It seems that the new 12c TABLE ACCESS BY INDEX ROWID 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); Share this:Like this:Like Loading... Related2 Comments » 2 Comments […] As you can see, this time, in contrast to the first two cases, a SORT ORDER BY operation kicks in. This operation becomes mandatory since the result set has been desired in an order that doesn’t match the order of the index columns. Spot in passing that when Oracle avoids a SORT ORDER BY it doesn’t visit the T1 table in a BATCHED mode and vice versa. I have already wrote a note about this here. […] Pingback by — April 29, 2017 @ | Thank you for this article. Comment by — March 24, 2020 @ | Leave a Reply Enter your comment here... Fill in your details below or click an icon to log in: Email (required) (Address never made public) Name (required) Website You are commenting using your WordPress.com account. ( / ) You are commenting using your Google account. ( / ) You are commenting using your Twitter account. ( / ) You are commenting using your Facebook account. ( / ) Connecting to %s Notify me of new comments via email. Notify me of new posts via email. Δ This site uses Akismet to reduce spam. . Search for:
DA: 38 PA: 3 MOZ Rank: 69
