Oracle 数据库中的 索引、约束、视图

约束：就是指对插入数据的各种限制，约束既可在建表的时候直接声明，也可以为已经建好的表添加约束。
视图：就是一个封装了各种复杂查询的语句，就称之为视图。
索引：是一种用于提升查询效率的数据库对象。通过快速定位数据的方法，来减少磁盘的 I/O 操作。索引信息与表独立存放，并且Oracle 数据库自动使用和维护索引。

一、约束

1）非空约束

CREATE TABLE TEST
(
    ID   VARCHAR2(36) NOT NULL, // 该字段不能为空
    NAME VARCHAR2(200)
)
/
INSERT INTO TEST(NAME) VALUES ('jane'); // [23000][1400] ORA-01400: 无法将 NULL 插入 ("SNOW_ZY"."TEST"."ID")

2）主键约束

CREATE TABLE TEST
(
    ID   VARCHAR2(36) PRIMARY KEY, // 不能重复、不能为空
    NAME VARCHAR2(200)
)
/
INSERT INTO TEST(ID, NAME) VALUES ('123', 'jane');
INSERT INTO TEST(ID, NAME) VALUES ('123', 'jane'); // [23000][1] ORA-00001: 违反唯一约束条件 (SNOW_ZY.SYS_C00124287)

3）唯一约束

CREATE TABLE TEST
(
    ID   VARCHAR2(36) UNIQUE, // 值不能重复 (空值除外)
    NAME VARCHAR2(200)
)
/
INSERT INTO TEST(ID, NAME) VALUES ('123', 'jane');
INSERT INTO TEST(ID, NAME) VALUES ('123', 'jane'); // [23000][1] ORA-00001: 违反唯一约束条件 (SNOW_ZY.SYS_C00124287)

4）条件约束

CREATE TABLE TEST
(
    ID   VARCHAR2(36),
    NAME VARCHAR2(200),
    AGE NUMBER CHECK ( AGE BETWEEN 0 AND 150) // 插入的数据必须是满足某些条件
)
/
INSERT INTO TEST(ID, NAME, AGE) VALUES ('123', 'jane', 100);
INSERT INTO TEST(ID, NAME, AGE) VALUES ('123', 'jane', 200); // [23000][2290] ORA-02290: 违反检查约束条件 (SNOW_ZY.SYS_C00124289)

5）外键 (references : 参考、证明)

CREATE TABLE PERSON
(
    ID   VARCHAR2(36) PRIMARY KEY ,
    NAME VARCHAR2(200)
)
/
INSERT INTO PERSON(ID, NAME) VALUES ('123', 'jane');
INSERT INTO PERSON(ID, NAME) VALUES ('456', 'kobe');

CREATE TABLE BOOK
(
    BID VARCHAR2(36),
    BNAME VARCHAR2(200),
    PID VARCHAR2(36) REFERENCES PERSON(ID) --书只属于一个人, 插入该值时回去父表中查询有没有对应的记录

    --建立约束: book_pid_fk, 与person 中的pid 形成主+外键关系
    --CONSTRAINT b00k_pid_fk FOREIGN KEY(PID) REFERENCES PERSON(ID)
)
/
INSERT INTO BOOK(BID, BNAME, PID) VALUES ('000', '追风筝的人', '123');
INSERT INTO BOOK(BID, BNAME, PID) VALUES ('001', '解忧杂货铺', '111'); // [23000][2291] ORA-02291: 违反完整约束条件 (SNOW_ZY.SYS_C00124291) - 未找到父项关键字

6）级联删除

  接上，此时已经有了Person 表与Book 表，且Book 表中的PID 字段与Person 表的ID 建立了外键关系，假设现在Person表中一条记录消失了，那此时该PID 在Book 表中的那行记录也应该消失。

