Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

In the case of high concurrency and high load, how to add fields to the table and set the DEFAULT value?

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.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report