"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