Query Rewrite Feature against Flashback Query

1.   Symptoms

Our monthly reports are taking 45 minutes.  The SQL should be able to take advantage of materialized views but this isn’t happening because they contain a flashback query clause like ‘as of timestamp’.

If the ‘as of timestamp’ clause is removed, the reports successfully use query rewrite.

2.   Description

The following demo test is used to reproduce this issue.

—  Create a base table for materialized view

SQL> CREATE TABLE T0001 AS SELECT * FROM DBA_OBJECTS;

—  Create materialized view log for Fast Refresh

SQL> CREATE MATERIALIZED VIEW LOG ON T0001 WITH ROWID, SEQUENCE (OBJECT_ID, OWNER) INCLUDING NEW VALUES;

—  Create materialized  for Query Rewrite

SQL> CREATE MATERIALIZED VIEW T0001_MV REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT SUM (OBJECT_ID) C1, OWNER C2 FROM T0001 GROUP BY OWNER;

SQL> SET AUTOTRACE TRACE EXPLAIN

SQL> SELECT SUM (OBJECT_ID), OWNER FROM T0001 GROUP BY OWNER;

Execution Plan
----------------------------------------------------------
Plan hash value: 2783142599
-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     8 |   104 |     3   (0)| 00:00:01 |
|   1 | MAT_VIEW REWRITE ACCESS FULL | T0001_MV |     8 |   104 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP

—————————————————————————

02/NOV/09 12:56:39.167518 AM +00:00

SQL> UPDATE T0001 SET OBJECT_ID = OBJECT_ID + 1 WHERE OWNER = ‘SYS’;

21011 rows updated.

SQL> COMMIT;

 

— Query Rewrite feature is DISABLED with Flashback Query (e.g.  AS OF TIMESTAMP)

SQL> SELECT SUM (OBJECT_ID), OWNER FROM T0001 AS OF TIMESTAMP TO_TIMESTAMP (‘2009-11-02 12:56′,’YYYY-MM-DD HH:MI’) GROUP BY OWNER;

Execution Plan
----------------------------------------------------------
Plan hash value: 748125282
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     6 |    66 |   552   (1)| 00:00:07 |
|   1 |  HASH GROUP BY    |       |     6 |    66 |   552   (1)| 00:00:07 |
|   2 |   TABLE ACCESS FULL| T0001 | 38491 |   413K|   550   (0)| 00:00:07 |

— Query Rewrite feature is DISABLED with flashback query even if with ‘REWRITE’ hint

SQL> SELECT /*+ REWRITE */ SUM (OBJECT_ID), OWNER FROM T0001 AS OF TIMESTAMP TO_TIMESTAMP (‘2009-11-02 12:56′,’YYYY-MM-DD HH:MI’) GROUP BY OWNER;

Execution Plan
----------------------------------------------------------
Plan hash value: 748125282
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     6 |    66 |   552   (1)| 00:00:07 |
|   1 |  HASH GROUP BY    |       |     6 |    66 |   552   (1)| 00:00:07 |
|   2 |   TABLE ACCESS FULL| T0001 | 38491 |   413K|   550   (0)| 00:00:07 |
----------------------------------------------------------------------------

— Confirm Query Rewrite feature is disabled with DBMS_MVIEW

— Clean REWRITE_TABLE for testing


SQL> DELETE FROM REWRITE_TABLE;

2 rows deleted.

 

— Confirm with Flashback Query ‘AS OF TIMESTAMP’

SQL> EXECUTE DBMS_MVIEW.EXPLAIN_REWRITE (‘SELECT SUM (OBJECT_ID), OWNER FROM T0001 AS OF TIMESTAMP TO_TIMESTAMP (”2009-11-02 12:56”,”YYYY-MM-DD HH:MI”) GROUP BY OWNER’);



PL/SQL procedure successfully completed.



— Check the Query Rewrite result for the above SQL statement

SQL> SELECT MESSAGE FROM REWRITE_TABLE ORDER BY SEQUENCE;

MESSAGE
----------------------------------------------------------------------------------------------------

QSM-01150: query did not rewrite

QSM-01304: query rewrite not possible because query is using flashback




SQL> DELETE FROM REWRITE_TABLE;

2 rows deleted.

— Confirm with Flashback Query with hint ‘REWRITE’

SQL> EXECUTE DBMS_MVIEW.EXPLAIN_REWRITE (‘SELECT /*+ REWRITE */ SUM (OBJECT_ID), OWNER FROM T0001 AS OF TIMESTAMP TO_TIMESTAMP (”2009-10-30 01:00”,”YYYY-MM-DD HH:MI”) GROUP BY OWNER’);

PL/SQL procedure successfully completed.

 

SQL> SELECT MESSAGE FROM REWRITE_TABLE ORDER BY SEQUENCE;

MESSAGE
----------------------------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01304: query rewrite not possible because query is using flashback
SQL> DELETE FROM REWRITE_TABLE;
2 rows deleted.

— Confirm with normal query with text matching

SQL> EXECUTE DBMS_MVIEW.EXPLAIN_REWRITE (‘SELECT SUM (OBJECT_ID), OWNER FROM T0001 GROUP BY OWNER’);

PL/SQL procedure successfully completed.


SQL> SELECT MESSAGE FROM REWRITE_TABLE ORDER BY SEQUENCE;

—————————————————————————————————-

QSM-01151: query was rewritten

QSM-01209: query rewritten with materialized view, T0001_MV, using text match algorithm

 

3.   Root Cause

Oracle Flashback Query does not support Query Rewrite feature on Materialized View from Oracle 9i to 11gR2.

It is documented in ‘Advanced Application Developer’s Guide 11g Release 2 (11.2)’ as

Oracle Flashback Query against a materialized view does not take advantage of query rewrite optimization.

This information is also documented in 9iR2 and 10gR2 Application Developer’s Guide.

 

4.   Solution

Is that a solution that can take advantage of query rewrite optimization when using flashback query against a materialized view?

To be continued…

Tags: , ,

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


%d bloggers like this: