First of all, I'm not sure if I need to avoid TABLE ACCESS BY INDEX ROWID. Our internal team wants us to avoid TABLE ACCESS BY INDEX ROWID, and would like to contain everything within the index.
Let me start with this question : Is TABLE ACCESS BY INDEX ROWID bad ? If socan someone say what are the issues ?
Table: We have a table with 15million rows.
I've the following query which performs "TABLE ACCESS BY INDEX ROWID"
SELECT COUNT (*)
FROM
(SELECT
*
FROM TERRITORY_DN_V amgen3
WHERE (amgen3.TERR_DIMENSION_CODE = 'Geo'
AND ( ((amgen3.TERR_DIM_NODE_INTG_ID = '300000000149103')
OR (amgen3.TERR_DIM_NODE_PARENT_INTG_ID = '300000000149103' AND amgen3.TERR_DIM_NODE_RANGE_HIGH_NAME >= 'CHIAPAS' AND amgen3.TERR_DIM_NODE_RANGE_LOW_NAME <= 'CHIAPAS')
)) ));
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 99 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 99 |
| 2 | CONCATENATION | | 1 | | 90 |00:00:00.01 | 99 |
| 3 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 6 |
| 4 | TABLE ACCESS BY INDEX ROWID| MOT_TERR_DIM_PARTITIONS | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 5 | INDEX UNIQUE SCAN | MOT_TERR_DIM_PARTITIONS_U1 | 1 | 1 | 1 |00:00:00.01 | 1 |
| 6 | TABLE ACCESS BY INDEX ROWID| TERRITORY_DN | 1 | 1 | 0 |00:00:00.01 | 4 |
|* 7 | INDEX RANGE SCAN | TERRITORY_DN_N10 | 1 | 1 | 0 |00:00:00.01 | 4 |
| 8 | NESTED LOOPS | | 1 | 1 | 90 |00:00:00.01 | 93 |
| 9 | TABLE ACCESS BY INDEX ROWID| MOT_TERR_DIM_PARTITIONS | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 10 | INDEX UNIQUE SCAN | MOT_TERR_DIM_PARTITIONS_U1 | 1 | 1 | 1 |00:00:00.01 | 1 |
|* 11 | TABLE ACCESS BY INDEX ROWID| TERRITORY_DN | 1 | 1 | 90 |00:00:00.01 | 91 |
|* 12 | INDEX RANGE SCAN | TERRITORY_DN_N9 | 1 | 1 | 90 |00:00:00.01 | 4 |
-----------------------------------------------------------------------------------------------------------------------
I've ensured that required indexes for both the conditions are in place.
To explain this further I'll split the same SQL into 2 parts and there is no TABLE ACCESS BY INDEX ROWID.
For example something like
Modification 1 - Remove the condition after the OR operator:
SELECT COUNT (*)
FROM
(SELECT
*
FROM TERRITORY_DN_V amgen3
WHERE (amgen3.TERR_DIMENSION_CODE = 'Geo'
AND ( ((amgen3.TERR_DIM_NODE_INTG_ID = '300000000149103')
)) ));
Modification 2 - Remove the condition before the OR operator::
SELECT COUNT (*)
FROM
(SELECT
*
FROM TERRITORY_DN_V amgen3
WHERE (amgen3.TERR_DIMENSION_CODE = 'Geo'
AND ( ( (amgen3.TERR_DIM_NODE_PARENT_INTG_ID = '300000000149103' AND amgen3.TERR_DIM_NODE_RANGE_HIGH_NAME >= 'CHIAPAS' AND amgen3.TERR_DIM_NODE_RANGE_LOW_NAME <= 'CHIAPAS')
)) ));
Could someone help me understand the problem and suggest on how to solve ?
Appreciate your help.