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