No Query Rewrite (QSM-01112) When SYSDATE Comparison in WHERE Clause (Doc ID 1364498.1)

Last updated on JUNE 13, 2012

Applies to:

Oracle Server - Enterprise Edition - Version 8.1.7.4 to 11.2.0.3 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.

Symptoms

A query would rewrite when an explicit to_date comparison was made in the where clause, but would not rewrite if the 'to_date' conversion was replaced by 'sysdate.'

Here is the materialized view (mview):

CREATE MATERIALIZED VIEW SLC_IHC_MV_TEST
BUILD IMMEDIATE
REFRESH COMPLETE ON COMMIT
ENABLE QUERY REWRITE
AS
select
pk, dt
from
demo_data
where dt >= to_date('01-JAN-2011 00:00:00','DD-MON-YYYY HH24:MI:SS');


This query rewrites:

query:
'select pk,dt from demo_data where dt >= to_date(''01-JAN-2011'',''dd-mon-yyyy'')'

STATEMENT_ID SEQUENCE MESSAGE
------------------------------------------------------------------------------
--
------------------------------------------------------------------------------
--------------------------
20110816_T1 2
QSM-01033: query rewritten with materialized view, SLC_IHC_MV_TEST


This query does not rewrite (note that SYSDATE had a value of '09-SEP-2011', which should be satisfied by the mview):

query:
'select pk, dt from demo_data where dt >= sysdate';

STATEMENT_ID SEQUENCE MESSAGE
------------------------------------------------------------------------------
--
------------------------------------------------------------------------------
--------------------------
20110816_T2 2
QSM-01112: WHERE clause of mv, SLC_IHC_MV_TEST, is more restrictive than query


Additionally, an explain plan of a simple select statement seems to show that value of SYSDATE is being evaluated, since the plan changes when the comparison operator changes:

=============================
No ROWS example (future records only)
select * from sh.sales where time_id > sysdate
=============================

Plan hash value: 2294783259
-----------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR | | 1 | 29 | 2 (0)| 00:00:01 | KEY | 28 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 1 | 29 | 2 (0)| 00:00:01 | KEY | 28 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 4 | BITMAP INDEX RANGE SCAN | SALES_TIME_BIX | | | | | KEY | 28 |
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TIME_ID">SYSDATE@!)
filter("TIME_ID">SYSDATE@!)


>>=============================
>>
Whole Table Example
>>
select * from sh.sales where time_id < sysdate
>>>
=============================
>>

>>Plan hash value: 279964487
>>
----------------------------------------------------
>>
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart|
>>> Pstop |
>> ----------------------------------------------------
>>
| 0 | SELECT STATEMENT | | 918K| 25M| 508 (5)| 00:00:07 | | |
>>
| 1 | PARTITION RANGE ITERATOR| | 918K| 25M| 508 (5)| 00:00:07 | 1 |
>> KEY |
>>
|* 2 | TABLE ACCESS FULL | SALES | 918K| 25M| 508 (5)| 00:00:07 | 1 |
>> KEY|

>>---------------------------------------------------

>>
>>Predicate Information (identified by operation id):
>>
---------------------------------------------------
>>
2 - filter("TIME_ID"<SYSDATE@!)>>


Question:
Therefore, shouldn't the value of SYSDATE in the query be evaluated when determining if the query will or will not rewrite?

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms