Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

spur230Nov 12 2009 — edited Nov 12 2009
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..
This post has been answered by Jonathan Lewis on Nov 12 2009
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 10 2009
Added on Nov 12 2009
#general-database-discussions, #index
3 comments
516 views