SQL优化（1）——全表扫描与索引测试

测试目的：初步测试CBO与RBO，以及全表扫描比索引快的情况。

 

1. 创建测试环境

 

SQL> select * from v$version;

 

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

 

SQL> create table test1 as select * from dba_objects where rownum < 1001;

 

表已创建。

 

SQL> insert into test1 select * from test1;

 

已创建1000行。

 

SQL> /

 

已创建2000行。

 

...

 

SQL> /

 

已创建4096000行。

 

SQL> update test1 set object_id = 3 where rownum < 4096000;

 

已更新4095999行。

 

SQL> commit;

 

提交完成。

 

2. 比较对于不同的数据分布，CBO与RBO执行计划的不同

 

2.1 测试CBO：自动根据统计数据选用不同的执行计划

 

-- 1. 因为object_id值为1的数据量占的比例很小，所以当object_id为1时，CBO选则使用索引：

SQL> explain plan for

  2  select count(object_name) from test1 where object_id=1;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 4180766814

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

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

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

|   0 | SELECT STATEMENT             |                     |     1 |    79 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE              |                     |     1 |    79 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| TEST1               |     1 |    79 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | IND_TEST1_OBJECT_ID |     1 |       |     2   (0)| 00:00:01 |

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

 

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

 

   3 - access("OBJECT_ID"=1)

 

Note

-----

   - dynamic sampling used for this statement

 

已选择19行。

 

-- 2. 因为object_id值为3的数据量占1半的比例，所以当object_id为3时，CBO选择使用全表扫描：

SQL> explain plan for

  2  select count(object_name) from test1 where object_id=3;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3896847026

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

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

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

|   0 | SELECT STATEMENT   |       |     1 |    79 | 21846   (3)| 00:04:23 |

|   1 |  SORT AGGREGATE    |       |     1 |    79 |            |          |

|*  2 |   TABLE ACCESS FULL| TEST1 |  3662K|   275M| 21846   (3)| 00:04:23 |

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

 

Predicate Information (identified by operation id):

 

PLAN_TABLE_OUTPUT

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

 

   2 - filter("OBJECT_ID"=3)

 

Note

-----

   - dynamic sampling used for this statement

 

已选择18行。

 

2.2 测试RBO：根据规则直接使用索引，而不考虑实际的数据。

 

-- 1. 当object_id值为1时，使用索引，此时是一个不错的执行计划：

SQL> explain plan for

  2  select /*+rule*/ count(object_name) from test1 where object_id=1;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 4180766814

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

| Id  | Operation                    | Name                |

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

|   0 | SELECT STATEMENT             |                     |

|   1 |  SORT AGGREGATE              |                     |

|   2 |   TABLE ACCESS BY INDEX ROWID| TEST1               |

|*  3 |    INDEX RANGE SCAN          | IND_TEST1_OBJECT_ID |

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

 

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

 

   3 - access("OBJECT_ID"=1)

 

Note

-----

   - rule based optimizer used (consider using cbo)

 

已选择19行。

 

-- 2. 当object_id值为3时，对于RBO，仍然会使用索引，不会考虑数据的分布：

SQL> explain plan for

  2  select /*+rule*/ count(object_name) from test1 where object_id=3;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 4180766814

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

| Id  | Operation                    | Name                |

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

|   0 | SELECT STATEMENT             |                     |

|   1 |  SORT AGGREGATE              |                     |

|   2 |   TABLE ACCESS BY INDEX ROWID| TEST1               |

|*  3 |    INDEX RANGE SCAN          | IND_TEST1_OBJECT_ID |

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

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

 

   3 - access("OBJECT_ID"=3)

 

Note

-----

   - rule based optimizer used (consider using cbo)

 

已选择19行。

 

3. 比较当object_id为3时，CBO的全表扫描与RBO的使用索引的执行时间：

 

-- 1. CBO：执行时间不到11秒

SQL> set timing on

SQL> select count(object_name) from test1 where object_id=3;

 

COUNT(OBJECT_NAME)

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

           4100095

 

已用时间:  00: 00: 10.60

 

SQL> select count(object_name) from test1 where object_id=3;

 

COUNT(OBJECT_NAME)

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

           4100095

 

已用时间:  00: 00: 10.95

 

SQL> select count(object_name) from test1 where object_id=3;

 

COUNT(OBJECT_NAME)

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

           4100095

 

已用时间:  00: 00: 10.76

 

SQL> select count(object_name) from test1 where object_id=3;

 

COUNT(OBJECT_NAME)

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

           4100095

 

已用时间:  00: 00: 10.51

 

-- 2. RBO：执行时间在18秒左右

SQL> select /*+rule*/ count(object_name) from test1 where object_id=3;

 

COUNT(OBJECT_NAME)

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

           4100095

 

已用时间:  00: 00: 16.75

 

SQL> select /*+rule*/ count(object_name) from test1 where object_id=3;

 

COUNT(OBJECT_NAME)

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

           4100095

 

已用时间:  00: 00: 17.00

 

SQL> select /*+rule*/ count(object_name) from test1 where object_id=3;

 

COUNT(OBJECT_NAME)

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

           4100095

 

已用时间:  00: 00: 18.45

 

SQL> select /*+rule*/ count(object_name) from test1 where object_id=3;

 

COUNT(OBJECT_NAME)

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

           4100095

 

已用时间:  00: 00: 18.50

 

4. 结论

 

1）CBO会自动根据统计数据来选择它认为更优化的执行计划，而RBO只是根据既定规则选择相应的执行计划。

2）索引并不是加速系统性能的开关，有时候使用全表扫描要比使用索引更快。

 

5. 一点补充

 

当可以直接在索引中查询出结果集时，Oracle也不会再执行全表扫描的。比如在上面的测试中，当object_id=3时，统计count(object_name)，Oracle会使用全表扫描，但如果统计count(object_id)或count(*)，Oracle就直接可以在索引中完成操作了。再看一下此时的执行计划：

 

SQL> explain plan for

  2  select count(object_id) from test1 where object_id=3;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1676651115

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

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

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

|   0 | SELECT STATEMENT      |                     |     1 |    13 |  7155   (4)| 00:01:26 |

|   1 |  SORT AGGREGATE       |                     |     1 |    13 |            |          |

|*  2 |   INDEX FAST FULL SCAN| IND_TEST1_OBJECT_ID |  3662K|    45M|  7155   (4)| 00:01:26 |

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

Predicate Information (identified by operation id):

 

PLAN_TABLE_OUTPUT

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

 

   2 - filter("OBJECT_ID"=3)

 

Note

-----

   - dynamic sampling used for this statement

 

已选择18行。

 

 

SQL> explain plan for

  2  select count(*) from test1 where object_id=3;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1676651115

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

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

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

|   0 | SELECT STATEMENT      |                     |     1 |    13 |  7155   (4)| 00:01:26 |

|   1 |  SORT AGGREGATE       |                     |     1 |    13 |            |          |

|*  2 |   INDEX FAST FULL SCAN| IND_TEST1_OBJECT_ID |  3662K|    45M|  7155   (4)| 00:01:26 |

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

Predicate Information (identified by operation id):

 

PLAN_TABLE_OUTPUT

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

 

   2 - filter("OBJECT_ID"=3)

 

Note

-----

   - dynamic sampling used for this statement

 

已选择18行。