当前位置： 首页 > news >正文

个人网站建设营销推广最新疫情最新消息

news 2025/7/7 7:23:03
个人网站建设营销推广,最新疫情最新消息,南宁网站建站公司,蚌埠建设网站公司一、讲故事 你是否遇到过开发人员添加字段，导致数据库锁表问题？ 但是令开发疑惑的事，他们添加字段，有的时候很快，有的时候很慢？ 为什么呢？ 询问得知，**加的慢时候是带上了default默…

一、讲故事

你是否遇到过开发人员添加字段，导致数据库锁表问题？
但是令开发疑惑的事，他们添加字段，有的时候很快，有的时候很慢？ 为什么呢？
询问得知，**加的慢时候是带上了default默认值，如果表的数据量很大，那么会花费很长时间。在加字段期间，表上还会加6级锁，连select都会被阻塞。**这在生产系统是不可接受的。
所以，任何DDL操作，都应该询问DBA是否有风险。

从11g开始，这种情况有了明显的改善。Oracle 11g引入了元数据唯一默认值的概念。如果增加的列符合default+not 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_name='TEST1';BYTES
----------301989888--19c 环境下表初始大小：SQL> select bytes from user_segments where segment_name='TEST1';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 a='aa';COUNT(*)
----------2550400
Elapsed: 00:00:00.24SQL> select bytes from user_segments where segment_name='TEST1';BYTES
----------578813952Elapsed: 00:00:00.01
情况2：default+not 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 b='bb';COUNT(*)
----------2550400Elapsed: 00:00:00.23SQL> select bytes from user_segments where segment_name='TEST1';BYTES
----------578813952Elapsed: 00:00:00.01

可以看到：

1.default+not null 的执行时间远小于只有default值的情况。

2.default+not null 表的大小也没有变化，说明表并没有实际去更新物理行，只是更新了数据字典。

这是11g对新增一个not null字段带有默认值进行了优化，默认值以及对应的表信息、列信息一起存储在一个新增数据字典表ecol$中 。

2.2 查询test1表中添加的默认值

SQL> select object_id from dba_objects where object_name = 'TEST1' and owner='SXC';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 a='aa';COUNT(*)
----------2550368SQL> select * from table(dbms_xplan.display_cursor);PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  b91qrsffc9t3d, child number 0
-------------------------------------
select count(*) from test1 where a='aa'Plan 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("A"='aa')Note
------ dynamic sampling used for this statement (level=2)PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------23 rows selected.

结论：采用filter(“A”=‘aa’) 过滤的方式，直接读取列数据。

情况2：default+not null
SQL> select count(*) from test1 where b='bb';COUNT(*)
----------2550368SQL> select * from table(dbms_xplan.display_cursor);PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  9xurwt6ytwjf0, child number 0
-------------------------------------
select count(*) from test1 where b='bb'Plan 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 (level=2)PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------23 rows selected.

结论：采用filter(NVL(“B”,‘bb’)=‘bb’)方式过滤，读取列数据，但是oracle认为default+not null 的方式添加的列是空列，所以用nvl函数做了一次转换。

※思考？
1、如果我们在列上建索引呢？
SQL> create index idx1 on test1 (b);Index created.SQL> select count(*) from test1 where b='bb';COUNT(*)
----------2550368SQL> select * from table(dbms_xplan.display_cursor);PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9xurwt6ytwjf0, child number 0
-------------------------------------
select count(*) from test1 where b='bb'Plan 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 (level=2)PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------23 rows selected.

结论：虽然从全表扫描变成索引扫描，但是一样是NVL(“B”,‘bb’)='bb’方式过滤数据。

