在测试一个SQL的时候发现10g的统计信息和建表的方式还有关系。

看一个简单的例子，测试版本10.2.0.1：

SQL> ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 0;

会话已更改。

SQL> CREATE TABLE T1 (ID NUMBER PRIMARY KEY);

表已创建。

SQL> INSERT INTO T1 SELECT ROWNUM FROM DBA_TABLES;

已创建1563行。

SQL> COMMIT;

提交完成。

SQL> SET AUTOT ON
SQL> SELECT MAX(ID) MAX, MIN(ID) MIN FROM T1;

MAX MIN
---------- ----------
1563 1

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

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| T1 | 164 | 2132 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

SQL> CREATE TABLE T2 (ID PRIMARY KEY) AS SELECT ROWNUM FROM DBA_TABLES;

表已创建。

SQL> SELECT MAX(ID) MAX, MIN(ID) MIN FROM T2;

MAX MIN
---------- ----------
1563 1

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

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN| SYS_C0013827 | 818 | 10634 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

为了避免动态收集统计信息的影响，这里先将其关闭。

上面的表T1和T2中的数据完全相同，唯一的区别在于T1先建表和索引，然后插入数据。而T2在建表和索引的同时插入数据。

导致的结果就是二者的执行计划不一样。对T1执行全表扫描，而对T2执行索引扫描。从执行效果看显然T2的执行效果更好。

检查二者的统计信息发现：

SQL> SELECT TABLE_NAME, DISTINCT_KEYS, NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED
2 FROM USER_INDEXES WHERE TABLE_NAME IN ('T1', 'T2');

TABLE_NAME DISTINCT_KEYS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
---------------- ------------- ---------- ----------- -------------------
T1
T2 1563 1563 1563 2007-06-06 17:18:13

Oracle对于CREATE TABLE AS SELECT方式进行优化，在完成了创建表的过程后，收集了统计信息。