Oracle的优化器

sql优化第一步：搞懂Oracle中的SQL的执行过程

从图中我们可以看出SQL语句在Oracle中经历了以下的几个步骤：

语法检查：检查SQL拼写是否正确，如果不正确，Oracle会报语法错误。
语义检查：检查SQL中的访问对象是否存在。比如我们在写SELECT语句的时候，列名写错了，系统就会提示错误。语法检查和语义检查的作用是保证SQL语句没有错误。
权限检查：看用户是否具备访问该数据的权限。
共享池检查：共享池（Shared Pool）是一块内存池，最主要的作用是缓存SQL语句和该语句的执行计划。Oracle通过检查共享池是否存在SQL语句的执行计划，来判断进行软解析，还是硬解析。
优化器：优化器中进行硬解析，也就是决定怎么做，比如创建解析树，生成执行计划。
执行器：当有了解析树和执行计划之后，就知道了SQL该怎么被执行，这样就可以在执行器中执行语句了。
共享池是Oracle中的术语，包括了库缓存，数据字典缓冲区等。**库缓存区，主要缓存SQL语句和执行计划。**数据字典缓冲区存储的是Oracle中的对象定义，比如表、视图、索引等对象。当对SQL语句进行解析的时候，如果需要相关的数据，会从数据字典缓冲区中提取。

硬解析和软解析
软解析：在共享池中，Oracle首先对SQL语句进行Hash运算，然后根据Hash值在库缓存（Library Cache）中查找，如果存在SQL语句的执行计划，就直接拿来执行，直接进入“执行器”的环节

硬解析：如果没有找到SQL语句和执行计划，就会进入“优化器”这个步骤，Oracle就需要创建解析树进行解析，生成执行计划

如何避免硬解析
在Oracle中，绑定变量是它的一大特色。绑定变量就是在SQL语句中使用变量，通过不同的变量取值来改变SQL的执行结果。

这样做的好处是能提升软解析的可能性，不足之处在于可能会导致生成的执行计划不能优化，因此是否需要绑定变量还需要视情况而定。
举个例子，我们可以使用下面的查询语句：

SQL> select * from student where student_id = 100;
1.
你也可以使用绑定变量，如：

SQL> select * from student where student_id = :student_id;
1.
这两个查询语句的效率在Oracle中是完全不同的。

如果在查询过student_id = 100以后，我们还需要继续查询101、102等学生的信息，那么我们每次使用第一种查询方式的时候，都会创建一个新鞋的查询解析。

使用第二种方式的时候，第一次查询之后，会在共享池中存在这类查询的执行计划，也就是上面提到的软解析。

绑定变量的方式可以减少硬解析，减少Oracle的解析工作量。但是因为参数不同，可能会导致SQL的执行效率不同，同时SQL优化也会比较困难。

sql优化第二步：看懂优化方式及其运行逻辑

Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条SQL可能有多种执行计划,但在某一时点,一定只有一种执行计划是最优的,花费时间是最少的。

相信你一定会用Pl/sql Developer、Toad等工具去看一个语句的执行计划,不过你可能对Rule、Choose、First rows、All rows这几项有疑问,因为我当初也是这样的,那时我也疑惑为什么选了以上的不同的项,执行计划就变了?

1、优化器的优化方式

Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。

A、RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。

B、CBO方式：依词义可知,它是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小 、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。在Oracle8及以后的版本,Oracle列推荐用CBO的方式。

我们要明了,不一定走索引就是优的 ,比如一个表只有两行数据,一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时对这个表做全表扫描(full table scan)是最好的。

新版本的oracle逐渐抛弃对Rule方式的支持,即使是Rule方式,最后sql执行效率的衡量标准都是,sql执行消耗了多少资源?对代价(COST)的优化方式,需要表,索引的统计信息,需要每天多表和索引进行定时的分析,但是统计信息也是历史的,有时候也不一定是最优的,统计信息等于就是一个人的经验,根据以前的经验来判断sql该怎么执行(得到优化的sql执行路径),所以具体优化执行的时候,先手工分析sql,看是用RBO方式消耗大,还是CBO消耗大;DBA的工作就是要根据当前oracle的运行日志,进行各种调整,使当前的oracle运行效率尽量达到最优.可以在运行期间,采用hint灵活地采用优化方式.

2、优化器的优化模式(Optermizer Mode)

优化模式包括Rule,Choose,First rows,All rows，FIRST_ROWS_n这五种方式,也就是我们以上所提及的。如下我解释一下：
一、CHOOSE
这个是Oracle的默认值。采用这个值时，Oracle即可以采用基于规则RBO，也可以采用基于代价CBO，到底使用那个值，取决于当前SQL的被访问的表中是不是有可以使用的统计信息。
如果有多个被访问的表，其中有一个或多个有统计信息，那么Oralce会对没有统计信息的表进行采样统计（即不全部采样），统计完成后，使用基于代价的优化方法CBO。
如果所有被访问的表都没有统计信息，Oracle就会采用基于规则的优化方法RBO。
二、ALL_ROWS
优化器将寻找能够在最短的时间内完成语句的执行计划。
不管是不是有统计信息，全部采用基于成本的优化方法CBO。
设置为这种CBO模式以后，将保证消耗的所有计算资源最小，尽管有时查询结束以后没有结果返回。all_rows的优化模式更倾向于全表扫描，而不是全索引扫描和利用索引排序，因此这种优化模式适合于数据查看实时性不是那么强的数据仓库、决策支持系统和面向批处理的数据库（batch-oriented databases）等。
三、FIRST_ROWS
CBO模式，使用成本和试探法相结合的方法，查找一种可以最快返回前面少数行的方法；这个参数主要用于向后兼容。
在oracle 9i之后这一选项已经过时，出于向后兼容的目的保留了这一选项，该选项的作用在于寻找能够在最短的时间内返回结果集的第一行的执行计划。这一规则倾向于促使优化器使用索引访问路径，偶尔会出现非常不恰当的访问路径。
设置为这种CBO模式以后，SQL语句返回结果的速度会尽可能的快，而不管系统全部的查询是否会耗时较长或者耗系统资源过多。由于利用索引会使查询速度加快，所以first_rows 优化模式会在全表扫描上进行索引扫描。这种优化模式一般适合于一些OLTP系统，满足用户能够在较短时间内看到较小查询结果集的要求。
四、FIRST_ROWS_N
不管是不是有统计信息，全部采用基于成本的优化方法CBO，并以最快的速度，返回前N行记录。
N的值可以为1，10，100，1000，优化器首先通过彻底分析第一个连接顺序来估计返回行的总数目。这样就可以知道查询可能获得的整个数据集的片段，并重新启动整个优化过程，其目标在于找到能够以最小的资源消耗返回整个数据片段的执行计划。
Oracle 9i 对一些预期返回结果集的数据量小的SQL语句优化模式进行了加强，增加了四个参数值：first_rows_1、first_rows_10、first_rows_100、first_rows_1000。CBO通过first_rows_n中的 n 值，决定了返回结果集数量的基数，我们可能仅仅需要查询结果集中的一部分，CBO就根据这样的n 值来决定是否使用索引扫描。
五、RULE
这个参数正好和ALL_ROWS相反，不管是不是统计信息，全部采用基于规则rbo的优化方法。
基于规则的优化器模式，RBO，是早期Oracle版本使用过的一种优化模式。由于RBO不支持自1994年Oracle版本的新特性，如bitmap indexes，table partitions，function-based indexes等，所以在以后Oracle版本中已经不再更新RBO，并且也不推荐用户使用RBO这种优化模式了。

