这是CBO无法解决的问题么？

kangaroo76

SQL> set timing on
SQL> set autotrace traceonly
SQL> select  count(*) from test where a='eygle' and b=1;

已用时间:  00: 00: 00.05

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=149 Card=1 Bytes=13)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=149 Card=5
           Bytes=65)

   3    2       INDEX (RANGE SCAN) OF 'I_BC' (NON-UNIQUE) (Cost=3 Card
          =147)





Statistics
----------------------------------------------------------
        354  recursive calls
          0  db block gets
        160  consistent gets
        113  physical reads
          0  redo size
        376  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> select  count(*) from test where a='eygle.com' and b=1;

已用时间:  00: 00: 00.04

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=149 Card=1 Bytes=13)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=149 Card=1
          42 Bytes=1846)

   3    2       INDEX (RANGE SCAN) OF 'I_BC' (NON-UNIQUE) (Cost=3 Card
          =147)





Statistics
----------------------------------------------------------
        354  recursive calls
          0  db block gets
        160  consistent gets
        113  physical reads
          0  redo size
        376  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
          1  rows processed

lsllcm

我也遇到同样plan, 在metalink上是这样解释的，_b_tree_bitmap_plans is a hidden parameter in 920X with the default value set to TRUE and that is the reason, you see bitmap execution plan with B-Tree indexes.

adamyang

偶的第二次测试也遇到这个问题的
去搜索了一下：
http://download-west.oracle.com/ ... /data_acc.htm#17701

CBO计算出的COST，bitmap conversion 小于INDEX (RANGE SCAN) OF 'I_BC'
所以走了bitmap conversion 计划。

adamyang

看了一下：
这条SQL：select count(*) from test where a='eygle' and b=1;
走bitmap conversion 计划，所有工作都是通过索引完成的；
走INDEX (RANGE SCAN) OF 'I_BC' 计划，的话会有一个   TABLE ACCESS (BY INDEX ROWID) OF 'TEST'　这步计算出的cost会比较高；
所以CBO选择了bitmap conversion 。

如果你用如下SQL：select * from test where a='eygle' and b=1;
就会走INDEX (RANGE SCAN) OF 'I_BC' 计划了。

我的测试：
SQL> select count(*) from test where a='eygle.com' and b=1;

已用时间:  00: 00: 00.06

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=13)
   1    0   SORT (AGGREGATE)
   2    1     BITMAP CONVERSION (COUNT)
   3    2       BITMAP AND
   4    3         BITMAP CONVERSION (FROM ROWIDS)
   5    4           SORT (ORDER BY)
   6    5             INDEX (RANGE SCAN) OF 'I_BC' (NON-UNIQUE) (Cost=
          3 Card=135)

   7    3         BITMAP CONVERSION (FROM ROWIDS)
   8    7           INDEX (RANGE SCAN) OF 'I_A' (NON-UNIQUE) (Cost=273
          1 Card=135)





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

SQL> select /*+index(test i_bc)*/count(*) from test where a='eygle.com' and b=1;


已用时间:  00: 00: 00.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=137 Card=1 Bytes=13)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=137 Card=1
          31 Bytes=1703)

   3    2       INDEX (RANGE SCAN) OF 'I_BC' (NON-UNIQUE) (Cost=3 Card
          =135)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        361  consistent gets
          0  physical reads
          0  redo size
        376  bytes sent via SQL*Net to client
        503  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 a='eygle.com' and b=1;

已选择30行。

已用时间:  00: 00: 00.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=137 Card=131 Bytes=2
          227)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=137 Card=131
           Bytes=2227)

   2    1     INDEX (RANGE SCAN) OF 'I_BC' (NON-UNIQUE) (Cost=3 Card=1
          35)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        114  consistent gets
          0  physical reads
          0  redo size
        800  bytes sent via SQL*Net to client
        514  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         30  rows processed