oracle全表扫过程讲解,Oracle 全表扫描及其执行计划

--先来做几个实验

a、演示表上的相关信息

scott@ORA11G> @idx_info

Enter value for owner: scott

Enter value for table_name: big_table

Table Name                Index Name                CL_NAM    CL_POS Status   IDX_TYP         DSCD

------------------------- ------------------------- --------- ------ -------- --------------- ----

BIG_TABLE                 BIG_TABLE_PK              ID             1 VALID    NORMAL          ASC

scott@ORA11G> @idx_stat

Enter value for input_table_name: big_table

Enter value for owner: scott

AVG LEAF BLKS AVG DATA BLKS

BLEV IDX_NAME        LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS

---- -------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------

1 BIG_TABLE_PK          208     100000             1             1       1483 20130524 10:45:51        1515     100000

--数据库参数设置

scott@ORA11G> show parameter optimizer_index_

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

optimizer_index_caching              integer     0

optimizer_index_cost_adj             integer     100

scott@ORA11G> show parameter optimizer_mode

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

optimizer_mode                       string      ALL_ROWS

b、查询返回20%数据行的情形

scott@ORA11G> alter system flush buffer_cache;

scott@ORA11G> select sum(object_id),avg(object_id) from big_table where id between 20000 and 40000;

Execution Plan

----------------------------------------------------------

Plan hash value: 3098837282                             -- 执行计划中，使用了索引范围扫描

---------------------------------------------------------------------------------------------

| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |              |     1 |    18 |   341   (0)| 00:00:05 |

|   1 |  SORT AGGREGATE              |              |     1 |    18 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| BIG_TABLE    | 20046 |   352K|   341   (0)| 00:00:05 |

|*  3 |    INDEX RANGE SCAN          | BIG_TABLE_PK | 20046 |       |    43   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("ID">=20000 AND "ID"<=40000)

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

351  consistent gets

351  physical reads

0  redo size

427  bytes sent via SQL*Net to client

349  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

scott@ORA11G> alter system flush buffer_cache;

scott@ORA11G> select /*+ full(big_table) */ sum(object_id),avg(object_id) from big_table where id between 20000 and 40000;

Execution Plan

----------------------------------------------------------

Plan hash value: 599409829                ---- 使用了提示执行为全表扫描

--------------------------------------------------------------------------------

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |     1 |    18 |   413   (1)| 00:00:05 |

|   1 |  SORT AGGREGATE    |           |     1 |    18 |            |          |

|*  2 |   TABLE ACCESS FULL| BIG_TABLE | 20046 |   352K|   413   (1)| 00:00:05 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("ID"<=40000 AND "ID">=20000)

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

1486  consistent gets

1484  physical reads

0  redo size

427  bytes sent via SQL*Net to client

349  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

--注意对比上面两次操作中的consistent gets与physical reads

c、查询返回30%数据行的情形

scott@ORA11G> alter system flush buffer_cache;

scott@ORA11G> select sum(object_id),avg(object_id) from big_table where id between 20000 and 50000;

Execution Plan

----------------------------------------------------------

Plan hash value: 599409829             --->尽管返回数据的总行数为30%，而此时优化器使用了全表扫描

--------------------------------------------------------------------------------

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |     1 |    18 |   413   (1)| 00:00:05 |

|   1 |  SORT AGGREGATE    |           |     1 |    18 |            |          |

|*  2 |   TABLE ACCESS FULL| BIG_TABLE | 30012 |   527K|   413   (1)| 00:00:05 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("ID"<=50000 AND "ID">=20000)

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

1486  consistent gets

1484  physical reads

0  redo size

427  bytes sent via SQL*Net to client

349  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

--下面使用提示来强制优化器走索引扫描

scott@ORA11G> alter system flush buffer_cache;

scott@ORA11G> select /*+ index(big_table big_table_pk) */ sum(object_id),avg(object_id)

2  from big_table where id between 20000 and 50000;

Execution Plan

----------------------------------------------------------

Plan hash value: 3098837282

---------------------------------------------------------------------------------------------

| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |              |     1 |    18 |   511   (1)| 00:00:07 |

|   1 |  SORT AGGREGATE              |              |     1 |    18 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| BIG_TABLE    | 30012 |   527K|   511   (1)| 00:00:07 |

|*  3 |    INDEX RANGE SCAN          | BIG_TABLE_PK | 30012 |       |    64   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("ID">=20000 AND "ID"<=50000)

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

526  consistent gets

526  physical reads

0  redo size

427  bytes sent via SQL*Net to client

349  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

--注意观察每一次测试时所耗用的物理读与逻辑读

--从上面的测试可以看出，当表上所返回的数据行数接近于表上的30%时，Oracle 倾向于使用全表扫描

--而对于表上所返回的数据行数接近于表上的30%的情形，我们给与索引提示，此时比全表扫描更高效，即全表扫描是低效的

--笔者同时测试了数据返回总行数接近80%的情形以及创建了一个百万记录的进行对比测试

--大致结论，如果查询所返回的数据的总行数仅仅是表上数据的百分之八十以下，而使用了全表扫描，即可认为该全表扫描是低效的

--注：

--具体情况需要具体分析，如果你的表是千万级的，返回总数据的百分之零点几都会导致很大的差异

--其次，表上的索引应具有良好的聚簇因子，如不然，测试的结果可能有天壤之别

--最后，上面所描述的返回总行数应与执行结果返回的行数有差异，是指多少行参与了sum(object_id)