oracle学习day2

一。sql执行顺序

1. From阶段:对数据源进行预处理，根据提供的运算符对语句中提到的各个表进行处理

首先对from子句中的各个数据源进行笛卡尔积运算，得到v1-1表
如果有外连接查询，根据on的条件筛选v1-1，得到v1-2
然后根据外连接的类型，将不满足on条件的行添加到v1-2中得到v1-3，如果还有剩余表，则将v1-3与下一张表重复上述步骤
如果有pivot、unpivot，则进行相关处理
最终返回v1

2. Where阶段：根据where子句中的条件筛选v1，得到v2
3. GROUP BY阶段:GROUP阶段按照指定的列名列表，将V2中的行进行分组，生成V3。

如果还使用了rollup、cube、grouping、grouping sets，则进行相关处理
最终返回v3

4. HAVING阶段:根据having子句指定的条件筛选v3，返回v4
5. SELECT阶段：从v4中取出select中指定的字段，返回v5

如果有字段要计算，则计算结果，比如使用了聚合函数，这时经过group by分组的每组内，就会进行聚合函数计算，最终每组只有一行
如果有字段使用了distinct关键字，则去除掉重复值的行

6. ORDER BY阶段：根据子句中包含的字段及指定的排序规则对v5排序，最终返回v6

注意

• oracle中，如果同时使用distinct和order by，则order by中的字段必须是select中包含的字段

二。sql处理顺序

1.运用HASH算法，得到一个HASH值，这个值可以通过V$SQLAREA.HASH_VALUE 查看

2.到shared pool 中的 library cache 中查找是否有相同的HASH值，如果存在，则无需硬解析，进行软解析

3.如果shared pool不存在此HASH值，则进行语法检查，查看是否有语法错误

4.如果没有语法错误，就进行语义检查，检查该SQL引用的对象是否存在，该用户是否具有访问该对象的权限

5.如果没有语义错误，对该SQL进行解析，生成解析树，执行计划

6.生成ORACLE能运行的二进制代码，运行该代码并且返回结果给用户

• 硬解析和软解析都在第5步进行
• 硬解析通常是昂贵的操作，大约占整个SQL执行的70%左右的时间，硬解析会生成执行树，执行计划，等等。
• 当再次执行同一条SQL语句的时候，由于发现library cache中有相同的HASH值，这个时候不会硬解析，而会软解析，就是跳过了生成解析树，生成执行计划这个耗时又耗CPU的操作，直接利用生成的执行计划运行

三。索引

1. 索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。
2. 索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率
3. 索引在逻辑上和物理上都与相关的表和数据无关。
4. 索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删除，不会对表产生影响
5. 索引对用户是透明的，无论表上是否有索引，sql语句的用法不变，oracle创建主键时会自动在该列上创建索引
6. 索引不一定会被使用，如果表的数据量过小，全表扫描速度会大于查找索引树的速度，oracle经过分析后就不会使用索引
7. 在Oracle数据库中，如果某列出现在查询的条件中，而该列的数据是无序的，查询时只能从第一行开始一行一行的匹配。创建索引就是对某些特定列中的数据进行排序或归类，生成独立的索引表。在某列上创建索引后，如果该列出现在查询条件中，Oracle会自动的引用该索引，先从索引表中查询出符合条件记录的 ROWID，由于 ROWID 是记录的物理地址，因此可以根据 ROWID快速的定位到具体的记录，当表中的数据非常多时，引用索引带来的查询效率非常可观
8. 如果表的数据在十万以上，然后经常在where子句中作为查询条件的列，查询出来的数据总是小于百分之五，可以为该列建立索引
9. 对于经常在表连接条件、order by、group by、where 中出现的字段，应该建立索引
10. 不要在数据量较小的表中建立索引，因为全表扫描可能更快
11. 如果一个经常作为查询、排序的列，不同值较少，比如性别列，那么可以考虑位图索引
12. 经常进行增删改操作的表尽量不要建立索引
13. 如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引
14. 至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）
15. 列中有很多空值，但经常查询该列上非空记录时应该建立索引
16. 建立组合索引时，最常查询的列要在最左边
17. LONG（可变长字符串数据，最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引
18. 限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）
19. like查询时不要将%放在首位，会放弃索引
20. 索引列不要使用not，会放弃索引，也不要比较空值
21. 在插入数据时，叶子节点索引块可能发生分裂;在删除数据时，则会将对应叶子节点中的数据删除，但是节点还是保留(高水位线);在更新数据时，会更新对应叶子节点中的索引键值
22. 如果返回的数据量较大，则全表扫描比使用索引效率会更高

索引分裂

索引重建

1. 前提：表上频繁发生update,delete操作、表上发生了alter table …move操作（move操作导致了rowid变化）
2. 标准：首先分析索引结构Analyze index index_name validate structure;，然后查询索引树高度select height,DEL_LF_ROWS/LF_ROWS from index_stats; 如果查询出来的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的场合 ， 该索引考虑重建 。
3. 推荐的方式1：rebuild重建alter index indexname rebuild; 或alter index indexname rebuild [online][nologgin];，因为使用现有索引项来重建新索引，为了防止客户操作时有其他用户在对这个表操作，如果不添加online，则会锁定当前表，尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题。rebuild 时， 当新的索引创建完成后 ， 把这个表的记录维护到新的索引中去 ， 然后 drop 掉旧的索引 ，rebuild 就完成了。因为生成日志会降低重建速度，所以也可以添加nologging参数不产生日志，因为只要是rebuild操作都会产生大量redo重做日志。还要检查表空间是否足够，因为要等新索引创建完成，才会删除旧索引，如果没有成功则不会影响旧索引。需要注意的是alter index index_name rebuild 命令中必须使用tablespace字句，以保证重建工作是在现有索引相同的表空间进行。
4. 推荐的方式二：合并索引alter index index_name coalesce。使用带有coalesce参数时重建期间不需要额外空间，它只是在重建索引时将处于同一个索引分支内的叶块拼合起来，这最大限度的减少了与查询过程中相关的潜在的加锁问题，但是，coalesce选项不能用来将一个索引转移到其他表空间。
5. 重建分区索引：重建一级分区索引Alter index indexname rebuild partition paritionname tablespace tablespacename;。重建二级分区索引(复合索引才有):Alter index indexname rebuild subpartition partitioname tablespace tablespacename;。

B树索引