Odd join selectivity

Good day, List,

I have been scratching my head, reading books (eg, page 288-291 of
Jonathan's CBO book) and I still feel like the answer is just beyond my
grasp.

Query:

select /*+ gather_plan_statistics */
 count(*)
from
 FAMIS_CP_BUDGET_CATEGORY cat, FAMIS_CP c, FAMIS_CP_BUDGET_DETAIL  b_d
where
 "C"."CP_NUMBER"="B_D"."CP_NUMBER"
AND "C"."ORG_ID"="B_D"."ORG_ID"
AND "B_D"."BUDGET_CATEGORY"="CAT"."BUDGET_CATEGORY"
AND "B_D"."ORG_ID"="CAT"."ORG_ID"
and "C"."CP_TYPE"="CAT"."CP_TYPE"
AND "C"."ORG_ID"="CAT"."ORG_ID"
/

note: the quotes came out of dbms_xplan.

The plan:

-------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                       | Starts | E-Rows
| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE      |                            |      1 |      1
|      1 |00:00:00.71 |    1726 |
|*  2 |   HASH JOIN          |                            |      1 |  54629|
273K|00:00:00.84 |    1726 |
|*  3 |    HASH JOIN         |                            |      1 |    852
|    852 |00:00:00.01 |      32 |
|   4 |     INDEX FULL SCAN  | FAMIS_CP_BUDGET_CAT_CAT_UN |      1 |      5
|      5 |00:00:00.01 |       1 |
|   5 |     TABLE ACCESS FULL| FAMIS_CP                   |      1 |    852
|    852 |00:00:00.01 |      31 |
|   6 |    TABLE ACCESS FULL | FAMIS_CP_BUDGET_DETAIL     |      1 |
273K|    273K|00:00:00.01 |    1694 |
-------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C"."CP_NUMBER"="B_D"."CP_NUMBER" AND
"C"."ORG_ID"="B_D"."ORG_ID" AND
              "B_D"."BUDGET_CATEGORY"="CAT"."BUDGET_CATEGORY" AND
"B_D"."ORG_ID"="CAT"."ORG_ID")
   3 - access("C"."CP_TYPE"="CAT"."CP_TYPE" AND "C"."ORG_ID"="CAT"."ORG_ID")

The 10053:

Using concatenated index cardinality for table FAMIS_CP
Revised join sel:2.3474e-04 = 1.0170e-04 * (1/852.00) * (1/5.0848e-04)
Join Card:  54629.24 = outer (852.00) * inner (273146.00) * sel (2.3474e-04)
Join Card - Rounded: 54629 Computed: 54629.24

The Question:
Where is 1.0170e-04 coming from? 1/852 is the density for CP_NUMBER, and
1/5.0848e-04 is the density for ORG_ID (both on FAMIS_CP), but I cannot for
the life of me figure out 1.0170e-04. I have to assume it is combined join
bit for the other table columns (B_D.BUDGET_CATEGORY and B_D.ORG_ID, density
= 1.8305E-06 for each) but am not certain at all. I do not believe this is
an issue of transitive closure (but could be wrong). Am I missing something
obvious?

Environment:
Oracle 10.2.0.2 on Solaris 8. Computed stats on all tables with default
method_opt.


Any and all help would be much appreciated!


-- 
Charles Schultz

Other related posts: