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，
执行如下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，
两个执行计划，COST相同，为什么Oracle选择的是idx_t1_01？
我们看下这两条语句的10053，SQL1的trace，如下所示，
SQL2的trace，如下所示，
的确，从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，难道数字顺序，对选择会有影响？
我们删除索引，重新创建这两个索引，但是这次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看两个索引叶子块数量是相同的，
但是SQL1的成本，我们看到是3.000578，
SQL2的成本是3.000691，
和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: 019c中，直接展示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');虽然这案例某些地方有些极端了，但是从这个过程当中，能得出以下几点，
1. 从trace中COST值的显示位数的增加，我们知道Oracle也是不断演进中。
2. 尤其在CBO下，一条SQL执行计划的成本，Oracle的计算和选择还是很精妙的，10053就像我们应用软件的日志，为我们呈现出Oracle选择的过程和依据，这是非常值得借鉴的。