Posted by I have a large partitioned table called TXN and I want to find the most recent transaction recorded, where the transaction timestamp column (TXN_TIMESTAMP) is the partition key. This table also happens to be sub-partitioned as well, but that’s not particular important for this example:
So we look at the execution plan and at first glance, it looks dire:
SQL> select max(TXN_TIMESTAMP) from TXN;
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 625K (2)| 02:05:03 | | |
| 1 | PARTITION RANGE ALL | | 1 | 15 | | | 142 | 1 |
| 2 | SORT AGGREGATE | | 1 | 15 | | | | |
| 3 | PARTITION LIST ALL | | 445M| 6368M| 625K (2)| 02:05:03 | KEY | KEY |
| 4 | TABLE ACCESS FULL | TXN | 445M| 6368M| 625K (2)| 02:05:03 | KEY | KEY |
------------------------------------------------------------------------------------------------------
Looks like it’s going to scan the whole table, but check Pstart/Pstop.
It’s going to start with the last partition (142) and keep moving upward until it hits partition 1 – which of course, it never will, so stops after scanning just the last partition
(Clarification: Obviously if the last partition is empty, then it will move onto the next-to-last partition and so forth).
Which is why it only takes…
SQL> select max(TXN_TIMESTAMP) from TXN;
MAX(TXN_TIMESTAMP)
-------------------------------
28-MAY-12 05.38.14.489075 AM
Elapsed: 00:00:01.11
…one second.
Nice.
Learning is not a spectator sport 