Forum Stats

  • 3,781,630 Users
  • 2,254,532 Discussions
  • 7,879,769 Comments

Discussions

Categories

Explain plan - index range scan rows increase while table access by row id

spur230
spur230 Member Posts: 399
edited Nov 12, 2009 2:03PM in General Database Discussions
I am using Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production. Please help me understand why rows returned from index range scan is 3 but table access by index row id is 10155. Please refer to explain plan ID 7 and 8. 


PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
                                                                                                    
---------------------------------------------------------------------------------------------       
| Id  | Operation                      |  Name                      | Rows  | Bytes | Cost  |       
---------------------------------------------------------------------------------------------       
|   0 | SELECT STATEMENT               |                            |  8308 |   446K|  4408 |       
|   1 |  SORT ORDER BY                 |                            |  8308 |   446K|  4408 |       
|*  2 |   HASH JOIN                    |                            |  8308 |   446K|  4316 |       
|*  3 |    HASH JOIN                   |                            |  8189 |   255K|  2256 |       
|*  4 |     INDEX FAST FULL SCAN       | TUNE_CHD_07                |  8071 | 72639 |   199 |       
|*  5 |     TABLE ACCESS BY INDEX ROWID| CLM_HDR_CLM_LN_STATUS      | 10155 |   228K|  2055 |       
|*  6 |      INDEX RANGE SCAN          | XIF3CLM_HDR_CLM_LN_STATUS  |     3 |       |   120 |       
|*  7 |    TABLE ACCESS BY INDEX ROWID | CLM_HDR_CLM_LN_STATUS      | 10155 |   228K|  2055 |       
|*  8 |     INDEX RANGE SCAN           | XIF3CLM_HDR_CLM_LN_STATUS  |     3 |       |   120 |       
---------------------------------------------------------------------------------------------       
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   2 - access("CHCLS"."CLAIM_HEADER_SID"="CHCLS1"."CLAIM_HEADER_SID")                               
   3 - access("CHD"."CLAIM_HEADER_SID"="CHCLS"."CLAIM_HEADER_SID")                                  
   4 - filter("CHD"."CLM_TYPE_CID"=2)                                                               
   5 - filter("CHCLS"."CLAIM_LINE_SID" IS NULL AND "CHCLS"."TO_DATE" IS NULL)                       
   6 - access("CHCLS"."STATUS_TYPE_CID"=8 AND "CHCLS"."STATUS_CID"=71)                              
   7 - filter("CHCLS1"."CLAIM_LINE_SID" IS NULL AND "CHCLS1"."TO_DATE" IS NULL)                     
   8 - access("CHCLS1"."STATUS_TYPE_CID"=2 AND "CHCLS1"."STATUS_CID"=130)                           
                                                                                                    
Note: cpu costing is off
Many thanks for any help..
Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,848 Gold Crown
    Accepted Answer
    spur230 wrote:
    I am using Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production. Please help me understand why rows returned from index range scan is 3 but table access by index row id is 10155. Please refer to explain plan ID 7 and 8. 


    |*  5 |     TABLE ACCESS BY INDEX ROWID| CLM_HDR_CLM_LN_STATUS      | 10155 |   228K|  2055 |       
|*  6 |      INDEX RANGE SCAN          | XIF3CLM_HDR_CLM_LN_STATUS  |     3 |       |   120 |       
|*  7 |    TABLE ACCESS BY INDEX ROWID | CLM_HDR_CLM_LN_STATUS      | 10155 |   228K|  2055 |       
|*  8 |     INDEX RANGE SCAN           | XIF3CLM_HDR_CLM_LN_STATUS  |     3 |       |   120 |
    Sometimes you just have to assume it's a bug. There have been several floating through different versions of Oracle where the number of rows fetched from the table is larger than the number of rowids fetched from the index. (Sometimes the optimizer literally copies the wrong value from the memory structures into the execution plan output, and you may be able to spot this in the 10053 trace).

    One possibility, though, is that the index stats and the table stats were collected at different times with different sample sizes and are sufficiency different to confuse the optimizer.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in
    fixed format
    .


"Science is more than a body of knowledge; it is a way of thinking" 
Carl Sagan
    · Share on TwitterShare on Facebook

Answers

  • 729338
    729338 Member Posts: 1,475
    Hi,

    The rows returned are different due to different clauses applied to them. for Index range scan it is using following where clause.

    ("CHCLS1"."STATUS_TYPE_CID"=2 AND "CHCLS1"."STATUS_CID"=130)

    For the Table access by row id it is using following filter.

    ("CHCLS1"."CLAIM_LINE_SID" IS NULL AND "CHCLS1"."TO_DATE" IS NULL)

    and it is going againt different objects.

    Regards
    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,848 Gold Crown
    Accepted Answer
    spur230 wrote:
    I am using Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production. Please help me understand why rows returned from index range scan is 3 but table access by index row id is 10155. Please refer to explain plan ID 7 and 8. 


    |*  5 |     TABLE ACCESS BY INDEX ROWID| CLM_HDR_CLM_LN_STATUS      | 10155 |   228K|  2055 |       
|*  6 |      INDEX RANGE SCAN          | XIF3CLM_HDR_CLM_LN_STATUS  |     3 |       |   120 |       
|*  7 |    TABLE ACCESS BY INDEX ROWID | CLM_HDR_CLM_LN_STATUS      | 10155 |   228K|  2055 |       
|*  8 |     INDEX RANGE SCAN           | XIF3CLM_HDR_CLM_LN_STATUS  |     3 |       |   120 |
    Sometimes you just have to assume it's a bug. There have been several floating through different versions of Oracle where the number of rows fetched from the table is larger than the number of rowids fetched from the index. (Sometimes the optimizer literally copies the wrong value from the memory structures into the execution plan output, and you may be able to spot this in the 10053 trace).

    One possibility, though, is that the index stats and the table stats were collected at different times with different sample sizes and are sufficiency different to confuse the optimizer.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in
    fixed format
    .


"Science is more than a body of knowledge; it is a way of thinking" 
Carl Sagan
    · Share on TwitterShare on Facebook
  • spur230
    spur230 Member Posts: 399
    edited Nov 12, 2009 2:03PM
    Thanks you for you prompt input.
    · Share on TwitterShare on Facebook
This discussion has been closed.