oracle 的索引index，视图view

/**********************
面试:如何优化你的数据库查询?

1.数据库的查询方式?
– 全表扫描：最慢 select * from tb_emp;
– 利用索引扫描：快，推荐使用。也是一张表，只有两列。key-value
– 共享语句：必须建立在全表扫描的基础之上，已经查过的数据会保留在缓存中，直接去缓存中查找。

索引 index
作用: 在数据库中用来加速对表的查询。
原理: 通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O
特点:
与表独立存放，但不能独立存在，必须属于某个表
由数据库自动维护，表被删除时，该表上的索引自动被删除。

索引的创建:
自动: 当在表上定义一个PRIMARY KEY 或者UNIQUE 约束条件时, 数据库自动创建一个对应的索引.
手动: 用户可以创建索引以加速查询.（不能乱指定，最好是经常要查的字段）

当创建索引的时候，Oracle会默认创建一个和当前表相关的索引页,
而索引页中保存了索引字段和真实的磁盘地址，
当用户发送sql语句带了索引的时候，Oracle会到索引页中查询索引字段，
直接定位磁盘IO，提取数据。所以索引数据快于全表扫描。

索引的维护
1.建立索引后，查询的时候需要在 where 条件中带索引的字段才可以使用索引。
2.在经常查询的字段上面建立索引。不要在所有字段上面建立索引。
3.因为索引是用来加快查询速度的，如果一张表经常做insert、delete、update，
而很少做 select,不建议建立索引，因为Oracle需要对索引进行额外的维护
如果一张表字段很少，不建议建立索引。
4.索引是由Oracle自动维护的。索引使用久了会产生索引碎片（磁盘碎片），
影响查询效果，所以使用久了需要手动进行维护(删除再重建)。
*/

select * from tb_stu;

insert into tb_stu values (9, ‘Jss’, 1);

– 创建 index
– create index 索引名 on 需要创建索引的表名(当做索引的列名);
create index stu_index on tb_stu(name);
– 在使用的时候，不用理，Oracle 会自动去调用索引进行查询
select * from tb_stu where name = ‘翠花’;

– 删除索引
drop index stu_index;

/*
sql语句的优化：
多使用共享语句 尽量使你的sql语句能够使用索引。
怎样使sql语句能够使用到索引呢：
当sql语句中包含not in,<>,is null,is not null,like ‘%%’的时候不会用索引。

IN: in 会拆成一堆 or 的,可以使用表的索引。

NOT IN:强列推荐不使用，因为它不能应用表的索引。

<> 操作符（不等于）: 不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。
优化方案:用其它相同功能的操作运算代替，如a<>0 改为 a>0 or a<0;a<>’’ 改为 a>’’.

IS NULL 或IS NOT NULL操作（判断字段是否为空）:
判断字段是否为空一般是不会应用索引的，
因为 B 树索引(oracle大多是使用 B 树索引)是不索引空值的。
优化方案:用其它相同功能的操作运算代替，如 a is not null 改为 a>0 或a>’’等。
is null 时，用一个缺省值代替空值，例如业务申请中状态字段不允许为空，缺省为申请。

LIKE：LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，
但是如果用得不好则会产生性能上的问题，

优化方案:如LIKE ‘%001%’ 这种查询不会引用索引，会产生全表扫描，
而LIKE ‘001%’则会引用范围索引。进行范围的查询，性能肯定大大提高。
*/

create table aaa (
comm int default 0,
status varchar(20) default ‘已发’
);

create table bbb (
email varchar(50) default ‘zsfz053@126.com’
);

select * from aaa where comm is null; – 不会用到 index
select * from bbb where email = ‘zsfz053@126.com’; – 会用到 index

– 创建用户时通过 dba 角色赋予权限，而不是手动赋予权限,则创建视图会报：没有权限
– 解决方案：使用 system 用户登录，再给 java9 赋予权限
grant create any table to jss with admin option;
grant create any view to jss with admin option;
grant select any table to jss with admin option;

– 数据字典都是视图
– 数据字典，其实就是 Oracle 自带的一些路七八糟的数据表
select * from User_tables;
select * from scott.emp;

– 同义字可以理解为表的别名，视图可以理解成 sql 语句的别名
–视图可以使复杂的查询变得简单
select deptno, sum(sal), max(sal), min(sal), count(*), avg(sal)
from scott.emp
group by deptno;

–创建简单视图(一个表)
create view aaaa
as
select deptno 部门, sum(sal) 总薪资, max(sal) 最大值, min(sal) 最小值, count(*) 总数, avg(sal) 平均数
from scott.emp
group by deptno;

–使用视图
select * from aaaa;
select * from bbbb;

–复杂视图(多个表)
create view bbbb
as
select d.dname 部门, d.loc 地址, e.*
from scott.dept d, scott.emp e
where d.deptno = e.deptno

–删除视图

–注意：可以通过视图操作表，但是不建议