/*************************************************
主题：执行计划探究（一）
看懂执行计划，需要了解的基础
*************************************************/
=====================================================================
create table TEST6
(
id NUMBER(1) not null,
name VARCHAR2(10)
)
ID NAME
-- ----------
0 0
1 1
2 2
3 A
4 B
5 a
7
--
CREATE TABLE TEST7
(
ID NUMBER(1),
NAME VARCHAR(10) ,
AGE NUMBER(2)
);
INSERT INTO TEST7(ID,NAME)
SELECT * FROM TEST6 WHERE ID<4；
=====================================================================1.索引列的选择
比较一下列中唯一键的数量和表中的行数，就可以判断该列的可选择性。
如果该列的“唯一键的数量/表中的行数”的比值越接近1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。
SELECT COUNT(DISTINCT ID) FROM TEST6; --结果为1，比较符合预期的列
SELECT COUNT(DISTINCT NAME)/COUNT(1) FROM TEST6; --结果为0.8
2.如何查看执行计划
(1)使用PLSQL 的解释计划窗口
输入SQL后，按F8执行。
或者在SQL窗口，选择代码段，按F5。
(2)设置autotrace
|
序号 |
命令 |
解释 |
|
1 |
SET AUTOTRACE OFF |
此为默认值，即关闭Autotrace |
|
2 |
SET AUTOTRACE ON EXPLAIN |
只显示执行计划 |
|
3 |
SET AUTOTRACE ON STATISTICS |
只显示执行的统计信息 |
|
4 |
SET AUTOTRACE ON |
包含2,3两项内容 |
|
5 |
SET AUTOTRACE TRACEONLY |
与ON相似，但不显示语句的执行结果 |
--试验了一下，好像只能在SQL PLUS里面这样执行，PLSQL中要报错Cannot SET AUTOTRACE
使用了SYSDBA连接也同样报错。
(3)使用SQL查询
SQL> EXPLAIN PLAN FOR SELECT COUNT(DISTINCT ID)/COUNT(1) FROM TEST6;
Explained
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
--或者 select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 2141808149
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (34)| 00:00:01
| 1 | SORT AGGREGATE | | 1 | 26 | |
| 2 | VIEW | VW_DAG_0 | 7 | 182 | 3 (34)| 00:00:01
| 3 | HASH GROUP BY | | 7 | 91 | 3 (34)| 00:00:01
| 4 | TABLE ACCESS FULL| TEST6 | 7 | 91 | 2 (0)| 00:00:01
---------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
15 rows selected
（4）set timing ON 可以返回执行的时间
--下面是oracle访问数据的存取方法
3. 全表扫描（Full Table Scans， FTS）
（1）当查询结果占全表数据量比例较高时使用，比走索引快；
（2）希望全表扫描（如一个表FULL，一个表索引扫描）。
3. 通过ROWID的表存取（Table Access by ROWID或rowid lookup）
过ROWID来存取数据可以快速定位到目标数据上，是Oracle存取单行数据的最快方法。
5. 索引扫描
索引扫描可以由2步组成：
（1） 扫描索引得到对应的rowid值。
（2） 通过找到的rowid从表中读出具体的数据。
使用场景：
（1）返回行比较少（最好少于5%）；
（2）返回字段包含索引字段。
--当然，前提是表要建了索引，没索引的话(*^__^*)
CREATE INDEX IDX_TEST6 ON TEST6(ID);
6. 索引唯一扫描（index unique scan）
通过唯一索引查找一个数值经常返回单个ROWID.如果存在UNIQUE 或PRIMARY KEY 约束（它保证了语句只存取单行）的话，Oracle经常实现唯一性扫描。
CREATE UNIQUE INDEX IDX_TEST6 ON TEST6(ID);
7. 索引范围扫描（index range scan）
使用一个索引存取多行数据，在唯一索引上使用索引范围扫描的典型情况下是在谓词（where限制条件）中使用了范围操作符（如>、<、<>、>=、<=、between）
使用index rang scan的3种情况：
（a） 在唯一索引列上使用了range操作符（> < <> >= <= between）
（b） 在组合索引上，只使用部分列进行查询，导致查询出多行
（c） 对非唯一索引列上进行的任何查询。
8. 索引全扫描（index full scan）
9. 索引快速扫描（index fast full scan）
扫描索引中的所有的数据块，与 index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。
10. 排序 - - 合并连接（Sort Merge Join， SMJ）
1)首先生成TEST5需要的数据，然后对这些数据按照连接操作关联列（如A.ID）进行排序; 2）随后生成TEST6需要的数据，然后对这些数据按照与sort source1对应的连接操作关联列（如B.ID）进行排序; 3）最后两边已排序的行被放在一起执行合并操作，即将2个row source按照连接条件连接起来
11. 嵌套循环（Nested Loops， NL）
因为TEST5的数据量少，所以作为驱动表。
在NESTED LOOPS连接中，Oracle读取TEST5中的每一行，然后在TEST6中检查是否有匹配的行，所有被匹配的行都被放到结果集中，然后处理TEST5中的下一行。这个过程一直继续，直到TEST5中的所有行都被处理。这是从连接操作中可以得到第一个匹配行的最快的方法之一，这种类型的连接可以用在需要快速响应的语句中，以响应速度为主要目标。
12. 哈希连接（Hash Join， HJ）
13. 笛卡儿乘积（Cartesian Product）
看见这个名称，不用多说什么了。