First Rows和All Rows是有冲突的.如果想最快第返回给用户,就不可能传递更多的结果,这就是First Rows返回最先检索到的行(或记录);而All Rows是为了尽量将所有的结果返回给用户,由于量大,用户就不会很快得到返回结果.就象空车能跑得很快,重装车只能慢慢地跑;

3、如何设定选用哪种优化模式

查看参数
show parameter optimizer_mode

修改参数
alter session set optimizer_mode='CHOOSE'

修改方式

a、Instance级别 

我们可以通过在init<SID>.ora文件中设定OPTIMIZER_MODE=RULE、OPTIMIZER_MODE=CHOOSE、OPTIMIZER_MODE=FIRST_ROWS、OPTIMIZER_MODE=ALL_ROWS去选用3所提的四种方式,如果你没设定OPTIMIZER_MODE参数则默认用的是Choose这种方式。 
init.ora和init<SID>.ora都在$ORACLE_HOME/dbs目录下,可以用find $ORACLE_HOME -name init*.ora查看该目录下的init文件.
init.ora是对全体实例有效的;init<SID>.ora只对指定的实例有效.

B、Sessions级别 

通过SQL> ALTER SESSION SET OPTIMIZER_MODE=<Mode>;来设定。 将覆盖init.ora,init<sid>.ora设定的优化模式,也可以在sql语句中采用hint强制选定优化模式.如下:

C、语句级别 

这些需要用到Hint,比如: 
SQL> SELECT /*+ RULE */ a.userid, 
2 b.name, 
3 b.depart_name 
4 FROM tf_f_yhda a, 
5 tf_f_depart b 
6 WHERE a.userid=b.userid; 
在这儿采用hint,强制采用基于规则(rule)的优化模式;
hint语法,/*+开头,*/结尾,中间填写强制采用的优化模式.

4、为什么有时一个表的某个字段明明有索引,当观察一些语的执行计划确不走索引呢？如何解决呢 ？

A、不走索引大体有以下几个原因 
♀你在Instance级别所用的是all_rows的方式 
♀你的表的统计信息(最可能的原因) 
♀你的表很小,上文提到过的,Oracle的优化器认为不值得走索引。 
B、解决方法 
♀可以修改init<SID>.ora中的OPTIMIZER_MODE这个参数,把它改为Rule或Choose,重起数据库。也可以使用4中所提的Hint. 
♀删除统计信息 
SQL>analyze table table_name delete statistics; 
♀表小不走索引是对的,不用调的。

或者用一下方法试试：
第一，我们要确定数据库运行在何种优化模式下，相应的参数是：optimizer_mode。可在 sqlplus中运行“show parameter optimizer_mode”来查看。oracle v7以来缺省的设置应是“choose”，即如果对已分析的表查询的话选择 cbo，否则选择 rbo。如果该参数设为“rule”，则不论表是否分析过，一概选用 rbo，除非在语句中用 hint强制。

第二，检查被索引的列或组合索引的首列是否出现在 pl/sql语句的 where子句中，这是“执行

计划”能用到相关索引的必要条件。

第三，看采用了哪种类型的连接方式。oracle的共有 sort merge join（smj）、hash join（hj）和 nested loop join（nl）。在两张表连接，且内表的目标列上建有索引时，只有nested loop才能有效地利用到该索引。smj即使相关列上建有索引，最多只能因索引的存在，避免数据排序过程。hj由于须做 hash运算，索引的存在对数据查询速度几乎没有影响.

第四，看连接顺序是否允许使用相关索引。假设表 emp的 deptno列上有索引，表 dept的列deptno上无索引，where语句有 emp.deptno=dept.deptno条件。在做 nl连接时，emp做为外表，先被访问，由于连接机制原因，外表的数据访问方式是全表扫描，emp.deptno上的索引显然是用不上，最多在其上做索引全扫描或索引快速全扫描。

第五，是否用到系统数据字典表或视图。由于系统数据字典表都未被分析过，可能导致极差的“执行计划”。但是不要擅自对数据字典表做分析，否则可能导致死锁，或系统性能下降。

第六，索引列是否函数的参数。如是，索引在查询时用不上。

第七，是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较，oracle会自动将字符型用 to_number()函数进行转换，从而导致第六种现象的发生。

第八，是否为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析，可用 sql语句“analyze table xxxx compute statistics for all indexes;”。oracle掌握了充分反映实际的统计数据，才有可能做出正确的选择.

第九，索引列的选择性不高。我们假设典型情况，有表 emp，共有一百万行数据，但其中的emp.deptno列，数据只有 4种不同的值，如 10、20、30、40。虽然 emp数据行有很多，oracle缺省认定表中列的值是在所有数据行均匀分布的，也就是说每种 deptno值各有 25万数据行与之对应。

假设 sql搜索条件 deptno=10，利用 deptno列上的索引进行数据搜索效率，往往不比全表扫描的高，oracle理所当然对索引“视而不见”，认为该索引的选择性不高。但我们考虑另一种情况，如果一百万数据行实际不是在 4deptno值间平均分配，其中有 99万行对应着值 10，5000行对应值 20，3000行对应值 30，2000行对应值 40。在这种数据分布图案中对除值为 10外的其它 deptno值搜索时，毫无疑问，如果索引能被应用，那么效率会高出很多。我们可以采用对该索引列进行单独分析，或用 analyze语句对该列建立直方图，对该列搜集足够的统计数据，使 oracle在搜索选择性较高的值能用上索引。

第十，索引列值是否可为空（null）。如果索引列值可以是空值，在 sql语句中那些需要返回 null值的操作，将不会用到索引，如 count（*），而是用全表扫描。这是因为索引中存储值不能为全空。

第十一，看是否有用到并行查询（pqo）。并行查询将不会用到索引。

第十二，看 pl/sql语句中是否有用到 bind变量。由于数据库不知道 bind变量具体是什么值，在做非相等连接时，如“<”，“>”,“like”等。oracle将引用缺省值，在某些情况下会对执行计划造成影响。如果从以上几个方面都查不出原因的话，我们只好用采用在语句中加 hint的方式强制oracle使用最优的“执行计划”。

hint采用注释的方式，有行注释和段注释两种方式。

如我们想要用到 a表的 ind_col1索引的话，可采用以下方式：

“select /*+ index（a ind_col1）*/ * from a where col1 = xxx;”

