Does "TABLE ACCESS BY INDEX ROWID" means optimizer using index or table?
oracle11g, performance, sql-tuning
Solution
`TABLE ACCESS BY INDEX ROWID` means that the Oracle kernel is going through your index and knows that not all needed information is contained in the index (columns needed are not in this index). Therefore it takes the pointer to the actual table data (rowid) and looks it up.
Popular trick to make things run faster is in this case including missing columns in the (non unique) index. It avoids one lookup in the table at the expense of larger indexes.
Problem
``` I have query that join two very big tables and ran explain plan on that it showing like this.. ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 31 | 7 (0)| 00:00:01 | | 1 | PX COORDINATOR | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | | | | | | 3 | NESTED LOOPS | | | | | | | 4 | NESTED LOOPS | | 1 | 31 | 7 (0)| 00:00:01 | | 5 | PX PARTITION HASH ALL | | 1 | 17 | 5 (0)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID| Tab1 | 1 | 17 | 5 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | Tab1_PK | 1 | | 4 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | tab2_PK | 1 | | 1 (0)| 00:00:01 | |* 9 | TABLE ACCESS BY INDEX ROWID | Tab2 | 1 | 14 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------------- Query: select t2.colC,t2,colD,t1.colX from tab2 t2 join tab1 t1 on t2.colA=t1.colA and t1.colB=2345 and t2.colC in (123,456,789); ``` Does `TABLE ACCESS BY INDEX ROWID` mean Optimizer is accessing `rowid` in Index or scanning table to get the `rowids`? Currently Query is finishing in few secs. But in the plan says its not using any index, both tables has appropriate indexes.