[Book Reviews: Oracle query optimization and rewriting] Chapter 4: book reviews oracle

[Book Reviews: Oracle query optimization and rewriting] Chapter 4: book reviews oracle

[Book Reviews: Oracle query optimization and rewriting] Chapter 4

BLOG document structure

I. 1 Guide

After reading this article, you can master the following skills and learn other things you do not know ,~ O (distinct _ distinct) O ~ :

① Special check usage

② Replace the update statement with the merge statement in SQL optimization (important)

If there are any errors or imperfections in this article, please correct me a lot. You can leave an ITPUB message or QQ message. Your criticism is the biggest motivation of my writing.

I. 2 experiment environment Introduction

Target Database: 11.2.0.3 RHEL6.5

I. 3 Preface

For the links in the first three chapters, refer to related connections:

[Book Reviews: Oracle query optimization rewrite] Chapter 1 http://blog.itpub.net/26736162/viewspace-1652985/

[Book Reviews: Oracle query optimization rewrite] Chapter 2 http://blog.itpub.net/26736162/viewspace-1654252/

[Book Reviews: Oracle query optimization rewrite] Chapter 3 http://blog.itpub.net/26736162/viewspace-1660422/

Today, I will write the fourth chapter of this book. Chapter 4 focuses on the correct usage of the UPDATE statement andWhen should the UPDATE statement be rewritten to MERGE?The contents in Chapter 4 are as follows:

Chapter 4 insert, update, and delete

4.1 Insert a new record

4.2 block insertion of certain Columns

4.3 copy table definitions and data

4.4 use with check option to restrict data input

4.5 insert statements for multiple tables

4.6 update with values in other tables

4.7 Merge Records

4.8 delete records that violate the integrity of the Reference

4.9 delete records with duplicate names

I. 4 special check usage

We know that sysdate cannot be used for check constraints, but what should we do when there is such a requirement? The following example uses view with check option.

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 <= sysdate with check option)

09:42:13 2 valuees (9999, 'test', sysdate + 1 );

Insert into (select empno, ename, hiredate from scott. emp where hiredate <= sysdate with check option)

*

ERROR at line 1:

ORA-01402: view with check option where-clause violation

Elapsed: 00:00:00. 12

09:42:14 SQL> insert into (select empno, ename, hiredate from scott. emp where hiredate <= sysdate with check option)

09:42:56 2 values (9999, 'test', sysdate-1 );

1 row created.

Elapsed: 00:00:00. 03

09:42:57 SQL>

1. 5 merge statements

An error-prone part of update is not to write the where clause, which will update the data of the entire table,One trick is to copy the value in set to the where clause.

In addition, we recommend that you change it to the merge statement when updating the multi-Table Association, because the merge into statement only accesses the table once:

[Oracle @ rhel6_lhr ~] $ Sqlplus/as sysdba

SQL * Plus: Release 11.2.0.3.0 Production on Tuesday May 19 10:26:55 2015

Copyright (c) 1982,201 1, Oracle. All rights reserved.

Connect:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

10:26:55 SQL> set auto on;

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

The table has been changed.

Used time: 00: 00: 01.23

10:30:04 SQL> update lhr. emp_bk

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;

3 rows have been updated.

Used time: 00: 00: 00.05

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

Statistics

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

69 recursive CILS

13 db block gets

121 consistent gets

9 physical reads

3012 redo size

837 bytes sent via SQL * Net to client

997 bytes encoded ed via SQL * Net from client

3 SQL * Net roundtrips to/from client

12 sorts (memory)

0 sorts (disk)

3 rows processed

Used time: 00: 00: 00.00

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

10:33:32 2 using (select B. dname, 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;

The three rows have been merged.

Used time: 00: 00: 00.03

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

Statistics

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

20 recursive cballs

7 db block gets

38 consistent gets

1 physical reads

1872 redo size

838 bytes sent via SQL * Net to client

942 bytes encoded ed 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>

Several other cases about Optimization Using merge statements:

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

The non-correlated form of the merge statement is used to demonstrate again the power of the http://blog.itpub.net/26736162/viewspace-1222423/.

Improve Performance with non-correlated form of MERGE statements: http://blog.itpub.net/26736162/viewspace-1218671/

Improve performance using non-correlated form of MERGE statements-back-Pass: http://blog.itpub.net/26736162/viewspace-1222417/

Take the index for why is it like a snail: http://blog.itpub.net/26736162/viewspace-1208814/

Summary

At this point, the fourth chapter of SQL query optimization is basically over, focusing on understanding and mastering the merge statements, especially the cases listed by elder brother. I hope it will be helpful for SQL optimization.

I. 7 about me

........................................ ........................................ ........................................ ........................................ ...........................

The author of this article: xiaomaimiao focuses only on the database technology and focuses more on the application of technology.

Itpub blog: http://blog.itpub.net/26736162

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

This article pdf version: http://yunpan.cn/QCwUAI9bn7g7w extraction code: af2d

QQ: 642808185 if QQ is added, enter the title of the article you are reading.

Creation Time ~ At Foreign Exchange Trading Center

<All Rights Reserved. Reprinted documents are allowed, but the source address must be indicated by link. Otherwise, the documents shall be held legally responsible.>

........................................ ........................................ ........................................ ........................................ ...........................