Why is Oracle using a skip scan for this query?

Question

Here's the tkprof output for a query that's running extremely slowly (WARNING: it's long :-) ):

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 should it be doing? Should I do some more hinting for this query?

EDIT: I should also point out that the query's where clause uses the columns in IDX_MBR_IDENTFN and no columns other than what's in that index. So as far as I can tell, I'm not skipping any columns.

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 LEADING hint to make sure tables were being queried in the right order. Thirdly, I removed the duplicate mbr_idn predicate. Lastly, I made IDX_MBR_IDENTFN unique. Altogether, this makes a drastic performance improvement (although it's still the most expensive query I'm running):

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

Answer 1

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.

Try to create a separate index on the column that is part of IDX_MBR_IDENTFN and used in your query.

---

For example, if your_table looks like this:

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 = 1 is likely to use the index, but in order to find the correct rows, it has to read every row of the index (id 1 to 4) and check the 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.

Answer 2

It would help if you identified what the columns in the indexes (PK_CLAIMANT and IDX_MBR_IDENTFN) are, and in what order.

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.

You also specify the predicate "mbr.mbr_idn = mbr_identfn.mbr_idn" in both the where clause and the join clause.

Answer 3

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.

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.

Regards, Rob.

Answer 4

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.

What is the primary key of MBR_IDENTFN? Is it MBR_IDN alone?

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.