oracle索引和约束

韶博雅

已于 2025-02-26 11:34:26 修改

阅读量224

点赞数 2

文章标签： oracle 前端数据库

于 2023-12-12 22:46:36 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_39735909/article/details/134960309

版权

V$SQL_PLAN
ALL_IND_COLUMNS
INDEX_STATS
DBA_INDEXES

1. 索引

1.1 Scott用户使用autotrace工具

SQL>conn / as sysdba
SQL>@/u01/oracle/sqlplus/admin/plustrce.sql
SQL> grant plustrace to scott;
SQL> conn scott/scott
SQL> set autotrace on;

1.2.几种常用的B树索引创建方法

1）唯一索引，指键值不重复。
SQL> create unique index empno_idx on emp1(empno);
2）非唯一索引（Unique or non_unique):
SQL> create index empno_idx on emp1(empno);
3）组合索引(Composite)：绑定了两个或更多列的索引。
SQL> create index job_deptno_idx on emp1(job,deptno);
4）反向键索引(Reverse)：将字节倒置后组织键值。当使用序列产生主键索引时，可以防止叶节点出现热块现象（考点）。缺点是无法提供索引范围扫描。
SQL> create index mgr_idx on emp1(mgr) reverse;
5）函数索引(Function base)：以索引列值的函数值为键值去组织索引
SQL> create index fun_idx on emp1(lower(ename));
6）压缩（Compress)：重复键值只存储一次，就是说重复的键值在叶块中就存一次，后跟所有与之匹配的rowid字符串。
SQL> create index comp_idx on emp1(sal) compress;
7）升序或降序（Ascending or descending)：叶节点中的键值排列默认是升序的。
SQL> create index deptno_job_idx on emp1(deptno desc, job asc);

1.3 索引的优化

