Oracle 唯一索引 vs 非唯一索引

在本文中，我们将介绍 Oracle 数据库中的唯一索引和非唯一索引的概念、用法和区别，并通过示例说明其使用场景和效果。

1. 唯一索引（Unique Index）

1.1 概念

唯一索引是用来确保表中的索引列的数值是唯一的。唯一索引的作用是使数据库的一列或一组列具有唯一性约束，即不允许出现重复的值。

1.2 创建唯一索引

在创建表时，可以在列上创建唯一索引，也可以在表创建后通过 ALTER TABLE 语句添加唯一索引。

以创建表时创建唯一索引为例：

CREATE TABLE student (
  id NUMBER(10) PRIMARY KEY,
  name VARCHAR2(50),
  age NUMBER(3)
);
/
CREATE UNIQUE INDEX student_id_uindex ON student (id);

或者在表创建后添加唯一索引：

ALTER TABLE student ADD CONSTRAINT student_id_uindex UNIQUE (id);

1.3 唯一索引的用途

唯一索引可以确保表中的某个或某些列的数值的唯一性。使用唯一索引可以提高查询数据的性能，避免重复记录的插入。

1.4 示例说明

以下是一个示例，展示如何使用唯一索引来确保表中的某一列的唯一性：

-- 创建表
CREATE TABLE employee (
  emp_id NUMBER(10) PRIMARY KEY,
  emp_name VARCHAR2(50),
  emp_email VARCHAR2(100)
);

-- 创建唯一索引
CREATE UNIQUE INDEX employee_email_uindex ON employee (emp_email);

-- 插入记录时，违反唯一性约束将导致插入失败
INSERT INTO employee (emp_id, emp_name, emp_email) VALUES (1, 'John', 'john@example.com');
INSERT INTO employee (emp_id, emp_name, emp_email) VALUES (2, 'Jane', 'john@example.com'); -- 插入失败

2. 非唯一索引（Non-Unique Index）

2.1 概念

非唯一索引是用来提高查询数据的性能，但不要求索引列的数值是唯一的。非唯一索引可以包含重复的键值，它通过聚集索引或辅助索引的方式存储数据。

2.2 创建非唯一索引

在创建表时，可以在列上创建非唯一索引，也可以在表创建后通过 ALTER TABLE 语句添加非唯一索引。

以创建表时创建非唯一索引为例：

CREATE TABLE order (
  order_id NUMBER(10),
  customer_id NUMBER(10),
  order_date DATE,
  product_name VARCHAR2(50)
);
/
CREATE INDEX order_customer_id_index ON order (customer_id);

或者在表创建后添加非唯一索引：

ALTER TABLE order ADD CONSTRAINT order_customer_id_index UNIQUE (customer_id);

2.3 非唯一索引的用途

非唯一索引用于加快查询数据的速度，同时允许重复的值存在。它适用于需要快速访问表中的数据，但不需要保持唯一约束的场景。

2.4 示例说明

以下是一个示例，展示如何使用非唯一索引来提高查询性能：

-- 创建表
CREATE TABLE product (
  product_id NUMBER(10) PRIMARY KEY,
  product_name VARCHAR2(50),
  price NUMBER(8,2)
);

-- 创建非唯一索引
CREATE INDEX product_price_index ON product (price);

-- 查询价格小于等于100的产品
SELECT * FROM product WHERE price <= 100;