`

如何对线上数据表创建唯一键或者主键约束

阅读更多
由于oracle中主键和唯一键的执行是通过唯一索引来保证的,在增加主键或者唯一键约束的时候需要建立相关的索引。因此,在线创建拥有大批量数据的表的约束的时候,会不会出现阻塞呢?来实验一下。
-- sid=147,先看看表T有没有已经可以使用的索引
SQL> select index_name,UNIQUENESS,table_name from user_indexes where table_name='T';
no rows selected

-- sid=143
SQL> select count(*) from t;
  COUNT(*)
----------
   1061469

SQL> alter table t
  2  add constraint uk_t_owner unique (owner,object_name,subobject_name,object_id,data_object_id,object_type)
  3  enable novalidate;
add constraint uk_t_owner unique (owner,object_name,subobject_name,object_id,data_object_id,object_type)
               *
ERROR at line 2:
ORA-02299: cannot validate (SCOTT.UK_T_OWNER) - duplicate keys found

-- sid=159,几乎是在同一个时间里面来执行下面这个句子
SQL> select sid,type,lmode,request from v$lock where sid=143;
       SID TY      LMODE    REQUEST
---------- -- ---------- ----------
       143 DL          3          0
       143 DL          3          0
       143 TM          4          0
       143 TM          3          0
       143 TX          6          0

-- sid=147,143session返回错误以后,执行下面这个句子,看看有没有索引建立
SQL> select index_name,UNIQUENESS,table_name from user_indexes where table_name='T';
no rows selected
从上面的这个实验,可以看出:
1)尽管我使用了ENABLE NOVALIDATE来创建唯一约束,但是由于表本来没有一个索引来给这个约束使用,是创建不成功的。原因就是,oracle由于没有发现现在表中没有可以使用的索引,因此它要自动来创建一个唯一索引给唯一约束使用,但是由于表中存在重复数据,因此就创建唯一索引不成功,于是返回错误。
2)在创建唯一约束的时候,oracle会锁住表,对表所加的锁包括DL锁,这个锁的作用是Direct loader parallel index create,因此可以知道它是在并行创建索引,这也证实了这个说法:当你要建立唯一约束和主键约束时,如果没有索引可以使用,oracle会自动创建相关索引。oracle还会对表加S TM锁和RX TM锁,这两个锁联合出现,导致在其执行期间,其他人除了能对表加RS TM锁以外,不能加任何其他的TM锁。最主要的是,请看例子中的红色字体,oracle对表T的数据加了一个X锁,很显然,如果是一个具有大量数据的在线的表,这会导致其他会话阻塞。
       上面是在没有索引的情况下去建立唯一约束的,会导致以上诸多问题。换一种思路,如果已经存在索引,再去建立唯一约束,会不会可能导致其他会话阻塞现象呢?继续做实验,很显然,我的表中有重复数据,因此需要建立一个非唯一约束(其实这也是一种方法,虽然oracle的主键和唯一键约束底层要有唯一索引,但是我们可以只给它一个非唯一索引,只要这个表中的数据不重复就可以了,因为oracle在判断的时候,还是要通过查询索引有没有重复值来达到是不是满足唯一的要求)。
-- sid=143
SQL> create index uk_t_owner
  2  on t(owner,object_name,subobject_name,object_id,data_object_id,object_type) ;
Index created.

-- sid=159
SQL> select sid,type,lmode,request from v$lock where sid=143;
       SID TY      LMODE    REQUEST
---------- -- ---------- ----------
       143 DL          3          0
       143 DL          3          0
       143 TM          4          0
       143 TM          3          0
       143 TX          6          0

-- sid=147
SQL> select index_name,UNIQUENESS,table_name from user_indexes where table_name='T';
INDEX_NAME                     UNIQUENES TABLE_NAME
------------------------------ --------- ------------------------------
UK_T_OWNER                     NONUNIQUE T
       可以发现,在创建索引的时候,在表上所加的锁和前面的实验一摸一样,因此在创建索引也会阻塞其他会话。从两个实验的比较,可以看到,前面实验oracle是在创建索引的时候就失败了,它当时所做的唯一工作就是在创建索引。
       那难道就没有方法了吗?就没有方法,在拥有大量数据,且在线的表上添加唯一约束或者主键约束,而不导致阻塞了吗?有,在oracle的SQL Reference文档中创建索引,有一个关键字可以解决这个问题,“ONLINE”,文档中是这样描述的:
ONLINE Specify ONLINE to indicate that DML operations on the table will be allowed during creation of the index.
       那来实验一下,看看结果:
-- sid=143
SQL> drop index uk_t_owner;
Index dropped.

-- sid=147
SQL> select index_name,UNIQUENESS,table_name from user_indexes where table_name='T';
no rows selected

-- sid=143
SQL> create index uk_t_owner
  2  on t(owner,object_name,subobject_name,object_id,data_object_id,object_type) online;
Index created.

-- sid=159
SQL> select sid,type,lmode,request from v$lock where sid=143;

       SID TY      LMODE    REQUEST
---------- -- ---------- ----------
       143 DL          3          0
       143 DL          3          0
       143 TM          2          0
       143 TM          4          0

-- sid=147
SQL> select index_name,UNIQUENESS,table_name from user_indexes where table_name='T';
INDEX_NAME                     UNIQUENES TABLE_NAME
------------------------------ --------- ------------------------------
UK_T_OWNER                     NONUNIQUE T
       从实验的结果可以看到,使用online创建索引的时候,oracle只对表加了RS TM和S TM锁,这两个锁的联合效果是,别的会话除了对表加RS TM锁外,还可以加S TM锁。这里最重要的一点是,使用online创建索引,oracle不对表中的数据加X锁,也就是说,这样就不会导致其他会话的阻塞。
       现在,索引已经建立,我们来建立约束,看会有什么样子的结果,请看实验结果。
-- sid=143,下面这个过程几乎瞬间完成
SQL> alter table t
  2  add constraint uk_t_owner unique (owner,object_name,subobject_name,object_id,data_object_id,object_type)
  3  enable novalidate;
Table altered
-- sid=159,由于上面操作的瞬间完成,因此没有测出有任何锁,但是并不代表没有加锁
SQL> select sid,type,lmode,request from v$lock where sid=143;
no rows selected
那么,到底在建立这个约束的时候,有没有加锁呢?我是没有测出来,我也没有找到相关的文档说明,这里是个空白,以后填补。
       在文档中,有关novalidate和validate是这样描述的:
VALIDATE ensures that existing data conforms to the constraint
NOVALIDATE means that some existing data may not conform to the constraint
ENABLE NOVALIDATE means that the constraint is checked, but it does not have to be true for all rows. This allows existing rows to violate the constraint, while ensuring that all new or modified rows are valid.
ENABLE VALIDATE is the same as ENABLE. The constraint is checked and is
guaranteed to hold for all rows.

Moving a single constraint from the ENABLE NOVALIDATE state to the ENABLE VALIDATE statedoes not block reads, writes, or other DDL statements. It can be done in parallel.
       也就是说,这个时候约束从enable novalidate到enable validate状态,不会产生阻塞。实验一下,看看结果。
-- sid = 143,由于我的数据量比较大,这个过程持续了相当长的时间
SQL> alter table t modify constraint uk_t_owner enable validate;
alter table t modify constraint uk_t_owner enable validate
*
ERROR at line 1:
ORA-02299: cannot validate (SCOTT.UK_T_OWNER) - duplicate keys found

-- sid =159,在143 session执行检查期间,查看其加锁情况
SQL> select sid,type,lmode,request from v$lock where sid=143;
no rows selected
       可见,的确没有加锁。
       因此,从以上实验,我们可以得出,在一个拥有大量数据,在线的(也就是说很有可能有其他来操作这个表)表上面建立一个主键约束或者唯一键约束,为了防止出现阻塞的现象,可使用下面的一般性步骤。
1.       先检查这个表有没有符合建立这个约束的索引。
2.       如果没有,先以online方式建立符合约束的非unique的索引。
3.       建立状态为ENABLE NOVALIDATE的约束。
4.       更改约束状态至ENABLE VALIDATE状态。
在这个步骤之中,如果你不先手工online建立索引,而是期待oracle在建立约束的时候自动建立索引,这样也会可能导致阻塞的,有第一个实验的结果就可以看出,因为oracle会自动给你建立一个unique索引,而且是非online方式。
       最后,来考虑一种特殊状态的方式建立约束,看看有些什么值得我们发现的。
-- sid = 143
SQL> alter table t
  2  drop unique(owner,object_name,subobject_name,object_id,data_object_id,object_type)
  3  cascade drop index;
Table altered.

-- sid = 147
SQL> select index_name,UNIQUENESS,table_name from user_indexes where table_name='T';
no rows selected

-- sid = 143,下面这个过程也几乎是在瞬间完成
SQL> alter table t add constraint uk_t_owner
  2  unique (owner,object_name,subobject_name,object_id,data_object_id,object_type)
  3  using index
  4  disable novalidate;
Table altered.

-- sid = 159,无法及时看到锁情况,但是并不代表没有加锁
SQL> select sid,type,lmode,request from v$lock where sid=143;
no rows selected

-- sid = 147
SQL> select index_name,UNIQUENESS,table_name from user_indexes where table_name='T';
no rows selected
       从上面这个实验,可以发现一个重要的问题,就是最后的红色字体部分,可以看出,当以disable novalidate建立主键或者唯一键约束时,如果先前表中没有符合条件的索引,oracle也不会自动创建索引。
       上面所说的阻塞现象,这样的一种现象,如果不是online方式创建索引,那么在建立索引的时候,oracle会对这个表中的所有数据行加上X锁,也就是别的会话在此期间不能对表做任何的DML操作。
       下面还有一种阻塞现象,我们先来看实验:
-- sid = 147
SQL> update t set object_id=20 where object_name='I_USER1' and owner='SYS';
21 rows updated.
-- sid = 143,发生阻塞
SQL> create index uk_t_owner
  2  on t(owner,object_name,subobject_name,object_id,data_object_id,object_type) online;
-- sid = 159
SQL> select sid,addr,kaddr,type,lmode,request from v$lock where sid IN (143,147) ORDER BY sid,addr,kaddr;
       SID ADDR     KADDR    TY      LMODE    REQUEST
---------- -------- -------- -- ---------- ----------
       143 42DC4528 42DC4540 TM          2          4
       143 42DC45D4 42DC45EC TM          4          0
       143 443BABF8 443BAC0C DL          3          0
       143 443BACB0 443BACC4 DL          3          0
       147 42DC447C 42DC4494 TM          3          0
       147 42E36C0C 42E36D28 TX          6          0
-- sid = 147
SQL> commit;
Commit complete.
-- sid = 159,马上执行
SQL> select sid,addr,kaddr,type,lmode,request from v$lock where sid IN (143,147) ORDER BY sid,addr,kaddr;
       SID ADDR     KADDR    TY      LMODE    REQUEST
---------- -------- -------- -- ---------- ----------
       143 42DC4528 42DC4540 TM          2          0
       143 42DC45D4 42DC45EC TM          4          0
       143 443BABF8 443BAC0C DL          3          0
       143 443BACB0 443BACC4 DL          3          0
--隔断时间以后
SQL> select sid,addr,kaddr,type,lmode,request from v$lock where sid IN (143,147) ORDER BY sid,addr,kaddr;
       SID ADDR     KADDR    TY      LMODE    REQUEST
---------- -------- -------- -- ---------- ----------
       143 42DC39D4 42DC3A1C TS          6          0
       143 42DC4528 42DC4540 TM          2          0
       143 42DC45D4 42DC45EC TM          4          0
       143 42E1A374 42E1A490 TX          6          0
       143 443BABF8 443BAC0C DL          3          0
       143 443BACB0 443BACC4 DL          3          0
-- sid = 143
Index created.
       也就是说,如果在以online方式创建索引以前,已经有其他会话更新了某些数据而没有提交,那么在创建索引的时候,会发生阻塞,知道那个会话提交完成后才能继续。另外最后又多了X TX锁和X TS锁,我猜想是因为由于在create index之前的update的行,在index编排到它们的时候,oracle认为这些行可能会发生更改,因此又在表上增加了这两个锁。
分享到:
评论

相关推荐

    rose建立数据库表组图

    主键是表中唯一标识一条记录的字段,对于数据库的完整性和一致性至关重要。 5. **创建关联(外键)**:如果表之间存在关联,可以通过创建关联类或直接在两个类之间画线来表示。关联线上的角色名可以代表外键字段名...

    (完整word版)数据库原理及应用教程答案.doc

    4. **完整性控制**: 数据完整性规则确保数据库中的数据准确无误,包括实体完整性(主键约束)、参照完整性和用户定义的完整性。 5. **数据库管理系统(DBMS)**: DBMS是用于创建、操作和管理数据库的软件,例如...

    erd.concepts.v5.1.0.0

    7. 约束:定义了数据的规则,如非空约束、唯一性约束和参照完整性约束。 标签 "erd.concepts.v5.1.0.0" 再次确认了讨论的主题,并可能提示这个版本增加了对某些概念的支持或改进。 至于压缩包子文件 "erd.concepts...

    【数据库设计报告】水果销售管理系统数据库设计报告.docx

    - 包括主键约束、外键约束、唯一性约束等。 8. **索引设计**: - 设计了索引以加快查询速度。 - 如对客户表中的姓名字段建立索引,便于快速搜索。 #### 五、心得体会 - **总结反思**: - 分享了项目开发过程...

    京东金融(网银在线)数据库开发管理规范-TABLE

    - **约束的命名**:约束名最大长度为30位,主键、唯一性、检查约束等均有特定的命名规则。 - **索引的命名**:索引名最大长度为30位,采用“表名_字段名_IDX”的格式。 - **脚本命名**:创建表脚本和修改线上表...

    php student -学籍管理系统1.zip

    通常,我们会创建如“学生表”、“课程表”、“成绩表”等数据表。学生表可能包含字段如学号、姓名、性别、出生日期等;课程表则记录课程名称、教师信息等;成绩表则关联学生和课程,记录每个学生的成绩。这些表格...

    数据库开发规范

    - 索引命名有固定格式,如主键约束`pk_表名_字段名`,唯一约束`uniq_表名_字段名`,普通索引`idx_表名_字段名`。 - 索引名称长度不超过30个字符,单表索引数量不超过5个,索引字段数建议不超过5个。 - 索引顺序应...

    数据库合集 E-R图属性集

    7. **数据库设计过程**:从E-R模型到实际数据库的创建,包括需求分析、概念设计(绘制E-R图)、逻辑设计(转换为关系模型)、物理设计(确定表的索引、分区等物理特性)。这个过程中,E-R图起着至关重要的作用,因为...

    2019-05-31-MySQL数据库设计规范1

    3. 适当地设计主键,确保唯一性和稳定性,一般使用整型自增字段。 4. 正确使用索引,尤其是对频繁查询的列。 2.1.3 列数据类型优化 选择最小的数据类型以节省空间,如使用TINYINT代替INT,VARCHAR代替CHAR。对于...

    mysql_数据库规范.docx

    - **索引设计**:合理创建主键和唯一索引,针对查询频繁的列建立普通索引,避免过多索引导致写操作性能下降。 - **分库分表、分区表**:根据业务需求,当数据量过大时,考虑水平分库分表或垂直分区,提高查询效率。 ...

    BBS毕业设计 Java web

    4. **SQL脚本**:编写SQL脚本来创建这些表,包括定义字段类型、长度、约束(如主键、外键、唯一性)以及索引以优化查询性能。 5. **前端技术**:HTML、CSS和JavaScript用于构建用户界面。使用模板引擎(如JSP、...

    数据库规范.pdf

    13. **主键设置**:每张表必须有一个主键,确保数据的唯一性。 14. **字段定义**:字段应设为`NOT NULL`并设置默认值,避免空值带来的问题。 15. **手机号存储**:使用`varchar(20)`存储手机号码,适应各国格式。 ...

    1850001-魏鹏程-实验(十)-数据库系统原理实验报告纸1

    通过这些表的创建和数据插入,魏鹏程同学成功地模拟了一个完整的饭店管理系统,涵盖了员工管理、客户管理、菜品管理、预定管理、桌台管理和盈利统计等多个关键功能,充分展示了对数据库设计和关系数据库理论的理解和...

    基于jsp的农业推广项目申报与管理系统源码数据库.doc

    在MySQL中创建以上提到的数据表,并定义相应的字段和约束条件。例如,在`users`表中,设置主键、唯一约束等来确保数据的完整性和一致性。 #### 四、后台系统和数据库的配置 ##### 4.1 后台服务器配置 - **操作...

    Inventory-Database-Project:这是我数据库课程中的一个项目,我们的任务是从ERD制作数据库,然后在SQL Oracle Database中进行编码

    关系模式是数据库的实际结构,它包括表名、列名(属性)、数据类型以及任何约束(如主键、唯一性约束等)。在这个过程中,每个实体变成一张表,实体的属性成为表的列,而关系则通过外键实现。 在Oracle SQL ...

Global site tag (gtag.js) - Google Analytics