I was discussing with my friend on date filters and how they impact the performance, thought this topic is worth blogging.
With tables holding historic data, you would see columns 'Effective Date or termination date' which would logically mark the record as inactive and also helps to pick the current active record. Again few store NULL or any futuristic date in these columns to determine the active status. Now from this perspective let us see how date formatting can impact the optimizer decisions
Let us walk through a set of SQL which has date filters,
At the very first look was not convinced with the plan, because i know the total number of active records where 26721 and not 4454, so let us see what is actual and expected.
Yes the actual cardinality is 26721. Now am not going to SQL Profiles to go further to validate the plan because my first bet is always on the data,application design, the first question is why this SQL and what use the operator '>' .
The SQL intent is to pick active trades whose end timestamp is 01-01-9999, so let us say this to Oracle and see how this overcomes the trouble created by the operator '> systimestamp' that is sky is the limit :)
Now with effective end timestamp values
Though the cardinality is still not right, we could see a change in the access method - index is used.
Now same query with a different date formatting
Both timestamp and to_timestamp yield the same results in terms of access path. So avoid using sky limit (> systimestamp) in your SQL's if you know what you are looking for. SQL's like these do they impact partitions, watch out for the next post.
With tables holding historic data, you would see columns 'Effective Date or termination date' which would logically mark the record as inactive and also helps to pick the current active record. Again few store NULL or any futuristic date in these columns to determine the active status. Now from this perspective let us see how date formatting can impact the optimizer decisions
Let us walk through a set of SQL which has date filters,
select * from tbl_objects where effective_end_ts > systimestamp Plan hash value: 2402742835 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 563 (100)| | |* 1 | TABLE ACCESS FULL| TBL_OBJECTS | 4454 | 695K| 563 (1)| 00:00:01 | ---------------------------------------------------------------------------------
select /*+ gather_plan_statistics */ * from tbl_objects where effective_end_ts > systimestamp Plan hash value: 2402742835 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 26721 |00:00:00.14 | 2539 | |* 1 | TABLE ACCESS FULL| TBL_OBJECTS | 1 | 4454 | 26721 |00:00:00.14 | 2539 | -------------------------------------------------------------------------------------------
The SQL intent is to pick active trades whose end timestamp is 01-01-9999, so let us say this to Oracle and see how this overcomes the trouble created by the operator '> systimestamp' that is sky is the limit :)
Now with effective end timestamp values
select * from tbl_objects where effective_end_ts = timestamp '9999-01-01 00:00:00' Plan hash value: 1709553517 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TBL_OBJECTS | 1 | 160 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_EFF_TS | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------
Now same query with a different date formatting
select * from tbl_objects where effective_end_ts =
(to_timestamp('01/01/9999 00:00:00','dd/mm/yyyy hh24:mi:ss'))
Plan hash value: 1709553517
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TBL_OBJECTS | 1 | 160 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EFF_TS | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
