网站建设中企,光明网,商业计划书ppt免费模板下载,装饰行业模板网站一、讲故事
你是否遇到过开发人员添加字段#xff0c;导致数据库锁表问题#xff1f; 但是令开发疑惑的事#xff0c;他们添加字段#xff0c;有的时候很快#xff0c;有的时候很慢#xff1f; 为什么呢#xff1f; 询问得知#xff0c;**加的慢时候是带上了default默…一、讲故事
你是否遇到过开发人员添加字段导致数据库锁表问题 但是令开发疑惑的事他们添加字段有的时候很快有的时候很慢 为什么呢 询问得知**加的慢时候是带上了default默认值如果表的数据量很大那么会花费很长时间。在加字段期间表上还会加6级锁连select都会被阻塞。**这在生产系统是不可接受的。 所以任何DDL操作都应该询问DBA是否有风险。
从11g开始这种情况有了明显的改善。Oracle 11g引入了元数据唯一默认值的概念。如果增加的列符合defaultnot null这两个条件的话它只会去更新数据字典不会去更新物理行所以操作可以很短的时间完成且不会对生产产生影响。读取列数据的时候优化器重写新列的查询以确保结果与默认定义一致。 注意这两个条件列符合default且not null缺一不可否则还是会很慢。
从Oracle 12c更进一步允许元数据默认值not null 可选因此在现有表中添加带有默认子句的新列将被作为一个元数据来处理而不管该列是否被定义not null两种方式操作都很快。
二、讲实战
1. 模拟大表
SQL drop table test1; #执行这个sql前需要确认test1表是不是能删除。表名可以自己换成别的表。Table dropped.SQL create table test as select * from dba_objects;Table created.SQL insert into test1 select * from test;79699 rows created
.....
SQL insert into test1 select * from test;1275200 rows created.SQL commit;Commit complete.--11g 环境下表初始大小
SQL select bytes from user_segments where segment_nameTEST1;BYTES
----------301989888--19c 环境下表初始大小SQL select bytes from user_segments where segment_nameTEST1;BYTES
----------739246080
2. 11g 环境下测试
2.1 增加字段
情况1只有default值
-- 不加not null约束
SQL alter table test1 add a varchar2(100) default aa;Table altered.Elapsed: 00:00:42.62SQL select count(*) from test1 where aaa;COUNT(*)
----------2550400
Elapsed: 00:00:00.24SQL select bytes from user_segments where segment_nameTEST1;BYTES
----------578813952Elapsed: 00:00:00.01
情况2defaultnot null
--加上not null 约束
SQL alter table test1 add b varchar2(100) default bb not null;Table altered.Elapsed: 00:00:00.04SQL select count(*) from test1 where bbb;COUNT(*)
----------2550400Elapsed: 00:00:00.23SQL select bytes from user_segments where segment_nameTEST1;BYTES
----------578813952Elapsed: 00:00:00.01
可以看到
1.defaultnot null 的执行时间远小于只有default值的情况。
2.defaultnot null 表的大小也没有变化说明表并没有实际去更新物理行只是更新了数据字典。
这是11g对新增一个not null字段带有默认值进行了优化默认值以及对应的表信息、列信息一起存储在一个新增数据字典表ecol$中 。
2.2 查询test1表中添加的默认值
SQL select object_id from dba_objects where object_name TEST1 and ownerSXC;OBJECT_ID
----------97662SQL set line222
SQL select colnum,binarydefval from ecol$ where tabobj# 97662;COLNUM BINARYDEFVAL
---------- ----------------------------------17 6262
SQL desc ecol$Name Null? Type----------------------------------------- -------- ----------------------------TABOBJ# NUMBERCOLNUM NUMBERBINARYDEFVAL BLOB-- 注意binarydefval 是blob 类型所有实际存储的值并不是数值6262可以通过下面的存储过程查看真实存储的值是bb
SQL SET SERVEROUT ON
SQL
SQL DECLARE2 V_BLOB VARCHAR2(32767) DEFAULT 6262;3 BEGIN4 FOR I IN 1..LENGTH(V_BLOB)/2 LOOP5 DBMS_OUTPUT.PUT(CHR(TO_NUMBER(SUBSTR(V_BLOB, (I-1) * 2 1, 2), XXX)));6 END LOOP;7 DBMS_OUTPUT.NEW_LINE;8 END;9 /
bbPL/SQL procedure successfully completed.
2.3 对比执行计划
如果我们从表中检索对应的列时候我们对比一下执行计划
情况1只有default值
SQL select count(*) from test1 where aaa;COUNT(*)
----------2550368SQL select * from table(dbms_xplan.display_cursor);PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID b91qrsffc9t3d, child number 0
-------------------------------------
select count(*) from test1 where aaaPlan hash value: 3896847026----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9972 (100)| |
| 1 | SORT AGGREGATE | | 1 | 52 | | |PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| TEST1 | 2886K| 143M| 9972 (1)| 00:02:00 |
----------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - filter(Aaa)Note
------ dynamic sampling used for this statement (level2)PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------23 rows selected.
结论采用filter(“A”‘aa’) 过滤的方式直接读取列数据。
情况2defaultnot null
SQL select count(*) from test1 where bbb;COUNT(*)
----------2550368SQL select * from table(dbms_xplan.display_cursor);PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9xurwt6ytwjf0, child number 0
-------------------------------------
select count(*) from test1 where bbbPlan hash value: 3896847026----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9974 (100)| |
| 1 | SORT AGGREGATE | | 1 | 52 | | |PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| TEST1 | 2886K| 143M| 9974 (1)| 00:02:00 |
----------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - filter(NVL(B,bb)bb)Note
------ dynamic sampling used for this statement (level2)PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------23 rows selected.
结论采用filter(NVL(“B”,‘bb’)‘bb’)方式过滤读取列数据但是oracle认为defaultnot null 的方式添加的列是空列所以用nvl函数做了一次转换。
※思考
1、如果我们在列上建索引呢
SQL create index idx1 on test1 (b);Index created.SQL select count(*) from test1 where bbb;COUNT(*)
----------2550368SQL select * from table(dbms_xplan.display_cursor);PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9xurwt6ytwjf0, child number 0
-------------------------------------
select count(*) from test1 where bbbPlan hash value: 325870156------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1391 (100)| |
| 1 | SORT AGGREGATE | | 1 | 52 | | |PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | INDEX FAST FULL SCAN| IDX1 | 2886K| 143M| 1391 (1)| 00:00:17 |
------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - filter(NVL(B,bb)bb)Note
------ dynamic sampling used for this statement (level2)PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------23 rows selected.
结论虽然从全表扫描变成索引扫描但是一样是NVL(“B”,‘bb’)bb’方式过滤数据。
2、后续新增加的数据呢
-- 增加2行数据一个用默认值一个不用默认值。
SQL select * from test1 where ownerT;no rows selectedSQL insert into test1 (owner,b) values (T,default);1 row created.SQL insert into test1 (owner,b) values (T,bbb);1 row created.SQL commit;Commit complete.--访问默认值查看执行计划
SQL select owner,b from test1 where ownerT and bbb;OWNER B
----------------------------------------
T bbSQL select * from table(dbms_xplan.display_cursor);PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID dyd1zgs10jr16, child number 0
-------------------------------------
select owner,b from test1 where ownerT and bbbPlan hash value: 4122059633---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9974 (100)| |
|* 1 | TABLE ACCESS FULL| TEST1 | 287 | 19803 | 9974 (1)| 00:02:00 |PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter((OWNERT AND NVL(B,bb)bb))Note
------ dynamic sampling used for this statement (level2)22 rows selected.--访问非默认值查看执行计划
SQL select owner,b from test1 where ownerT and bbbb;OWNER B
----------------------------------------
T bbbSQL select * from table(dbms_xplan.display_cursor);PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5mbna21dqafdw, child number 0
-------------------------------------select owner,b from test1 where ownerT and bbbbPlan hash value: 2734693028-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100) | |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 287 | 19803 | 9 (0)| 00:00:01 |PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | IDX1 | 403 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter(OWNERT)2 - access(NVL(B,bb)bbb)Note
-----PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------- dynamic sampling used for this statement (level2)24 rows selected.
结论新增的数据执行计划中一样都是采用NVL(“B”,‘bb’)bb’方式。
2.4 表压缩后限制
1. 添加列报错
表压缩之后defaultnot null是可以操作的但是只有default是不行的。
SQL alter table test1 compress;Table altered.SQL alter table test1 add c number default 3 not null;Table altered.SQL alter table test1 add d number default 4;
alter table test1 add d number default 4*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
解决办法但是这种方法只有之后新插入的行才是有值的之前已经存在的行是没有的还是空所以需要找时间批量提交修改。
SQL alter table test1 add d number;Table altered.SQL alter table test1 modify d number default 4;Table altered.SQL select count(*) from test1 where d4;COUNT(*)
----------0-- 可以批量更改
SQL update test1 set d4 where d is null;2550368 rows updated.SQL commit;Commit complete.
2. 删除列报错
-- 直接drop column报错
SQL alter table test1 drop column c;
alter table test1 drop column c*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables-- 尝试先把列设成unused方式再删除一样报错
SQL alter table test1 set unused column c;Table altered.SQL alter table test1 drop unused columns;
alter table test1 drop unused columns
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
结论根据Doc ID 1574318.1 描述暂时没有好的解决方法只能针对oltp compress 方式可以删除列。
SQL alter table t2 compress for oltp; --加上for oltpTable altered.SQL alter table t2 drop column id;Table altered.SQL alter table t2 compress;Table altered.SQL alter table t2 drop column T1_ID;
alter table t2 drop column T1_ID*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
官方文档
3. 在Oracle19C 环境下测试
3.1 增加字段
情况1只有default值
-- 不加not null约束
SQL alter table test1 add a varchar2(100) default aa;Table altered.Elapsed: 00:00:00.02SQL select count(*) from test1 where aaa;COUNT(*)
----------4643072Elapsed: 00:00:01.05SQL select bytes from user_segments where segment_nameTEST1;BYTES
----------739246080Elapsed: 00:00:00.01
情况2defaultnot null
--加上not null 约束
SQL alter table test1 add b varchar2(100) default bb not null;Table altered.Elapsed: 00:00:00.06SQL select count(*) from test1 where bbb;COUNT(*)
----------4643072Elapsed: 00:00:00.23SQL select bytes from user_segments where segment_nameTEST1;BYTES
----------739246080Elapsed: 00:00:00.01
可以看到
1.和11g不一样defaultnot null 和只加default 速度一样快。
2.两种方式表的大小都没有变化说明表都没有实际去更新物理行都只是更新了数据字典。
3.2 查询test1表中添加的默认值
SQL select object_id from dba_objects where object_name TEST1 and ownerSXC;OBJECT_ID
----------73797SQL set line222
SQL select colnum,binarydefval from ecol$ where tabobj# 73797;COLNUM BINARYDEFVAL
---------- -----------------------28 616129 6262-- 比11g 多出一行不管是否有加上not null 条件都被存储到了ecol$表中。SQL SET SERVEROUT ON
SQL DECLARE2 V_BLOB VARCHAR2(32767) DEFAULT 6161;3 BEGIN4 FOR I IN 1..LENGTH(V_BLOB)/2 LOOP5 DBMS_OUTPUT.PUT(CHR(TO_NUMBER(SUBSTR(V_BLOB, (I-1) * 2 1, 2), XXX)));6 END LOOP;7 DBMS_OUTPUT.NEW_LINE;8 END;9 /
aaSQL DECLARE2 V_BLOB VARCHAR2(32767) DEFAULT 6262;3 BEGIN4 FOR I IN 1..LENGTH(V_BLOB)/2 LOOP5 DBMS_OUTPUT.PUT(CHR(TO_NUMBER(SUBSTR(V_BLOB, (I-1) * 2 1, 2), XXX)));6 END LOOP;7 DBMS_OUTPUT.NEW_LINE;8 END;9 /
bbPL/SQL procedure successfully completed.
3.3 对比执行计划
如果我们从表中检索对应的列时候我们对比一下执行计划
情况1只有default值
SQL select count(*) from test1 where aaa;COUNT(*)
----------4643072SQL select * from table(dbms_xplan.display_cursor);PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 12bwjcp0zanhm, child number 0
-------------------------------------select count(*) from test1 where aaaPlan hash value: 3896847026----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 394 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| TEST1 | 72548 | 212K| 394 (1)| 00:00:01 |
----------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT(SYS_NC00027$,0)),NULL,NVL(A,aa),0,NVL(A,aa),1,A)aa)20 rows selected.Elapsed: 00:00:14.13
结论和11g 不一样这里即采用了nvl函数转化也多了一个隐藏列SYS_NC00027$通过下面的SQL可以查询到。这个隐藏列是在添加default的列的时候增加的添加defaultnot null 的列没有。
SQL select column_name,column_id,hidden_column,virtual_column from dba_tab_cols where table_nameTEST1 and ownerSXC;COLUMN_NAME COLUMN_ID HIDDEN VIRTUA
---------------------------------------- ------ ------------------
OWNER 1 NO NO
...
MODIFIED_VSNID 26 NO NO
SYS_NC00027$ YES NO -- 隐藏列
A 27 NO NO
B 28 NO NO
情况2defaultnot null
SQL select count(*) from test1 where bbb;COUNT(*)
----------4643072SQL select * from table(dbms_xplan.display_cursor);PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9xurwt6ytwjf0, child number 0
-------------------------------------
select count(*) from test1 where bbbPlan hash value: 3896847026----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 394 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| TEST1 | 72548 | 212K| 394 (1)| 00:00:01 |
----------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - filter(NVL(B,bb)bb)19 rows selected.Elapsed: 00:00:01.22
结论和11g 方式一样都是采用filter(NVL(“B”,‘bb’)‘bb’)过滤读取列数据并且使用了nvl函数。
※思考
1、如果我们在列上建索引呢
2、后续新增加的数据呢结论测试下来大体结果和11g 一样。 都会使用隐藏列SYS_NC00027$和NVL函数转化。
3.4 表压缩后限制
1. 添加列
SQL alter table test1 compress;Table altered.SQL alter table test1 add c number default 3 not null;Table altered.SQL alter table test1 add d number default 4;Table altered.
结论和11g不一样19c表compress之后 两种方式都能添加列 。
但是在12c环境下表compress之后 两种方式都不能添加列 。
SQL create table T1 ( a number, b varchar2(10) ) compress ;Table created.SQL alter table T1 add c varchar2(20) default test not null ;
alter table T1 add c varchar2(20) default test not null*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tablesSQL alter table T1 add c varchar2(20) default test ;
alter table T1 add c varchar2(20) default test*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
2. 删除列报错
-- 直接drop column报错
SQL alter table test1 drop column c;
alter table test1 drop column c*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables-- 尝试先把列设成unused方式再删除一样报错
SQL alter table test1 set unused column c;Table altered.SQL alter table test1 drop unused columns;
alter table test1 drop unused columns
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
结论和11g一样暂时没有好的解决方法只能针对oltp compress 方式可以删除列。
