Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

View merging for Oracle query transformation

2025-01-25 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

one。 Simple view merge: a view merge for a target sql that does not contain external joins and that defines a sql statement that does not contain aggregate functions such as distinct,group by.

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'

View merge:

SELECT t1.prod_id, t1.prod_name FROM products T1 WHERE t1.prod_id = view_1.prod_id 4 AND t1.prod_list_price > 1000 Execution Plan---Plan hash value: 3569 238377 Murray- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |- -| 0 | SELECT STATEMENT | | 213K | 10m | 648m (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 | 145k | 00:00:02 | | | | * 7 | HASH JOIN | | 213K | 9160K | 528K (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 | 523K (1) | 00:00:07 | 1 | 28 |- -

Disable view merging:

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 Murray- | 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 | 119K | 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 | 145K | 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')

In general, if oracle does not choose to merge a target sql with a view, the keyword "view" will be seen in the sql's execution plan, and the value of the Name column corresponding to the keyword is the name of the view.

Never think that as long as the VIEW keyword appears in the execution plan, it means that oracle must not do view merging, this is not necessarily, because there are other special cases, in these cases, even if oracle does view merging, the corresponding VIEW keyword will appear in the middle of the execution plan, such as the temporary view that appears after inde_jion, for the target sql that meets the simple conditional view merging conditions. Oracle always merges views on it, regardless of cost.

Second, external join view merge: it refers to the view merge for the target sql that uses external joins and the view definition sql statement with the view does not contain aggregate functions such as distinct,group by. The use of an outer join here means that an outer join is used between the table and the view of the external query, or in the view definition sql statement of that view. Limitations of external join views: when the target view is externally joined with the table of the external query, the precondition for merging the target view is that either the view is used as the drive table of the outer join, or the view is used as the driven table of the outer join, but its view definition contains a table in the sql statement.

SELECT t1.prod_id, t1.prod_name FROM products T1 views 2 3 WHERE t1.prod_id (+) = view_2.prod_id 594703 rows selected.Execution Plan---Plan hash value: 523667190Murray- | 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 | 119K | 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 | 189 K | 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 |- -

After changing the driven table, there is no view merge (and the view definition sql has 2 table associations):

SELECT t1.prod_id, t1.prod_name FROM products T1 views 2 3 WHERE t1.prod_id = view_2.prod_id (+) 4 5 SELECT t1.prod_id, t1.prod_name FROM products T1 3 WHERE t1.prod_id = view_2.prod_id (+) 4 594703 rows selectedExecution Plan---Plan hash value: 2735117096 Murray- | 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 | 119K | | 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 | 145K | 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 |-- -

When there is only one table in the view definition sql:

You can merge views:

Create or replace view view_3 asSELECT t2.prod_id FROM sales T2 WHERE t2.amount_sold > 700SELECT t1.prod_id, t1.prod_name FROM products T1 camera 3 3 WHERE t1.prod_id = view_3.prod_id (+) 39317 rows selected.Execution Plan---Plan hash value: 1313708566 Murray- -| 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 |-- -

three。 Complex view merging:

Complex view merging (Complex View Mergin) is a view merge that defines the target sql of group by or distinct in the sql statement of the view with the view.

Like simple view merging and externally joined view merging, view merging of complex views with group by or distinct in the view definition sql statement also means taking apart the definition sql statement and merging the base table with the table in the external query, which usually means that the group by or distinct operation in the above view definition sql statement will be delayed, that is, the table join will usually be done first. Then do the group by or distinct operation, instead of doing the group by or distinct operation inside the view as you did without complex view merging, and then join the table in the external query.

Delays in group by or distinct operations corresponding to complex view merging do not always lead to improvements in execution efficiency and performance. For example, if the group by or distinct operation can filter out most of the data and the table join does not filter the data effectively, then it is more efficient to do the group by or distinct operation inside the view, and then do the table join with the table in the external query, but if the table join can filter most of the data and the group by or distinct operation does not filter the data effectively, then do the table join first It is obviously more efficient to perform group by or distinct operations.

It is precisely because the merging of complex views does not always improve the execution efficiency and performance of the target sql, so in oracle 10g and later versions, corresponding to the merging of complex views, only when the cost value of the equivalent sql after the merging of complex views is less than that of the original sql, oracle will perform the view merging of complex sql to the target 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 |

-

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 263

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report