1）索引唯一扫描(index unique scan)
通过唯一索引查找一个数值返回单个ROWID。对于唯一组合索引，要在where的谓词“=”后包含所有列的“布尔与”。
2）索引范围扫描(index range scan)
在非唯一索引上，可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。
使用index rang scan的3种情况：
(在唯一索引列上使用了range操作符(> < <> >= <= between)
在唯一组合索引上，对组合索引使用部分列进行查询(含引导列），导致查询出多行
对非唯一索引列上进行的任何查询。不含‘布尔或’
3）索引全扫描(index full scan)
对整个index进行扫描，并且顺序的读取其中数据。
CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时，才进行全Oracle索引扫描，
4）索引快速扫描(index fast full scan)
扫描索引中的所有的数据块，fast full scan 在读取叶子块时的顺序完全由物理存储位置决定，并采取多块读，每次读取DB_FILE_MULTIBLOCK_READ_COUNT个块。
CBO能够索引全扫描和快速扫描的前提是：所要的数据必须能从索引中可以直接得到，因此不再需要查询基表。

1.4 索引的碎片问题

对基表做DML操作，便导致对索引表块的自动更改操作，尤其是基表的delete操作会引起index表的index entries的逻辑删除。
注意：只有当一个索引块中的全部index entry都被删除了，这个块才会被收回。
如果update基表索引列，则索引块会发生entry delete，再entry insert，这都些动作都可能产生索引碎片
测试：
第一步，搭建环境
SQL> create table t (id int);
SQL> create index ind_1 on t(id);
SQL>
begin
  for i in 1..1000000 loop
	insert into t values (i);
		if mod(i, 100)=0 then
		commit;
		end if;
  end loop;
end;
/
第二步，收集统计信息，并查看视图
SQL> analyze index ind_1 validate structure;
SQL> select name,HEIGHT,PCT_USED,DEL_LF_ROWS/LF_ROWS from index_stats;
第三步，基表高强度更新，造成索引碎块
SQL> delete t where rownum<700000;
第四步，重复第二步，查看视图

1.5 重建索引

查询索引的两个动态视图
DBA_INDEXES
DBA_IND_COLUMNS
在Oracle文档里并没有清晰的给出索引碎片的量化标准。
Oracle建议通过Segment Advisor(段顾问）解决表和索引的碎片问题，如果你想自行解决，可以通过查看index_stats视图，当以下三种情形之一发生时，说明积累的碎片应该整理了。
1.HEIGHT >=4   
2 PCT_USED< 50% (相对值）  
3 DEL_LF_ROWS/LF_ROWS>0.2
SQL> alter index ind_1 rebuild online;
11.7 索引不可用（unusable）和不可见（invisible）
1）仅仅保存索引定义，不删除索引，也不更新索引。
SQL> alter index ind_1 unusable;	
索引被设定为unusable后，如再次使用需要做rebuild。
SQL> alter index ind_1 rebuild;	
2）在11g里，Oracle提供了一个新特性(Index Invisible)来降低直接删除索引或禁用索引的风险。我们可以在创建索引时指定invisible属性或者用alter语句来修改索引为invisible(visible)
SQL> alter index ind_1 invisible;
SQL> select index_name,status,VISIBILITY from user_indexes;
索引不可见其实是对优化器来说不可见，索引维护还是正常进行的。

2 约束

2.1 约束的概念

1）约束的作用：
约束是数据库能够实施业务规则以及保证数据遵循实体--关系模型的一种手段。如果违反约束，将自动回滚出现问题的整个语句，而不是语句中的单个操作，也不是整个事务。
2）约束的语法：
列级定义：只能引用一个列，表中可以有多个列级约束。
表级定义：引用一个或多个列，通常用来定义主键。
追加定义：建表后，再通过alter table命令追加的约束。
查看约束的两个数据字典视图
select * from user_constraints;
select * user_cons_columns;

2.2 非空约束

列级定义：
create table t1 (id number(2) not null, name varchar2(4))
追加非空约束：
如：alter table t1 modify ename not null;有问题
非空约束没有表级定义
Alter table t1 modify ename(列名) constraint aaa not null;

2.3唯一性约束

1）unique不包括空值的唯一性 
2）单列可设unique+not null约束，约束之间没有“，”
create table a (id int unique not null, name char(10));
3）单表中unique约束没有数量限制。
4）unique约束的列上有索引。
列级定义
create table t1(id number(2) unique,name varchar2(4));
表级定义
create table t2(id number(2),name varchar2(4),constraint id_uk unique(id));
追加定义
alter table t2 add CONSTRAINT id_uk UNIQUE (id);

2.4 主键约束

主键约束语法上与唯一约束类似
1）每个表只能建立一个主键约束，primary key=unique key + not null，主键约束可以是一列，也可以是组合多列。
2）主键列上需要索引，如果该列没有索引会自动建立一个unique index, 如果该列上已有索引（非唯一也可以），那么就借用这个索引，由于是借用的索引，当主键约束被删除后借用的索引不会被删除。同理，多列组合的主键，需要建立多列组合索引，而多列主键的单列上还可以另建单列索引。
3）主键约束和唯一约束不能同时建立在一个列上。
4）主机约束和外键约束可以在同一列上。（典型的例子是一个表上的两列是组合主键，但这两列的每列都是其他表的外键）
关于主键和索引关联的问题:（这个地方考点较多)
SQL> 
create table t (id int, name char(10));
insert into t values (1, 'sohu');
insert into t values (2, 'sina');
commit;
SQL> create index t_idx on t(id);
下面这两句话是一样的效果，因为缺省情况下id列已经有索引t_id了，建主键时就会自动用这个索引（考点）。
SQL> alter table t add constraint pk_id primary key (id);
SQL> alter table t add constraint pk_id primary key (id) using index t_idx;
SQL> select CONSTRAINT_NAME,TABLE_NAME,INDEX_NAME  from user_constraints;
SQL> alter table t drop constraint pk_id;   删除了约束，索引还在，本来就是借用的索引。
SQL> select index_name from user_indexes;
INDEX_NAME
------------------------------
PK_EMP
PK_DEPT
T_IDX
SQL> drop table t purge;   t_idx是和t表关联的，关键字purge使表和索引一并永久删除了。
也可以使用using子句在建表、建约束、建索引一条龙下来，当然primary key 也会自动使用这个索引(考点）。删除该约束，索引还存在。
SQL> create table t (id int,name char(10),constraint pk_id primary key(id) using index (create index t_idx on t(id)));

2.5 外键约束

作用：引用主键构成完整性约束
1）外键约束和unique约束都可以有空值。
2）外键需要参考主键约束，但也可以参考唯一键约束。
3）外键和主键一般分别在两个表中，但也可以同处在一个表中。
SQL> create table emp1 as select * from emp;
SQL> create table dept1 as select * from dept;
SQL> alter table dept1 add constraint pk_dept1 primary key(deptno);
SQL> ALTER TABLE emp1 ADD CONSTRAINT fk_emp1 FOREIGN KEY(deptno) REFERENCES dept1(deptno);
主外键构成完成性约束，有两个要点：
1）插入或更新有外键的记录时，必须参照主键，（子要依赖父）。
2）有外键表时，主键表的记录不能做DML删除，（父不能舍子），
如果一定要删除, 需要在建立外键时加 on delete cascade子句或on delete set null子句 
测试ON DELETE CASCADE子句
报错：ORA-02292: integrity constraint (SCOTT.E_FK) violated - child record found
删除外键约束，使用ON DELETE CASCADE关键字重建外键,
SQL>alter table emp1 drop constraint fk_emp1;
SQL>ALTER TABLE emp1 ADD CONSTRAINT fk_emp1 FOREIGN KEY(deptno) REFERENCES dept1(deptno) ON DELETE CASCADE;
视图delete_rule列会显示CASCADE,否则显示NO ACTION(考点)
SQL>select constraint_name,constraint_type,status,delete_rule from user_constraints;
测试：
SQL>delete from dept1 where deptno=30
再查看emp1表的deptno已经没有30号部门了，如果再对dept1的操作进行rollback，emp1的子记录也随之rollback
ON DELETE CASCADE要慎用，父表中删除一行数据就可能引发子表中大量数据丢失。
为此，还有on delete set null子句，顾名思义是子表不会删除（丢失）记录，而是将外键的值填充null。
如果disable dept1主键约束并使用级联cascade关键字，则emp1的外键也会disable, 若再次enable dept1主键，则emp1外键仍然保持disable.

SQL> alter table dept1 disable constraints pk_dept1 cascade;
SQL> alter table dept1 enable constraints pk_dept1;

SQL>select constraint_name,constraint_type,status,delete_rule from user_constraints;
SQL> drop table dept1 purge;
报错：ORA-02449: 表中的唯一/主键被外键引用
SQL> drop table dept1 cascade constraints purge;
注意：这时外键约束也被删除了（考点）

2.6 CHECK约束

1、作用：CHECK约束是检查一个表达式，表达式的逻辑结果为”F“时，违反约束则语句失败。
表达式使用”( )“扩起来
涉及多列的表达式，只能使用表级定义描述。
2、语法:
列级定义
SQL> create table emp1 (empno int,sal int check (sal>500),comm int);
表级定义
SQL> create table emp2 (empno int,sal int,comm int,check(sal>500));
追加定义
SQL> alter table emp2 add constraint e_no_ck check (empno is not null);
验证
SQL> insert into emp2 values(null,1,1);

报错：ORA-02290: 违反检查约束条件 (SCOTT.E_NO_CK)
3、check约束中的特殊情况
1）check约束中的表达式中不能使用变量日期函数（考点）
SQL> alter table emp1 add constraint chk check(hiredate<sysdate);
报错：ORA-02436: 日期或系统变量在 CHECK 约束条件中指定错误
SQL> alter table emp1 add constraint chk check(hiredate<to_date('2000-01-01','yyyy-mm-dd'));   这句是可以的
2）级联约束
CREATE TABLE test2 (
    pk NUMBER PRIMARY KEY,
    fk NUMBER,
    col1 NUMBER,
    col2 NUMBER,
    CONSTRAINT fk_constraint FOREIGN KEY (fk) REFERENCES test2,
    CONSTRAINT ck1 CHECK (pk > 0 and col1 > 0),
    CONSTRAINT ck2 CHECK (col2 > 0)
    )
/
当删除列时, 看看会发生什么?
SQL> ALTER TABLE test2 DROP (col1);   这句不能执行，在constraint ck1 中使用了该列
如果一定要删除级联约束的列，带上cascade constraints才行
SQL> ALTER TABLE test2 DROP (col1) cascade constraints;
3）check中可以使用in表达式，如(check deptno in(10,20))
实验环境：两张表，每张都有主键约束和非空约束
测试drop table t1/t2 cascade constraint/constraints purge;中加s和不加s的区别

2.7 约束的四种状态

enable validate     :无法输入违反约束的行，而且表中所有行都要符合约束
enable novalidate   :表中可以存在不合约束的状态，,但对新加入数据必须符合约束条件. 
disable novalidate  :可以输入任何数据，表中或已存在不符合约束条件的数据.
disable validate    :不能对表进行插入/更新/删除等操作,相当于对整个表的read only设定.
                 （如是主键，会删除索引，当enable后，又建立了索引）
更改约束状态是一个数据字典更新，将对所有session有效。
1）测试enable novalidate 这种组态的用法
常用于当在表中输入了一些测试数据后﹐而上线后并不想去清除这些违规数据﹐但想从此开始才执行约束。
假设已经建立了一个emp1表，也插入了数据，如果有一天想在empno上加入primary key 但是之前有不符合（not null+unique)约束的，怎样才能既往不咎呢？
SQL>create table emp1 as select * from emp;   没有约束考过来
SQL>update emp1 set empno=7788 where empno=7369;   设置一个重值
SQL>alter table emp1 add constraint pk_emp1 primary key (empno);   因要检查主键唯一性，拒绝建立此约束
alter table emp1 add constraint pk_emp1 primary key (empno) enable novalidate;   这句话也不行，原因是唯一索引在捣乱
SQL>create index empno_index on emp1(empno);   建一个普通索引不受unquie的限制
SQL>alter table emp1 add constraint pk_emp1 primary key (empno) enable novali
从此之后，这个列的DML操作还是要符合主键约束（not null+unique)。

2）将disable novalidate，enable novalidate和enable validate三种状态组合起来的用法：
这种组合，可以避免因有个别不符合条件的数据而导致大数据量的传输失败。
假设有a表是源数据表，其中有空值，b表是a表的归档表，设有非空约束,现要将a表数据（远程）大批量的插入到b表（本地）。
alter table b modify constraint b_nn1 disable novalidate;   先使B表非空约束无效。
insert into b select * from a;   大批数据可以无约束插入，空值也插进B表里了。
alter table b modify constraint b_nn1 enable novalidate;   既往不咎，但若新输入数据必须符合要求。
update b set channel='NOT KNOWN'where channel is null;   将所有空值填充了，新老数据都符合要求了。
alter table b modify constraint b_nn1 enable validate;   最终是约束使能+验证生效，双管齐下。

2.8 延迟约束

12.4.1作用：
在插入大批量数据时先不检查约束（节约时间），而当提交时集中在一起检查约束。注意：一旦有一条DML语句违反了约束，整个提交都将失败，全军覆没。
如何设置：
分为可延迟（deferrable）可以通过查询User_Constraints视图获得当前所有关于约束的系统信息.
查看user_constraints中的两个字段
Deferrable  是否为延迟约束 值为:Deferrable或Not Deferrable(缺省）.
Deferred    是否采用延迟 值为:Immediate（缺省）或Deferred.
这两个字段的关系是：
1）如果创建约束时没有指定Deferrable 那么以后也无法使约束成为延迟约束（只有通过重建约束时再指定它是延迟约束）
2）一个约束只有被定义成Deferrable，那么这个约束session级才可以在deferred和immediate两种状态间相互转换
12.4.2延迟约束示例：
SQL> alter table emp1 add constraint chk_sal check(sal>500) deferrable;   建立可延迟约束
已将chk_sal 约束设为Deferrable了，下面可有两种面向session的方案：
1）约束不延迟，插入数据立刻检查约束
SQL>set constraint chk_sal immediate;  
2）约束延迟，提交时将整个事务一起检查约束
SQL>set constraint chk_sal deferred;     
使用set constraint切换只影响当前会话，
也可以在建立约束时一次性指定系统级(全局设定所有会话）的延迟约束  
①SQL>alter table emp1 add constraint chk_sal check(sal>500) deferrable initially immediate;
②SQL>alter table emp1 add constraint chk_sal check(sal>500) deferrable initially deferred;

3 实验：

3.1 scott下

SQL>create table a (id int, name char(10));
SQL>create index id_idx on a(id);
SQL>alter table a add constraint unq_id unique(id);
SQL>create sequence a_id start with 1 increment by 1;
SQL>create view v as select id from a;
SQL>insert into a values(a_id.nextval,'tim');
SQL>insert into a values(a_id.nextval,'ran');
SQL>commit;

SQL> select * from a;
SQL> select index_name from user_indexes where table_name='A';   查看有关索引
SQL> select constraint_name from user_constraints where table_name='A';   查看有关约束
SQL> select object_name from user_objects where object_type='SEQUENCE';   查看有关序列
SQL> select text from user_views where view_name='V';   查看有关视图	
SQL> alter table a drop column id;
重复查看有关信息，索引和约束随该列数据虽然被删除，但序列和视图的定义还在。

3.2 查询列，索引，执行计划间的关系

select sql_id,machine,program,count(*) from v$session group by sql_id,machine,program order by 4 desc;
select sql_text from v$sql where sql_id='xxx';
select * from table(dbma_xplan.display_awr('sql_ix'));
select * from all_ind_columns idx where idx.table_name='tbname';
select spl.hash_value,spl.plan_hash_value from v$sql_plan where spl.sql_id='xxx';
select owner from all_tables where table_mame='xxx';