分类： Oracle
2012-05-14 09:10:42
10g关于物化视图的执行计划较9i有较大变化，变化的关键词为“细化”.Oracle对于物化视图的执行计划细化，将物化视图的扫描和全表扫描区分开：
SQL> SET AUTOT ON EXP
SQL> SELECT COUNT(*) FROM MV_BASE;
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 3034976462
-------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Cost (%CPU)| TIME |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | MAT_VIEW ACCESS FULL| MV_BASE | 1 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Note
-----
- dynamic sampling used FOR this statement
SQL> SELECT /*+ REWRITE */ TYPE, COUNT(*) FROM T_BASE GROUP BY TYPE;
no ROWS selected
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1008429399
----------------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |更多Oracle视频教程分享：http://www.cuug.com/
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV_BASE | 1 | 30 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used FOR this statement
10g关于物化视图的执行计划较9i变化的优势就在于在9i以前，很难从执行计划中区分扫描的是表还是物化视图。用户可以更清楚的知道自己正在处理的是物化视图或表中的哪一个。
