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

Life lies in diligence.

已于 2022-05-29 09:33:57 修改

阅读量1.1k

点赞数

分类专栏： sql 文章标签：数据库 oracle

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

本文链接：https://blog.csdn.net/csdn_okcheng/article/details/113174770

版权

sql 专栏收录该内容

17 篇文章 4 订阅

订阅专栏

数据库对象

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

Life lies in diligence.

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
【Oracle】第10天索引、视图、序列

索引定义分类语法建一个索引建一个位图索引定义分类语法建一个索引CREATE INDEX IND_NAME ON TABLE_NAME(COLUMN_NAME);建一个位图索引CREATE BITMAP INDEX IND_NAME ON TABLE_NAME(COLUMN_NAME);
复制链接

扫一扫