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 the TABLE ACCESS BY INDEX ROWIDOptimizer refer to rowidthe index table or scan to get rowids?
Query is currently ending in a few seconds. But the plan says that it does not use any index, both tables have corresponding indexes.
source
share