index skip scan和index full scan 的应用

Leo_Mu

         今天看《让oracle跑的更快》一书里有如下一段让我看了非常费解。这里只做部分截图。求ml帮助解析。
这里的id列和第一列按照书上讲的应该是同一列。这两个表结构是相同的，只是第一个表的id列只有四个值，而第二个表的id列是没有重复的。
       由于书是扫描的不能全部粘贴过来，希望ml能帮我讲解。非常感谢。

psufnxk2000

想想是怎么存的。

iimax

最近正在看concept 11203
Index Skip Scan  An index skip scan uses logical subindexes of a composite index. The
database "skips" through a single index as if it were searching separate indexes. Skip
scanning is beneficial if there are few distinct values in the leading column of a
composite index and many distinct values in the nonleading key of the index.

The database may choose an index skip scan when the leading column of the
composite index is not specified in a query predicate. For example, assume that you
run the following query for a customer in the sh.customers table:
SELECT * FROM sh.customers WHERE cust_email = 'Abbey@company.com';
The customers table has a column cust_gender whose values are either M or F.
Assume that a composite index exists on the columns (cust_gender, cust_email).
Example3–1 shows a portion of the index entries.
Example 3–1 Composite Index Entries
F,Wolf@company.com,rowid
F,Wolsey@company.com,rowid
F,Wood@company.com,rowid
F,Woodman@company.com,rowid
F,Yang@company.com,rowid
F,Zimmerman@company.com,rowid
M,Abbassi@company.com,rowid
M,Abbey@company.com,rowid
The database can use a skip scan of this index even though cust_gender is not
specified in the WHERE clause.
In a skip scan, the number of logical subindexes is determined by the number of
distinct values in the leading column. In Example 3–1, the leading column has two
possible values. The database logically splits the index into one subindex with the key
F and a second subindex with the key M.
When searching for the record for the customer whose email is Abbey@company.com,
the database searches the subindex with the value F first and then searches the
subindex with the value M. Conceptually, the database processes the query as follows:
SELECT * FROM sh.customers WHERE cust_gender = 'F'
  AND cust_email = 'Abbey@company.com'
UNION ALL
SELECT * FROM sh.customers WHERE cust_gender = 'M'
  AND cust_email = 'Abbey@company.com';

Leo_Mu

iimax 发表于 2013-5-17 09:16
最近正在看concept 11203
Index Skip Scan  An index skip scan uses logical subindexes of a composite i ...

扣了半天单词后，发现这个老外写的让我看懂了。多谢您给我的英语！