0

I came to a execution plan where oracle choose to access the index but instead of using the columns as predicate it only filter by them.

My question is what are the reasons that can cause that(all the involved columns are in the index).

For example(However I want it to be more a generic question):

...
|* 18 |      HASH JOIN              |                 |  4064K|   294M|   307M| 55108   (1)| 00:11:02 |       |       |
|  19 |       PARTITION RANGE SINGLE|                 |  6456K|   233M|       | 12217   (1)| 00:02:27 |    20 |    20 |
|* 20 |        INDEX FAST FULL SCAN | IX_TAB_TEMP1    |  6456K|   233M|       | 12217   (1)| 00:02:27 |    20 |    20 |
|  21 |       PARTITION RANGE SINGLE|                 |  6456K|   233M|       | 12217   (1)| 00:02:27 |    20 |    20 |
|* 22 |        INDEX FAST FULL SCAN | IX_TAB_TEMP1    |  6456K|   233M|       | 12217   (1)| 00:02:27 |    20 |    20 |

...
  18 - access("ITEM"."PARENTID"="HOL"."MID")
  20 - filter("ITEM"."ID1"=1110 AND "ITEM"."ID2"=1112)
  22 - filter("HOL"."ID1"=1110 AND "HOL"."ID2"=1112)

Index is (ID1, ID2, MID, PARENTID) local. partition is range on ID1, ID2

1
  • 1
    It means that optimizer prefers to read index entirely with multi-block reads and then filter it. Mar 17 '14 at 15:05
1

Based on the statistics the oprimizer has estimated this as the cheapest way to get the data. A INDEX FAST FULL SCAN reads the entire index as it is stored on disk using multiblock read. This kind of operation is prefered to other index operation because a high number/fraction of rows with ID1=1110 and ID2=1112 exists in the index and the data is not needed sorted. It is prefered to a full table scan because all the data needed (ID1, ID2, MID, PARENTID) is contained in the index.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.