这是运行速度极慢的查询的 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))

********************************************************************************

基于我对 Oracle 的 documentation of skip scans 的阅读，当索引的第一列具有少量唯一值时，跳过扫描最有用。问题是该列的第一个索引具有大量唯一值。那么我是否正确地假设跳过扫描在这里是错误的呢？另外，它应该进行什么样的扫描？我是否应该为此查询做更多提示？

编辑:我还应该指出，查询的 where 子句使用 IDX_MBR_IDENTFN 中的列，除了该索引中的内容外，没有其他列。据我所知，我没有跳过任何专栏。

编辑 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))

最佳答案

索引跳过扫描意味着忽略索引的第一列。这会降低性能，因为 Oracle 已读取第一列的每个项目，并检查第二(或第三，...)列是否是您搜索的内容。这通常比全表扫描(取决于您的查询)快，但比索引范围扫描慢。

尝试在属于 IDX_MBR_IDENTFN 的一部分并在您的查询中使用的列上创建一个单独的索引。

例如，如果 your_table 如下所示:

id  status
1   0
2   0
3   0
4   1

并且您在 (id, status) 上有一个复合索引，查询 Select * From your_table Where status = 1 可能会使用该索引，但为了要找到正确的行，它必须读取索引的每一行(id 1 到 4)并检查 status。

更新:以下索引可以进一步提高性能，但如果真的有帮助，您将不得不尝试:

mbr_identfn( identfd_type, identfd_number, entity_active, mbr_idn )

这甚至可以帮助避免提示。

https://stackoverflow.com/questions/2776884/