唯一索引与使用唯一索引的唯一约束 [英] Unique index vs. unique constraint that uses a unique index

问题描述

我正在尝试比较两个架构，以确保它们是同步的.在一个模式中，我在2列上看到一个唯一索引.除此之外，我看到了使用唯一索引的唯一约束.

I am trying to compare two schemas to ensure that they are in sync. In one schema, I see a unique index on 2 columns. In addition to this, I see a unique constraint that uses the unique index.

我是Oracle的新手，但根据 唯一约束只能确保没有重复，并且不一定要建立索引.为了提高性能，应在FK列上添加索引，以使约束得以有效执行.

I'm new to Oracle, but my general understanding and according to this a unique constraint only ensures no duplicates, that it isn't necessarily indexed. For performance an index should be added over the FK columns to allow the constraint to be performed efficiently.

在目标数据库中，我只有唯一索引HD_FILL_DISPNSNG_FEE_VAL_AK1.

In my target database, I have only the unique index HD_FILL_DISPNSNG_FEE_VAL_AK1.

由于该索引被标记为"UNIQUE"，这不提供索引的唯一约束和性能优势吗?我在该主题上看到了很多帖子，例如引用的链接，但是在将其应用于这种特殊情况时遇到了麻烦.

Since this index it is marked "UNIQUE" doesn't this provide both the unique constraint and the performance benefits of an index? I see many posts on the subject like the referenced link but I am having trouble applying it to this particular situation.

问题:由于我拥有唯一索引，因此删除UNIQUE CONSTRAINT是否会有任何不利影响?同样，使用索引不同于唯一索引的唯一约束又如何呢?

Questions: Would there be any downside to removing the UNIQUE CONSTRAINT since I have the unique index? Similarly, how does a unique constraint that uses an index different than a unique index?

CREATE UNIQUE INDEX RXFINODS_O.HD_FILL_DISPNSNG_FEE_VAL_AK1 ON RXFINODS_O.HD_FILL_DISPNSNG_FEE_VAL
(CVRG_TY_CD, VAL_EFF_START_DT)
LOGGING
TABLESPACE RXFINODS_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

-- 
-- Non Foreign Key Constraints for Table HD_FILL_DISPNSNG_FEE_VAL 
-- 
ALTER TABLE RXFINODS_O.HD_FILL_DISPNSNG_FEE_VAL ADD (
  CONSTRAINT HD_FILL_DSPSNG_FEE_VAL_AK1
  UNIQUE (CVRG_TY_CD, VAL_EFF_START_DT)
  USING INDEX .HD_FILL_DISPNSNG_FEE_VAL_AK1
  ENABLE VALIDATE);

推荐答案

，它不一定要建立索引."出于性能原因，Oracle总是要确保任何唯一约束(包括主键约束)都有一个索引.默认情况下，创建唯一约束时，Oracle会创建一个唯一索引.

"that it isn't necessarily indexed." Oracle, for performance reasons, is always going to insure that there is an index for any unique constraint (including primary key constraints.) By default Oracle will create a unique index when the unique constraint is created.

使用不同于唯一索引的索引的唯一约束如何?"

"how does a unique constraint that uses an index different than a unique index?"

索引允许重复的条目.

示例1:可延展的约束.插入时，索引需要允许重复的条目，因为在事务提交之前允许重复.然后在提交时，Oracle检查重复项.

Example 1: Deferable constraints. On insert, the index needs to allow duplicate entries because duplicates are allowed until the transaction commits. Then on commit, Oracle checks for duplicates.

示例2:需要在A，B和A上建立索引. C出于性能原因. A上需要唯一的约束.可以告诉Oracle使用A，B和A上的索引.即使索引不唯一，C也会对A强制执行唯一性约束.因此，Oracle不必维护另一个索引.

Example 2: An index is needed on A, B & C for performance reasons. A unique constraint is needed on A. One can tell Oracle to use the index on A, B & C to enforce the unique constraint on A even if the index is non-unique. So Oracle will not have to maintain another index.

由于我拥有唯一索引，因此删除UNIQUE CONSTRAINT是否有任何不利影响?"

"Would there be any downside to removing the UNIQUE CONSTRAINT since I have the unique index?"

消除约束没有任何余地.

There is no upside to removing the constraint.

这篇关于唯一索引与使用唯一索引的唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！