ITPub博客

首页 > 应用开发 > IT综合 > Enabled Novalidated的作用!

Enabled Novalidated的作用!

原创 IT综合 作者:warehouse 时间:2008-04-24 12:16:50 0 删除 编辑

By placing a constraint in the enabled novalidated state, you enable the constraint without locking the table.如doc所言,把constriant由状态disable变成enable时在表上会产生lock,期间所有的DML, queries, or DDL都被阻塞(If you change a constraint from disabled to enabled, then the table must be locked. No new DML, queries, or DDL can occur, because there is no mechanism to ensure that operations on the table conform to the constraint during the enable operation.)。阻塞的原因是:because there is no mechanism to ensure that operations on the table conform to the constraint during the enable operation。而enabled novalidated对新数据生效对表里现有数据不产生作用,正是利用这个特性,oracle巧妙的实现了约束从enabled novalidated到enable不产生lock,从而也就不会引起在enabled novalidated变成enable期间阻塞表上的读写操作。oracle利用在表上的一致性查询来校验老数据是否满足约束,如果满足就把其状态变成enable,如果不一直就报错,在修改期间如果有新数据插入则enabled novalidated会发挥作用来校验。(The enabled novalidated state prevents operations violating the constraint from being performed on the table).

其实Enabled Novalidated的作用:

表面看是仅仅对新数据产生作用,对老数据不产生作用;实质上是解决了把约束的状态从disable变成enable而引起的lock从而阻塞对该表的读写操作!

[@more@]

Using Enabled Novalidated Constraints

An enabled novalidated constraint behaves similarly to an enabled validated constraint for new data. Placing a constraint in the enabled novalidated state signifies that any new data entered into the table must conform to the constraint. Existing data is not checked. By placing a constraint in the enabled novalidated state, you enable the constraint without locking the table.

If you change a constraint from disabled to enabled, then the table must be locked. No new DML, queries, or DDL can occur, because there is no mechanism to ensure that operations on the table conform to the constraint during the enable operation. The enabled novalidated state prevents operations violating the constraint from being performed on the table.

An enabled novalidated constraint can be validated with a parallel, consistent-read query of the table to determine whether any data violates the constraint. No locking is performed, and the enable operation does not block readers or writers to the table. In addition, enabled novalidated constraints can be validated in parallel: Multiple constraints can be validated at the same time and each constraint's validity check can be determined using parallel query.

Use the following approach to create tables with constraints and indexes:

  1. Create the tables with the constraints. NOT NULL constraints can be unnamed and should be created enabled and validated. All other constraints (CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY) should be named and created disabled.


    Note:

    By default, constraints are created in the ENABLED state.


  2. Load old data into the tables.
  3. Create all indexes, including indexes needed for constraints.
  4. Enable novalidate all constraints. Do this to primary keys before foreign keys.
  5. Allow users to query and modify data.
  6. With a separate ALTER TABLE statement for each constraint, validate all constraints. Do this to primary keys before foreign keys. For example,
    CREATE TABLE t (a NUMBER CONSTRAINT apk PRIMARY KEY DISABLE,
    b NUMBER NOT NULL);
    CREATE TABLE x (c NUMBER CONSTRAINT afk REFERENCES t DISABLE);
    

    Now you can use Import or Fast Loader to load data into table t.

    CREATE UNIQUE INDEX tai ON t (a); 
    CREATE INDEX tci ON x (c); 
    ALTER TABLE t MODIFY CONSTRAINT apk ENABLE NOVALIDATE;
    ALTER TABLE x MODIFY CONSTRAINT afk ENABLE NOVALIDATE;
    

    At this point, users can start performing INSERTs, UPDATEs, DELETEs, and SELECTs on table t.

    ALTER TABLE t ENABLE CONSTRAINT apk;
    ALTER TABLE x ENABLE CONSTRAINT afk;
    

    Now the constraints are enabled and validated.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/19602/viewspace-1002922/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    717
  • 访问量
    5123188