【Oracle】第10天索引、视图、序列

数据库对象详解

原创已于 2022-05-29 09:33:57 修改 · 1.2k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #oracle

于 2021-03-02 09:11:03 首次发布

sql 专栏收录该内容

17 篇文章

订阅专栏

本文详细介绍了数据库中的三种核心对象——索引、视图和序列。针对索引，文章深入解析了不同类型的索引及其应用场景；对于视图，文章阐述了其定义、作用及建立方法；关于序列，文章解释了其用途及创建过程。

数据库对象

1.索引
2.视图
3.序列
注意事项

1.索引

定义

它记录了索引列中的数据以及对应的物理位置(ROWID)
在思考索引出现的意义之前不妨来了解一下数据库检索数据的方式:

全盘扫描：按照条件在对应数据列从第一条数据检索到最后一条数据，一旦条件在某条数据上成立，就将对应的ROWID取出，直到检索到最后一条，然后按照ROWID去对应的物理位置获取整行数据
索引扫描：按照条件在对应索引列从大概位置开始检索，检索到最后一条符合条件的数据即停止检索，符合条件的数据获取对应的ROWID，最后按照ROWID去对应的物理位置获取整行数据
一般情况下索引扫描的速度要高于全盘扫描，所以建立有效的索引可能是程序运行中提高查询效率的的有效方法，也是sql优化的重要手段

注意：
索引扫描本质上是牺牲了DML语句的操作效率来换取更高的查询效率，若表中建立了索引，在维护该表数据时，会因数据的改变而使索引重新进行排序，这就导致DML的操作时间增加

注意：
有些程序中会在DML操作前先将索引置为失效，等DML操作结束后再重新激活索引

命名规范

索引的命名规范：

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'

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 最大值是10²⁷
递减时：最小值是-10²⁷ 最大值是 -1

【Oracle】第10天 索引、视图、序列

数据库对象

1.索引

定义

分类

按照存储形式的不同分类

1）B-TREE索引(索引列原始数据+ROWID)

2）位图索引(位图+ROWID)

3）反向键索引(索引列原始数据的反向存储+ROWID)

4）基于函数的索引(将索引列原始数据经函数处理后存储+ROWID)

按照唯一性不同来分类

唯一索引

不唯一索引

以上两种不同分类角度的相互制约关系

按照索引附加的列的个数不同分类

单列索引

复合索引（联合索引）