1

I'm attempting to tune a query and I want to be able to tell if partition pruning is happening. On one part of the query, the I'm getting a Pstart and Pstop value = "KEY":

| Id  | Operation                                 | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
|*  5 |      INDEX RANGE SCAN                     | IX_SOMEINDEX                   |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |

Another line of the execution plan shows a Pstart and Pstop value = "ROWID":

| Id  | Operation                                 | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
|  10 |        TABLE ACCESS BY GLOBAL INDEX ROWID | LOOP                           |     1 |    18 |     2   (0)| 00:00:01 | ROWID | ROWID |

Basically I'm looking for definitions of what ROWID and KEY mean within the context of Pstart and Pstop.

2

Broadly, they mean that the partition(s) that need to be accessed will be determined at runtime. In the first plan, where both are KEY, some earlier step in the plan is producing one or more values for the partition key. Those partition keys are then used to determine which partitions Oracle needs to scan the index for. In the second plan, the ROWID from the global index tells Oracle with partition to probe for each row that is being retrieved.

  • Thanks, that makes sense. I'll be even more grateful if you can also tell me if there's a way to make Oracle show me exactly which partitions were accessed. – jubil Oct 14 '14 at 13:11
  • 2
    @jubil - Short of tracing the session, I don't know of a way to do that. – Justin Cave Oct 14 '14 at 13:46

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.