一般的常识认为类似 field like '%aaa%' 的查询是无法用到field字段的索引的。
不过今天看oracle的执行计划发现不是这样的。sql语句如下的时候：
SELECT count(*) FROM mytable WHERE code LIKE UPPER('%20071119%')
得到的Plan如下：
SELECT STATEMENT CHOOSECost: 41 Bytes: 22 Cardinality: 1
2 SORT AGGREGATE Bytes: 22 Cardinality: 1
1 INDEX FAST FULL SCAN NON-UNIQUE Index_Mytable_Code Cost: 41 Bytes: 85,844 Cardinality: 3,902
显然是用了建在code 上的索引
各位大虾，不知道这是何解？？？？但是下面的这个sql语句就是另外一种情况：
SELECT SUM(amount) sum FROM mytable WHERE code LIKE UPPER('%20071119%')
它得到的plan如下：
Plan
SELECT STATEMENT CHOOSECost: 4,547 Bytes: 25 Cardinality: 1
2 SORT AGGREGATE Bytes: 25 Cardinality: 1
1 TABLE ACCESS FULL NEWMAN.Comm_Contract Cost: 4,547 Bytes: 97,550 Cardinality: 3,902
这个就用了一个全表扫描，速度非常慢，符合我的常识所以就有了两个问题：
1、为什么第一句sql用到了索引？
2、如何让第二句也用上索引？
不过今天看oracle的执行计划发现不是这样的。sql语句如下的时候：
SELECT count(*) FROM mytable WHERE code LIKE UPPER('%20071119%')
得到的Plan如下：
SELECT STATEMENT CHOOSECost: 41 Bytes: 22 Cardinality: 1
2 SORT AGGREGATE Bytes: 22 Cardinality: 1
1 INDEX FAST FULL SCAN NON-UNIQUE Index_Mytable_Code Cost: 41 Bytes: 85,844 Cardinality: 3,902
显然是用了建在code 上的索引
各位大虾，不知道这是何解？？？？但是下面的这个sql语句就是另外一种情况：
SELECT SUM(amount) sum FROM mytable WHERE code LIKE UPPER('%20071119%')
它得到的plan如下：
Plan
SELECT STATEMENT CHOOSECost: 4,547 Bytes: 25 Cardinality: 1
2 SORT AGGREGATE Bytes: 25 Cardinality: 1
1 TABLE ACCESS FULL NEWMAN.Comm_Contract Cost: 4,547 Bytes: 97,550 Cardinality: 3,902
这个就用了一个全表扫描，速度非常慢，符合我的常识所以就有了两个问题：
1、为什么第一句sql用到了索引？
2、如何让第二句也用上索引？
SELECT ID FROM mytable WHERE code LIKE UPPER('%20071119%')
的计划如下：SELECT STATEMENT CHOOSECost: 795 Bytes: 105,354 Cardinality: 3,902
4 VIEW NEWMAN.index$_join$_001 Cost: 795 Bytes: 105,354 Cardinality: 3,902
3 HASH JOIN Bytes: 105,354 Cardinality: 3,902
1 INDEX FAST FULL SCAN UNIQUE PK_mytable_ID Cost: 236 Bytes: 105,354 Cardinality: 3,902
2 INDEX FAST FULL SCAN NON-UNIQUE Index_Mytable_Code Cost: 236 Bytes: 105,354 Cardinality: 3,902
用到了索引 ，速度非常快。但是
SELECT ID,amount FROM mytable WHERE code LIKE UPPER('%20071119%')
的计划就是
SELECT STATEMENT CHOOSECost: 4,547 Bytes: 117,060 Cardinality: 3,902
1 TABLE ACCESS FULL mytable Cost: 4,547 Bytes: 117,060 Cardinality: 3,902
就没用到索引，速度非常的慢
这个sql的所有记录都可以在一个index里面得到，使用的是INDEX FAST FULL SCAN ，就是把这个index当作一个表使用，这个index建立在code列上，所有符合条件的code列都在index上面，所以就没必要使用表。
2、如何让第二句也用上索引？
SELECT SUM(amount) sum FROM mytable WHERE code LIKE UPPER('%20071119%')
amount列不在index中，如果也使用index scan，必须从index获得rowid再去表中获得amout列的值，oracle认为代价可能比全表scan还高，所以选择table scan
要使用index scan 可以在code和amount上面建立index，就可以使用index fast full scanT@ora>SELECT COUNT(*) FROM T WHERE OWNER LIKE UPPER('%20071119%') ;
Elapsed: 00:00:00.00Execution Plan
----------------------------------------------------------
Plan hash value: 4149176714-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 72 (7)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_OWNER | 8 | 136 | 72 (7)| 00:00:01 |
-----------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter("OWNER" LIKE '%20071119%')Note
-----
- dynamic sampling used for this statement默认不走index scan
T@ora>SELECT SUM(OBJECT_ID) FROM T WHERE OWNER LIKE UPPER('%20071119%') ;
Elapsed: 00:00:00.00Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 369 (3)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T | 8 | 240 | 369 (3)| 00:00:02 |
---------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter("OWNER" LIKE '%20071119%')Note
-----
- dynamic sampling used for this statement加hint 强制indexscan
T@ora>SELECT/*+index(t)*/ SUM(OBJECT_ID) FROM T WHERE OWNER LIKE UPPER('%20071119%') ;
Elapsed: 00:00:00.00Execution Plan
----------------------------------------------------------
Plan hash value: 1633656054------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 195 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 8 | 240 | 195 (4)| 00:00:01 |
|* 3 | INDEX FULL SCAN | IDX_OWNER | 2773 | | 126 (6)| 00:00:01 |
------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 3 - filter("OWNER" LIKE '%20071119%')Note
-----
- dynamic sampling used for this statement建多列index，就可以index scan
T@ora>create index idx_OWNER_OBJECT_ID on t(owner,object_id);Index created.Elapsed: 00:00:00.29
T@ora>SELECT SUM(OBJECT_ID) FROM T WHERE OWNER LIKE UPPER('%20071119%') ;
Elapsed: 00:00:00.01Execution Plan
----------------------------------------------------------
Plan hash value: 3716430985---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 89 (6)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_OWNER_OBJECT_ID | 8 | 240 | 89 (6)| 00:00:01 |
---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter("OWNER" LIKE '%20071119%')Note
-----
- dynamic sampling used for this statementT@ora>
首先跟你讲几个常识：1、使用索引不一定比全表扫描好。2、尽量用日期类型保存日期。3、索引扫描获得的是记录的rowid，如果需要访问具体的记录则要进行一次TABLE ACCESS BY INDEX ROWID 。4、分析表很关键。下面所有分析都是在你已经对表进行分析过后的讨论，如果不知道什么是表分析，googleit。需要b树的知识：
对于第一个sql
SELECT count(*) FROM mytable WHERE code LIKE UPPER('%20071119%')SELECT STATEMENT CHOOSECost: 41 Bytes: 22 Cardinality: 1
2 SORT AGGREGATE Bytes: 22 Cardinality: 1
1 INDEX FAST FULL SCAN NON-UNIQUE Index_Mytable_Code Cost: 41 Bytes: 85,844 Cardinality: 3,902
这里为什么会用索引呢？仔细看可以发现使用的是INDEX FAST FULL SCAN，这种scan有两个特点：多块读取，读取所有索引块包括内部块和叶块。所以可以通过INDEX FAST FULL SCAN来代替全表扫描来计算count(*)：首先是只要遍历一遍索引Index_Mytable_Code就可以确定有多少记录满足 code LIKE UPPER('%20071119%')，而不需要进行TABLE ACCESS BY INDEX ROWID找具体的记录；其次和全表扫描一样都是多块读取。所以无论如何都比全表扫描快，自然oracle会使用。
下面的这个sql语句就是另外一种情况：
SELECT SUM(amount) sum FROM mytable WHERE code LIKE UPPER('%20071119%')Plan
SELECT STATEMENT CHOOSECost: 4,547 Bytes: 25 Cardinality: 1
2 SORT AGGREGATE Bytes: 25 Cardinality: 1
1 TABLE ACCESS FULL NEWMAN.Comm_Contract Cost: 4,547 Bytes: 97,550 Cardinality: 3,902 这里为什么没有使用索引呢，首先看看能不能使用上面提到的INDEX FAST FULL SCAN，因为这里要计算 SUM(amount) ，这样的话就必须要得到具体的记录才行，而不是仅仅获得数量。就是说如果使用这个索引，还要进行一次TABLE ACCESS BY INDEX ROWID，这样就不一定比全表扫描快了。对比看一下楼上对此sql强制使用索引的plan:
------------------------------------------------------------------------------------------
¦ Id ¦ Operation ¦ Name ¦ Rows ¦ Bytes ¦ Cost (%CPU) ¦ Time ¦
------------------------------------------------------------------------------------------
¦ 0 ¦ SELECT STATEMENT ¦ ¦ 1 ¦ 30 ¦ 195 (4) ¦ 00:00:01 ¦
¦ 1 ¦ SORT AGGREGATE ¦ ¦ 1 ¦ 30 ¦ ¦ ¦
¦ 2 ¦ TABLE ACCESS BY INDEX ROWID ¦ T ¦ 8 ¦ 240 ¦ 195 (4) ¦ 00:00:01 ¦
¦* 3 ¦ INDEX FULL SCAN ¦ IDX_OWNER ¦ 2773 ¦ ¦ 126 (6) ¦ 00:00:01 ¦
------------------------------------------------------------------------------------------ 比使用全表扫表多了两个步骤： INDEX FULL SCAN 、 TABLE ACCESS BY INDEX ROWID。
第一个步骤INDEX FULL SCAN是对索引的一个扫描，它有两个特点：扫描所有的叶子节点（比INDEX FAST FULL SCAN少扫描了内部节点），一次一块的读取。
第二个步骤TABLE ACCESS BY INDEX ROWID是再上一个步骤得到需要的记录的ROWID后，使用ROWID获得具体的记录。概念已经有了，那么为什么oracle默认没有使用索引呢？举个极端的例子，假设所有的记录都满足 code LIKE UPPER('%20071119%')，则：
1、使用全表扫描：多块读取所有记录块。
2、使用上述索引：单块读取所有叶子节点、读取所有记录块。
看到没有？在这种极端的情况下，使用索引会比全表扫描多整整一步（单块读取所有叶子节点），而且第二步只会比全表扫描一次慢不会比全表扫描快。
好了，希望能让楼主能有一些概念了。总结一下再：1、使用索引不一定比全表扫描好，通过上面的例子应该明确了。2、尽量用日期类型保存日期，这里就不多说了，但是能影响性能和数据完整性。4、分析表很关键，上面的例子只是说明了可能全表扫描比索引好，当然也可能不好，那我们怎么办呢？答案就是分析表，分析表可以帮助oracle做出正确的判断。ps:3楼的问题作为家庭作业^-^。
1、code是编码，编码中包括了日期的信息，条件是对编码的查询，因此无法用日期字段来解决。
2、已经对表进行了分析。现在的问题是，
SELECT SUM(amount) sum FROM mytable WHERE code LIKE UPPER('%20071119%')
在我强制使用索引的 时候，也就是改成
SELECT /*+index(mytable)*/ SUM(amount) sum FROM mytable WHERE code LIKE UPPER('%20071119%')
的时候使用因为使用了索引，速度快了很多。但是因为用了一些中间件的缘故，我无法使用强制使用索引。那该如何进行性能优化呢？除了建立包括code和amount在内的索引。
我的认识是这样的，之所以
SELECT SUM(amount) sum FROM mytable WHERE code LIKE UPPER('%20071119%')
没有使用索引，是oracle在基于代价的CBO方式下，认为这句语句用全表扫描的效果要好于用索引来查然后再用rowid获得数据的方式。所以就选择进行了一次全表扫描。
因此有如下的解决方案：
1、对表进行重新分析，让oracle找到更好的执行计划。
2、建一个包括code和amount的组合索引，以改变oracle的执行计划，让oracle用这个索引进行查找。
3、用hint的方式强制oracle使用索引。我现在的问题是除了这几种外，还有没有什么方法？？
可能是我举例不恰当，不是说刚好会是一个日期的，什么可能性都存在，例如 code LIKE UPPER('%QB2C-2000%')，所以不能做operateTime = to_date（'20071119','YYYYMMDD') 这样的更改。
实际上，如果你建过索引，并且进行了表分析以后，oracle要进行全表扫描而不是使用索引的话，就让它进行全表扫描，不要怕。
2、如果确实oracle做了愚蠢的决定，的确应该使用索引而不是全表扫描的话。（这种情况很少，在做出这个判断的时候要做很多数据收集工作而不是凭借喜好）
所谓的应该使用索引至少包括两个方面：sql跑的更快或者占用更少的系统资源。
那么你就通过hint告诉它使用索引。
如果你因为某些原因不能使用hint的话，那么oracle还提供了很多参数来影响执行计划：
optimizer_index_caching、optimizer_index_cost_adj等等等等。
3、扩展开来说，为了获得更好的性能，你还可以重新设计表，比如使用索引组织表、分区，对经常查询的聚合数据建立聚合表等等。但是这些都要在对业务很清楚的情况下才能做，比如我现在就做不了，因为不知道你的需求。
4、没有度量不要进行性能调整。这是最佳实践。一定要记住你的目标，目标决定一切。当然了可以用更漂亮的话来说：需求决定设计。