"Book Review: Oracle Query Optimization Rewrite" chapter fourth

"Book Review: Oracle Query Optimization Rewrite" chapter fourth

Blog Document structure diagram

650) this.width=650; "title=" wpse60b.tmp "border=" 0 "alt=" wpse60b.tmp "src=" http://s3.51cto.com/wyfs02/M00/6D/0B/ Wkiom1varbyhcepqaabjdaqoiyu258.jpg "" 291 "height=" 192 "/>

I. 1 guide

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

Special usage of ①check

Using the merge statement instead of the UPDATE statement (emphasis) in ②sql optimization

If there are errors or imperfections in this article please correct me, itpub message or QQ can be, your criticism refers to my writing is the greatest motivation.

I. 2 Introduction to the experimental environment

Target Library: 11.2.0.3 RHEL6.5

I. 3 PREFACE

Links to the first 3 chapters refer to related connections:

"Book Review: Oracle Query Optimization Rewrite" chapter http://blog.itpub.net/26736162/viewspace-1652985/

"Book Review: Oracle Query Optimization Rewrite" chapter II http://blog.itpub.net/26736162/viewspace-1654252/

"Book Review: Oracle Query Optimization Rewrite" chapter III http://blog.itpub.net/26736162/viewspace-1660422/

Today to write the fourth chapter of this book, the fourth chapter mainly tells the correct usage of the UPDATE statement, and when the UPDATE statement should be rewritten as merge, the contents of the fourth chapter are as follows:

4th INSERT, UPDATE and delete

4.1 Inserting a new record

4.2 Block insertions to a few columns

4.3 Definition and data of duplicate tables

4.4 Restricting data entry with CHECK OPTION

4.5 Multi-Table INSERT statement

4.6 Updating with values from other tables

4.7 Merging Records

4.8 Deleting records that violate referential integrity

4.9 Delete a duplicate name record

I. 4 SPECIAL usage of 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 resolved with the View Plus 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 VALUES (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>

I. 5 Merge statement

An error-prone area about update is to not write the WHERE clause, which will update the data for the entire table, and one technique is to copy the values from the set into the WHERE clause.

In addition, it is recommended that you modify the merge statement when you do a long list of associated updates, because the merge into statement accesses only one table at a time:

[Email protected]_lhr ~]$ sqlplus/as SYSDBA

Sql*plus:release 11.2.0.3.0 Production on Tuesday May 19 10:26:55 2015

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

Connect to:

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 Autot on;

10:28:05 sql> ALTER TABLE LHR.EMP_BK add dname varchar2 (+) Default ' Noname ';

The table has changed.

Time used: 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 ', ' RES Erch '))

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 have been updated.

Time used: 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)

Statistical information

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

Recursive calls

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

Sorts (memory)

0 Sorts (disk)

3 Rows processed

Time used: 00:00:00.00

10:33:13 sql> Merge into LHR.EMP_BK a

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;

3 rows have been merged.

Time used: 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)

Statistical information

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

Recursive calls

7 db block gets

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>

A few other examples of using the merge statement to optimize:

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

Using the non-associative form of the merge statement to re-manifest God: http://blog.itpub.net/26736162/viewspace-1222423/

Improve performance with the non-associative form of the merge statement: http://blog.itpub.net/26736162/viewspace-1218671/

Using the non-associative form of the merge statement to improve performance---post: http://blog.itpub.net/26736162/viewspace-1222417/

Let's go. Index why still like a snail: http://blog.itpub.net/26736162/viewspace-1208814/

I. 6 SUMMARY

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

I. 7 About ME

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

In this paper, wheat seedling, only focus on the database technology, pay more attention to the use of technology

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

This address: http://blog.itpub.net/26736162/viewspace-1661906/

This article PDF version: http://yunpan.cn/QCwUAI9bn7g7w Extract code: af2d

qq:642808185 flocculent QQ Please indicate the title of the article you are reading

Date of creation: 2015-05-19 09:00~ 2015-05-19 11:20 in forex trading center

< copyright, the article is allowed to reprint, but must be linked to the source address, otherwise investigate legal liability!>

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

"Book Review: Oracle Query Optimization Rewrite" chapter fourth