注意，注释符必须跟在 select之后，且注释中的“+”要紧跟着注释起始符“/*”或“–”，否则

hint就被认为是一般注释，对 pl/sql语句的执行不产生任何影响。

5、其它相关

A、如何看一个表或索引是否是统计信息 

SQL>SELECT * FROM user_tables 
WHERE table_name=<table_name> 
AND num_rows is not null; 

SQL>SELECT * FROM user_indexes 
 WHERE table_name=<table_name> 
AND num_rows is not null; 

b、如果我们采用CBO的方式,我们应及时去更新表和索引的统计信息,以免生形不切合实的执行计划。 
SQL> ANALYZE TABLE table_name COMPUTE STATISTICS; 
SQL> ANALYZE INDEX index_name ESTIMATE STATISTICS; 

所以查询优化是提高数据库性能和减少查询响应时间的关键。在Oracle数据库中，有许多技巧可以用来优
化查询操作。下面将介绍八个提升查询效率的秘诀。

1、使用合适的索引：
1）了解查询的访问模式，使用适当的索引类型（B树索引、位图索引等）。
2）对经常使用的查询字段创建索引，以加快检索速度。
3）避免过多的索引，因为索引的增加会导致写操作的开销。

2、编写高效的查询语句：
1）避免使用SELECT *，只选择需要的列，减少磁盘I/O。
2）使用合适的WHERE子句，尽量减少结果集的大小。
3）使用合适的连接方式（INNER JOIN、LEFT JOIN等），确保连接的正确性和高效性。
使用查询的where语句逻辑和索引关系

sql语句编写注意问题:
下面就某些 sql语句的 where子句编写中需要注意的问题作详细介绍。在这些 where子句中，即使某些列存在索引，但是由于
编写了劣质的 sql，系统在运行该 sql语句时也不能使用该索引，而同样使用全表扫描，这就造成了响应速度的极大降低。

1)is null与 is not null
不能用 null作索引，任何包含 null值的列都将不会被包含在索引中。任何在 where子句中使用 is null或 is not null的语句优化
器是不允许使用索引的。

2)语句中的函数
语句中如有函数操作如（upper,substr等）时，优化器是不会使用索引的，应尽量少用或不用。

3)带通配符（%）的 like语句
如果通配符（%）在搜寻词首出现，oracle系统不会使用索引。在很多情况下可能无法避免这种情况，然而当通配符出现在字
符串其他位置时，优化器就能利用索引。
在下面的查询中索引得到了使用：
select * from user_m where loginid like ‘r%’;

4)order by语句
order by语句决定了 oracle如何将返回的查询结果排序。order by语句对要排序的列没有什么特别的限制。但任何在 order by
语句的非索引项或者有计算表达式都将降低查询速度,应尽量少用。
order by 条件要与where中条件一致，否则order by不会利用索引进行排序**
-- 不走age索引
SELECT * FROM t order by age;
-- 走age索引
SELECT * FROM t where age > 0 order by age;

5)not
在查询时经常在 where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用 and（与）、or（或）以
及 not（非）。not可用来对任何逻辑运算符号取反。not运算符包含在另外一个逻辑运算符中，这就是不等于（<>）运算符。
换句话说，即使不在查询 where子句中(!=也不行)
显式地加入 not词，not仍在运算符中，见下例：
select * from employee where salary<>3000;
对这个查询，可以改写为不使用 not：select * from employee where salary<3000 or salary>3000;
虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许对
salary列使用索引，而第一种查询则不能使用索引。

6)in和 exists
在 where子句中使用子查询。在 where子句中可以使用两种格式的子查询。第一种格式是使用in操作符：第二种格式是使用 
exist操作符：第二种格式要远比第一种格式的效率高。应尽可能使用 not exists来代替 not in。

7)条件的顺序问题
条件列和索引列的顺序要保持对应

8)使用 union、intersect、minus;消除对大型表行数据的顺序存取
对连接的列进行索引，还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的 where子句强迫优
化器使用顺序存取。下面的查询将强迫对 orders表执行顺序操作：
select＊ from orders where (customer_num=104 and order_num>1001) or
order_num=1008
虽然在 customer_num和 order_num上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个
语句要检索的是分离的行的集合，所以应该改为如下语句：
select＊ from orders where customer_num=104 and order_num>1001 union
select＊ from orders where order_num=1008

9)尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。**如下：
SELECT * FROM t WHEREid = 1 OR id = 3
优化方式：可以用union代替or。如下
SELECT * FROM t WHERE id = 1
UNION
SELECT * FROM t WHERE id = 3

10)尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。**
可以将表达式、函数操作移动到等号右侧。如下：
-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9

11)当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索
引进行全表扫描。**如下：
SELECT username, age, sex FROM T WHERE 1=1
优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and。

这样就能利用索引路径处理查询。

3、使用优化器提示（Hint）(最后研究这个)：
1）根据具体情况，使用Hint指令告诉优化器如何执行查询。
2）例如，使用INDEX hint指定使用某个索引，或者使用LEADING hint指定连接的顺序。

一、提示（Hint）概述
1为什么引入Hint?
Hint是Oracle数据库中很有特色的一个功能，是很多DBA优化中经常采用的一个手段。那为什么Oracle会考虑引入优化器呢？
基于代价的优化器是很聪明的，在绝大多数情况下它会选择正确的优化器，减轻DBA的负担。
但有时它也聪明反被聪明误，选择了很差的执行计划，使某个语句的执行变得奇慢无比。此时就需要DBA进行人为的干预，
告诉优化器使用指定的存取路径或连接类型生成执行计划，从而使语句高效地运行。Hint就是Oracle提供的一种机制，用来告
诉优化器按照告诉它的方式生成执行计划。

2不要过分依赖Hint
当遇到SQL执行计划不好的情况，应优先考虑统计信息等问题，而不是直接加Hint了事。如果统计信息无误，应该考虑物理结
构是否合理，即没有合适的索引。只有在最后仍然不能SQL按优化的执行计划执行时，才考虑Hint。
毕竟使用Hint，需要应用系统修改代码，Hint只能解决一条SQL的问题，并且由于数据分布的变化或其他原因(如索引更名)
等，会导致SQL再次出现性能问题。

3Hint的弊端
Hint是比较"暴力"的一种解决方式，不是很优雅。需要开发人员手工修改代码。
Hint不会去适应新的变化。比如数据结构、数据规模发生了重大变化，但使用Hint的语句是感知变化并产生更优的执行计划。
Hint随着数据库版本的变化，可能会有一些差异、甚至废弃的情况。此时，语句本身是无感知的，必须人工测试并修正。

4Hint与注释关系
提示是Oracle为了不破坏和其他数据库引擎之间对SQL语句的兼容性而提供的一种扩展功能。Oracle决定把提示作为一种特殊
的注释来添加。它的特殊性表现在提示必须紧跟着DELETE、INSERT、UPDATE或MERGE关键字。
换句话说，提示不能像普通注释那样在SQL语句中随处添加。且在注释分隔符之后的第一个字符必须是加号。在后面的用法
部分，会详细说明。

5Hint功能
Hint提供的功能非常丰富，可以很灵活地调整语句的执行过程。通过Hint，我们可以调整：
优化器类型
优化器优化目标
数据读取方式(访问路径)
查询转换类型
表间关联的顺序
表间关联的类型
并行特性
其他特性

二、Hint用法
1语法
1）关键字说明
DELETE、INSERT、SELECT和UPDATE是标识一个语句块开始的关键字，包含提示的注释只能出现在这些关键字的后面，
否则提示无效。
"+"号表示该注释是一个提示，该加号必须立即跟在"/*"的后面，中间不能有空格。
hint是下面介绍的具体提示之一，如果包含多个提示，则每个提示之间需要用一个或多个空格隔开。
text是其它说明hint的注释性文本

2）提示中的错误
提示中的语法错误不会报错，如果解析器不能解析它，就会把它看做一个普通注释处理。这也是容易造成困惑的一点，使用
的Hint到底是否起效？可以采用一些手段，检查提示的有效性。需要注意的是，那些语法正确但引用对象错误的提示是不会被
报告的。
explain plan + dbms_xplan
使用dbms_xplan输出中的note选项。
10132事件
在10g中，这个事件产生的输出文档的末尾有一部分内容专门讲提示。通过它可以检查两个方面:一是每个用到的提示都会被
列出来。如果漏掉了哪个，就说明这个提示没有被识别；二是检查是否有一些信息指明了出现提示错误(如果出错，err值将大
于0)。

3）提示中的对象
SELECT /*+ INDEX(table_name index_name) */ ...
table_name是必须要写的，且如果在查询中使用了表的别名，在hint也要用表的别名来代替表名。
index_name可以不必写，Oracle会根据统计值选一个索引。
如果索引名或表名写错了，那这个hint就会被忽略。
如果指定对象是视图，需要按此方法指定。/*+hint view.table ...*/，其中table是view中的表。
一个很常见的错误时，在使用提示的时候最易犯的错误是与表的别名有关。正确的规则是，当在提示中使用表时，只要表有
别名就应该使用别名而不是表名。

