Query Rewrite problem as of 11.2.0.4

      2 Comments on Query Rewrite problem as of 11.2.0.4

Here is an interesting tips in 11.2.0.4 and later (tested also in 12c),

Simple example that shows Query rewrite using a materialized view.

sqlplus scott/tiger

SQL>create materialized view TEST_MV2 enable query rewrite as select * from DEPT join EMP using (DEPTNO);

SQL>alter session set query_rewrite_enabled=true;

SQL>set autotrace traceonly explain

SQL> select * from DEPT join EMP using (DEPTNO);

Execution Plan
———————————————————-
Plan hash value: 1155595132

——————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————-
| 0 | SELECT STATEMENT | | 14 | 756 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| TEST_MV2 | 14 | 756 | 3 (0)| 00:00:01 |
——————————————————————————————-

But

SQL> select ename from DEPT join EMP using (DEPTNO);

Execution Plan
———————————————————-
Plan hash value: 3956160932

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 14 | 126 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“EMP”.”DEPTNO” IS NOT NULL)

 

No Rewrite!

 

SQL> select blocks from user_tables where table_name=’EMP’;

BLOCKS
———-
5

SQL> c/EMP/TEST_MV2
1* select blocks from user_tables where table_name=’TEST_MV2′
SQL> /

BLOCKS
———-
4

The Materialized view path should be less I/O expensive…

In 11.2.0.3 the rewrite is done in both cases.

Thanks for your feedbacks

jko

 

The following two tabs change content below.

Jacques

I am Oracle Certified Master 11g & 12c database architect with significant experience in heterogeneous environments, and strong ability to lead complex and critical projects requiring multiple technical implementations. at Trivadis SA

2 thoughts on “Query Rewrite problem as of 11.2.0.4

  1. Franck Pachot

    Hello Jacques,
    Oracle Support has been able to reproduce my testcase case and opened bug# 17651484 with Oracle Development.
    As often, a severity 4 SR was answered very fast 🙂
    Cheers,
    Franck

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *