Use the Index, Luke

尼玛，这网站太好了：http://use-the-index-luke.com/sql/clustering/index-only-scan-covering-index

The index covers the entire query so it is also called a covering index.而这个Covering Index必须包括Select里的列

To cover an entire query, an index must contain all columns from the SQL statement—in particular also the columns from the select clause

这就解释了在下面的例子里（处于Join里的表也一样，join的条件也是where)：

SELECT [Extent5].[COMPANY_CODE] AS [COMPANY_CODE3], [Extent5].[FACTORY] AS [FACTORY3], [Extent5].[MACHINE_CODE] AS [MACHINE_CODE1], [Extent6].[COMPANY_CODE] AS [COMPANY_CODE4], [Extent6].[FACTORY] AS [FACTORY4], [Extent6].[JOB_NUMBER] AS [JOB_NUMBER3], [Extent6].[PROCESS_STAGE] AS [PROCESS_STAGE2]–,[Extent6].*
??????????? FROM? [dbo].[JOB_STAGE_LINES] AS [Extent5]
??????????? INNER JOIN [dbo].[JOB_STAGE] AS [Extent6] ON ([Extent5].[PROCESS_STAGE] = [Extent6].[PROCESS_STAGE]) AND ([Extent5].[JOB_NUMBER] = [Extent6].[JOB_NUMBER]) AND ([Extent5].[FACTORY] = [Extent6].[FACTORY]) AND ([Extent5].[COMPANY_CODE] = [Extent6].[COMPANY_CODE])???????????
??????????? WHERE ‘3’ = [Extent5].[RECIPE_LINE_TYPE]
????

注释掉的红色如果放出来，就会把index scan变成tale scan(table full access in oracle)

?

这篇:http://myoracleguide.com/msa/Diagnosing_Why_a_Query_is_Not_Using_an_Index.htm 则是一个补充,可以自查为什么index没用上。