2提示的作用域
查询块
初始化参数提示对整个SQL语句起作用，其他的提示仅仅对查询块起作用。仅仅对单个查询块起作用的提示，必须在它控制
的查询块内指定。
例外 - 全局提示
可以使用点号引用包含在其他查询块(假设这些块已命名)中的对象。全局提示的语法可以支持两层以上的引用，对象间必须用
点号分隔。
命名查询块
既然where子句中的子查询是没有命名的，它们的对象就不能被全局提示引用。为了解决这个问题，10g中使用了另一种方法
来解决-命名查询块。查询优化器可以给每个查询生成一个查询块名，而且还可以使用提示qb_name手工为每个查询块命名。
大多数提示都可以通过参数来指定在那个查询块中有效。
*在提示中通过@来引用一个查询块。

3提示数据字典
Oracle在11g的版本中提供了一个数据字典—V$SQL_HINT。通过这个数据字典可以看到提示的出现版本、概要数据版本、
SQL特性以及相反提示等。
INVERSE
这个hint相反操作的hint。
VERSION
代表着这个hint正式公布引入的版本。

三、Hint分类

1和优化器相关的
 
当对优化器为某个语句所制定的基本执行计划不满意时，最好的办法就是通过提示来转换优化器的模式，并观察其转换后的
结果，看是否已经达到期望程度。如果只通过转换优化器的模式就可以获得非常好的执行计划，则就没有必要额外使用更为
复杂的提示了。

OPT_PARAM
这个提示的作用就是使我们在某条语句中指定某个系统参数值。

ALL_ROWS
为实现查询语句整体最优化而引导优化器制定最少成本的执行计划。这个提示会使优化器选择一条可最快检索所有查询行的
路径，而代价就是在检索一行数据时，速度很慢。

FIRST_ROWS 
为获得最佳响应时间而引导优化器制定最少成本的执行计划。这个提示会使优化器选择可最快检索出查询的第一行(或指定行)
数据的路径，而代价就是检索很多行时速度就会很慢。利用FIRST_ROWS来优化的行数，默认值为1，这个值介于10到1000
之间，这个使用FIRST_ROWS(n)的新方法是完全基于代价的方法。它对n很敏感，如果n值很小，CBO就会生成包含嵌套循
环以及索引查找的计划；如果n很大，CBO会生成由哈希连接和全表扫描组成的计划(类似ALL_ROWS)。

CHOOSE
依据SQL中所使用到的表的统计信息存在与否，来决定使用RBO还是CBO。在CHOOSE模式下，如果能够参考表的统计信
息，则将按照ALL_ROWS方式执行。除非在查询中的所有表都没有经过分析，否则choose提示会对整个查询使用基于代价的
优化。如果在多表连接中有一个表经过分析过，那么就会对整个查询进行基于代价的优化。

RULE 
使用基于规则的优化器来实现最优化执行，即引导优化器根据优先顺序规则来决定查询条件中所使用到的索引或运算符的执
行顺序来制定执行计划。这个提示强制oracle优先使用预定义的一组规则，而不是对数据进行统计；同时该提示还会使这个语
句避免使用其他提示，除了DRIVING_SITE和ORDERED(不管是否进行基于规则的优化，这两个提示都可使用)。

2和访问路径相关的
 
FULL
告诉优化器通过全表扫描方式访问数据。这个提示只对所指定的表进行全表扫描，而不是查询中的所有表。FULL提示可以改
善性能。这主要是因为它改变了查询中的驱动表，而不是因为全表扫描。在使用其他某些提示时，也必须使用FULL提示。只
有访问整个表时，才可利用CACHE提示将表进行缓存。并行组中的某些提示也必须使用全表扫描。

CLUSTER
引导优化器通过扫描聚簇索引来从索引表中读取数据。

HASH
引导优化器按照哈希扫描的方式从表中读取数据。

INDEX
告诉优化器对指定表通过索引的方式访问数据。当访问数据会导致结果集不完整时，优化器将忽略这个Hint。

NO_INDEX
告诉优化器对指定表不允许使用索引。这个提示会禁止优化器使用指定索引。可以在删除不必要的索引之前在许多查询中禁
止索引。如果使用了NO_INDEX，但是没有指定任何索引，则会执行全表扫描。如果对某个索引同时使用了NO_INDEX和会
之产生冲突的提示(如INDEX)，这时两个提示都会被忽略掉。

INDEX_ASC
利用索引从表中读取数据时，引导优化器对提示中所指定索引的索引列值按照升序使用范围扫描。

INDEX_COMBINE
告诉优化器强制选择位图索引。这个提示会使优化器合并表上的多个位图索引，而不是选择其中最好的索引(这是INDEX提示
的用途)。还可以使用index_combine指定单个索引(对于指定位图索引，该提示优先于INDEX提示)。对于B树索引，可以使用
AND_EQUAL提示而不是这个提示。

INDEX_JOIN
索引关联，当谓词中引用的列上都有索引的时候，可以通过索引关联的方式来访问数据。这个提示可以将同一个表的各个不
同索引进行合并，这样就只需要访问这些索引就可以了，节省了回表查询的时间。但只能在基于代价的优化器中使用该提
示。这个提示不仅允许只访问表上的索引，这样可以扫描更少的代码块，并且它比使用索引并通过rowid扫描整个表快5倍。

INDEX_DESC
利用索引从表中读取数据时，引导优化器对提示中所指定索引的索引列值按照降序使用范围扫描。

INDEX_FFS
告诉优化器以INDEX FFS(index fast full scan)的方式访问数据。INDEX_FFS提示会执行一次索引的快速全局扫描。这个提示
只访问索引，而不是对应的表。只有查询需要检索的信息都在索引上时，才使用这个提示。特别在表有很多列时，使用该提
示可以极大地改善性能。

INDEX_SS
强制使用index skip scan的方式访问索引。当在一个联合索引中，某些谓词条件并不在联合索引的第一列时(或者谓词并不在
联合索引的第一列时)，可以通过index skip scan来访问索引获得数据。当联合索引第一列的唯一值很少时，使用这种方式比
全表扫描的方式效率要高。

3和查询转换相关的
USE_CONCAT
将含有多个OR或者IN运算符所连接起来的查询语句分解为多个单一查询语句，并为每个单一查询语句选择最优化查询路径，
然后再将这些最优化查询路径结合在一起，以实现整体查询语句的最优化目的。只有在驱动查询条件中包含OR的时候，才可
以使用该提示。

NO_EXPAND
引导优化器不要为使用OR运算符号(或IN运算符)的条件制定相互结合的执行计划。正好和USE_CONCAT相反。

