试验表的Analyze，以及新建表是否Analyze，新建索引后是否立即能使用

一直以来，想弄明白新建的表，是否自动生成了统计信息，以便于对其执行查询时的执行计划的正确选择。今天，就通过一个简单的试验，来弄明白这个，并了解 Analyze后，到底生成了哪些统计信息。

 

SQL> create table test as select * from dba_objects;

Table created

SQL> set autot traceonly

SQL> select t.object_id, t.data_object_id, t.object_name, t.subobject_name
  from test t
 where t.owner = 'TEST';   2    3 

已选择4433行。

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  6790 |   835K|   173   (2)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| TEST |  6790 |   835K|   173   (2)| 00:00:03 |
--------------------------------------------------------------------------

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

   1 - filter("T"."OWNER"='TEST')

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
         85  recursive calls
          0  db block gets
       1118  consistent gets
          0  physical reads
          0  redo size
     201867  bytes sent via SQL*Net to client
       3714  bytes received via SQL*Net from client
        297  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       4433  rows processed

发现'dynamic sampling used for this statement' 的问题，表示执行计划使用的是表的动态采样数据，系统没有对Test表做过统计。再查询user_tables和dba_tables视图，记录信息，便于比较analyze后的信息，从而找到变化的信息。

 

下面，分析表，再重新查看其执行计划

SQL> ANALYZE TABLE test COMPUTE STATISTICS;

Table analyzed
SQL> select t.object_id, t.data_object_id, t.object_name, t.subobject_name
  from test t
 where t.owner = 'TEST';    2    3 

已选择4433行。

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2609 | 96533 |   173   (2)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| TEST |  2609 | 96533 |   173   (2)| 00:00:03 |
--------------------------------------------------------------------------

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

   1 - filter("T"."OWNER"='TEST')

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1049  consistent gets
          0  physical reads
          0  redo size
     201867  bytes sent via SQL*Net to client
       3714  bytes received via SQL*Net from client
        297  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       4433  rows processed

 

可以看到，这个时候执行计划就采用了前面Analyze的表分析数据，执行计划更优化了。下面，对比user_tables和dba_tables视图，看哪些数据项发生了变化，也就是分析表产生的结果有哪些了。

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS SAMPLE_SIZE LAST_ANALYZED
---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- ----------- -------------
                                                                                                                                            
     54786        775          121        868          0          97                         0                   0       54786 2011-1-17 16:

analyze产出变化得有以上项，未分析之前都是空得，分析之后都有了值，这些数值都是从系统视图sys.tab$ 得到。

 

通过以上试验，我们得出以下结论：

1、表create后，是不会自动产生统计信息的。

2、表analyze后，会产生哪些统计信息。

3、在表不是必须drop再create的情况下，尽量不要轻易的采用drop再create表的方式，这样不利于统计信息的采集和执行计划的优化。

 

在做一个实验，看新建索引后是否立刻使用：

1、create table test1 as select * from dba_objects;

2、create index idx_owner on test1(owner);

3、set autotrace traceonly;

4、select * from test1 where wner = 'SYS';

     查看执行计划是全表扫描。

5、analyze table test1 compute statistics;

6、  select * from test1 where wner = 'SYS';

     查看执行计划是走索引的。

 

结论：新建索引后没有统计分析，索引是不起作用的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20577218/viewspace-702313/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20577218/viewspace-702313/