Oracle implicit conversions affect materialized view query overrides

Today, someone asked me a materialized view query rewrite problem, finally found that the problem and materialized view of the function is not much relationship, but the implicit conversion caused by the problem.

Let's illustrate the problem by example:

Sql> CREATE TABLE T (

2 ID number,

3 time Date,

4 other VARCHAR2 (4000))

5 partition by range (time)

6 (partition P1 values less than (to_date (' 2008-1-1 ', ' yyyy-mm-dd ')),

7 partition P2 values less than (to_date (' 2009-1-1 ', ' yyyy-mm-dd ')),

8 partition P3 values less than (to_date (' 2010-1-1 ', ' yyyy-mm-dd ')),

9 partition P4 values less than (to_date (' 2011-1-1 ', ' yyyy-mm-dd '));

Table created.

sql> INSERT INTO T

2 Select RowNum, Sysdate-rownum, Lpad (' A ', 4000, ' a ')

3 from Dba_objects;

76162 rows created.

Sql> create materialized view Log on T

2 with ROWID, sequence

3 (ID, time)

4 including new values;

Materialized view log created.

Sql> Create materialized View mv_t

2 Refresh Fast

3 Enable query rewrite as

4 Select Time, COUNT (*)

5 from T

6 Group by Time;

Materialized view created.

Here's a look at whether materialized views can query overrides:

Sql> set AUTOT on exp

Sql> Select Time, COUNT (*)

2 from T

3 Where time > To_date (' 2009-1-1 ', ' yyyy-mm-dd ')

4 and Time < To_date (' 2009-1-10 ', ' yyyy-mm-dd ')

5 Group by Time;

Time COUNT (*)

-------------- ----------

April-January-09 1

September-January-09 1

January-January-09 1

May-January-09 1

March-January-09 1

February-January-09 1

August-January-09 1

July-January-09 1

June-January-09 1

9 rows selected.

Execution Plan

----------------------------------------------------------

Plan Hash value:1712400360

-------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

-------------------------------------------------------------------------------------

| 0 |      SELECT STATEMENT |     |   9 |    198 | 33 (4) | 00:00:01 |

|* 1 | Mat_view REWRITE ACCESS full|     mv_t |   9 |    198 | 33 (4) | 00:00:01 |

-------------------------------------------------------------------------------------

predicate information (identified by Operation ID):