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))
Mark Hoxey 