Odd join selectivity
Date: Thu, 6 Mar 2008 10:45:52 -0600
Message-ID: <7b8774110803060845q14a03ee3q2d9ddcea136dd6be@mail.gmail.com>
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 | 852273K| 273K|00:00:00.01 | 1694 |
| 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 |
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 -- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 06 2008 - 10:45:52 CST