In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
In the case of high concurrency and high load, how to add fields to the table and set the DEFAULT value?
Before Oracle 12c, when the Oracle table had hundreds of millions of data, efficiency and security had to be considered when performing the "ALTER TABLE XXX ADD COLUMN_XX VARCHAR2 (2) DEFAULT 'XXX';" operation on the table. If executed directly, a level 6 table lock will be added to the table in the process, that is, even the query will have to wait, which is a very dangerous operation on the production library. Because Oracle not only updates the data dictionary, but also refreshes all records during the above operation, and causes the Undo tablespace to soar, the right thing to do is to separate the update data dictionary from the update field values.
For example, table LKILL.T_KILL has about 4500W of data, and it takes 21 minutes to add a field C_LHR directly, as shown below:
12:20:17 SYS@RACLHR2 > ALTER TABLE LKILL.T_KILL ADD C_LHR VARCHAR2 (100) DEFAULT 'LHR'
Table altered.
Elapsed: 00:21:58.53
If you change it to the following way, the performance of this operation can be significantly improved, but if the original record in the table is empty, the default value of the new record will be set to LHR. Then the default value of the original record needs to be updated in batches, submitted in batches or updated with the system package DBMS_PARALLEL_EXECUTE when the system is idle, so that the table will not be locked in large quantities. Please refer to the batch update section of this book [REF _ Ref24783\ n\ h 3.1.10.5 REF _ Ref24783\ h batch insert, batch update, batch deletion, batch submission]. As follows:
12:42:17 SYS@RACLHR2 > ALTER TABLE LKILL.T_KILL ADD A_LHR VARCHAR2
Table altered.
Elapsed: 00:00:00.35
13:53:54 SYS@RACLHR2 > ALTER TABLE LKILL.T_KILL MODIFY A_LHR VARCHAR2 (100) DEFAULT 'LHR'
Table altered.
Elapsed: 00:00:00.06
It is important to note that starting with Oracle 11g, when adding a non-empty column with a default value (note 2 conditions, NOT NULL and default values), Oracle will not use this default value to physically update existing rows, Oracle will only store the new column metadata (NOT NULL constraints and DEFAULT default values), so that the addition of non-empty columns with default values to the table can be completed in an instant. Of course, there is a partial NVL function cost when retrieving the column from the table. Specific nuances can be analyzed through the 10046 event, which will not be analyzed in detail here.
Starting with Oracle 12c, DDL statement optimization for adding columns with empty columns with default values is supported, that is, the efficiency of the following two SQL statements is the same, and there is no table lock:
ALTER TABLE LKILL.T_KILL ADD A_LHR VARCHAR2 (100)
ALTER TABLE LKILL.T_KILL ADD A_LHR VARCHAR2 (100) NOT NULL
The example is as follows:
LHR@OCPLHR1 > select * from v$version where rownum set time on
16:59:00 LHR@OCPLHR1 > set timing on
16:59:08 LHR@OCPLHR1 > CREATE TABLE T1 AS
16:59:21 2 SELECT ROWNUM N1
16:59:21 3 TRUNC ((ROWNUM-1) / 3) N2
16:59:21 4 TRUNC (DBMS_RANDOM.VALUE (ROWNUM, ROWNUM * 10)) N3
16:59:21 5 DBMS_RANDOM.STRING ('Utility, 10) cl
16:59:21 6 FROM DUAL
16:59:21 7 CONNECT BY LEVEL SELECT d.bytes FROM user_segments d WHERE d.segmentationnamekeeper T1'
BYTES
-
7340032
Elapsed: 00:00:00.09
17:01:00 LHR@OCPLHR1 > ALTER TABLE T1 ADD c_ddl NUMBER DEFAULT 666
Table altered.
Elapsed: 00:00:25.29
17:02:07 LHR@OCPLHR1 > SELECT d.bytes FROM user_segments d WHERE d.segmentations
BYTES
-
8388608
Elapsed: 00:00:00.01
17:02:13 LHR@OCPLHR1 > ALTER TABLE T1 ADD c_ddl2 NUMBER DEFAULT 888 not null
Table altered.
Elapsed: 00:00:00.08
17:02:37 LHR@OCPLHR1 > SELECT d.bytes FROM user_segments d WHERE d.segmentations
BYTES
-
8388608
Elapsed: 00:00:00.01
As you can see, in Oracle 11g, a SQL statement with a NOT NULL constraint can add a column in an instant, while a SQL statement with only a default value takes 25 seconds. In addition, after the execution of the SQL statement with NOT NUL constraints, the size of the table does not change, which indicates that Oracle has not made a physical update.
Let's take a look at its execution plan, and be careful not to use the "SET AUTOT ON" method here, otherwise you won't be able to see its actual execution plan:
17:05:30 LHR@OCPLHR1 > SELECT COUNT (*) FROM T1 WHERE c_ddl2=888
COUNT (*)
-
200000
Elapsed: 00:00:00.02
17:05:39 LHR@OCPLHR1 > select * from table (dbms_xplan.display_cursor)
PLAN_TABLE_OUTPUT
SQL_ID bq50v8z914juk, child number 0
-
SELECT COUNT (*) FROM T1 WHERE c_ddl2=888
Plan hash value: 3724264953
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 0 | SELECT STATEMENT | 282100 | | |
| | 1 | SORT AGGREGATE | | 1 | 13 |
| | * 2 | TABLE ACCESS FULL | T1 | 199K | 2530K | 282 (2) | 00:00:04 |
Predicate Information (identified by operation id):
2-filter (NVL ("C_DDL2", 888) = 88)
Note
-
-dynamic sampling used for this statement (level=2)
23 rows selected.
17:08:55 LHR@OCPLHR1 > SELECT * FROM T1 WHERE rownum CREATE INDEX idx_c_ddl2 ON T1 (c_ddl2)
Index created.
Elapsed: 00:00:00.71
17:31:08 LHR@OCPLHR1 > update T1 set caterpillar 8881 'where rownum commit
Commit complete.
Elapsed: 00:00:00.00
17:31:16 LHR@OCPLHR1 > SELECT * FROM T1 WHERE c_ddl2=8881
N1 N2 N3 CL C_DDL C_DDL2
--
1 0 8 XYGGZXRRYR 666 8881
Elapsed: 00:00:00.01
17:31:24 LHR@OCPLHR1 > select * from table (dbms_xplan.display_cursor)
PLAN_TABLE_OUTPUT
SQL_ID 0sm5s7zkvycrq, child number 0
-
SELECT * FROM T1 WHERE c_ddl2=8881
Plan hash value: 1464185165
-
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-
| | 0 | SELECT STATEMENT | 2 (100) | |
| | 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 34 | 2 (0) | 00:00:01 |
| | * 2 | INDEX RANGE SCAN | IDX_C_DDL2 | 1 | | 1 (0) | 00:00:01 |
-
Predicate Information (identified by operation id):
2-access ("C_DDL2" = 8881)
19 rows selected.
Elapsed: 00:00:00.11
Surprisingly, the index was used.
Let's take a look at the implementation in Oracle 12c:
LHR@lhr121 > set line 120
LHR@lhr121 > select * from v$version where rownum CREATE TABLE T1 AS
2 SELECT ROWNUM N1
3 TRUNC ((ROWNUM-1) / 3) N2
4 TRUNC (DBMS_RANDOM.VALUE (ROWNUM, ROWNUM * 10)) N3
DBMS_RANDOM.STRING ('Utility, 10) cl
6 FROM DUAL
7 CONNECT BY LEVEL SELECT d.bytes FROM user_segments d WHERE d.segmentationnameplate T1'
BYTES
-
4194304
Elapsed: 00:00:00.33
LHR@lhr121 > ALTER TABLE T1 ADD c_ddl NUMBER DEFAULT 666
Table altered.
Elapsed: 00:00:00.65
LHR@lhr121 > SELECT d.bytes FROM user_segments d WHERE d.segmentationnameplate T1'
BYTES
-
4194304
Elapsed: 00:00:00.14
LHR@lhr121 > ALTER TABLE T1 ADD c_ddl2 NUMBER DEFAULT 888 not null
Table altered.
Elapsed: 00:00:00.15
LHR@lhr121 > SELECT d.bytes FROM user_segments d WHERE d.segmentationnameplate T1'
BYTES
-
4194304
Elapsed: 00:00:00.09
LHR@lhr121 > SELECT COUNT (*) FROM T1 WHERE c_ddl2=888
COUNT (*)
-
100000
Elapsed: 00:00:00.02
LHR@lhr121 > select * from table (dbms_xplan.display_cursor)
PLAN_TABLE_OUTPUT
SQL_ID bq50v8z914juk, child number 1
-
SELECT COUNT (*) FROM T1 WHERE c_ddl2=888
Plan hash value: 3724264953
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 0 | SELECT STATEMENT | 122100 | | |
| | 1 | SORT AGGREGATE | | 1 | 13 |
| | * 2 | TABLE ACCESS FULL | T1 | 100K | 1269K | 122K (1) | 00:00:01 |
Predicate Information (identified by operation id):
2-filter (NVL ("C_DDL2", 888) = 88)
Note
-
-statistics feedback used for this statement
23 rows selected.
Elapsed: 00:00:00.05
LHR@lhr121 > SELECT COUNT (*) FROM T1 WHERE c_ddl=666
COUNT (*)
-
100000
Elapsed: 00:00:00.04
LHR@lhr121 > select * from table (dbms_xplan.display_cursor)
PLAN_TABLE_OUTPUT
-
SQL_ID dph3gfp6f0jja, child number 1
-
SELECT COUNT (*) FROM T1 WHERE c_ddl=666
Plan hash value: 3724264953
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 0 | SELECT STATEMENT | 122100 | | |
| | 1 | SORT AGGREGATE | | 1 | 13 |
| | * 2 | TABLE ACCESS FULL | T1 | 1000 | 13000 | 122 (1) | 00:00:01 |
Predicate Information (identified by operation id):
2-filter (DECODE (TO_CHAR (SYS_OP_VECBIT ("SYS_NC00005 $", 0)), NULL,NVL ("
C_DDL "," C_DDL "," C_DDL ") = 666)
20 rows selected.
Elapsed: 00:00:00.12
LHR@lhr121 > SELECT d.column_name, d.columnstationid.hiddenwritten column FROM Dba_Tab_Cols d.virtualizedcolumn d WHERE d.tableprinted nameplate recording T1 'order by column_id
COLUMN_NAME COLUMN_ID HID VIR
N1 1 NO NO
N2 2 NO NO
N3 3 NO NO
CL 4 NO NO
C_DDL 5 NO NO
C_DDL2 6 NO NO
SYS_NC00005 $YES NO
7 rows selected.
Elapsed: 00:00:00.32
LHR@lhr121 >
You can clearly see from the example that in Oracle 12c, the addition of DDL optimizations with default values has been extended to include empty columns with default values. Oracle uses an unexposed function, SYS_OP_VECBIT, and a new hidden column, SYS_NC00005 $, because it is not physically updated.
& description:
For more details on batch updates and the use of DBMS_PARALLEL_EXECUTE, please refer to my BLOG: ① http://blog.itpub.net/26736162/viewspace-2140626/ ② http://blog.itpub.net/26736162/viewspace-1684396
About Me
. .
● author: wheat seedlings, part of the content is sorted out from the network, if there is any infringement, please contact the wheat seedlings to delete
● article is updated synchronously on itpub (http://blog.itpub.net/26736162/abstract/1/), blog Park (http://www.cnblogs.com/lhrbest) and personal Wechat official account (xiaomaimiaolhr).
● article itpub address: http://blog.itpub.net/26736162/abstract/1/
● article blog park address: http://www.cnblogs.com/lhrbest
● pdf version of this article, personal introduction and wheat seedling cloud disk address: http://blog.itpub.net/26736162/viewspace-1624453/
● database written examination interview questions database and answers: http://blog.itpub.net/26736162/viewspace-2134706/
● DBA Baodian Jinri Toutiao address: http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826
. .
● QQ group number: 230161599 (full), 618766405
● WeChat group: you can add me Wechat, I will pull everyone into the group, if you are the one
● contact me, please add QQ friend (646634621), indicate the reason for adding
● completed in Mordor from 06:00 on 2018-02-01 to 24:00 on 2018-02-31.
The content of the ● article comes from the study notes of wheat seedlings, and some of it is sorted out from the Internet. Please forgive me if there is any infringement or improper place.
Copyright ● all rights reserved, welcome to share this article, please reserve the source for reprint
. .
The micro store of ● wheat seedlings: https://weidian.com/s/793741433?wfr=c&ifr=shopdetail
A series of database books published by ● wheat seedlings: http://blog.itpub.net/26736162/viewspace-2142121/
● good news: wheat seedlings OCP and OCM have started classes. For more information, please click: http://blog.itpub.net/26736162/viewspace-2148098/
. .
Use Wechat client to scan the following QR code to follow the Wechat official account (xiaomaimiaolhr) and QQ group (DBA treasure book) of wheat seedlings and learn the most practical database technology.
Wheat seedling Wechat official account wheat seedling DBA treasure QQ group 2 "DBA written test interview treasure book" readers group wheat seedling micro shop
. .
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: 203
*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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.