DELETE FROM PERSON WHERE ID = '123'; // [23000][2292] ORA-02292: 违反完整约束条件 (SNOW_ZY.SYS_C00124293) - 已找到子记录

  删除Person 表的某行记录，会提示已找到子记录，因为在book 表中该人的id 已经被使用了， 所以想要删除该人时，应该首先在book 中删除该人的书本信息。、

  如果想完成删除person 表中数据的时候，同时将book 表中用到该id 的记录一起删除，则需要使用 级联删除（ON DELETE CASCADE）。

CREATE TABLE BOOK
(
    BID VARCHAR2(36),
    BNAME VARCHAR2(200),
    PID VARCHAR2(36),

    --建立约束: book_pid_fk, 与person 中的pid 形成主+外键关系
    CONSTRAINT b00k_pid_fk FOREIGN KEY(PID) REFERENCES PERSON(ID) ON DELETE CASCADE //级联删除, 删除主键的同时, 用到该主键当外键的记录也会同步删除
)
/
INSERT INTO BOOK(BID, BNAME, PID) VALUES ('000', '追风筝的人', '123');

DELETE FROM PERSON WHERE ID = '123'; //Person 表中 ID=123 的记录被删除, book 表中 PID=123 的记录被删除

7）语句添加约束

1. 为Person 表添加名为PERSON_PID_PK ，主键为ID 的约束

ALTER TABLE PERSON ADD CONSTRAINT PERSON_PID_PK PRIMARY KEY(ID);

--添加约束完美写法, 添加前先判断该约束是否存在
DECLARE number202203031610 number;
begin
    SELECT COUNT(1) INTO number202203031610 FROM ALL_CONSTRAINTS WHERE TABLE_NAME = UPPER('A39') AND CONSTRAINT_NAME = UPPER('PKNAME_A00');
    if number202203031610 = 0 then
        EXECUTE IMMEDIATE
        'ALTER TABLE A39 ADD CONSTRAINT PKNAME_A00 PRIMARY KEY (A00)';
    end if;
end;
/

2. 为Person 表的NAME 字段添加名为PERSON_NAME_UK 的唯一约束

ALTER TABLE PERSON ADD CONSTRAINT PERSON_NAME_UK UNIQUE(NAME);

3. 为Person 表的Age 字段添加名为PERSON_AGE_CK 的检查约束

ALTER TABLE PERSON ADD CONSTRAINT PERSON_AGE_CK CHECK(AGE BETWEEN 0 AND 150);

4. 为Book 表中的PID 字段添加与Person 表的主-外键约束，名为PERSON_BOOK_FK，要求带级联删除

ALTER TABLE BOOK ADD CONSTRAINT PERSON_BOOK_FK FOREIGN KEY (PID) REFERENCES PERSON(PID) ON DELETE CASCADE;

8）删除约束

ALTER TABLE BOOK DROP CONSTRAINT B00K_PID_FK; -- 约束直接被删除

9）禁 / 启用约束

ALTER TABLE BOOK DISABLE CONSTRAINT B00K_PID_FK; -- 约束会被禁用, 但是约束还存在

ALTER TABLE BOOK ENABLE CONSTRAINT B00K_PID_FK; -- 启用约束, 前提是表中的数据全都满足约束的条件

10）查看表中的约束

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM ALL_CONSTRAINTS WHERE TABLE_NAME = UPPER('BOOK');

CONSTRAINT_TYPE	USE
C	Check on a table Column（检查约束）
O	Read Only on a view Object（只读约束）
P	Primary Key Object（主键约束）
R	Referential AKA Foreign Key Column（外键约束）
U	Unique Key Column（唯一约束）
V	Check Option on a view Object（视图约束）

二、视图

  视图：就是一个封装了各种复杂查询的语句，就称之为视图。

1）创建视图

CREATE OR REPLACE VIEW EMP(EMP00, EMP01) AS (SELECT EMP00, EMP01 FROM EMPLOYEE);

2）查看视图

SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME = 'EMP';

3）修改视图

  默认情况下创建的视图，如果更新了，则会自动将此数据从视图中删除，之后会更新原本的数据。

