有时候不能太相信CBO啊。

wei-xh

谢谢 〇〇 ，已经按照您说的格式化了

magic007

我前面说的的确有误，UNION 操作去掉重复，是可能会导致结果集变小。

不知道你是怎么生成的测试表，按你的说法是根据DBA_OBJECTS生成的数据，但实际上OBJECT_NAME和OBJECT_ID具有相关性，你按OBJECT_NAME排序，与OBJECT_ID排序差别不大，除非系统中有很多的分区表或者很多SCHEMA下有相同的名字。在我自己的电脑上测试出来，按OBJECT_NAME排序的测试表，其clustering factor仅仅是blocks的2倍左右，远远小于行数。

如果你的数据没有错误，走了索引之后，逻辑读的确应该是很高才是。

wqhhust

to wei-xh.

oracle选择的Plan cost是5202   ，你加上hint后的cost是3540。 按理说oracle应该选择cost最低的Plan

wei-xh

按照如下方式建立的高聚簇的测试表：
SQL> create table test as select * from dba_objects order by object_name;

表已创建。

SQL> insert into test select * from dba_objects order by object_name;

已创建49841行。

SQL> /

连续执行16次。

SQL> update test set object_id=145 where object_id like '13%';

已更新19998行。

SQL>commit;

分析表：
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',
                                TABNAME    => 'TEST',
                                CASCADE    => TRUE,
                                METHOD_OPT => 'FOR  all columns SIZE auto');
END;


此时表记录数：
SQL> SELECT COUNT(*) FROM TEST;

  COUNT(*)
----------
    897138

聚簇索引值：
SQL> SELECT clustering_factor FROM User_Indexes WHERE index_name='IND_OBJECT_ID' ;

CLUSTERING_FACTOR
-----------------
           897120

两个值非常的接近。

[ 本帖最后由 wei-xh 于 2010-5-3 12:25 编辑 ]

magic007

我使用同样的方法生成TEST表：

SQL> SELECT COUNT(*) FROM TEST;

  COUNT(*)
----------
    803664
已选择 1 行。

SQL> SELECT clustering_factor FROM User_Indexes WHERE index_name='IND_OBJECT_ID' ;

     Cluster
      Factor
------------
     789,783

已选择 1 行。

SQL> set arraysize 1000
SQL> set autot traceonly
SQL> select * from test where object_id=145;

已选择17792行。


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1015 Card=18984 By
          tes=1765512)

   1    0   TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=1015 Card=1898
          4 Bytes=1765512)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      11145  consistent gets
          0  physical reads
          0  redo size
     874181  bytes sent via SQL*Net to client
        690  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      17792  rows processed

SQL> select /*+ index(test) */ * from test where object_id=145;

已选择17792行。


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=18715 Card=18984 B
          ytes=1765512)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (TABLE) (Cost=1871
          5 Card=18984 Bytes=1765512)

   2    1     INDEX (RANGE SCAN) OF 'IND_OBJECT_ID' (INDEX) (Cost=44 C
          ard=18984)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4000  consistent gets
          0  physical reads
          0  redo size
     874238  bytes sent via SQL*Net to client
        690  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      17792  rows processed

SQL> show parameter multiblock

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
db_file_multiblock_read_count        integer     16

这里使用索引的逻辑读是4000，仅仅是访问这一个表。与楼主的测试相差比较大。如果忽略CPU成本和缓存命中率，Oracle的选择是没有错的。
我的测试环境是Oracle 10.2.0.4 for Windows。


[ 本帖最后由 magic007 于 2010-5-3 18:59 编辑 ]

wei-xh

斑竹DBA手记里有几节写的很精彩，还希望以后能继续出书造福我们这些菜鸟。

magic007

多谢支持