REWRITE
当表连接的对象是数据量比较大的表或者需要获得使用统计函数处理过的结果时，为了提高执行速度可预先创建物化视图。
当用户要求查询某个查询语句时，优化器会在从表中和从物化视图中读取数据的两种方法中选择一个更有效的方法来读取数
据。该执行方法称之为查询重写。使用REWRITE提示引导优化器按照该方式执行。

MERGE
为了能以最优方式从视图或者嵌套视图中读取数据，通过变换查询语句来直接读取视图使用的基表数据，该过程被称之为视
图合并。不同的情况其具体使用类型也有所不同。该提示主要在视图未发生合并时被使用。尤其是对比较复杂的视图或者嵌
套视图(比如使用了GROUP BY或DISTINC的视图)使用该提示，有时会取得非常好的效果。

UNNEST
提示优化器将子查询转换为连接的方式。也就是引导优化器合并子查询和主查询并且将其向连接类型转换。

NO_UNNEST
引导优化器让子查询能够独立地执行完毕之后再跟外围的查询做FILTER。

PUSH_PRED
使用该提示可以将视图或嵌套视图以外的查询条件推入到视图之内。

NO_PUSH_PRED
使用该提示确保视图或嵌套视图以外的查询条件不被推入到视图内部。

PUSH_SUBQ
使用该提示引导优化器为不能合并的子查询制定执行计划。不能合并的子查询被优先执行之后，该子查询的执行结果将扮演
缩减主查询数据查询范围的提供者角色。通常在无法执行子查询合并的情况下，子查询扮演的都是检验者角色，所以子查询
一般被放在最后执行。在无法被合并的子查询拥有较少的结果行，或者该子查询可以缩减主查询查询范围的情况下，可以使
用该提示引导优化器最大程度地将该子查询放在前面执行，以提高执行速度。但如果子查询执行的是远程表或者排序合并连
接的一部分连接结果，则该提示将不起任何作用。

NO_PUSH_SUBQ
使用该提示将引导优化器将不能实现合并的子查询放在最后执行。在子查询无法缩减主查询的查询范围，或者执行子查询开
销较大的情况下，将这样的子查询放在最后执行可以在某种程度上提高整体的执行效率。也就是说，尽可能地使用其他查询
条件最大程度地缩减查询范围之后，再执行子查询。

4和表连接顺序相关的
这些提示可以调整表连接的顺序。调整表连接的顺序并不是只能使用这些提示，在嵌套循环连接方式中也可以让提示来引导
优化器使用由驱动查询条件所创建的索引。然而，该方法只有在使用的索引和表连接顺序同时被调整的情况下才比较有效。
一般而言，这些提示主要在执行多表连接和表之间的连接顺序比较混乱的情况下才使用，也在排序合并连接或哈希连接方式
下，为引导优化器优先执行数据量比较少得表时使用。

LEADING
在一个多表关联的查询中，这个Hint指定由哪个表作为驱动表，即告诉优化器首先要访问那个表上的数据。引导优化器使用
LEADING指定的表作为表连接顺序中的第一个表。该提示既与FROM中所描述的表的顺序无关，也与作为调整表连接顺序的
ORDERED提示不同，并且在使用该提示时并不需要调整FROM中所描述的表的顺序。当该提示与ORDERED提示同时使用
时，该提示被忽略。
这个提示类似ORDERED提示，它允许指定驱动查询的表，然后由优化器来判断下一个要访问的表。如果使用这个提示指定
多张表，那么就可以忽略这个提示。

ORDERED
引导优化器按照FROM中所描述的表的顺序执行连接。如果和LEADING提示被一起使用，则LEADING提示将被忽略。由于
ORDERED只能调整表连接的顺序并不能改变表连接的方式，所以为了改变表的连接方式，经常将USE_NL、USE_MERGE
提示与ORDERED提示放在一起使用。

5和表连接操作相关的

USE_NL
使用该提示引导优化器按照嵌套循环连接方式执行表连接。它只是指出表连接的方式，对于表连接顺序不会有任何影响。

USE_MERGE
引导优化器按照排序合并连接方式执行连接。在有必要的情况下，推荐将该提示与ORDERED提示一起使用。提示通常用于
获得查询的最佳吞吐量。假设将两个表连接在一起，从每个表返回的行集将被排序，然后再被合并(也就是合并排序)，从而组
成最终的结果集。由于每个行先被排序之后才进行合并，所以在给定查询中检索所有行时，速度将会最快。如果需要以最快
速度返回第一行，就应该使用USE_NL提示。

USE_HASH
该提示引导优化器按照哈希连接方式执行连接。在执行哈希连接时，如果由于某一边的表比较小，从而可以在内存中实现哈
希连接，那么就能够获得非常好的执行速度。由于在大部分情况下优化器会通过对统计信息的分析来决定Build Input和Prove 
Input，所以建议不要使用ORDERED提示随意改变表的连接顺序。但是当优化器没能做出正确判断时，或者像从嵌套视图中
所获得的结果集合那样不具备统计信息时，可以使用该提示。

6和并行相关的

PARALLEL
指定SQL执行的并行度，这个值将会覆盖表自身设定的并行度。如果这个值为default，CBO使用系统参数。从表中读取大量
数据和执行DML操作时使用该提示来指定SQL的并行操作。一般情况下需要在该提示中指定将要使用的并行线程个数。如果
在该提示中没有指定并行度的个数，则优化器将使用PARALLEL_THREADS_PER_CPU参数所指定的值进行自动计算。如果
在定义表时指定了PARALLEL，那么在能够使用并行操作的情况下，即使没有使用该提示，优化器也会按照指定的并行级别
选择并行操作。但是如果想在DELETE、INSERT、UPDATE、MERGE等DML操作中使用并行操作，则必须要在会话中设置
ALTER SESSION ENABLE PARALLEL DML。在某个会话中所设置的并行级别也可以被引用在内部的GROUP BY或者排序
操作中。在并行操作中如果出现了某个限制要素，则该提示将被忽略。

NOPARALLEL/NO_PARALLEL
在SQL语句禁止使用并行。在有些版本中用NO_PARALLEL提示来代替NOPARALLEL提示。

PQ_DISTRIBUTE
为了提高并行连接的执行速度，使用该提示来定义使用何种方法在主从进程之间(例如生产者进程和消费者进程)分配各连接表
的数据行。

PARALLEL_INDEX
为了按照并行操作的方式对分区索引进行索引范围扫描而使用该提示，并且可以指定进程的个数。

APPEND
让数据库以直接加载的方式(direct load)将数据加载入库。这个提示不会检查当前是否有插入所需要的块空间，相反它会直接
将数据添加到新块中。这样会浪费空间，但可以提高插入的性能。需要注意的是，数据将被存储在HWM之上的位置。

APPEND_VALUES
在11.2中，Oracle新增了APPEND_VALUES提示，使得INSERT INTO VALUES语句也可以使用直接路径插入。

CACHE
在全表扫描之后，数据块将留在LRU列表的最活跃端。如果设置表的CACHE属性，它的作用和HINT一样。这个提示会将全表
扫描全部缓存到内存中。如果表很大，会占用大量内存。因此适用于用户经常访问的较小的表。

NOCACHE
引导优化器将通过全表扫描方式获取的数据块缓存在LRU列表的最后位置，这样可以让数据库实例缓存中的这些数据块被优
先清除。这是优化器在Buffer Cache中管理数据块的默认方法(仅针对全表扫描)。

QB_NAME
使用该提示为查询语句块命名，在其他查询语句块可以直接使用该查询语句块的名称。

