Oracle查询转换之视图合并
一.简单视图合并：指针对那些不含外连接，以及所带视图定义sql语句中不含distinct，group by等聚合函数的目标sql的视图合并。
create or replace view view_1 asSELECT t2.prod_id FROM sales t2, customers t3 WHERE t2.cust_id = t3.cust_id AND t3.cust_gender = 'M';
视图合并：
SELECT t1.prod_id, t1.prod_name FROM products t1,view_1 WHERE t1.prod_id = view_1.prod_id 4 AND t1.prod_list_price > 1000;Execution Plan----------------------------------------------------------Plan hash value: 3569238377-----------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |-----------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 213K| 10M| 648(2)| 00:00:08 | | ||* 1 | HASH JOIN | | 213K| 10M| 648(2)| 00:00:08 | | ||* 2 | VIEW | index$_join$_004 | 27750 | 189K| 119(1)| 00:00:02 | | ||* 3 | HASH JOIN | | | | | | | || 4 | BITMAP CONVERSION TO ROWIDS| | 27750 | 189K| 2(0)| 00:00:01 | | ||* 5 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_GENDER_BIX | | | | | | || 6 | INDEX FAST FULL SCAN | CUSTOMERS_PK | 27750 | 189K| 145(0)| 00:00:02 | | ||* 7 | HASH JOIN | | 213K| 9160K| 528(2)| 00:00:07 | | ||* 8 | TABLE ACCESS FULL | PRODUCTS | 17 | 595 | 3(0)| 00:00:01 | | || 9 | PARTITION RANGE ALL | | 918K| 8075K| 523(1)| 00:00:07 | 1 | 28 || 10 | TABLE ACCESS FULL | SALES | 918K| 8075K| 523(1)| 00:00:07 | 1 | 28 |-----------------------------------------------------------------------------------------------------------------------
禁用视图合并：
SELECT /*+ no_merge(view_1) */ t1.prod_id, t1.prod_name FROM products t1, view_1 WHERE t1.prod_id = view_1.prod_id AND t1.prod_list_price > 1000 ;10156 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 2109926904-------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows| Bytes | Cost (%CPU)| Time| Pstart| Pstop |-------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT || 213K| ×××K| 649 (2)| 00:00:08 ||||* 1 | HASH JOIN || 213K| ×××K| 649 (2)| 00:00:08 ||||* 2 | TABLE ACCESS FULL | PRODUCTS| 17 | 595 | 3 (0)| 00:00:01 |||| 3 | VIEW | VIEW_1| 918K| 11M| 644 (2)| 00:00:08 ||||* 4 | HASH JOIN || 918K| 14M| 644 (2)| 00:00:08 ||||* 5 | VIEW | index$_join$_004| 27750 | 189K| 119 (1)| 00:00:02 ||||* 6 | HASH JOIN |||| ||||| 7 | BITMAP CONVERSION TO ROWIDS|| 27750 | 189K| 2 (0)| 00:00:01 ||||* 8 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_GENDER_BIX ||| ||||| 9 | INDEX FAST FULL SCAN | CUSTOMERS_PK | 27750 | 189K| 145 (0)| 00:00:02 |||| 10 | PARTITION RANGE ALL || 918K| 8075K| 523 (1)| 00:00:07 | 1 | 28 || 11 | TABLE ACCESS FULL | SALES| 918K| 8075K| 523 (1)| 00:00:07 | 1 | 28 |-------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("T1"."PROD_ID"="VIEW_1"."PROD_ID") 2 - filter("T1"."PROD_LIST_PRICE">1000) 4 - access("T2"."CUST_ID"="T3"."CUST_ID") 5 - filter("T3"."CUST_GENDER"='M') 6 - access(ROWID=ROWID) 8 - access("T3"."CUST_GENDER"='M')一般来说，如果oracle并没有选择对带视图的目标sql执行合并的话，那么该sql的执行计划中就会见到"view"关键字，并且该关键字所对应的Name列的值就是该视图的名称.
千万 不要认为只要执行计划中出现了VIEW关键字就表明oracle一定没有做视图合并，这是不一定的，因为还存在着其他的一些特殊情形，在这些情形下，即使oracle做了视图合并，其对应执行计划中叶会出现VIEW关键字，比如inde_jion后面出现的临时视图，对于符合简单条件视图合并条件的目标sql。oracle始终会对其进行视图合并，不管成本。
二:外连接视图合并：是指针对那些使用了外连接，以及所带视图的视图定义sql语句中不含distinct，group by等聚合函数的目标sql的视图合并。这里使用外连接的含义指外部查询的表和视图之间使用了外连接，或该视图的视图定义sql语句中使用了外连接。外连接视图的限制：当目标视图和外部查询的表做外连接时，该目标视图可以做外连接视图合并的前提条件是，要么该视图被作为外连接的驱动表，要么该视图虽然被作为外连接的被驱动表但它的视图定义的sql语句中指包含一个表。
SELECT t1.prod_id, t1.prod_name FROM products t1,view_2 3 WHERE t1.prod_id(+) = view_2.prod_id;594703 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 523667190------------------------------------------------------------------------------------------------------------------------| Id | Operation| Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT| | 918K| 40M| 649 (2)| 00:00:08 | | ||* 1 | HASH JOIN RIGHT OUTER| | 918K| 40M| 649 (2)| 00:00:08 | | || 2 | TABLE ACCESS FULL| PRODUCTS | 72 | 2160 | 3 (0)| 00:00:01 | | ||* 3 | HASH JOIN| | 918K| 14M| 644 (2)| 00:00:08 | | ||* 4 | VIEW | index$_join$_004 | 27750 | 189K| 119 (1)| 00:00:02 | | ||* 5 | HASH JOIN| | | | | | | || 6 | BITMAP CONVERSION TO ROWIDS| | 27750 | 189K| 2 (0)| 00:00:01 | | ||* 7 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_GENDER_BIX | | | | | | || 8 | INDEX FAST FULL SCAN| CUSTOMERS_PK | 27750 | 189K| 145 (0)| 00:00:02 | | || 9 | PARTITION RANGE ALL| | 918K| 8075K| 523 (1)| 00:00:07 | 1 | 28 || 10 | TABLE ACCESS FULL| SALES | 918K| 8075K| 523 (1)| 00:00:07 | 1 | 28 |------------------------------------------------------------------------------------------------------------------------
更改驱动表后，没有进行视图合并（且视图定义sql有2个表关联）：
SELECT t1.prod_id, t1.prod_name FROM products t1,view_2 3 WHERE t1.prod_id = view_2.prod_id(+) 4 ； 5 SELECT t1.prod_id, t1.prod_name FROM products t1,view_2 3 WHERE t1.prod_id = view_2.prod_id(+) 4 ;594703 rows selectedExecution Plan----------------------------------------------------------Plan hash value: 2735117096-------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows| Bytes | Cost (%CPU)| Time| Pstart| Pstop |-------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT || 918K| 37M| 649 (2)| 00:00:08 ||||* 1 | HASH JOIN OUTER || 918K| 37M| 649 (2)| 00:00:08 |||| 2 | TABLE ACCESS FULL | PRODUCTS| 72 | 2160 | 3 (0)| 00:00:01 |||| 3 | VIEW | VIEW_2| 918K| 11M| 644 (2)| 00:00:08 ||||* 4 | HASH JOIN || 918K| 14M| 644 (2)| 00:00:08 ||||* 5 | VIEW | index$_join$_004| 27750 | 189K| 119 (1)| 00:00:02 ||||* 6 | HASH JOIN |||| ||||| 7 | BITMAP CONVERSION TO ROWIDS|| 27750 | 189K| 2 (0)| 00:00:01 ||||* 8 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_GENDER_BIX ||| ||||| 9 | INDEX FAST FULL SCAN | CUSTOMERS_PK | 27750 | 189K| 145 (0)| 00:00:02 |||| 10 | PARTITION RANGE ALL || 918K| 8075K| 523 (1)| 00:00:07 | 1 | 28 || 11 | TABLE ACCESS FULL | SALES| 918K| 8075K| 523 (1)| 00:00:07 | 1 | 28 |-------------------------------------------------------------------------------------------------------------------------
当视图的定义sql中只有一个表：
可以进行视图合并：
create or replace view view_3 asSELECT t2.prod_id FROM sales t2 WHERE t2.amount_sold>700 SELECT t1.prod_id, t1.prod_name FROM products t1,view_3 3 WHERE t1.prod_id = view_3.prod_id(+);39317 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1313708566-------------------------------------------------------------------------------------------------| Id | Operation | Name| Rows| Bytes | Cost (%CPU)| Time| Pstart| Pstop |-------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT || 560K| 20M| 531 (2)| 00:00:07 ||||* 1 | HASH JOIN OUTER || 560K| 20M| 531 (2)| 00:00:07 |||| 2 | TABLE ACCESS FULL | PRODUCTS | 72 | 2160 | 3 (0)| 00:00:01 |||| 3 | PARTITION RANGE ALL|| 560K| 4922K| 526 (2)| 00:00:07 | 1 | 28 ||* 4 | TABLE ACCESS FULL | SALES| 560K| 4922K| 526 (2)| 00:00:07 | 1 | 28 |-------------------------------------------------------------------------------------------------
三.复杂视图合并：
复杂视图合并（Complex View Mergin）是针对那些所带视图的视图定义sql语句中中含有group by或distinct的目标sql的视图合并.
和简单视图合并，外连接视图合并一样，对这种视图定义sql语句中含有group by或distinct的复杂视图做视图合并也同样意味着要把其定义sql语句拆开，并把其中的基表和外部查询中的表合并，这通常意味着上述视图定义sql语句中的group by或distinct操作会被推迟执行，也就是说，这种情况下通常会先做表连接，再做group by 或distinct操作，而不是像未做复杂视图合并时那样在视图内部做完group by或distinct操作，然后才和外部查询中的表做表连接。
复杂视图合并所对应的group by 或distinct操作延迟，并不一定总是能带来执行效率以及性能上的提升。例如，如果group by或distinct操作能过滤掉绝大部分的数据且表连接并不能有效过滤数据的话，那么先在视图内部做group by或者distinct操作，然后和外部查询中的表做表连接的执行效率会更高些，但是如果表连接能过滤绝大部分数据而group by或distinct操作并不能有效过滤数据的话，那么先做表连接，在做group by或distinct的操作的执行效率显然会更好一些。
正是因为复杂视图合并并不一定总是能带来目标sql的执行效率及性能上得提升，所以oracle 10g及以后的版本中，对应复杂视图的合并，只有当经过复杂视图合并后等价sql的成本值小于原sql的成本值时，oracle才会对目标sql执行复杂sql的视图合并。
SQL> set lines 200 pagesize 1000
SELECT t1.cust_id, t1.cust_last_name
FROM customers t1, products t2, view_3 t3
WHERE t1.cust_id = t3.cust_id
AND t2.prod_id = t3.prod_id
AND t3.total > 1000
AND t2.prod_category = 'Hardware'
AND t1.cust_year_of_birth = 1977
7 8 AND t1.cust_marital_status = 'married';
32 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3286306050
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 61 | 5002 | 544 (2)| 00:00:07 | | |
|* 1 | FILTER | | | | | | | |
| 2 | HASH GROUP BY | | 61 | 5002 | 544 (2)| 00:00:07 | | |
|* 3 | HASH JOIN | | 1201 | 98482 | 543 (2)| 00:00:07 | | |
| 4 | TABLE ACCESS BY INDEX ROWID | PRODUCTS | 14 | 462 | 3 (0)| 00:00:01 | | |
|* 5 | INDEX RANGE SCAN | PRODUCTS_PROD_CAT_IX | 14 | | 1 (0)| 00:00:01 | | |
|* 6 | HASH JOIN | | 6007 | 287K| 540 (2)| 00:00:07 | | |
| 7 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 46 | 1610 | 13 (0)| 00:00:01 | | |
| 8 | BITMAP CONVERSION TO ROWIDS| | | | | | | |
| 9 | BITMAP AND | | | | | | | |
|* 10 | BITMAP INDEX SINGLE VALUE| CUSTOMERS_YOB_BIX | | | | | | |
|* 11 | BITMAP INDEX SINGLE VALUE| CUSTOMERS_MARITAL_BIX | | | | | | |
| 12 | PARTITION RANGE ALL | | 918K| 12M| 525 (2)| 00:00:07 | 1 | 28 |
| 13 | TABLE ACCESS FULL | SALES | 918K| 12M| 525 (2)| 00:00:07 | 1 | 28 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUM("AMOUNT_SOLD")>1000)
3 - access("T2"."PROD_ID"="PROD_ID")
5 - access("T2"."PROD_CATEGORY"='Hardware')
6 - access("T1"."CUST_ID"="CUST_ID")
10 - access("T1"."CUST_YEAR_OF_BIRTH"=1977)
11 - access("T1"."CUST_MARITAL_STATUS"='married')
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
1914 consistent gets
1619 physical reads
0 redo size
1461 bytes sent via SQL*Net to client
546 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32 rows processed
SELECT /*+ no_merge(t3) */t1.cust_id, t1.cust_last_name
FROM customers t1, products t2, view_3 t3
WHERE t1.cust_id = t3.cust_id
AND t2.prod_id = t3.prod_id
AND t3.total > 1000
AND t2.prod_category = 'Hardware'
AND t1.cust_year_of_birth = 1977
8 AND t1.cust_marital_status = 'married';
32 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 215761499
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 70 | 550 (6)| 00:00:07 | | |
| 1 | NESTED LOOPS | | 1 | 70 | 550 (6)| 00:00:07 | | |
| 2 | NESTED LOOPS | | 1 | 70 | 550 (6)| 00:00:07 | | |
| 3 | NESTED LOOPS | | 1 | 47 | 549 (6)| 00:00:07 | | |
| 4 | VIEW | VIEW_3 | 1 | 26 | 548 (6)| 00:00:07 | | |
|* 5 | FILTER | | | | | | | |
| 6 | HASH GROUP BY | | 1 | 14 | 548 (6)| 00:00:07 | | |
| 7 | PARTITION RANGE ALL | | 918K| 12M| 525 (2)| 00:00:07 | 1 | 28 |
| 8 | TABLE ACCESS FULL | SALES | 918K| 12M| 525 (2)| 00:00:07 | 1 | 28 |
|* 9 | TABLE ACCESS BY INDEX ROWID| PRODUCTS | 1 | 21 | 1 (0)| 00:00:01 | | |
|* 10 | INDEX UNIQUE SCAN | PRODUCTS_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 11 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 12 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 23 | 1 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------
