oracle 组合视图,查询转换之视图合并

视图合并常常发生在当外部查询块的谓语出现下列项的时候

能够在另一个查询块的索引中使用的列

能够在另一个查询块的分区截断中使用的列

在一个连接视图中能够返回限制行数

SQL> set autotrace traceonly  exp stat;

SQL> select * from t1 ,(select * from t2) v where t1.object_id=v.object_id and v.object_id>10;

已选择49843行。

执行计划

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

Plan hash value: 2959412835

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

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

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

|   0 | SELECT STATEMENT   |      | 45076 |  6955K|       |   583   (2)| 00:00:08 |

|*  1 |  HASH JOIN         |      | 45076 |  6955K|  4008K|   583   (2)| 00:00:08 |

|*  2 |   TABLE ACCESS FULL| T2   | 45075 |  3477K|       |    55   (4)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| T1   | 63719 |  4915K|       |    56   (6)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

2 - filter("T2"."OBJECT_ID">10)

3 - filter("T1"."OBJECT_ID">10)

Note

-----

- dynamic sampling used for this statement

统计信息

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

0  recursive calls

0  db block gets

3792  consistent gets

0  physical reads

0  redo size

3379156  bytes sent via SQL*Net to client

36927  bytes received via SQL*Net from client

3324  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

49843  rows processed

SQL> select /*+ NO_QUERY_TRANSFORMATION */ * from t1 ,(select * from t2) v where t1.object_id=v.object_id and v.object_id>10;

已选择49843行。

执行计划

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

Plan hash value: 157564253

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

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

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

|   0 | SELECT STATEMENT    |      | 45075 |  6954K|       |   583   (2)| 00:00:07 |

|*  1 |  HASH JOIN          |      | 45075 |  6954K|  4008K|   583   (2)| 00:00:07 |

|   2 |   VIEW              |      | 45075 |  3477K|       |    55   (4)| 00:00:01 |

|*  3 |    TABLE ACCESS FULL| T2   | 45075 |  3477K|       |    55   (4)| 00:00:01 |

|   4 |   TABLE ACCESS FULL | T1   | 63719 |  4915K|       |    55   (4)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("T1"."OBJECT_ID"="V"."OBJECT_ID")

3 - filter("T2"."OBJECT_ID">10)

Note

-----

- dynamic sampling used for this statement

统计信息

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

12  recursive calls

0  db block gets

3948  consistent gets

0  physical reads

0  redo size

3379156  bytes sent via SQL*Net to client

36927  bytes received via SQL*Net from client

3324  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

49843  rows processed

SQL> select  * from t1 ,(select /*+ NO_MERGE */ * from t2) v where t1.object_id=v.object_id and v.object_id>10;

已选择49843行。

执行计划

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

Plan hash value: 157564253

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

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

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

|   0 | SELECT STATEMENT    |      | 45075 |  6954K|       |   583   (2)| 00:00:07 |

|*  1 |  HASH JOIN          |      | 45075 |  6954K|  4008K|   583   (2)| 00:00:07 |

|   2 |   VIEW              |      | 45075 |  3477K|       |    55   (4)| 00:00:01 |

|*  3 |    TABLE ACCESS FULL| T2   | 45075 |  3477K|       |    55   (4)| 00:00:01 |

|   4 |   TABLE ACCESS FULL | T1   | 63719 |  4915K|       |    55   (4)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("T1"."OBJECT_ID"="V"."OBJECT_ID")

3 - filter("T2"."OBJECT_ID">10)

Note

-----

- dynamic sampling used for this statement

统计信息

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

10  recursive calls

0  db block gets

3948  consistent gets

0  physical reads

0  redo size

3379156  bytes sent via SQL*Net to client

36927  bytes received via SQL*Net from client

3324  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

49843  rows processed

如上 NO_QUERY_TRANSFORMATION，NO_MERGE 可阻止合并

另外还有一些情况如果出现的话 也会阻止视图合并的发生。

如果一个查询块包含解析函数或聚合函数、集合运算(union、intersect、minus)，order by 子句或者使用了rownum，视图将会被禁止或限制。当然，即使出现了上述的情况也可以用merge强制合并

视图合并时通过隐藏参数 _complex_view_merging 控制的

SQL> set autotrace off;

SQL> set autotrace traceonly   exp;

SQL> select t1.* from t1,(select object_id,count(1) cnt from t2 group by object_id ) v where t1.object_id=v.object_id ;

执行计划

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

Plan hash value: 3819158848

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

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

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

|   0 | SELECT STATEMENT     |      | 45075 |  4049K|       |   448   (4)| 00:00:06 |

|*  1 |  HASH JOIN           |      | 45075 |  4049K|  1104K|   448   (4)| 00:00:06 |

|   2 |   VIEW               |      | 45075 |   572K|       |    61  (14)| 00:00:01 |

|   3 |    HASH GROUP BY     |      | 45075 |   572K|       |    61  (14)| 00:00:01 |

|   4 |     TABLE ACCESS FULL| T2   | 45075 |   572K|       |    54   (2)| 00:00:01 |

|   5 |   TABLE ACCESS FULL  | T1   | 63719 |  4915K|       |    55   (4)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("T1"."OBJECT_ID"="V"."OBJECT_ID")

Note

-----

- dynamic sampling used for this statement

SQL> select /*+ merge(v) */t1.* from t1,(select object_id,count(1) cnt from t2 group by object_id ) v where t1.object_id=v.object_id ;

执行计划

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

Plan hash value: 3719832502

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

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

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

|   0 | SELECT STATEMENT       |      | 45076 |  4578K|       |   458   (4)| 00:00:06 |

|   1 |  HASH GROUP BY         |      | 45076 |  4578K|       |   458   (4)| 00:00:06 |

|*  2 |   HASH JOIN            |      | 45076 |  4578K|  1104K|   452   (2)| 00:00:06 |

|   3 |    INDEX FAST FULL SCAN| IT2  | 45075 |   572K|       |    27   (4)| 00:00:01 |

|   4 |    TABLE ACCESS FULL   | T1   | 63719 |  5662K|       |    55   (4)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("T1"."OBJECT_ID"="OBJECT_ID")

Note

-----

- dynamic sampling used for this statement