DRIVING_SITE
这个提示在分布式数据库操作中有用。指定表是处理连接所在的位置。可以限制通过网络处理的信息量。此外，还可以建立
远程表的本地视图来限制从远程站点检索的行。本地视图应该有where子句，从而视图可以在将行发送回本地数据库之前限制
从远程数据库返回的行。

DYNAMIC_SAMPLING
提示SQL执行时动态采样的级别。这个级别为0~10，它将覆盖系统默认的动态采样级别。等级越高，所获得统计信息的准确
率越高。该提示的功能就是为了确保将动态采样原理应用在单个SQL中。

AND_EQUAL
这个提示会使优化器合并表上的多个索引，而不是选择其中最好的索引(这是INDEX提示的用途)。这个提示与前面的
INDEX_JOIN提示有区别，以此指定的合并索引随后需访问表，而INDEX_JOIN提示则只需访问索引。如果发现需经常用到这
个提示，可能需要删除这些单个索引而改用一个组合索引。需要查询条件里面包括所有索引列，然后取得每个索引中得到的
rowid列表。然后对这些对象做merge join，过滤出相同的rowid后再去表中获取数据或者直接从索引中获得数据。在10g中，
and_equal已经废弃了，只能通过hint才能生效。

CARDINALITY
向优化器提供对某个查询语句的整体或部分的预测基数值，并通过参考该基数值来为查询语句制定执行计划。如果在该提示
中没有指定表的名称，则该基数值将被视为从该查询语句所获得的最终结果行数。
四、Hint使用示例
下面通过一个例子说明一下提示的使用及在什么情况下提示会被忽略。
 (1).构建表
(2).使用INDEX提示
*在某些情况下，如果CBO认为Hint会导致错误结果，那么Hint则会忽略。该例子中因为ID字段可能为空，而索引是保存空值
的，因此count(*)使用索引将导致错误的结果，故而使用了全表扫描，忽略了Hint。
(3).使用INDEX提示（非空字段）
*ID字段不可为空，因此COUNT可用索引扫描的方式处理，Hint生效了。

4、统计信息的准确性：
1）收集和更新表的统计信息，以便优化器可以更好地选择执行计划。
2）使用ANALYZE命令或DBMS_STATS包来收集和更新统计信息。

5、使用分区表：
1）对大型表进行分区，可以提高查询效率，并且简化数据维护操作。
2）分区表可以根据某个列的值将数据划分为多个子表，从而减少查询范围。

6、使用临时表和内存表（这里其实内存表更快，千万级大概快20%，但是会大量占用内存影响其他程序的运行）：
1）对于复杂的查询，可以使用临时表或内存表来暂存中间结果，以加快查询速度。
2）临时表和内存表不会写入磁盘（这个不一定，得看具体内存大小和查到得表大小），因此访问速度更快。

7、批量提交和绑定变量：
1）对于大批量的插入、更新或删除操作，使用批量提交（Bulk Insert）可以显著提高性能。
2）、使用绑定变量而不是直接拼接SQL语句，可以减少SQL解析的开销，提高查询效率。

8、使用合适的并行化策略：
1）、对于大型查询或涉及大量数据的操作，可以使用并行执行（Parallel Execution）来加速查询。
2）、通过设置适当的并行度，可以充分利用服务器的多核处理能力。

1、什么时候使用并行?
常见的场景有：
a）
    普通SQL最常见的情况就是大表的全表扫描，还有就是大的索引的快速全扫描（注意，index fast full scan可以使用并行，
    index full scan 不能使用并行）。
    需要纠正一个误区：SQL执行慢就可以通过使用并行或是增加并行来提高速度。
    正解：并行能否发挥作用要看SQL的具体执行计划，比如标量子查询或是DB link，增大并行带来的性能提升是微乎其微
    的！
    多大的表算大表？
    至少要百万级以上记录的表吧。如果几亿甚至十几亿记录数的表全表扫描不使用并行，SQL的执行时间会相当长，特别是
    表在SQL执行的过程中如果还有其他session的DML操作的时候。
    OLTP系统的正常事务一般不会使用大表全扫描的执行计划，如果有一些统计分析的业务，建议在系统资源相对空闲的时候
    开启并行。
b）
    用create table As Select创建一张大表，如
    create table test parallel 16 as select .... from t1,t2 where .....;
    alter table test noparallel;
c)
    创建或重建索引
    create index idx_test on table_A(name) parallel 8;
    alter index idx_test noparallel;
d)
    大表收集统计信息，可以设置并行，如degree=>8
    其他不常见的操作还有表压缩等，一些比较耗时的分区操作也可以查查语法，看看是否支持并行操作。

2、并行度的选择
    一般使用2的幂作为并行度，如2、4、8、16等，正常情况并行度不要设置太高，建议最多不要超过32。当然，特殊情况特
    殊对待，强悍的系统（比如exadata），如果需要非常高的响应速度，并行度再多个几倍也不是问题。并行高的时候并发就
    要减少，否则可能会耗光并行资源。

3、并行hint的写法
    通常我们都会使用hint在SQL级别设置并行，一般不在表上和索引上设置并行度，所以我们上面并行创建表和索引的例子，
    后面都伴随着一个noparallel，如果在创建表或索引时使用了并行，要把它改成noparallel或parallel 1 ：
    alter table/index table_name/index_name noparallel/parallel 1;
Hint的写法在10g和11g+有很大差别，11g+就方便很多。
10g 及以下：
    每个需要并行的表都要指定并行，如 /*+ parallel(a 4) parallel(b 4) */  ，如果SQL涉及的表较多，那么hint会比较长；如果内
    联视图较多，经常会出来遗漏的情况。如果某个表没有指定并行，那么就只能串行，如果某个大表忘了写，就会出现性能瓶
    颈。
11g+：
    只要在整个sql的任何一个关键字（select、update、insert、delete、merge）后面出现一次parallel(n)，那么整个SQL相关
    的表，都会使用并行，在写法上非常的简洁，而且不会遗漏。现在新开发的应用都应该是11gR2以上了，忘了10g的写法吧。
    
 注意：
/*+ parallel */ 或 /*+ parallel 8 */是错误的并行hint写法，这些不正确的写法会导致SQL使用一个比较大的并行度，消耗大量的
系统资源。

4、并行DML
    DML有4种，INSERT、DELETE、UPDATE还有MERGE，如：
    insert /*+ parallel(4) */ into t1 select .... from ....;
这个写法将会在select部分使用并行度为4的并行，DML部分的并行并没有真正的启用，DML的并行默认是关闭的，如果需要
使用，必须在session级别通过下面命令开启：
    alter session enable parallel dml;--推荐写法
    或者alter session force parallel dml parallel n; --用force的语法，可以使下面的dml即使不用parallel
    的hint，也会使用并行度
    
    为n的并行。
执行这个命令后，才真正开启了DML的并行。
注意：
开启了DML的并行后，接下来的DML语句将会产生一个表锁，在commit之前，当前session 不能对该表做查询和dml操作，其
他session也不能对该表做DML操作。
所以建议，并行dml语句，应该在语句执行后立即commit; 然后再关闭并行dml，完整的过程应该是：
alter session enable parallel dml;
your dml；
commit；
alter session disable parallel dml;
或者alter session force parallel dml parallel 1;

补充：
    parallel 的hint并不能保证sql一定会使用并行，如果优化器认为sql使用索引更高效，可能会使用索引而不使用并行。如果要
    确保SQL使用并行，有时可能要结合full 的hint，这种情况不多见。

