统计信息！

exec dbms_stats.gather_schema_stats(ownname=>'u1',options=>'gather',cascade=>true);             --收集schema下面所用对象的统计信息。
exec dbms_stats.gather_table_stats(ownname=>'u1',tabname=>'t',cascade=>true);     --收集指定表包括索引的统计信息。
select table_name,num_rows,avg_row_len,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from user_tables;     --查看统计信息。

首先创建一张表，里面id=1的记录只有一条，然后收集了统计信息。

SQL> create table t as select 1 id,o.* from dba_objects o where rownum = 1;

表已创建。

SQL> create index t_ind on t(id);

索引已创建。

SQL> select count(*) from t;

  COUNT(*)
----------
         1

SQL> select table_name,num_rows,avg_row_len,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from user_tables;

TABLE_NAME                       NUM_ROWS AVG_ROW_LEN TO_CHAR(LAST_ANALYZ
------------------------------ ---------- ----------- -------------------
T

SQL> exec dbms_stats.gather_table_stats(ownname=>'u1',tabname=>'t',cascade=>true);

PL/SQL 过程已成功完成。

SQL> select table_name,num_rows,avg_row_len,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from user_tables;

TABLE_NAME                       NUM_ROWS AVG_ROW_LEN TO_CHAR(LAST_ANALYZ
------------------------------ ---------- ----------- -------------------
T                                       1          73 2011-11-05 23:38:15

然后插入5万多条id=99的记录。没有再次收集统计信息。

SQL> insert into t select 99 id,o.* from dba_objects o;

已创建50293行。


SQL> commit;

提交完成。

SQL> select count(*) from t where id = 99;

  COUNT(*)
----------
     50293

查看统计信息还是旧的：

SQL> select table_name,num_rows,avg_row_len,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from user_tables;

TABLE_NAME                       NUM_ROWS AVG_ROW_LEN TO_CHAR(LAST_ANALYZ
------------------------------ ---------- ----------- -------------------
T                                       1          73 2011-11-05 23:38:15

根据谓词条件id=99查询，可以看见这个表中除了1条记录，其它的都是id=99，返回了5万多条记录。这里应该走全表扫描才对的，可是选择了索引。

SQL> select * from t where id = 99;

已选择50293行。


执行计划
----------------------------------------------------------
Plan hash value: 1376202287

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    73 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    73 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=99)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       7633  consistent gets
        126  physical reads
      11464  redo size
    2408070  bytes sent via SQL*Net to client
      37272  bytes received via SQL*Net from client
       3354  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50293  rows processed

重新收集统计信息，再次查询就对了，全部扫描！

SQL> set autotrace off;
SQL> exec dbms_stats.gather_table_stats(ownname=>'u1',tabname=>'t',cascade=>true);

PL/SQL 过程已成功完成。

SQL> select table_name,num_rows,avg_row_len,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from user_tables;

TABLE_NAME                       NUM_ROWS AVG_ROW_LEN TO_CHAR(LAST_ANALYZ
------------------------------ ---------- ----------- -------------------
T                                   50294          96 2011-11-05 23:45:09

SQL> set autotrace traceonly;
SQL> select * from t where id = 99;

已选择50293行。


执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 50289 |  4714K|   170   (3)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T    | 50289 |  4714K|   170   (3)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=99)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       7475  consistent gets
          0  physical reads
          0  redo size
    2408070  bytes sent via SQL*Net to client
      37272  bytes received via SQL*Net from client
       3354  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50293  rows processed

可以看出统计信息的重要性了吧，会影响一个sql的执行计划。