♣
题目 部分
在Oracle中,在高并发、高负载的情况下,如何给表添加字段并设置DEFAULT值?
♣
答案部分
在Oracle 12c之前,当Oracle表数据量上亿时,对表执行“ALTER TABLE XXX ADD COLUMN_XX VARCHAR2(2) DEFAULT 'XXX';”操作时,效率及安全性是必须要考虑的因素。若直接执行,则会在该过程中给表加上6级表锁,也就是连查询都需要等待,这在生产库上是相当危险的操作。因为Oracle在执行上述操作过程中,不仅要更新数据字典,还会刷新全部的记录,并且会使得Undo表空间暴涨,所以,正确的做法是将更新数据字典和更新字段值分开。
例如,表LKILL.T_KILL约有4500W的数据,直接添加一个字段C_LHR需要花费21分钟,如下所示:
112:20:17 SYS@RACLHR2> ALTER TABLE LKILL.T_KILL ADD C_LHR VARCHAR2(100) DEFAULT 'LHR'; 2Table altered. 3Elapsed: 00:21:58.53
若修改为如下的方式,则可以显著提高这个操作的性能,但表中原有的记录对于新添加的列为空,新增记录默认值会设置为LHR,那么原有记录的默认值就需要在系统空闲的时候进行批量更新、批量提交或采用系统包DBMS_PARALLEL_EXECUTE来更新,这样不至于大批量锁表,请参考本书中分批更新的部分【】。如下所示:
112:42:17 SYS@RACLHR2> ALTER TABLE LKILL.T_KILL ADD A_LHR VARCHAR2(100); 2Table altered. 3Elapsed: 00:00:00.35 413:53:54 SYS@RACLHR2> ALTER TABLE LKILL.T_KILL MODIFY A_LHR VARCHAR2(100) DEFAULT 'LHR'; 5Table altered. 6Elapsed: 00:00:00.06
需要注意的是,从Oracle 11g开始,当添加一个带有默认值的非空列时(注意2个条件,NOT NULL和默认值),Oracle不会使用这个默认值来物理更新现有存在的行,Oracle只会存储这个新列元数据(NOT NULL约束和DEFAULT默认值),从而使得对该表的添加带有默认值的非空列操作可以在瞬间完成。当然,从表中检索该列时,会有部分的NVL函数代价。具体的细微差别可以通过10046事件来分析,这里不再详细解析。
从Oracle 12c开始,支持具有默认值的空列的添加列的DDL语句优化,即如下2条SQL语句的效率是一样的,也不存在锁表的现象了:
1ALTER TABLE LKILL.T_KILL ADD A_LHR VARCHAR2(100); 2ALTER TABLE LKILL.T_KILL ADD A_LHR VARCHAR2(100) NOT NULL;
示例如下所示:
1LHR@OCPLHR1> select * from v$version where rownum<=1; 2 3BANNER 4-------------------------------------------------------------------------------- 5Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production 6 7LHR@OCPLHR1> set time on 816:59:00 LHR@OCPLHR1> set timing on 916:59:08 LHR@OCPLHR1> CREATE TABLE t1 AS 1016:59:21 2 SELECT ROWNUM N1, 1116:59:21 3 TRUNC((ROWNUM - 1) / 3) N2, 1216:59:21 4 TRUNC(DBMS_RANDOM.VALUE(ROWNUM, ROWNUM * 10)) N3, 1316:59:21 5 DBMS_RANDOM.STRING('U', 10) cl 1416:59:21 6 FROM DUAL 1516:59:21 7 CONNECT BY LEVEL <= 200000; 16 17Table created. 18 19Elapsed: 00:00:05.72 20 2116:59:45 LHR@OCPLHR1> SELECT d.bytes FROM user_segments d WHERE d.segment_name='T1'; 22 23 BYTES 24---------- 25 7340032 26 27Elapsed: 00:00:00.09 2817:01:00 LHR@OCPLHR1> ALTER TABLE t1 ADD c_ddl NUMBER DEFAULT 666 ; 29 30Table altered. 31 32Elapsed: 00:00:25.29 3317:02:07 LHR@OCPLHR1> SELECT d.bytes FROM user_segments d WHERE d.segment_name='T1'; 34 35 BYTES 36---------- 37 8388608 38 39Elapsed: 00:00:00.01 4017:02:13 LHR@OCPLHR1> ALTER TABLE t1 ADD c_ddl2 NUMBER DEFAULT 888 not null; 41 42Table altered. 43 44Elapsed: 00:00:00.08 4517:02:37 LHR@OCPLHR1> SELECT d.bytes FROM user_segments d WHERE d.segment_name='T1'; 46 47 BYTES 48---------- 49 8388608 50 51Elapsed: 00:00:00.01
可以看出,在Oracle 11g中,加了NOT NULL约束的SQL语句,可以在瞬间完成添加列的操作,而只设置了默认值的SQL语句使用了25秒的时间。另外,加了NOT NULL约束的SQL语句执行完毕后,表的大小没有变化,这也说明了Oracle并没有做物理更新。
下面查看其执行计划,注意在这里不要使用“SET AUTOT ON”的方式,否则不能看到其真实的执行计划:
117:05:30 LHR@OCPLHR1> SELECT COUNT(*) FROM t1 WHERE c_ddl2=888; 2 3 COUNT(*) 4---------- 5 200000 6 7Elapsed: 00:00:00.02 817:05:39 LHR@OCPLHR1> select * from table(dbms_xplan.display_cursor); 9 10PLAN_TABLE_OUTPUT 11------------------------------------------------------------- 12SQL_ID bq50v8z914juk, child number 0 13------------------------------------- 14SELECT COUNT(*) FROM t1 WHERE c_ddl2=888 15 16Plan hash value: 3724264953 17 18--------------------------------------------------------------------------- 19| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 20--------------------------------------------------------------------------- 21| 0 | SELECT STATEMENT | | | | 282 (100)| | 22| 1 | SORT AGGREGATE | | 1 | 13 | | | 23|* 2 | TABLE ACCESS FULL| T1 | 199K| 2530K| 282 (2)| 00:00:04 | 24--------------------------------------------------------------------------- 25 26Predicate Information (identified by operation id): 27--------------------------------------------------- 28 29 2 - filter(NVL("C_DDL2",888)=888) 30 31Note 32----- 33 - dynamic sampling used for this statement (level=2) 34 35 3623 rows selected. 37 3817:08:55 LHR@OCPLHR1> SELECT * FROM t1 WHERE rownum<=1; 39 40 N1 N2 N3 CL C_DDL C_DDL2 41---------- ---------- ---------- ---------- ---------- ---------- 42 1 0 8 XYGGZXRRYR 666 888
可以看到,在谓词部分出现了NVL函数。所以,Oracle认为C_DDL2列是空列。
下面测试是否可以使用索引:
117:29:24 LHR@OCPLHR1> CREATE INDEX idx_c_ddl2 ON t1(c_ddl2); 2 3Index created. 4 5Elapsed: 00:00:00.71 617:31:08 LHR@OCPLHR1> update t1 set c_ddl2='8881' where rownum<=1; 7 81 row updated. 9 10Elapsed: 00:00:00.05 1117:31:13 LHR@OCPLHR1> commit; 12 13Commit complete. 14 15Elapsed: 00:00:00.00 1617:31:16 LHR@OCPLHR1> SELECT * FROM t1 WHERE c_ddl2=8881; 17 18 N1 N2 N3 CL C_DDL C_DDL2 19---------- ---------- ---------- ---------- ---------- ---------- 20 1 0 8 XYGGZXRRYR 666 8881 21 22Elapsed: 00:00:00.01 2317:31:24 LHR@OCPLHR1> select * from table(dbms_xplan.display_cursor); 24 25PLAN_TABLE_OUTPUT 26------------------------------------------------------------------------------- 27SQL_ID 0sm5s7zkvycrq, child number 0 28------------------------------------- 29SELECT * FROM t1 WHERE c_ddl2=8881 30 31Plan hash value: 1464185165 32 33------------------------------------------------------------------------------------------ 34| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 35------------------------------------------------------------------------------------------ 36| 0 | SELECT STATEMENT | | | | 2 (100)| | 37| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 34 | 2 (0)| 00:00:01 | 38|* 2 | INDEX RANGE SCAN | IDX_C_DDL2 | 1 | | 1 (0)| 00:00:01 | 39------------------------------------------------------------------------------------------ 40 41Predicate Information (identified by operation id): 42--------------------------------------------------- 43 44 2 - access("C_DDL2"=8881) 45 46 4719 rows selected. 48 49Elapsed: 00:00:00.11
令人惊喜的是,使用了索引。
下面看看在Oracle 12c中的执行情况:
1LHR@lhr121> set line 120 2LHR@lhr121> select * from v$version where rownum<=1; 3 4BANNER CON_ID 5-------------------------------------------------------------------------------- ---------- 6Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0 7 8Elapsed: 00:00:00.00 9LHR@lhr121> CREATE TABLE t1 AS 10 2 SELECT ROWNUM N1, 11 3 TRUNC((ROWNUM - 1) / 3) N2, 12 4 TRUNC(DBMS_RANDOM.VALUE(ROWNUM, ROWNUM * 10)) N3, 13 DBMS_RANDOM.STRING('U', 10) cl 14 6 FROM DUAL 15 7 CONNECT BY LEVEL <= 100000; 16 17Table created. 18 19Elapsed: 00:00:09.41 20LHR@lhr121> SELECT d.bytes FROM user_segments d WHERE d.segment_name='T1'; 21 22 BYTES 23---------- 24 4194304 25 26Elapsed: 00:00:00.33 27LHR@lhr121> ALTER TABLE t1 ADD c_ddl NUMBER DEFAULT 666 ; 28 29Table altered. 30 31Elapsed: 00:00:00.65 32LHR@lhr121> SELECT d.bytes FROM user_segments d WHERE d.segment_name='T1'; 33 34 BYTES 35---------- 36 4194304 37 38Elapsed: 00:00:00.14 39LHR@lhr121> ALTER TABLE t1 ADD c_ddl2 NUMBER DEFAULT 888 not null; 40 41Table altered. 42 43Elapsed: 00:00:00.15 44LHR@lhr121> SELECT d.bytes FROM user_segments d WHERE d.segment_name='T1'; 45 46 BYTES 47---------- 48 4194304 49 50Elapsed: 00:00:00.09 51 52LHR@lhr121> SELECT COUNT(*) FROM t1 WHERE c_ddl2=888; 53 54 COUNT(*) 55---------- 56 100000 57 58Elapsed: 00:00:00.02 59LHR@lhr121> select * from table(dbms_xplan.display_cursor); 60 61PLAN_TABLE_OUTPUT 62----------------------------------------------------------------------------- 63SQL_ID bq50v8z914juk, child number 1 64------------------------------------- 65SELECT COUNT(*) FROM t1 WHERE c_ddl2=888 66 67Plan hash value: 3724264953 68 69--------------------------------------------------------------------------- 70| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 71--------------------------------------------------------------------------- 72| 0 | SELECT STATEMENT | | | | 122 (100)| | 73| 1 | SORT AGGREGATE | | 1 | 13 | | | 74|* 2 | TABLE ACCESS FULL| T1 | 100K| 1269K| 122 (1)| 00:00:01 | 75--------------------------------------------------------------------------- 76 77Predicate Information (identified by operation id): 78--------------------------------------------------- 79 80 2 - filter(NVL("C_DDL2",888)=888) 81 82Note 83----- 84 - statistics feedback used for this statement 85 86 8723 rows selected. 88 89Elapsed: 00:00:00.05 90 91LHR@lhr121> SELECT COUNT(*) FROM t1 WHERE c_ddl=666; 92 93 COUNT(*) 94---------- 95 100000 96 97Elapsed: 00:00:00.04 98LHR@lhr121> select * from table(dbms_xplan.display_cursor); 99 100PLAN_TABLE_OUTPUT 101------------------------------------------------------------------------------------------------------------------------ 102SQL_ID dph2gfp6f0jja, child number 1 103------------------------------------- 104SELECT COUNT(*) FROM t1 WHERE c_ddl=666 105 106Plan hash value: 3724264953 107 108--------------------------------------------------------------------------- 109| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 110--------------------------------------------------------------------------- 111| 0 | SELECT STATEMENT | | | | 122 (100)| | 112| 1 | SORT AGGREGATE | | 1 | 13 | | | 113|* 2 | TABLE ACCESS FULL| T1 | 1000 | 13000 | 122 (1)| 00:00:01 | 114--------------------------------------------------------------------------- 115 116Predicate Information (identified by operation id): 117--------------------------------------------------- 118 119 2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00005$",0)),NULL,NVL(" 120 C_DDL",666),'0',NVL("C_DDL",666),'1',"C_DDL")=666) 121 122 12320 rows selected. 124 125Elapsed: 00:00:00.12 126LHR@lhr121> SELECT d.column_name, d.column_id,d.hidden_column,d.virtual_column FROM Dba_Tab_Cols d WHERE d.table_name='T1' order by column_id; 127 128COLUMN_NAME COLUMN_ID HID VIR 129--------------- ---------- --- --- 130N1 1 NO NO 131N2 2 NO NO 132N3 3 NO NO 133CL 4 NO NO 134C_DDL 5 NO NO 135C_DDL2 6 NO NO 136SYS_NC00005$ YES NO 137 1387 rows selected. 139 140Elapsed: 00:00:00.32 141LHR@lhr121>
从示例可以清楚地看到,在Oracle 12c中,添加具有默认值的DDL优化已扩展到包括默认值的空列。Oracle使用了一个未公开的函数SYS_OP_VECBIT和新的隐藏列SYS_NC00005$,因为该列没有被物理更新。