除了以上的八个秘诀，还可以通过调整数据库参数、优化硬件配置、使用数据库缓存和内存管理等方式进一步提升查询性能。然而，需要根据具体情况和业务需求进行优化，避免过度优化导致其他问题。

在进行查询优化时，建议使用数据库性能监控工具进行实时监测和性能分析，以便及时发现潜在的问题并采取相应的优化措施。此外，定期进行数据库维护和性能调优也是必要的，以保证数据库的高效稳定运行。

总结起来，Oracle数据库查询优化需要综合考虑索引的使用、查询语句的编写、统计信息的准确性、分区表的使用、临时表和内存表的利用、批量提交和绑定变量、并行化策略等方面。通过合理的优化手段，可以显著提升数据库查询效率和性能，满足业务需求。

sql优化第三步：搞懂Oracle中的执行计划

一：什么是Oracle执行计划？
执行计划是一条查询语句在Oracle中的执行过程或访问路径的描述

二：怎样查看Oracle执行计划？
因为我一直用的PLSQL远程连接的公司数据库，所以这里以PLSQL为例：
①：配置执行计划需要显示的项：
工具 —> 首选项 —> 窗口类型 —> 计划窗口 —> 根据需要配置要显示在执行计划中的列

执行计划配置
执行计划的常用列字段解释：
基数（Rows）：Oracle估计的当前操作的返回结果集行数
字节（Bytes）：执行该步骤后返回的字节数
耗费（COST）、CPU耗费：Oracle估计的该步骤的执行成本，用于说明SQL执行的代价，理论上越小越好（该值可能与实际有出入）
时间（Time）：Oracle估计的当前操作所需的时间
②：打开执行计划：
在SQL窗口执行完一条select语句后按 F5 即可查看刚刚执行的这条查询语句的执行计划
这种方法获得得数据不一定是准确的，想要获取准确的数据应该使用：select * from table(dbms_xplan.display_cursor(null,null));的方式来获取执行计划
或者查询V$SQL_PLAN 视图也可以
注：在PLSQL中使用SQL命令查看执行计划的话，某些SQL*PLUS命令PLSQL无法支持，比如SET AUTOTRACE ON

术语：
驱动表（Driving Table）：
表连接时首先存取的表，又称外层表（Outer Table），这个概念用于 NESTED LOOPS（嵌套循环） 与 HASH JOIN（哈希
连接）中；
如果驱动表返回较多的行数据，则对所有的后续操作有负面影响，故一般选择小表（应用Where限制条件后返回较少行数的
表）作为驱动表。

匹配表（Probed Table）：
又称为内层表（Inner Table），从驱动表获取一行具体数据后，会到该表中寻找符合连接条件的行。故该表一般为大表（应
用Where限制条件后返回较多行数的表）。

三：看懂Oracle执行计划

①：执行顺序：
根据Operation缩进来判断，缩进最多的最先执行；（缩进相同时，最上面的最先执行）
例：上图中 INDEX RANGE SCAN 和 INDEX UNIQUE SCAN 两个动作缩进最多，最上面的 INDEX RANGE SCAN 先执行；
同一级如果某个动作没有子ID就最先执行
同一级的动作执行时遵循最上最右先执行的原则
例：上图中 TABLE ACCESS BY GLOBAL INDEX ROWID 和 TABLE ACCESS BY INDEX ROWID 两个动作缩进都在同一级，则位于上面的 TABLE ACCESS BY GLOBAL INDEX ROWID 这个动作先执行；这个动作又包含一个子动作 INDEX RANGE SCAN，则位于右边的子动作 INDEX RANGE SCAN 先执行；
图示中的SQL执行顺序即为：
INDEX RANGE SCAN —> TABLE ACCESS BY GLOBAL INDEX ROWID —> INDEX UNIQUE SCAN —> TABLE ACCESS BY INDEX ROWID —> NESTED LOOPS OUTER —> SORT GROUP BY —> SELECT STATEMENT, GOAL = ALL_ROWS
（ 注：PLSQL提供了查看执行顺序的功能按钮(上图中的红框部分) ）

②：对图中动作的一些说明：

1. 上图中 TABLE ACCESS BY … 即描述的是该动作执行时表访问（或者说Oracle访问数据）的方式；
   表访问的几种方式：（非全部）
   TABLE ACCESS FULL（全表扫描）
   TABLE ACCESS BY ROWID（通过ROWID的表存取）
   TABLE ACCESS BY INDEX SCAN（索引扫描）
   （1） TABLE ACCESS FULL（全表扫描）：
   Oracle会读取表中所有的行，并检查每一行是否满足SQL语句中的 Where 限制条件；
   全表扫描时可以使用多块读（即一次I/O读取多块数据块）操作，提升吞吐量；
   使用建议：数据量太大的表不建议使用全表扫描，除非本身需要取出的数据较多，占到表数据总量的 5% ~ 10% 或以上

全表扫描将读取高水位（High Warter Mark，HWM）之下的所有数据块，所有行都要经WHERE子句过滤来判断是否满足条
件。当Oracle执行全表扫描时，会按顺序读取每个块且只读一次，如果能够一次读取多个块，那么可以有效地提高效率，初
始化参数DB_FILE_MULTIBLOCK_READ_COUNT用来设置在一次I/O中可以读取多少个数据块。通常应该避免全表扫描，但
是在检索大量数据时全表扫描优于索引扫描，这正是因为全表扫描可以在一次I/O中读取多个块，从而减少了I/O的次数。在使
用全表扫描的同时也可以使用并行来提高扫描的速度。全表扫描的Hint为：FULL(T)。

CBO优化器在以下几种情况下会选择全表扫描：
①　无合适的索引。
②　检索表中绝大多数的数据。
③　表非常小。例如，表中的块小于DB_FILE_MULTIBLOCK_READ_COUNT，只需一次I/O。如果这样的表被频繁使用，应
该执行“ALTER TABLE TABLE_NAME STORAGE(BUFFER_POOL KEEP);”将表保存在内存中。
④　高并行度。如果在表级设置了较高的并行度，例如“ALTER TABLE T_NAME PARALLEL 4;”，那么通常会选择全表扫描。
通常建议在语句级用HINT来实现并行，例如/*+ FULL(T_NAME) PARALLEL(T_NAME 4)*/。
⑤　太旧的统计数据。如果表没有进行过分析或很久没有再次分析，那么CBO可能会错误的认为表含有及少的数据块。
⑥　在语句中嵌入了全表扫描的Hint。
⑦　WHERE子句的索引列上只存在极少数不同的值。

需要注意的是，由于全表扫描是扫描高水位以下的所有数据块，所以即使使用DELETE语句清空了目标表中的所有数据，高水
位线还是会在原来的位置，这意味着对该表的全表扫描操作所耗费的时间与删除之前相比并不会有明显的改观。

（2） TABLE ACCESS BY ROWID（通过ROWID的表存取） :
先说一下什么是ROWID？

ROWID是由Oracle自动加在表中每行最后的一列伪列，既然是伪列，就说明表中并不会物理存储ROWID的值；
你可以像使用其它列一样使用它，只是不能对该列的值进行增、删、改操作；
一旦一行数据插入后，则其对应的ROWID在该行的生命周期内是唯一的，即使发生行迁移，该行的ROWID值也不变。
让我们再回到 TABLE ACCESS BY ROWID 来：
行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID可以快速定位到目标数据上，这也是Oracle中存取单行数据最快的方法；

