Qurey rewrite is rewrite query.

<!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:DrawingGridVerticalSpacing>7.8 磅</w:DrawingGridVerticalSpacing> <w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery> <w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:SpaceForUL/> <w:BalanceSingleByteDoubleByteWidth/> <w:DoNotLeaveBackslashAlone/> <w:ULTrailSpace/> <w:DoNotExpandShiftReturn/> <w:AdjustLineHeightInTable/> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!-- [if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->

To speed up the query the Oracle will choose the materialized view automatically if the parameter take effect.

SQL> create MATERIALIZED VIEW SCOTT.mv1

2 tablespace users

3 BUILD IMMEDIATE

4 REFRESH COMPLETE

5 ENABLE QUERY REWRITE

6 AS select e.ename,d.loc from scott.emp e,scott.dept d where e.deptno=d.deptno;

实体化视图已创建。

SQL> set autot traceonly explain

SQL> select e.ename,d.loc from scott.emp e,scott.dept d where e.deptno=d.deptno;

SQL> set lines 345

SQL> /

执行计划

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

Plan hash value: 2958490228

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

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

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

| 0 | SELECT STATEMENT | | 14 | 210 | 3 (0)| 00:00:01 |

| 1 | MAT_VIEW REWRITE ACCESS FULL| MV1 | 14 | 210 | 3 (0)| 00:00:01 |

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

Note

-----

- dynamic sampling used for this statement (level=2)

Close the query rewrite option.

SQL> ALTER SESSION SET query_rewrite_enabled =false;

会话已更改。

SQL> select e.ename,d.loc from scott.emp e,scott.dept d where e.deptno=d.deptno;

执行计划

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

Plan hash value: 844388907

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

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

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

| 0 | SELECT STATEMENT | | 14 | 280 | 6 (17)| 00:00:01 |

| 1 | MERGE JOIN | | 14 | 280 | 6 (17)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 2 (0)| 00:00:01 |

| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |

|* 4 | SORT JOIN | | 14 | 126 | 4 (25)| 00:00:01 |

| 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("E"."DEPTNO"="D"."DEPTNO")

filter("E"."DEPTNO"="D"."DEPTNO")