2、后续新增加的数据呢？
-- 增加2行数据，一个用默认值，一个不用默认值。
SQL> select * from test1 where owner='T';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  owner='T' and b='bb';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  owner='T' and b='bb'Plan 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(("OWNER"='T' AND NVL("B",'bb')='bb'))Note
------ dynamic sampling used for this statement (level=2)22 rows selected.--访问非默认值，查看执行计划
SQL> select owner,b from test1 where  owner='T' and b='bbb';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  owner='T' and b='bbb'Plan 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("OWNER"='T')2 - access(NVL("B",'bb')='bbb')Note
-----PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------- dynamic sampling used for this statement (level=2)24 rows selected.

结论：新增的数据，执行计划中一样都是采用NVL(“B”,‘bb’)='bb’方式。

2.4 表压缩后限制

1. 添加列报错

表压缩之后，default+not 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 d=4;COUNT(*)
----------0-- 可以批量更改
SQL> update test1 set d=4 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 a='aa';COUNT(*)
----------4643072Elapsed: 00:00:01.05SQL> select bytes from user_segments where segment_name='TEST1';BYTES
----------739246080Elapsed: 00:00:00.01
情况2：default+not 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 b='bb';COUNT(*)
----------4643072Elapsed: 00:00:00.23SQL> select bytes from user_segments where segment_name='TEST1';BYTES
----------739246080Elapsed: 00:00:00.01

可以看到：

1.和11g不一样，default+not null 和只加default 速度一样快。

2.两种方式表的大小都没有变化，说明表都没有实际去更新物理行，都只是更新了数据字典。

3.2 查询test1表中添加的默认值

SQL> select object_id from dba_objects where object_name = 'TEST1' and owner='SXC';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 a='aa';COUNT(*)
----------4643072SQL> select * from table(dbms_xplan.display_cursor);PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  12bwjcp0zanhm, child number 0
-------------------------------------select count(*) from test1 where a='aa'Plan 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的列的时候增加的，添加default+not null 的列没有。

SQL> select column_name,column_id,hidden_column,virtual_column from dba_tab_cols where table_name='TEST1' and owner='SXC';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
情况2：default+not null
SQL> select count(*) from test1 where b='bb';COUNT(*)
----------4643072SQL> select * from table(dbms_xplan.display_cursor);PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9xurwt6ytwjf0, child number 0
-------------------------------------
select count(*) from test1 where b='bb'Plan 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 方式可以删除列。

http://www.dinnco.com/news/10698.html

相关文章：

  • 东台网站建设深圳网络营销网站设计
  • 网站建设安全性指标seo快速排名首页
  • 安阳网站建设哪家好拉新任务接单放单平台
  • 电影网站怎么做流量网站排名seo软件
  • 杭州网站运营厦门关键词排名提升
  • 济宁建设局网站招聘会网络软文营销是什么意思
  • 网站建设排行优化师是做什么的
  • 淘宝网店网站建设目的做网页
  • php网站整合discuz新手怎么做销售
  • 用动易建设网站教程营销做得好的品牌
  • 吉林住房和城乡建设厅网站小说网站排名免费
  • 龙岗区住房建设局网站高清免费观看电视网站
  • 兰州营销型网站建设软文推荐
  • 大视觉网站建设网络营销是什么意思
  • 合肥瑶海区系统优化软件推荐
  • 专做外贸衣服鞋网站有哪些seo专业培训课程
  • 企业邮箱注册域名是什么seort什么意思
  • 没有网站怎么做淘宝客谈谈你对seo概念的理解
  • bootstrap的网站模板网站自然优化
  • 专业网站建设公司兴田德润优惠吗搜索引擎优化seo的英文全称是
  • 专业手机网站公司哪家好郑州网站优化软件
  • 网站上怎么做动画广告视频2020最新推广方式
  • 怎么购买国外的域名seo云优化外包
  • POS机网站怎么做广告推广费用一般多少
  • 黄浦区seo网站建设杭州seo软件
  • WordPress子站站群各行业关键词
  • 最好建网站系统的软件查网站流量的网址
  • 做电影网站解析网站建设公司排行榜
  • 网站导航条做多高广州百度推广外包
  • 如何在网站做宣传seo名词解释