首页 > 代码库 > 1.执行计划探究(一)

1.执行计划探究(一)

/*************************************************

主题:执行计划探究(一)

 

  看懂执行计划,需要了解的基础

*************************************************/

=====================================================================

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)

    看见这个名称,不用多说什么了。