首页 > Linux操作系统 > Linux操作系统 > constraint states

constraint states

原创 Linux操作系统 作者:v_fantasy 时间:2009-02-17 14:53:54 0 删除 编辑

constraint states includes:

  • ENABLE ensures that all incoming data conforms to the constraint

  • DISABLE allows incoming data, regardless of whether it conforms to the constraint

  • VALIDATE ensures that existing data conforms to the constraint

  • NOVALIDATE means that some existing data may not conform. to the constraint

In addition:

  • ENABLE VALIDATE is the same as ENABLE. The constraint is checked and is guaranteed to hold for all rows.

  • 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.

    In an ALTER TABLE statement, ENABLE NOVALIDATE resumes constraint checking on disabled constraints without first validating all data in the table.

  • DISABLE NOVALIDATE is the same as DISABLE. The constraint is not checked and is not necessarily true.

  • DISABLE VALIDATE disables the constraint, drops the index on the constraint, and disallows any modification of the constrained columns.

    For a UNIQUE constraint, the DISABLE VALIDATE state enables you to load data efficiently from a nonpartitioned table into a partitioned table using the EXCHANGE PARTITION clause of the ALTER TABLE statement.

Transitions between these states are governed by the following rules:

  • ENABLE implies VALIDATE, unless NOVALIDATE is specified.

  • DISABLE implies NOVALIDATE, unless VALIDATE is specified.

  • VALIDATE and NOVALIDATE do not have any default implications for the ENABLE and DISABLE states.

  • When a unique or primary key moves from the DISABLE state to the ENABLE state, if there is no existing index, a unique index is automatically created. Similarly, when a unique or primary key moves from ENABLE to DISABLE and it is enabled with a unique index, the unique index is dropped.

  • When any constraint is moved from the NOVALIDATE state to the VALIDATE state, all data must be checked. (This can be very slow.) However, moving from VALIDATE to NOVALIDATE simply forgets that the data was ever checked.

  • Moving a single constraint from the ENABLE NOVALIDATE state to the ENABLE VALIDATE state does not block reads, writes, or other DDL statements. It can be done in parallel.

分区是为了实现对大数据的更高性能的操作,可以对表和索引进行分区。分区的方法包括Range, Hash, List, range-hash, range-list.
Index的分区有两种,global index指的是index的分区不和底层的table一致,它是用户自定义的,local index则与底层的table有一样的列,一样的分区数和一样的分区边界。
对分区表的维护包括add, coalesce, drop, exchange, merge, modify, move, redefine, rebuild, rename, split, truncate.
exchange partition可以用于各种不同的分区表之间,分区表与非分区表之间的转换,这是通过交换它们之间的data segments实现的。
在数据仓库中,exchange partition可以实现对新的增量数据的高速加载,同时,还可以实现对老数据分区的清空。

对于分区表上的UNIQUE约束而言,通过指定DISABLE VALIDATION, exchange partition可以实现对数据的更高效率的加载。同时,通过指定DISABLE VALIDATION, UNIQUE约束将不需要创建index.
1. 索引会变得很大
2. 索引很少用于查询执行计划
3. 索引可能是global的,这会给维护操作带来不利的影响。

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。



  • 博文量
  • 访问量