UPDATE EMP SET EMP.EMP01='周康2' WHERE EMP00='7350F9DE-F5BC-461A-B499-F86680D2DA4E'; --EMP 视图与EMPLOYEE 表中数据都修改了

  虽然可以修改视图达到修改数据库数据的目的，但是还是存在隐患问题。视图最好还是不要更新。

4）视图的保护机制

  在建立视图的时候，可以指定两个参数，保护视图的创建规则：WITH CHECK OPTION；视图只读：WITH READ ONLY

--1. 保护视图的创建规则
CREATE OR REPLACE VIEW EMP(EMP00, EMP01) AS SELECT EMP00, EMP01 FROM EMPLOYEE
    WHERE EMP00='7350F9DE-F5BC-461A-B499-F86680D2DA4E' WITH CHECK OPTION CONSTRAINT EMP_CK;

  此时视图为：

  此时如果去更新EMP00，会报错，因为EMP00 是创建视图的WHERE 字段，被保护了，所以不能修改，但是可以修改EMP01；

UPDATE EMP SET EMP.EMP00='7350F9DE-F5BC-461A-B499-F86680D2DA4A' WHERE EMP00='7350F9DE-F5BC-461A-B499-F86680D2DA4E';
--[44000][1402] ORA-01402: 视图 WITH CHECK OPTION where 子句违规

UPDATE EMP SET EMP.EMP01='周康3' WHERE EMP00='7350F9DE-F5BC-461A-B499-F86680D2DA4E'; --正常执行

--2. 只读视图
CREATE OR REPLACE VIEW EMP(EMP00, EMP01) AS SELECT EMP00, EMP01 FROM EMPLOYEE
    WHERE EMP00='7350F9DE-F5BC-461A-B499-F86680D2DA4E' WITH READ ONLY; --视图的所有字段都不能修改

UPDATE EMP SET EMP.EMP01='周康3' WHERE EMP00='7350F9DE-F5BC-461A-B499-F86680D2DA4E';
--[99999][42399] ORA-42399: 无法对只读视图执行 DML 操作

三、索引

1. 什么是索引 (Index)

  索引是一种用于提升查询效率的数据库对象。通过快速定位数据的方法，来减少磁盘的 I/O 操作。索引信息与表独立存放，并且Oracle 数据库自动使用和维护索引。

2. 索引分类

  索引大体上分为两类，唯一性索引 与 非唯一索引。

3. 创建索引的方式

  ① 自动创建：在定义主键或唯一键约束时，系统会自动在相应的字段上创建唯一性索引。
  ② 手动创建：用户可以在其他列上创建非唯一索引，用来提高查询效率。

1）索引优缺点

建立索引的优点：

1. 大大加快了数据的检索速度。
2. 创建唯一性索引，保证数据库表中每一行数据的唯一性。
3. 加速表与表之间的连接。
4. 在使用分组和排序字句进行数据检索时，可以显著地减少查询中分组和排序的时间。

索引的缺点：

1. 索引需要占用物理空间。
2. 当对表中的数据进行增删改查，同时也需要动态的维护索引，降低了数据的维护速度。

2）创建与使用索引的原则

创建索引： 创建索引一般有两个目的，维护被索引列的唯一性和提供快速访问表中数据的策略。小于5M的表，最好不要使用索引来查询，表越小，越适合用全表扫描。

1. 在SELECT操作占大部分的表上创建索引。
2. 在WHERE字句中出现最频繁的列上创建索引。
3. 在选择查询高的列上创建索引（补充索引选择性，最高是1，eg：primary key）。
4. 复合索引的主列应该是最有选择性的和WHERE限定条件最常用的列，并以此类推第二列。

使用索引： 查询结果是所有数据行的 5%以下时，使用index查询效果最好。WHERE条件中经常用到表的多列时，使用复合索引效果会好于几个单列索引，因为当sql 语句所查询的列，全都出现在复合索引时，此时由于 oracle 只需要查询索引块即可获取所有数据，当然比使用多个单列索引要快得多。索引利于SELECT，但对经常 增改删 的表会降低效率。

