1.索引
定义
- 它记录了索引列中的数据以及对应的物理位置(ROWID)
- 在思考索引出现的意义之前不妨来了解一下数据库检索数据的方式:
- 全盘扫描:按照条件在对应数据列从第一条数据检索到最后一条数据,一旦条件在某条数据上成立,就将对应的ROWID取出,直到检索到最后一条,然后按照ROWID去对应的物理位置获取整行数据
- 索引扫描:按照条件在对应索引列从大概位置开始检索,检索到最后一条符合条件的数据即停止检索,符合条件的数据获取对应的ROWID,最后按照ROWID去对应的物理位置获取整行数据
- 一般情况下索引扫描的速度要高于全盘扫描,所以建立有效的索引可能是程序运行中提高查询效率的的有效方法,也是sql优化的重要手段
注意:
索引扫描本质上是牺牲了DML语句的操作效率来换取更高的查询效率,若表中建立了索引,在维护该表数据时,会因数据的改变而使索引重新进行排序,这就导致DML的操作时间增加
注意:
有些程序中会在DML操作前先将索引置为失效,等DML操作结束后再重新激活索引
分类
按照存储形式的不同分类
1)B-TREE索引(索引列原始数据+ROWID)
B-TREE索引是ORACLE的默认索引类型,工作中最常见、使用范围最广的索引
- 适用场景:列基数比较大的时候使用
列基数:该列不重复数据的个数 COUNT(DISTINCT COL)
2)位图索引(位图+ROWID)
位图索引在创建时,会扫描整张表,为索引列的每个取值建立一个不重复的位图(BITMAP)来描述该取值
- 适用场景:列基数比较小的时候使用(性别、婚姻状况)
位图:
3)反向键索引(索引列原始数据的反向存储+ROWID)
反向索引可以视作一种特殊的B-TREE索引,存储索引列的反向值,目的是为防止B-TREE索引在某叶上数据量占比过高而使用的一种索引
- 适用场景:原始数据分支不明显但反向数据分支明显的列(例如身高集中在一米多)
4)基于函数的索引(将索引列原始数据经函数处理后存储+ROWID)
基于函数的索引可以视作一种特殊的B-TREE索引,存储函数处理后的数据,目的是为了适应在某个字段上以原值建立了索引,但是在筛选时在该字段上经常加函数,导致索引无法生效的情况
- 适用场景:对某列进行筛选时经常需要配合函数使用(例如查找姓名中的首字母)
按照唯一性不同来分类
唯一索引
索引列中不可能出现重复值
不唯一索引
索引列中可能出现重复值
以上两种不同分类角度的相互制约关系
B-TREE索引可以建立唯一索引,位图索引不能建立唯一索引
按照索引附加的列的个数不同分类
单列索引
复合索引(联合索引)
命名规范
索引的命名规范:
IND_表名_列名
语法
建一个B-TREE/非唯一索引
CREATE INDEX IND_NAME ON TABLE_NAME(COLUMN_NAME);
建一个位图索引
CREATE BITMAP INDEX IND_NAME ON TABLE_NAME(COLUMN_NAME);
建一个反向索引
CREATE INDEX IND_NAME ON TABLE_NAME(COLUMN_NAME) REVERSE;
建一个基于函数的索引
CREATE INDEX IND_NAME ON TABLE_NAME(FUNCTION(COLUMN_NAME));
建一个唯一索引
CREATE UNIQUE INDEX IND_NAME ON TABLE_NAME(COLUMN_NAME);
注意:如果没有添加关键字UNIQUE,则默认视为添加的这个INDEX 为非唯一索引
建一个复合索引
CREATE INDEX IND_NAME ON TB_NAME(COL_NAME1,COL_NAME2...);
索引的禁用
ALTER INDEX IND_NAME UNUSABLE;
索引的重建
ALTER INDEX IND_NAME REBUILD;
索引的删除
DROP INDEX IND_NAME;
索引相关的数据字典
数据字典名:USER_INDEXES
- 所有索引
SELECT INDEX_NAME,--索引名称
INDEX_TYPE,--索引类型
TABLE_NAME,--表名
UNIQUENESS,--是否唯一
STATUS,--索引状态 VALID 可用的 UNUSABLE 不可用的
TABLESPACE_NAME,--表空间
LOGGING--是否记录日志
FROM USER_INDEXES
WHERE 1=1
AND INDEX_NAME ='IND_EMP_DEPTNO'
AND TABLE_NAME ='EMP';
- 索引列
SELECT INDEX_NAME,--索引名称
TABLE_NAME,--表名
COLUMN_NAME,--列名
COLUMN_POSITION,--字段在索引中的位置
DESCEND --排序方式默认ASC
FROM USER_IND_COLUMNS
WHERE INDEX_NAME ='IND_EMP_UPENAME';
- 索引函数
SELECT INDEX_NAME,--索引名称
TABLE_NAME,--表名
COLUMN_EXPRESSION--
FROM USER_IND_EXPRESSIONS
WHERE INDEX_NAME ='IND_EMP_UPENAME'
相关建议与注意事项
- 1.如果对某大表进行筛选时,某列或某几列频繁出现在WHERE子句中,并且检索出的数据低于总行数的15%(50%),应考虑在这些列上建立索引
- 2.如果对某大表进行排序时,某列或某几列频繁出现在ORDER BY子句中,应考虑在这些列上建立索引
- 3.小表不要建立索引
- 4.对于含有空值的列,如果经常在查询时查询非空值,建议在该列上建立索引;如果经常在查询时查询空值,建议在该列上建立基于函数的索引
- 5.为了提高表连接的性能,应在连接列上建立索引(建立一般普通的索引即可)
- 6.索引是数据库的一种实体对象,级别类似于表,会占用内存空间,ORACLE会自动进行索引维护,表和索引可以建立在不同的表空间
- 7.对于列基数比较大的列,适合B-TREE索引,列基数比较小的列,适合位图索引
- 8.对于复合索引,至少要引用到索引列中的第一个列才会使用该索引
- 9.某列可以出现在多个索引中,但相同的某列或某几列无法多次建立索引。 --就是可以有多个组合,但不能有重复组合,不同顺序的相同几列视为不同组合
- 10.索引建立后并不一定会被引用,ORACLE会分析整个SQL后做出最优的执行方式
- 11.ORACLE会自动在主键约束和唯一约束列上建立唯一索引
- 12.通配符出现在搜索词的首位时不会引用索引
- 13.在索引列上使用任何不等号时,或对空值进行判断时,索引不会生效
- 14.同一用户下,索引名不能重复。表被删掉(DROP)时,基于该表建立的索引也会一并删除
2.视图
定义
视图是将SQL查询语句的结果动态保存到数据库中的一张虚拟表
虚拟表:虚拟表是一个与物理表相对的概念,其与物理表有着本质上的区别
- 1)视图可以像普通的物理表一样去使用
- 2)视图本身不存储数据,它只是动态地获取表的查询结果
- 3)从基表(视图)中获取数据,非常像物理表,对它的操作同任何其它的表一样
基表:视图的来源表
建立视图的权限要求
GRANT CREATE VIEW TO USERNAME;
作用
1.提供各种数据表现形式, 以便符合用户的使用习惯(比如起别名的习惯)
2.隐藏数据的逻辑复杂性并简化查询语句(字典表)
3.提供安全性保证(列)
4.简化用户权限的管理
语法
建立一个视图
CREATE[OR REPLACE] VIEW V_VIEWNAME[(别名1,别名2 ...)] AS SQL语句
[WITH CHECK OPTION]--可读写的视图
[WITH READ ONLY] --只读的视图
例题1:创建一个视图,内容包含员工信息表中20号部门员工的工号、姓名和工资
CREATE VIEW V_TEST1VIEW AS SELECT EMPNO,ENAME,SAL FROM EMP WHERE DEPTNO IN (20);
- 1)可以通过对视图的增删改来达到对基表的增删改
2)对于增加数据,视图本身没有限制,新增的数据需符合基表的要求即可。若新增的数据符合视图的定义,则会同时体现在视图和基表上,若不符合视图的定义,新增数据仅会体现在基表上
3)对于删除和修改:无法对视图中没有的数据进行操作
4)若添加了WITH CHECK OPTION参数,新增的数据必须要符合视图定义的要求
5)若添加了WITH READ ONLY参数,该视图只能查询,不能增删改
例题2:创建一个视图,内容包含各部门名称、工作地点、小写姓名、薪资、奖金、薪资奖金合计值
CREATE VIEW V_TEST2VIEW AS SELECT DNAME,LOC,LOWER(ENAME) 小写姓名 ,SAL,COMM,SAL+NVL(COMM,0) 薪资奖金合计值 FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO(+) ;
- 该视图是基于多个基表建立起来的,且查询语句中包含函数表达式
1)对于基表中原有的列,定义视图时可以不加别名;对于基表中原本没有的列,定义视图时必须加别名;
2)此时无论是否有添加WITH READ ONLY参数,都不能通过增删改视图来增删改基表
例题3:基于上一个例题创建的视图,再创建一个视图,内容包含各部门名称,各部门薪资总计
CREATE VIEW V_TEST3VIEW AS SELECT V2.DNAME,SUM(SAL) 各部门薪资总计 FROM V_TEST2VIEW V2 GROUP BY DNAME;
- 1)基于其他视图建立新视图时,其他视图的基表发生变化也会引起新视图的变化
修改一个视图
REPLACE VIEW VIEW_NAME AS SQL语句;
例题4:修改视图3的定义信息,内容改为各部门名称,各部门人数总计和平均薪资
CREATE OR REPLACE VIEW V_TEST3VIEW AS SELECT DNAME,COUNT(*) 人数总计 ,AVG(SAL) 平均薪资 FROM V_TEST2VIEW GROUP BY DNAME;
→
删除一个视图
DROP VIEW V_NAME;
视图相关数据字典
数据字典名:USER_VIEWS
SELECT VIEW_NAME,--视图名称
TEXT,--视图定义
READ_ONLY --是否只读
FROM USER_VIEWS;
物化视图
物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,任何对视图的查询,Oracle都实际上转换为视图SQL语句的查询。
- 和普通视图的区别
普通视图是不存储任何数据的,只有定义,在查询中是转换为对应的定义SQL去查询,物化视图是将数据转换为一个表,实际存储着数据,这样查询数据,就不用关联一大堆表
赋予建立物化视图的权限
GRANT CREATE materialized VIEW TO USER;
物化视图的两种分类
ON DEMAND 的物化视图
指定物化视图每天刷新一次:
CREATE MATERIALIZED VIEW MV_NAME REFRESH FORCE ON DEMAND START WITH SYSDATE NEXT SYSDATE+1
ON COMMIT 的物化视图
CREATE MATERIALIZED VIEW MV_NAME REFRESH FORCE ON COMMIT AS SELECT * FROM TABLE_NAME;
3.序列
定义
序列是ORACLE提供的一组能够自动增长的序号。常常用来生成每条数据的唯一标识
赋予建立序列的权力
GRANT CREATE SEQUENCE TO USER_NAME ;
创建
CREATE SEQUENCE SQU_NAME --序列名称
START WITH N --初始序号 递增:默认MINVALUE 递减:默认MAXVALUE
INCREMENT BY N --增长幅度 N为正 递增 N为负 递减
MINVALUE N|NOMINVALUE
MAXVALUE N|NOMAXVALUE
CACHE N|NOCACHE --缓存N个序号,默认缓存20个,尽量设置大一点的值
CYCLE|NOCYCLE --是否循环
举例
CREATE SEQUENCE SEQ_A
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 20
CACHE 10
NOCYCLE ;
←创建一个从1开始,最大到20 ,每次增加1,缓存为10 个序号不循环的序列
使用
- 当前值:
CURRVAL - 下一个值:
NEXTVAL - 注意:
1)第一次使用CURRVAL必须先使用一次NEXTVAL
2)第一次使用NEXTVAL得到的是序列中的起始值
SELECT SEQ_A.NEXTVAL FROM DUAL;
→点一次数字变化一次
修改
ALTER SEQUENCE SEQ_NAME CYCLE;--修改循环
ALTER SEQUENCE SEQ_NAME INCREMENT BY 3
- 注意:
无法直接调整初始值,只能删除后重建,或先设置一个新的涨幅,通过nextval执行到目标序号后再调回旧的涨幅
删除
DROP SEQUENCE SEQ_NAME;
注意事项
- 1.CACHE值小于等于CYCLE值(指一次循环能够生成的序号个数)–FLOOR((MAXVALUE-MINVALUE)/INCREMENT)+1
- 2.涨幅必须是非零整数
- 3.若未指定最大值和最小值 递增时:最小值是1 最大值是1027
递减时:最小值是-1027 最大值是 -1