Cute partition elimination trick

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s