1. 使用与不使用索引的区别

SELECT EMP00, EMP01 FROM EMPLOYEE WHERE EMP00 NOT IN (SELECT EMP00 FROM EMP) --语句A

SELECT EMP00, EMP01 FROM EMPLOYEE WHERE NOT EXIST (SELECT EMP00 FROM EMP WHERE EMPLOYEE.EMP00 = EMP.EMP00) --语句B

  这两条sql语句实现的结果是相同的，但是当数据量非常大的时候，两条语句执行效率会差很多。在执行A的时候，Oracle会对整个EMP表进行扫描，不会使用建立在EMP上的EMP00索引，执行B的时候，由于在子查询中使用了联合查询，Oracle只是对EMP表进行部分的数据扫描，并利用了EMP00列的索引，所以B的效率要比A高。

2. 复合索引的注意点，以及索引无效的隐式写法

  WHERE字句中的字段,必须是复合索引的第一个字段。一个索引是按照f1, f2, f3的次序建立的，如果WHERE的字句是f2 = val2，则会因为f2 不是索引的第一个字段从而导致无法使用索引。

  WHERE字句中的字段，不应该参加任何形式的计算，任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等，查询时要尽量将操作符移至等号右边。一下操作符会显示地阻止oracle使用索引：IS NULL、IS NOT NULL、NOT IN、!=、LIKE、NUMERIC_COL+0、DATE_COL+0、CHAR_COL || ''、TO_CHAR、TO_DATE、TO_NUMBER 等。

SELECT EMP01 FROM EMPLOYEE WHERE EMP02 = '0' AND TO_DATE(UPDATETIME) > to_DATE('2022-01-01', 'YYYY-MM-DD');
--UPDATETIME列的索引会失效

3）创建 \ 删除索引

CREATE INDEX ABC ON EMPLOYEE(EMP00, EMP01);
CREATE INDEX CBA ON EMPLOYEE(EMP01, EMP00);

  索引ABC 与 CBA 是不一样的，索引ABC 对于WHERE EMP00 = '1'这样的查询语句更有效，而CBA 索引对于WHERE EMP01 = '周康'这样的查询语句更有效。因此建立索引的时候，字段的组合顺序是非常重要的，一般情况下需要经常访问的字段放在组合索引字段的前面。

DROP INDEX ABC; --删除索引

4）查看索引

SELECT * FROM USER_INDEXES; --查询现有的索引
SELECT * FROM USER_IND_COLUMNS; --可得到索引建立在那些字段上

  要注意的是索引和表都是独立存在的，在为索引指定表空间的时候，不要将被索引的表和索引指向同一个表空间，这样可以避免产生 IO 冲突。使oracle 能够并行访问存放在不同硬盘中的索引数据和表数据，更好的提高查询速度。

5）索引类型

B树索引（B-Tree Index）： 创建索引的默认类型，结构是一棵树，采用的是平衡 B 树算法。

位图索引（BitMap Index）： 如果表中的某些字段取值范围比较小，比如职员性别、分数列 ABC 级等等，只有几种取值，这样的字段如果建 B树索引没有意义，不能提高检索效率。此时就要考虑使用位图索引。

CREATE BITMAP INDEX EMP05Index ON EMPLOYEE(EMP05); --EMP05Index 索引名, EMPLOYEE(EMP05) 表名(字段名)

6）管理索引

1. 先插入数据后创建索引，向表中插入大量数据之前最好不要先创建索引，因为如果先创建索引。那么在插入每行数据的时候都要更改索引，这样会大大降低插入数据的速度。
2. 设置合理的索引列顺序。
3. 限制每个表索引的数量。
4. 删除不必要的索引。
5. 为每个索引指定表空间。
6. 经常做 insert、delete 尤其是 update 的表最好定期 exp、imp 表数据，定期整理数据从而降低碎片（要停应用，以保持数据一致性），有索引的最好定期 rebuild 索引，以降低索引碎片，提高效率（rebuild 索引期间只允许做 select 操作）。