ORACLE10g柱状图信息对执行计划的影响

我们知道,在Oracle Database 10g中,Oracle优化器的缺省模式由以前的CHOOSE，更改为现在的ALL_ROWS.

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

查看NAME列数据分布情况:

SQL> select name,count(*) from test group by name;

NAME                   COUNT(*)
-------------------- ----------
msptest                       1
tsptest                    1034

在TEST表NAME列上有一个索引:

SQL> select INDEX_NAME,COLUMN_NAME from user_ind_columns where table_name='TEST';

INDEX_NAME                     COLUMN_NAME
------------------------------ --------------------------------
TT                             NAME

没有做表分析的情况,我们看一下执行计划:
SQL> set autotrace traceonly
SQL> select * from test where name='msptest';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=4 Bytes=84)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=1 Card=4 Byt
          es=84)

   2    1     INDEX (RANGE SCAN) OF 'TT' (NON-UNIQUE) (Cost=1 Card=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        564  bytes sent via SQL*Net to client
        651  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select * from test where name='tsptest';

1034 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=4 Bytes=84)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=1 Card=4 Byt
          es=84)

   2    1     INDEX (RANGE SCAN) OF 'TT' (NON-UNIQUE) (Cost=1 Card=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        154  consistent gets
          0  physical reads
          0  redo size
      25871  bytes sent via SQL*Net to client
       1399  bytes received via SQL*Net from client
         70  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1034  rows processed

NAME列上tsptest总共有1034条记录,走全表的效率要优于索引扫描,但这里却走了索引扫描,显然优化器做出了错误的选择.   

只分析一下表,试试看:
SQL> analyze table test compute statistics;

Table analyzed.

SQL>  select * from test where name='msptest';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=518 Bytes=7
          252)

   1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=518 Bytes=7252)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        564  bytes sent via SQL*Net to client
        651  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

只需返回一条数据，但做了全表扫描，这是不合理的执行计划。因为，它只是知道name列有两个不同的值，但oracle不知道每个不同的name分别有多少记录，oracle默认为这些数据的分布是完全均匀的，所以，当用name作条件时，oracle会认为会返回总记录的二分之一。

SQL> select * from test where name='tsptest';

1034 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=518 Bytes=7
          252)

   1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=518 Bytes=7252)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         82  consistent gets
          0  physical reads
          0  redo size
      25871  bytes sent via SQL*Net to client
       1399  bytes received via SQL*Net from client
         70  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1034  rows processed
 

删除统计信息,对表TEST生成柱状图后在做同样的查询:

SQL> analyze table test delete statistics;

Table analyzed.

SQL> analyze table test compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> select * from test where name='msptest';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=16)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
          es=16)

   2    1     INDEX (RANGE SCAN) OF 'TT' (NON-UNIQUE) (Cost=1 Card=1)

Statistics
----------------------------------------------------------
         22  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        564  bytes sent via SQL*Net to client
        651  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select * from test where name='tsptest';

1034 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1034 Bytes=
          16544)

   1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=1034 Bytes=1654
          4)

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         82  consistent gets
          0  physical reads
          0  redo size
      25871  bytes sent via SQL*Net to client
       1399  bytes received via SQL*Net from client
         70  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1034  rows processed

 可见，生成了柱状图后，oracle会根据数据的实际分布情况选择合适的执行计划，提高性能。