溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

Oracle查詢轉(zhuǎn)換之視圖合并

發(fā)布時(shí)間：2020-04-17 13:04:59 來源：網(wǎng)絡(luò) 閱讀：1400 作者：llc018198 欄目：關(guān)系型數(shù)據(jù)庫

一.簡單視圖合并：指針對(duì)那些不含外連接，以及所帶視圖定義sql語句中不含distinct，group by等聚合函數(shù)的目標(biāo)sql的視圖合并。

create or replace view view_1 as
SELECT 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并沒有選擇對(duì)帶視圖的目標(biāo)sql執(zhí)行合并的話，那么該sql的執(zhí)行計(jì)劃中就會(huì)見到“view”關(guān)鍵字，并且該關(guān)鍵字所對(duì)應(yīng)的Name列的值就是該視圖的名稱.

千萬 不要認(rèn)為只要執(zhí)行計(jì)劃中出現(xiàn)了VIEW關(guān)鍵字就表明oracle一定沒有做視圖合并，這是不一定的，因?yàn)檫€存在著其他的一些特殊情形，在這些情形下，即使oracle做了視圖合并，其對(duì)應(yīng)執(zhí)行計(jì)劃中葉會(huì)出現(xiàn)VIEW關(guān)鍵字，比如inde_jion后面出現(xiàn)的臨時(shí)視圖，對(duì)于符合簡單條件視圖合并條件的目標(biāo)sql。oracle始終會(huì)對(duì)其進(jìn)行視圖合并，不管成本。

二:外連接視圖合并：是指針對(duì)那些使用了外連接，以及所帶視圖的視圖定義sql語句中不含distinct，group by等聚合函數(shù)的目標(biāo)sql的視圖合并。這里使用外連接的含義指外部查詢的表和視圖之間使用了外連接，或該視圖的視圖定義sql語句中使用了外連接。外連接視圖的限制：當(dāng)目標(biāo)視圖和外部查詢的表做外連接時(shí)，該目標(biāo)視圖可以做外連接視圖合并的前提條件是，要么該視圖被作為外連接的驅(qū)動(dòng)表，要么該視圖雖然被作為外連接的被驅(qū)動(dòng)表但它的視圖定義的sql語句中指包含一個(gè)表。

   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 |
------------------------------------------------------------------------------------------------------------------------

更改驅(qū)動(dòng)表后，沒有進(jìn)行視圖合并（且視圖定義sql有2個(gè)表關(guān)聯(lián)）：

 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 selected
Execution 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 |
-------------------------------------------------------------------------------------------------------------------------

當(dāng)視圖的定義sql中只有一個(gè)表：

可以進(jìn)行視圖合并：

 create or replace view view_3 as
SELECT 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 |
-------------------------------------------------------------------------------------------------

三.復(fù)雜視圖合并：

復(fù)雜視圖合并（Complex View Mergin）是針對(duì)那些所帶視圖的視圖定義sql語句中中含有g(shù)roup by或distinct的目標(biāo)sql的視圖合并.

    和簡單視圖合并，外連接視圖合并一樣，對(duì)這種視圖定義sql語句中含有g(shù)roup by或distinct的復(fù)雜視圖做視圖合并也同樣意味著要把其定義sql語句拆開，并把其中的基表和外部查詢中的表合并，這通常意味著上述視圖定義sql語句中的group by或distinct操作會(huì)被推遲執(zhí)行，也就是說，這種情況下通常會(huì)先做表連接，再做group by 或distinct操作，而不是像未做復(fù)雜視圖合并時(shí)那樣在視圖內(nèi)部做完group by或distinct操作，然后才和外部查詢中的表做表連接。

    復(fù)雜視圖合并所對(duì)應(yīng)的group by 或distinct操作延遲，并不一定總是能帶來執(zhí)行效率以及性能上的提升。例如，如果group by或distinct操作能過濾掉絕大部分的數(shù)據(jù)且表連接并不能有效過濾數(shù)據(jù)的話，那么先在視圖內(nèi)部做group by或者distinct操作，然后和外部查詢中的表做表連接的執(zhí)行效率會(huì)更高些，但是如果表連接能過濾絕大部分?jǐn)?shù)據(jù)而group by或distinct操作并不能有效過濾數(shù)據(jù)的話，那么先做表連接，在做group by或distinct的操作的執(zhí)行效率顯然會(huì)更好一些。

    正是因?yàn)閺?fù)雜視圖合并并不一定總是能帶來目標(biāo)sql的執(zhí)行效率及性能上得提升，所以oracle 10g及以后的版本中，對(duì)應(yīng)復(fù)雜視圖的合并，只有當(dāng)經(jīng)過復(fù)雜視圖合并后等價(jià)sql的成本值小于原sql的成本值時(shí)，oracle才會(huì)對(duì)目標(biāo)sql執(zhí)行復(fù)雜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 |       |       |

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


向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手