Query Rewrite problem as of 11.2.0.4

      5 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
 


     

Leave a Reply

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