COST值相同？是真是假？-蒲公英云

这两天碰到一个问题，一条SQL的两个执行计划COST成本值相同，Oracle是怎么选择的？

首先，创建测试表，以及两个索引，

create table t1 as select * from dba_objects;
create index idx_t1_01 on t1(owner, object_name, created);
create index idx_t1_02 on t1(owner, created, object_name);

我们要测试的，就是这条SQL，其中owner是等值条件，object_name和created是不等值条件，从以上的索引定义可以看到，无论采用哪种索引都无需回表，

select owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>sysdate-10;

1. Oracle 11g下的测试

执行如下SQL，记为SQL1，返回记录数0，

SQL> select owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>sysdate-10;
no rows selected

其执行计划，如下所示，用到了idx_t_01的索引，COST是2，

format_png

执行如下SQL，记为SQL2，强制使用idx_t_02，返回记录数0，

SQL> select /*+ index(T1, IDX_T1_02) */  owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>sysdate-10;
no rows selected

其执行计划，如下所示，COST同样是2，

format_png 1

两个执行计划，COST相同，为什么Oracle选择的是idx_t1_01？

我们看下这两条语句的10053，SQL1的trace，如下所示，

format_png 2

SQL2的trace，如下所示，

format_png 3

的确，从COST来看，都是2.00，有什么其他条件，能帮助Oracle做出选择？

经albert指点，dbsnake有篇文章《CBO对于Cost值相同的索引的选择》，介绍的场景，和这个很像，

这意味着对于Oracle 10gR2及其以上的版本，CBO对于Cost值相同的索引的选择实际上会这样：
1、如果Cost值相同的索引的叶子块数量不同，则Oracle会选择叶子块数量较少的那个索引；
2、如果Cost值相同的索引的叶子块数量相同，则Oracle会选择索引名的字母顺序在前面的那个索引。

P.S. 原文链接，

http://www.dbsnake.net/handle-equally-costed-indexes.html)

从trace看，idx_t1_01和idx_t2_02的索引叶子块数量都是589，按照如上，Oracle会选择索引名的字母顺序在前面的索引，这里两个索引的前缀都是“idx_t1_0”，唯独结尾不同，1和2，难道数字顺序，对选择会有影响？

format_png 4

我们删除索引，重新创建这两个索引，但是这次idx_t1_01和idx_t1_02索引字段调换下顺序，

drop index idx_t1_01;
drop index idx_t1_02;
create index idx_t1_01 on t1(owner, created, object_name);
create index idx_t1_02 on t1(owner, object_name, created);

然而这次Oracle仍选择的是(owner, object_name, created)，只是这次索引名称是idx_t1_02，trace信息不展示了，两个索引的成本值和上面的相同，说明并不是在COST值相同，而且索引叶子快数量相同的情况下，选择索引名字母顺序在前面的索引，这就很奇怪了，

SQL> select owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>sysdate-10;
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name      | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |      1 |        |     2 (100)|          |      0 |00:00:00.01 |       6 |      5 |
|*  1 |  INDEX RANGE SCAN| IDX_T1_02 |      1 |      1 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       6 |      5 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OWNER"='SYS' AND "OBJECT_NAME" LIKE 'DBA_HIST%' AND "CREATED">SYSDATE@!-10 AND "CREATED" IS NOT NULL)
       filter(("OBJECT_NAME" LIKE 'DBA_HIST%' AND "CREATED">SYSDATE@!-10))

2. Oracle 19c下的测试

我们转战19c，相同表、数据、索引，以及测试的SQL1和SQL2，

create table t1 as select * from dba_objects;
create index idx_t1_01 on t1(owner, object_name, created);
create index idx_t1_02 on t1(owner, created, object_name);
select owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>sysdate-10;
select /*+ index(T1,IDX_T1_02) */ owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>sysdate-10;

Oracle还是选择的idx_t1_01，

SQL> select owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>sysdate-10;
----------------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name      | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |      1 |        |     3 (100)|          |      0 |00:00:00.01 |      10 |
|*  1 |  INDEX RANGE SCAN| IDX_T1_01 |      1 |      1 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |      10 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OWNER"='SYS' AND "OBJECT_NAME" LIKE 'DBA_HIST%' AND "CREATED">SYSDATE@!-10 AND "CREATED" IS NOT NULL)
       filter(("OBJECT_NAME" LIKE 'DBA_HIST%' AND "CREATED">SYSDATE@!-10))

而且强制用idx_t1_02的成本值，和idx_t1_01相同，

SQL> select /*+ index(T1, IDX_T1_02) */  owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>sysdate-10;
----------------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name      | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |      1 |        |     3 (100)|          |      0 |00:00:00.01 |       4 |
|*  1 |  INDEX RANGE SCAN| IDX_T1_02 |      1 |      1 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OWNER"='SYS' AND "CREATED">SYSDATE@!-10 AND "OBJECT_NAME" LIKE 'DBA_HIST%' AND "CREATED" IS NOT NULL)
       filter("OBJECT_NAME" LIKE 'DBA_HIST%')

从trace看两个索引叶子块数量是相同的，

format_png 5

但是SQL1的成本，我们看到是3.000578，

format_png 6

SQL2的成本是3.000691，

format_png 7

和11g的trace比较一下，11g的trace中SQL的成本是保留两位小数，都是2.00，而在19c，SQL的成本是保留6位小数，因此SQL2的成本是比SQL1高的，所以通过COST比较就可以得出选择的执行计划。没牵扯到索引叶子块数量和索引名称的字母顺序。

有理由猜测，11g中两个执行计划对应的COST很可能在小数两位以内是相同的，例如一个是2.001，一个是2.002，因为trace只显示小数点右侧两位，所以看到的是2.00，都是相同的，其实不同，

Index: IDX_T1_01
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 0.00  Bytes: 0
Index: IDX_T1_02
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 0.00  Bytes: 0

19c中，直接展示6位小数，因此就可以看出两者的不同，

Index: IDX_T1_02
         Cost: 3.000578  Degree: 1  Resp: 3.000578  Card: 0.000364  Bytes: 0.000000
Index: IDX_T1_02
         Cost: 3.000691  Degree: 1  Resp: 3.000691  Card: 0.000364  Bytes: 0.000000

另外，上述SQL有些特殊，返回记录数是0，多少有些左右成本的计算了，

select owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>sysdate-10;

如果用这些条件，返回记录数>0，默认还是会选择idx_t1_01的索引，但此时SQL1和SQL2的COST就会大相径庭，在整数位就可以看出不同了，

select owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>to_date('2019-04-17 01:03:50','yyyy-mm-dd hh24:mi:ss');