ROWID表示行在数据块中的具体位置，ROWID是查找具体行的最快方式。可以在WHERE子句中写入ROWID，但是不推荐
这么做。通常都是通过索引来获得ROWID，但如果被检索的行都包含在索引中时，那么直接访问索引就能得到所需的数据则
不会使用ROWID。对Oracle中的堆表而言，可以通过Oracle内置的ROWID伪列得到对应行记录所在的ROWID的值，然后通
过DBMS_ROWID包中的相关方法（DBMS_ROWID.ROWID_RELATIVE_FNO获取文件号、
DBMS_ROWID.ROWID_BLOCK_NUMBER获取块号和DBMS_ROWID.ROWID_ROW_NUMBER获取行号）将上述ROWID
伪列的值翻译成对应数据行的实际物理存储地址。

需要注意的是，在通过ROWID直接访问数据的情况下，执行计划中常常是“TABLE ACCESS BY USER ROWID”，而非索引回
表读的操作（TABLE ACCESS BY INDEX ROWID）。

回表：
当对一个列创建索引之后，索引会包含该列的键值以及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫
回表。回表一般是单块读，回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描了，应该直接走全
表扫描。

在进行SQL优化的时候，一定要注意回表次数！特别是要注意回表的物理I/O次数！

#####Oracle12c新特性-Table access by index rowid batched 的解释#########
Table access by index rowid batched（表访问索引行批处理）是一种Oracle数据库查询执行计划中的操作，表示在执行查询
时，通过索引表的行ID来访问数据表，并且可以一次获取多个行的数据。
简单来说，当一条查询SQL语句包含一个WHERE条件时，数据库会通过这个条件来筛选满足的所有数据。在使用索引表查找
行的行ID后，如果需要获取行的具体数据（例如SELECT查询需要获取某些列的数据），就需要通过访问数据表来实现。
Table access by index rowid batched就是通过在访问索引表时一次性获取多个行的行ID，并且将它们按照一定批次分组，然
后批量访问这些行所在的数据表的方式来优化查询执行效率的。

（3） TABLE ACCESS BY INDEX SCAN（索引扫描）：
在索引块中，既存储每个索引的键值，也存储具有该键值的行的ROWID。
一个数字列上建索引后该索引可能的概念结构如下图：

所以索引扫描其实分为两步：
Ⅰ：扫描索引得到对应的ROWID
Ⅱ：通过ROWID定位到具体的行读取数据

（1）索引唯一扫描（INDEX UNIQUE SCAN）
索引唯一扫描是针对唯一性索引（UNIQUE INDEX）的扫描，它仅仅适用于WHERE条件里是等值查询的目标SQL。因为扫
描的对象是唯一性索引，所以索引唯一性扫描的结果至多只会返回一条记录。对于组合唯一索引而言，WHERE条件需要列出
所有的索引列才能使用索引唯一扫描。

（2）索引范围扫描（INDEX RANGE SCAN）
使用索引范围扫描的3种情况：
①在唯一索引列上使用了范围操作符（>、<、<>、>=、<=、BETWEEN）。
②在组合索引上，只使用部分列进行查询，导致查询出多行。
③在非唯一索引列上进行的任何查询。

（3）索引全扫描（INDEX FULL SCAN）
索引全扫描需要扫描目标索引所有叶子块的所有索引行。这里需要注意的是，索引全扫描需要扫描目标索引的所有叶子块，
但这并不意味着需要扫描该索引的所有分支块。在默认情况下，Oracle在做索引全扫描时只需要通过访问必要的分支块定位
到位于该索引最左边的叶子块的第一行索引行，就可以利用该索引叶子块之间的双向指针链表，从左至右依次顺序扫描该索
引所有叶子块的所有索引行了。索引全扫描的执行结果是有序的，并且是按照该索引的索引键值列来排序，这也意味着走索
引全扫描能够既达到排序的效果，又同时避免了对该索引的索引键值列的真正排序操作默认情况下，索引全扫描的扫描结果
的有序性就决定了索引全扫描是不能够并行执行的，并且通常情况下索引全扫描使用的是单块读。通常情况下，索引全扫描
是不需要回表的，所以索引全扫描适用于目标SQL的查询列全部是目标索引的索引键值列的情形。默认情况下，索引全扫描
的扫描结果的有序性就决定了索引全扫描是不能够并行执行的，并且通常情况下索引全扫描使用的是单块读。通常情况下，
索引全扫描是不需要回表的，所以索引全扫描适用于目标SQL的查询列全部是目标索引的索引键值列的情形。Oracle中能做
索引全扫描的前提条件是目标索引至少有一个索引键值列的属性是NOT NULL。

（4）索引快速全扫描（INDEX FAST FULL SCAN）
和索引全扫描一样，索引快速全扫描也需要扫描目标索引所有叶子块的所有索引行。索引快速全扫描的Hint为INDEX_FFS。
索引快速全扫描与索引全扫描相比有如下三点区别：
①　索引快速全扫描只适用于CBO，而索引全扫描既可以用于CBO也可以用于RBO。
②　索引快速全扫描可以使用多块读，也可以并行执行。
③　索引快速全扫描的执行结果不一定是有序的。这是因为索引快速全扫描时Oracle是根据索引行在磁盘上的物理存储顺序
来扫描，而不是根据索引行的逻辑顺序来扫描的，所以扫描结果才不一定有序（对于单个索引叶子块中的索引行而言，其物
理存储顺序和逻辑存储顺序一致；但对于物理存储位置相邻的索引叶子块而言，块与块之间索引行的物理存储顺序则不一定
在逻辑上有序）。

（5）索引跳跃扫描（INDEX SKIP SCAN）
索引跳跃扫描发生在多个列建立的复合索引上，如果SQL中谓词条件只包含索引中的部分列，并且这些列不包含建立索引时
的第一列（前导列），就可能发生索引跳跃扫描。索引跳跃扫描仅仅适用于那些目标索引前导列的DISTINCT值数量较少、后
续非前导列的可选择性又非常好的情形，因为索引跳跃扫描的执行效率一定会随着目标索引前导列的DISTINCT值数量的递增
而递减。可以通过“ALTER SYSTEM SET "_OPTIMIZER_SKIP_SCAN_ENABLED" = FALSE SCOPE=SPFILE;”来禁用索引
跳跃扫描。索引跳跃扫描的Hint为INDEX_SS。

（6）Index Join Scans 索引关联扫描
索引联接扫描是多个索引的哈希联接，它们一起返回查询请求的所有列。数据库不需要访问表，因为所有数据都是从索引中
检索的。
在以下情况下，优化器将考虑使用索引联接：
多个索引的哈希联接检索查询所请求的所有数据，而无需访问表。
从表中检索行的成本比不从表中检索行而读取索引要高。索引联接通常很昂贵。例如，在扫描两个索引并将它们结合在一起
时，选择最有选择性的索引然后探查表的成本通常较低。
也可以使用提示指定索引连接。 INDEX_JOIN(table_name)
  在索引联接扫描中，始终避免表访问。例如，在单个表上联接两个索引的过程如下：
		扫描第一个索引以检索行标识。
		扫描第二个索引以检索行ID。
		通过rowid执行哈希联接以获取行。

如果还有不懂得执行计划（比如table access by global index rowid这个中的global 是什么意思【其实是表空间建立后和索引关联导致的:局部索引和全局索引】)可以看下官方调优文档