为什么 Oracle 对这个查询使用跳过扫描？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2776884/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why is Oracle using a skip scan for this query?
提问by Jason Baker
Here's the tkprof output for a query that's running extremely slowly (WARNING: it's long :-) ):
这是运行非常缓慢的查询的 tkprof 输出（警告：它很长 :-)）：
SELECT mbr_comment_idn, mbr_crt_dt, mbr_data_source, mbr_dol_bl_rmo_ind, mbr_dxcg_ctl_member, mbr_employment_start_dt, mbr_employment_term_dt, mbr_entity_active, mbr_ethnicity_idn, mbr_general_health_status_code, mbr_hand_dominant_code, mbr_hgt_feet, mbr_hgt_inches, mbr_highest_edu_level, mbr_insd_addr_idn, mbr_insd_alt_id, mbr_insd_name, mbr_insd_ssn_tin, mbr_is_smoker, mbr_is_vip, mbr_lmbr_first_name, mbr_lmbr_last_name, mbr_marital_status_cd, mbr_mbr_birth_dt, mbr_mbr_death_dt, mbr_mbr_expired, mbr_mbr_first_name, mbr_mbr_gender_cd, mbr_mbr_idn, mbr_mbr_ins_type, mbr_mbr_isreadonly, mbr_mbr_last_name, mbr_mbr_middle_name, mbr_mbr_name, mbr_mbr_status_idn, mbr_mpi_id, mbr_preferred_am_pm, mbr_preferred_time, mbr_prv_innetwork, mbr_rep_addr_idn, mbr_rep_name, mbr_rp_mbr_id, mbr_same_mbr_ins, mbr_special_needs_cd, mbr_timezone, mbr_upd_dt, mbr_user_idn, mbr_wgt, mbr_work_status_idn
FROM (SELECT /*+ FIRST_ROWS(1) */ mbr_comment_idn, mbr_crt_dt, mbr_data_source, mbr_dol_bl_rmo_ind, mbr_dxcg_ctl_member, mbr_employment_start_dt, mbr_employment_term_dt, mbr_entity_active, mbr_ethnicity_idn, mbr_general_health_status_code, mbr_hand_dominant_code, mbr_hgt_feet, mbr_hgt_inches, mbr_highest_edu_level, mbr_insd_addr_idn, mbr_insd_alt_id, mbr_insd_name, mbr_insd_ssn_tin, mbr_is_smoker, mbr_is_vip, mbr_lmbr_first_name, mbr_lmbr_last_name, mbr_marital_status_cd, mbr_mbr_birth_dt, mbr_mbr_death_dt, mbr_mbr_expired, mbr_mbr_first_name, mbr_mbr_gender_cd, mbr_mbr_idn, mbr_mbr_ins_type, mbr_mbr_isreadonly, mbr_mbr_last_name, mbr_mbr_middle_name, mbr_mbr_name, mbr_mbr_status_idn, mbr_mpi_id, mbr_preferred_am_pm, mbr_preferred_time, mbr_prv_innetwork, mbr_rep_addr_idn, mbr_rep_name, mbr_rp_mbr_id, mbr_same_mbr_ins, mbr_special_needs_cd, mbr_timezone, mbr_upd_dt, mbr_user_idn, mbr_wgt, mbr_work_status_idn, ROWNUM AS ora_rn
FROM (SELECT mbr.comment_idn AS mbr_comment_idn, mbr.crt_dt AS mbr_crt_dt, mbr.data_source AS mbr_data_source, mbr.dol_bl_rmo_ind AS mbr_dol_bl_rmo_ind, mbr.dxcg_ctl_member AS mbr_dxcg_ctl_member, mbr.employment_start_dt AS mbr_employment_start_dt, mbr.employment_term_dt AS mbr_employment_term_dt, mbr.entity_active AS mbr_entity_active, mbr.ethnicity_idn AS mbr_ethnicity_idn, mbr.general_health_status_code AS mbr_general_health_status_code, mbr.hand_dominant_code AS mbr_hand_dominant_code, mbr.hgt_feet AS mbr_hgt_feet, mbr.hgt_inches AS mbr_hgt_inches, mbr.highest_edu_level AS mbr_highest_edu_level, mbr.insd_addr_idn AS mbr_insd_addr_idn, mbr.insd_alt_id AS mbr_insd_alt_id, mbr.insd_name AS mbr_insd_name, mbr.insd_ssn_tin AS mbr_insd_ssn_tin, mbr.is_smoker AS mbr_is_smoker, mbr.is_vip AS mbr_is_vip, mbr.lmbr_first_name AS mbr_lmbr_first_name, mbr.lmbr_last_name AS mbr_lmbr_last_name, mbr.marital_status_cd AS mbr_marital_status_cd, mbr.mbr_birth_dt AS mbr_mbr_birth_dt, mbr.mbr_death_dt AS mbr_mbr_death_dt, mbr.mbr_expired AS mbr_mbr_expired, mbr.mbr_first_name AS mbr_mbr_first_name, mbr.mbr_gender_cd AS mbr_mbr_gender_cd, mbr.mbr_idn AS mbr_mbr_idn, mbr.mbr_ins_type AS mbr_mbr_ins_type, mbr.mbr_isreadonly AS mbr_mbr_isreadonly, mbr.mbr_last_name AS mbr_mbr_last_name, mbr.mbr_middle_name AS mbr_mbr_middle_name, mbr.mbr_name AS mbr_mbr_name, mbr.mbr_status_idn AS mbr_mbr_status_idn, mbr.mpi_id AS mbr_mpi_id, mbr.preferred_am_pm AS mbr_preferred_am_pm, mbr.preferred_time AS mbr_preferred_time, mbr.prv_innetwork AS mbr_prv_innetwork, mbr.rep_addr_idn AS mbr_rep_addr_idn, mbr.rep_name AS mbr_rep_name, mbr.rp_mbr_id AS mbr_rp_mbr_id, mbr.same_mbr_ins AS mbr_same_mbr_ins, mbr.special_needs_cd AS mbr_special_needs_cd, mbr.timezone AS mbr_timezone, mbr.upd_dt AS mbr_upd_dt, mbr.user_idn AS mbr_user_idn, mbr.wgt AS mbr_wgt, mbr.work_status_idn AS mbr_work_status_idn
FROM mbr JOIN mbr_identfn ON mbr.mbr_idn = mbr_identfn.mbr_idn
WHERE mbr_identfn.mbr_idn = mbr.mbr_idn AND mbr_identfn.identfd_type = :identfd_type_1 AND mbr_identfn.identfd_number = :identfd_number_1 AND mbr_identfn.entity_active = :entity_active_1)
WHERE ROWNUM <= :ROWNUM_1)
WHERE ora_rn > :ora_rn_1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 9936 0.46 0.49 0 0 0 0
Execute 9936 0.60 0.59 0 0 0 0
Fetch 9936 329.87 404.00 0 136966922 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 29808 330.94 405.09 0 136966922 0 0
Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: 36 (JIVA_DEV)
Rows Row Source Operation
------- ---------------------------------------------------
0 VIEW (cr=102 pr=0 pw=0 time=2180 us)
0 COUNT STOPKEY (cr=102 pr=0 pw=0 time=2163 us)
0 NESTED LOOPS (cr=102 pr=0 pw=0 time=2152 us)
0 INDEX SKIP SCAN IDX_MBR_IDENTFN (cr=102 pr=0 pw=0 time=2140 us)(object id 341053)
0 TABLE ACCESS BY INDEX ROWID MBR (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN PK_CLAIMANT (cr=0 pr=0 pw=0 time=0 us)(object id 334044)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: HINT: FIRST_ROWS
0 VIEW
0 COUNT (STOPKEY)
0 NESTED LOOPS
0 INDEX MODE: ANALYZED (SKIP SCAN) OF 'IDX_MBR_IDENTFN'
(INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'MBR'
(TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_CLAIMANT'
(INDEX (UNIQUE))
********************************************************************************
Based on my reading of Oracle's documentation of skip scans, a skip scan is most useful when the first column of an index has a low number of unique values. The thing is that the first index of this column has a high number of uniques. So am I correct in assuming that a skip scan is the wrong thing to do here? Also, what kind of scan shouldit be doing? Should I do some more hinting for this query?
根据我对 Oracle 的跳过扫描文档的阅读，当索引的第一列具有少量唯一值时，跳过扫描最有用。问题是该列的第一个索引具有大量唯一值。那么我假设跳过扫描是错误的做法是否正确？此外，什么样的扫描应该是可以做什么？我应该为这个查询做更多的提示吗？
EDIT: I should also point out that the query's where clause uses the columns in IDX_MBR_IDENTFNand no columns other than what's in that index. So as far as I can tell, I'm not skipping any columns.
编辑：我还应该指出，查询的 where 子句使用列中的列，IDX_MBR_IDENTFN并且除了该索引中的列之外没有其他列。所以据我所知，我没有跳过任何列。
EDIT 2: I've done a few things to speed this query up. First of all, I removed the paging. As it turns out, this query only returns one row anyway. Secondly, I added a LEADINGhint to make sure tables were being queried in the right order. Thirdly, I removed the duplicate mbr_idnpredicate. Lastly, I made IDX_MBR_IDENTFNunique. Altogether, this makes a drastic performance improvement (although it's still the most expensive query I'm running):
编辑 2：我做了一些事情来加速这个查询。首先，我删除了分页。事实证明，这个查询无论如何只返回一行。其次，我添加了一个LEADING提示以确保以正确的顺序查询表。第三，我删除了重复的mbr_idn谓词。最后，我做了IDX_MBR_IDENTFN独一无二的。总而言之，这极大地提高了性能（尽管它仍然是我正在运行的最昂贵的查询）：
SELECT /*+ LEADING (mbr_identfn, mbr) */ mbr.comment_idn AS mbr_comment_idn, mbr.crt_dt AS mbr_crt_dt, mbr.data_source AS mbr_data_source, mbr.dol_bl_rmo_ind AS mbr_dol_bl_rmo_ind, mbr.dxcg_ctl_member AS mbr_dxcg_ctl_member, mbr.employment_start_dt AS mbr_employment_start_dt, mbr.employment_term_dt AS mbr_employment_term_dt, mbr.entity_active AS mbr_entity_active, mbr.ethnicity_idn AS mbr_ethnicity_idn, mbr.general_health_status_code AS mbr_general_health_status_code, mbr.hand_dominant_code AS mbr_hand_dominant_code, mbr.hgt_feet AS mbr_hgt_feet, mbr.hgt_inches AS mbr_hgt_inches, mbr.highest_edu_level AS mbr_highest_edu_level, mbr.insd_addr_idn AS mbr_insd_addr_idn, mbr.insd_alt_id AS mbr_insd_alt_id, mbr.insd_name AS mbr_insd_name, mbr.insd_ssn_tin AS mbr_insd_ssn_tin, mbr.is_smoker AS mbr_is_smoker, mbr.is_vip AS mbr_is_vip, mbr.lmbr_first_name AS mbr_lmbr_first_name, mbr.lmbr_last_name AS mbr_lmbr_last_name, mbr.marital_status_cd AS mbr_marital_status_cd, mbr.mbr_birth_dt AS mbr_mbr_birth_dt, mbr.mbr_death_dt AS mbr_mbr_death_dt, mbr.mbr_expired AS mbr_mbr_expired, mbr.mbr_first_name AS mbr_mbr_first_name, mbr.mbr_gender_cd AS mbr_mbr_gender_cd, mbr.mbr_idn AS mbr_mbr_idn, mbr.mbr_ins_type AS mbr_mbr_ins_type, mbr.mbr_isreadonly AS mbr_mbr_isreadonly, mbr.mbr_last_name AS mbr_mbr_last_name, mbr.mbr_middle_name AS mbr_mbr_middle_name, mbr.mbr_name AS mbr_mbr_name, mbr.mbr_status_idn AS mbr_mbr_status_idn, mbr.mpi_id AS mbr_mpi_id, mbr.preferred_am_pm AS mbr_preferred_am_pm, mbr.preferred_time AS mbr_preferred_time, mbr.prv_innetwork AS mbr_prv_innetwork, mbr.rep_addr_idn AS mbr_rep_addr_idn, mbr.rep_name AS mbr_rep_name, mbr.rp_mbr_id AS mbr_rp_mbr_id, mbr.same_mbr_ins AS mbr_same_mbr_ins, mbr.special_needs_cd AS mbr_special_needs_cd, mbr.timezone AS mbr_timezone, mbr.upd_dt AS mbr_upd_dt, mbr.user_idn AS mbr_user_idn, mbr.wgt AS mbr_wgt, mbr.work_status_idn AS mbr_work_status_idn
FROM mbr JOIN mbr_identfn ON mbr.mbr_idn = mbr_identfn.mbr_idn
WHERE mbr_identfn.identfd_type = :identfd_type_1 AND mbr_identfn.identfd_number = :identfd_number_1 AND mbr_identfn.entity_active = :entity_active_1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10102 0.45 0.42 0 0 0 0
Execute 10102 0.44 0.52 0 0 0 0
Fetch 10102 1.60 1.81 0 218121 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 30306 2.50 2.75 0 218121 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 36 (JIVA_DEV)
Rows Row Source Operation
------- ---------------------------------------------------
0 NESTED LOOPS (cr=3 pr=0 pw=0 time=96 us)
0 TABLE ACCESS BY INDEX ROWID MBR_IDENTFN (cr=3 pr=0 pw=0 time=88 us)
0 INDEX UNIQUE SCAN UK_CLM_IDFN (cr=3 pr=0 pw=0 time=77 us)(object id 334118)
0 TABLE ACCESS BY INDEX ROWID MBR (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN PK_CLAIMANT (cr=0 pr=0 pw=0 time=0 us)(object id 334044)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 NESTED LOOPS
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'MBR_IDENTFN' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'UK_CLM_IDFN' (INDEX
(UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'MBR' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_CLAIMANT' (INDEX
(UNIQUE))
回答by Peter Lang
Index skip scan means, that the first column of the index is ignored. This costs performance since Oracle has read every item of the first column, and check if the second (or third, ...) column is what you searched for. This usually is faster than a full-table scan (depends on your query), but slower than a index range scan.
索引跳过扫描意味着，索引的第一列被忽略。这会降低性能，因为 Oracle 已读取第一列的每个项目，并检查第二（或第三，...）列是否是您搜索的内容。这通常比全表扫描（取决于您的查询）快，但比索引范围扫描慢。
Try to create a separate index on the column that is part of IDX_MBR_IDENTFNand used in your query.
尝试在属于IDX_MBR_IDENTFN查询并在查询中使用的列上创建单独的索引。
For example, if your_tablelooks like this:
例如，如果your_table看起来像这样：
id status
1 0
2 0
3 0
4 1
and you have a compound index on (id, status), the query Select * From your_table Where status = 1is likely to use the index, but in order to find the correct rows, it has to read every row of the index (id 1to 4) and check the status.
并且您在 上有一个复合索引(id, status)，查询Select * From your_table Where status = 1很可能会使用该索引，但是为了找到正确的行，它必须读取索引的每一行（id1到4）并检查status.
Update: The following index could improve performance a little further, but you will have to try if it really helps:
更新：以下索引可以进一步提高性能，但您必须尝试它是否真的有帮助：
mbr_identfn( identfd_type, identfd_number, entity_active, mbr_idn )
This could even help to avoid the hint.
这甚至可以帮助避免提示。
回答by Rob van Wijk
I would shift my focus away from the skip scan.
我会把我的注意力从跳过扫描转移开。
The tkprof snippet shows you that your first priority should be to reduce the number of times you issue this statement. Currently you are executing this statement 9936 times. And each execution takes only 405/9936 seconds. Reasonably fast. But not if you execute it 9936 times.
tkprof 片段向您展示了您的首要任务应该是减少您发出此语句的次数。目前您正在执行此语句 9936 次。并且每次执行只需要 405/9936 秒。相当快。但如果你执行它 9936 次就不会了。
So this statement is almost sure inside a loop construct. In each iteration you provide a different input parameter set(:identfd_type_1, :identfd_number_1, :entity_active_1, :ROWNUM_1, :ora_rn_1). Rewrite this loop construct to have this statement execute once for the entire set, and your performance problem will probably a thing from the past. If not, please post the new tkprof output.
所以这个语句在循环结构中几乎是肯定的。在每次迭代中，您提供不同的输入参数集（：identfd_type_1、：identfd_number_1、：entity_active_1、：ROWNUM_1、：ora_rn_1）。重写这个循环结构，让这个语句对整个集合执行一次，你的性能问题可能会成为过去。如果没有，请发布新的 tkprof 输出。
Regards, Rob.
问候，罗伯。
回答by Dave Costa
To explain the skip scan ... this appears to be the relevant predicate portion of the query:
为了解释跳过扫描......这似乎是查询的相关谓词部分：
WHERE mbr_identfn.mbr_idn = mbr.mbr_idn
AND mbr_identfn.identfd_type = :identfd_type_1
AND mbr_identfn.identfd_number = :identfd_number_1
AND mbr_identfn.entity_active = :entity_active_1
If the execution start with MBR_IDENTFN, then we don't yet have a value for MBR_IDN to look up in the index; this means we can't do a unique or range scan. But we have values given (as bind variables) for the other three columns of the index, so we can do a skip scan. Oracle is choosing to do this in order to avoid accessing the base table at all, which seems sensible.
如果执行以 MBR_IDENTFN 开始，那么我们还没有 MBR_IDN 的值可以在索引中查找；这意味着我们不能进行唯一扫描或范围扫描。但是我们已经为索引的其他三列给出了值（作为绑定变量），所以我们可以进行跳过扫描。Oracle 选择这样做是为了完全避免访问基表，这似乎是明智的。
What is the primary key of MBR_IDENTFN? Is it MBR_IDN alone?
MBR_IDENTFN 的主键是什么？是单独的 MBR_IDN 吗？
I am thinking you should have a separate index on MBR_IDENTFN with some or all of IDENTFD_TYPE, IDENTFD_NUMBER, and ENTITY_ACTIVE as the leading columns. This would allow a range or unique scan to be done instead of a skip scan.
我认为您应该在 MBR_IDENTFN 上有一个单独的索引，其中部分或全部 IDENTFD_TYPE、IDENTFD_NUMBER 和 ENTITY_ACTIVE 作为前导列。这将允许执行范围或唯一扫描而不是跳过扫描。
回答by Gary Myers
It would help if you identified what the columns in the indexes (PK_CLAIMANT and IDX_MBR_IDENTFN) are, and in what order.
如果您确定索引中的列（PK_CLAIMANT 和 IDX_MBR_IDENTFN）是什么以及以什么顺序排列，这将有所帮助。
I suspect it is a datetype issue. If, for example, mbr_identfn.identfd_type is the leading column of the index and is numeric, but your :identfd_type_1 is character variable (or vice versa) it becomes unusable. However if there are few types, then the index can be used with a skip scan.
我怀疑这是一个日期类型问题。例如，如果 mbr_identfn.identfd_type 是索引的前导列并且是数字，但您的 :identfd_type_1 是字符变量（反之亦然），它将变得不可用。但是，如果类型很少，则索引可以与跳过扫描一起使用。
You also specify the predicate "mbr.mbr_idn = mbr_identfn.mbr_idn" in both the where clause and the join clause.
您还可以在 where 子句和 join 子句中指定谓词“mbr.mbr_idn = mbr_identfn.mbr_idn”。
