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

[book Review: Oracle query Optimization rewriting] Chapter 4

BLOG document structure diagram

I. 1 introduction

Technical enthusiasts, after reading this article, you can master the following skills, and you can also learn some other knowledge that you do not know, ~ O (∩ _ ∩) Olympiad:

Special usage of ① check

Use merge statements instead of update statements in ② sql optimization (focus)

If there are any mistakes or imperfections in this article, please correct them as much as you can, ITPUB or QQ. Your criticism is the biggest motivation of my writing.

I. 2 introduction to the experimental environment

Target library: 11.2.0.3 RHEL6.5

I. 3 preface

The links in the first three chapters refer to related links:

[book Review: Oracle query Optimization rewriting] Chapter 1 http://blog.itpub.net/26736162/viewspace-1652985/

[book Review: Oracle query Optimization rewriting] Chapter 2 http://blog.itpub.net/26736162/viewspace-1654252/

[book Review: Oracle query Optimization rewriting] Chapter 3 http://blog.itpub.net/26736162/viewspace-1660422/

Today, let's write the content of the fourth chapter of this book. The fourth chapter mainly talks about the correct use of the UPDATE sentence and when the UPDATE sentence should be rewritten to MERGE. The contents of the fourth chapter are as follows:

Chapter 4 insert, update and delete

4.1 insert a new record

4.2 prevent insertions on certain columns

4.3 definition and data of replication tables

4.4 restrict data entry with WITH CHECK OPTION

4.5 Multi-table insert statement

4.6 Update with values from other tables

4.7 merge records

4.8 Delete records that violate referential integrity

4.9 Delete records with duplicate names

Special usage of I. 4 check

We know that sysdate cannot be used for check constraints, but what happens when there is such a requirement? The following example can be solved by adding with check option to the view.

09:39:08 SQL > create table ttt (create_date date check (create_date > sysdate))

Create table ttt (create_date date check (create_date > sysdate))

*

ERROR at line 1:

ORA-02436: date or system variable wrongly specified in CHECK constraint

09:41:56 SQL > insert into (select empno,ename,hiredate from scott.emp where hiredate set autot on

10:28:05 SQL > alter table lhr.emp_bk add dname varchar2 (50) default 'noname'

The table has changed.

Time spent: 00: 00: 01.23

10:30:04 SQL > update lhr.emp_bk a

10:30:09 2 set a.dname = (select b.dname from lhr.dept_bk b where b.deptno=a.deptno and b.dname in ('ACCOUNTING','RESERCH'))

10:30:09 3 WHERE EXISTS (select 1 from lhr.dept_bk b where b.deptno=a.deptno and b.dname in ('ACCOUNTING','RESERCH'))

10:30:09 4

3 rows updated.

Time spent: 00: 00: 00.05

Carry out the plan

Plan hash value: 3525057516

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

| | 0 | UPDATE STATEMENT | | 4 | 544 | 28 (18) | 00:00:01 |

| | 1 | UPDATE | EMP_BK |

| | * 2 | HASH JOIN SEMI | | 4 | 544 | 8 (13) | 00:00:01 |

| | 3 | TABLE ACCESS FULL | EMP_BK | 14 | 1596 | 3 (0) | 00:00:01 |

| | * 4 | TABLE ACCESS FULL | DEPT_BK | 1 | 22 | 4 (0) | 00:00:01 |

| | * 5 | TABLE ACCESS FULL | DEPT_BK | 1 | 22 | 4 (0) | 00:00:01 |

Predicate Information (identified by operation id):

2-access ("B". "DEPTNO" = "A". "DEPTNO")

4-filter ("B". "DNAME" = 'ACCOUNTING' OR "B". "DNAME" =' RESERCH')

5-filter ("B". "DEPTNO" =: B1 AND ("B". "DNAME" = 'ACCOUNTING' OR

"B". "DNAME" = 'RESERCH'))

Note

-

-dynamic sampling used for this statement (level=2)

Statistical information

69 recursive calls

13 db block gets

121 consistent gets

9 physical reads

3012 redo size

837 bytes sent via SQL*Net to client

997 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

12 sorts (memory)

0 sorts (disk)

3 rows processed

Time spent: 00: 00: 00.00

10:33:13 SQL > merge into lhr.emp_bk a

10:33:32 2 using (select b.dnamejie deptno from lhr.dept_bk b where b.dname in ('ACCOUNTING','RESERCH')) bb

10:33:32 3 on (bb.deptno=a.deptno)

10:33:32 4 when matched then

10:33:32 5 update set a.dname = bb.dname

10:33:32 6

3 lines have been merged.

Time spent: 00: 00: 00.03

Carry out the plan

Plan hash value: 1386289611

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

| | 0 | MERGE STATEMENT | | 4 | 492 | 8 (13) | 00:00:01 |

| | 1 | MERGE | EMP_BK |

| | 2 | VIEW | | |

| | * 3 | HASH JOIN | | 4 | 592 | 8 (13) | 00:00:01 |

| | * 4 | TABLE ACCESS FULL | DEPT_BK | 1 | 22 | 4 (0) | 00:00:01 |

| | 5 | TABLE ACCESS FULL | EMP_BK | 14 | 1764 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

3-access ("DEPTNO" = "A". "DEPTNO")

4-filter ("B". "DNAME" = 'ACCOUNTING' OR "B". "DNAME" =' RESERCH')

Note

-

-dynamic sampling used for this statement (level=2)

Statistical information

20 recursive calls

7 db block gets

38 consistent gets

1 physical reads

1872 redo size

838 bytes sent via SQL*Net to client

942 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

3 rows processed

10:33:32 SQL >

There are several other examples of using the merge statement to optimize:

Change update to merge (max+decode): http://blog.itpub.net/26736162/viewspace-1244055/

Show your power again in the unrelated form of the merge sentence: http://blog.itpub.net/26736162/viewspace-1222423/

Use non-associative forms of MERGE statements to improve performance: http://blog.itpub.net/26736162/viewspace-1218671/

Use the non-associative form of MERGE statements to improve performance-post: http://blog.itpub.net/26736162/viewspace-1222417/

Why is the index still like a snail when it's gone: http://blog.itpub.net/26736162/viewspace-1208814/

I. 6 Summary

To this SQL query optimization rewrite the fourth chapter of the basic over, focusing on the understanding and mastery of merge sentences, especially the elder brother listed several cases, hoping to help to do SQL optimization of children's shoes.

1. 7 about me

. .

The author: wheat seedlings, only focus on the technology of database, pay more attention to the application of technology.

ITPUB BLOG: http://blog.itpub.net/26736162

Address: http://blog.itpub.net/26736162/viewspace-1661906/

Pdf version of this article: http://yunpan.cn/QCwUAI9bn7g7w extraction code: af2d

If you add QQ to QQ:642808185, please indicate the title of the article you are reading

Creation time and place: 2015-05-19 09 2015-05-19 11:20 at the × × trading center

. .

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.