管理权限、视图、索引

1. 管理权限

1.1 创建用户

1.1.1 通过命令窗口，也就是SQL窗口连接到SYSTEM用户：

SQL> CONN SYSTEM/123456;  --CONN 用户名/密码;

显示以下内容则是连接成功： 

Connected to Oracle Database 1lg Enterprise Edition Release 11.2.0.1.0Connected as SYSTEM 

1.1.2 通过命令窗口执行创建用户命令，创建用户：

CREATE USER 用户名 IDENTIFIED BY 密码;

--CREATE USER WANGWU IDENTIFIEN BY 123456;

1.2 给新建的用户赋权

 创建会话权限、赋予建表的权限、创建序列权限、创建存储过程权限、创建视图权限 

---创建会话权限 
GRANT CREATE SESSION TO WANGWU;
--赋予建表权限
GRANT CREATE TABLE TO WANGWU; --给建表权限
--给创建序列权限
GRANT CREATE SEOUENCE TO WANGWU;
--给创建存储过程权限
GRANT CREATE PROCEDURE TO WANGWU;
--给创建视图权限
GRANT CREATE VIEW TO WANGWU;

1.3 修改用户信息

可以使用SYSTEM用户对其他用户进行修改用户名和修改密码

--在SYSTEM用户下
--查看当下所有的用户
SELECT USERNAME FROM DBA USERS;
--修改用户密码
ALTER USER 用户名 IDENTIFIED BY 新密码;
--修改用户处于锁定状态/非锁定状态
ALTER USER 用户名 ACCOUNT LOCK/UNLOCK;
--删除用户
DROP USER 用户名 CASCADE;

1.4 收回权限

可以通过SYSTEM用户收回其余用户的权限

--收回权限
REVOKE 权限 FROM 用户名；

2. 视图(VIEW)

 2.1 定义

视图就是将一段SELECT语句逻辑或者说是结果集封装到视图中 

 2.2 创建语法

CREATE OR REPLACE VIEW 视图名 
AS 
SELECT XXX FROM 表名 WHERE 条件;

2.3 视图和表的区别 

视图中没有真实存储数据，在SELECT查询语句时视图和表没有区别。视图就是将已定的SELECT语句逻辑封装到视图里面，方便后续的每一次调用。

2.4 物化视图

会保存数据，占空间，提前将查询数据保存到内存中，后续查询效率更高

物化视图需要注意的点： 

1.物化视图需要手动更新，否则只能保留上一次SELECT查询的结果集，可以设置定时replace视图语句 

2.普通视图是在底表发生改变的时候，视图查询的结果也会发生改变。而物化视图是在底表发生改变的时候，需要人工去刷新一遍才会更新视图查询结果，而且物化视图可以简单的理解成一张物理表，有对应的物理存储

MATERIALIZED VIEWS

CREATE OR REPLACE MATERIALIZED VIEWS 视图名 AS SELECT 查询结果集;

2.5 总结

 1. 视图是一段封装SELECT语句的结果集，不保存具体数据；跟表最大的区别就是没有存储数据

 2. 可以用视图更新底表，局限性比较高，必须是单表且要求更新的字段是源表中的，否则就无法更新

3. 索引 

3.1 定义及分类 

定义：索引就是排序的列表，其中包含索引的值以及该值所在行的物理地址，在创建索引的时候，会生成一张类似于目录的表，索引表包含两个字段(加索引的列、ROWID)，后续查询数据时，先在索引表中找到满足条件数据的ROWID，然后再返回源表快速定位到每个ROWID对应的行

索引包括：普通索引、唯一索引、位图索引、组合索引、基于函数索引、反向键索引

作用： 加快数据的查询、提高检索效率

注意：表中字段加了索引之后，会自动排序，同时索引一般在数据量很大的时候适合加 

3.2 如何查看执行计划 

按F5查看执行计划，没有走索引也就是走全表扫描关键字：TABLE ACCESS FULL 

走索引关键字：TABLE ACCESS BY INDEX，创建唯一约束可能会走索引

3.3 普通索引 

 定义：通过物理地址ROWID查询数据，ORACLE会为主键和唯一约束创建普通索引

 语法：CREATE INDEX 索引名 ON 表名(字段名);

3.4 唯一索引 

用法：字段值唯一不重复、允许为空，用在主键或者是数据唯一的字段上，主键会自动生成唯一索引

语法：CREATE UNIQUE INDEX 索引名 ON 表名(字段名);

3.5 位图索引

用法：适用于重复性比较高、基数小，选项少的字段，比如性别(男、女)，婚姻状态(未婚、已婚、离异)，学历(高中及以下、本科、大专、硕士、博士)

语法：CREATE BITMAP INDEX 索引名 ON 表名(字段名);

3.6 组合索引

用法：适用于WHERE后面有多个条件的时候

语法：CREATE INDEX 索引名 ON 表名(字段名1，字段名2);

特点：组合索引必须同时使用索引字段或者是仅使用字段名1时才会生效 ，第一个字段没有被调用的时候不会走索引，以下是解决办法：

SELECT /*+INDEX (EMP_INDEX TEST_TOGETHER)*/* FROM EMP_INDEX WHERE JOB = 'CLERK';

3.7 基于函数的索引

 用法：适用于WHERE 之后的字段加函数的场景，因为字段加了函数之后原来的索引会失效

 语法： CREATE INDEX 索引名 ON 表名(函数名(字段名));

3.8 总结 

作用：提升查询效率
原理：对加索引的列，按照特定的规律进行排序，生成索引表，后续的查询会先在索引表中找到满足条件的数据对应的ROWID,然后通过ROWID去源表中找到对应的行;
索引种类：唯一索引、位图索引、组合索引、函数索引
索引缺点：索引是一个数据库对象，会生成索引表，会占用一定的空间，还会影响表的增删改效率;
索引其他优点：增加排序和分组的效率;
数据量小的时候，使用索引不一定比全表扫描快;
索引有时候会失效，失效场景如下：
    1.索引列用到函数时，会失效;
    2.索引列进行算数运算时，会失效;
    SELECT * FROM EMP_BAK WHERE EMPNO-10=7788;
    3.索引列进行不等值判断，也会失效
    SELECT * FROM EMP_BAK WHERE EMPNO<>7788;
    4.索引列进行模糊查询，%在前会失效;
    SELECT * FROM EMP_BAK WHERE ENAME LIKE 'S%'
    SELECT * FROM EMP_BAK WHERE ENAME LIKE '%S'
    SELECT * FROM EMP_BAK WHERE ENAME LIKE '_S'
    5.索引列的空值过滤,会失效;
    SELECT * FROM EMP WHERE EMPNO IS NULL;
    6.组合索引，没有引用到第一列时，索引也会失效;
    SELECT * FROM EMP_BAK
    WHERE DEPTNO=10 AND JOB='MANAGER';  --会走索引
    SELECT * FROM EMP_BAK
    WHERE DEPTNO=10;                   --会走索引
    SELECT * FROM EMP_BAK
    WHERE JOB='MANAGER';              --不会走索引
    
索引适用场景：表总数据量大，查询的数据小，占总表15%左右;