Implicit Timestamp Conversions… yet again

After blogging about implicit date/timestamp conversions not once but twice you would like to think that I’m fully aware of the problems and would never stumble over them myself. Wrong!

Okay, in my defence, it was only an ad-hoc query in a test environment that I was running but it raises yet another scenario that’s worthy of a short post. The table I was querying was range partitioned on a timestamp column with each partition just one day. I wanted to query just the last 4 days so my query resembled:

SELECT *
FROM   daily_partitioned_table
WHERE  tstamp >= SYSTIMESTAMP - INTERVAL '4' DAY

What could be simpler, right? Well, after waiting for a couple of minutes for a result I realised something was wrong. If we look at the execution plan for such a query we will find the problem:

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
Plan hash value: 620599123

--------------------------------------------------------------------------------------
| Id  | Operation           | Name                    | Rows  | Bytes |Pstart| Pstop |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                         |    25 |   375 |      |       |
|   1 |  PARTITION RANGE ALL|                         |    25 |   375 |    1 |1048575|
|*  2 |   TABLE ACCESS FULL | DAILY_PARTITIONED_TABLE |    25 |   375 |    1 |1048575|
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("TSTAMP"))>=SYS_EXTRACT_UTC(SYSTIMESTAMP(6)-INTERVAL'+0
              4 00:00:00' DAY(2) TO SECOND(0)))

From here it’s quite simple to see what’s happening; Oracle did not do any partition elimination as shown by the Pstart and Pstop columns. The reason for this is explained by the predicate details. Oracle is applying the functions SYS_EXTRACT_UTC(INTERNAL_FUNCTION(“TSTAMP”)) to the TSTAMP column, which thereby prevents partition elimination from occurring. A quick look up of documentation for SYSTIMESTAMP reveals the return data type to be TIMESTAMP WITH TIME ZONE. So, Oracle’s approach to resolving the query is to implicitly convert the TSTAMP column to TIMESTAMP WITH TIME ZONE, rather than converting the SYSTIMESTAMP expression to a TIMESTAMP data type.

Of course the solution was trivial:

SELECT *
FROM   daily_partitioned_table
WHERE  tstamp >= CAST(SYSTIMESTAMP - INTERVAL '4' DAY AS TIMESTAMP)

We can now see that partition elimination has taken place, as shown by the Pstart column containing the value of KEY:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 4082052782

-------------------------------------------------------------------------------------------
| Id  | Operation                | Name                    | Rows  | Bytes |Pstart| Pstop |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                         |    69 |  1035 |   |          |
|   1 |  PARTITION RANGE ITERATOR|                         |    69 |  1035 |  KEY |1048575|
|*  2 |   TABLE ACCESS FULL      | DAILY_PARTITIONED_TABLE |    69 |  1035 |  KEY |1048575|
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("TSTAMP">=CAST(SYSTIMESTAMP(6)-INTERVAL'+04 00:00:00' DAY(2) TO SECOND(0) AS TIMESTAMP))
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