I am having trouble trying to determine how Oracle is computing the cardinality shown below, please note that this is all part of a larger SQL statement, but I'm purely interested in the computation of the cardinality of the table shown in the 10053 trace:
SQL of interest:
select
mid.doc_number,
mid.doc_type,
mid.doc_rev,
mid.doc_change,
mid.title,
mid.status_code,
mid.issue_date
from
mnt_i_doc mid
where
mid.status_code = 'SI' and
mid.doc_type in ('AG', 'ADS') and
not exists
(select
null
from
mntn_pcomp mp,
mnt_r mr
where
mp.doc_number = mid.doc_number and
mp.doc_type = mid.doc_type and
mr.mr_id = mp.mr_id and
mr.ver_num = mp.ver_num and
mr.status_code = 'VO')
Portion of explain plan:
...
| 5 | TABLE ACCESS BY INDEX ROWID | MNTN_PCOMP | 81 | 1539 | 3 | 00:00:01 |
| 6 | INDEX RANGE SCAN | MNTN_PCOMP#IE3 | 68 | | 1 | 00:00:01 |
...
Predicate Information:
----------------------
...
3 - filter((INTERNAL_FUNCTION("MID"."DOC_TYPE") AND "MID"."STATUS_CODE"='SI'))
6 - access("MP"."DOC_NUMBER"=:B1 AND "MP"."DOC_TYPE"=:B2)
...
Piece of 10053 trace:
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#20): DOC_NUMBER(VARCHAR2)
AvgLen: 7.00 NDV: 7253 Nulls: 731186 Density: 1.7065e-004
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 208
Column (#21): DOC_TYPE(VARCHAR2)
AvgLen: 4.00 NDV: 19 Nulls: 731186 Density: 6.1240e-008
Histogram: Freq #Bkts: 19 UncompBkts: 4902 EndPtVals: 19
Table: MNTN_PCOMP Alias: MP
Card: Original: 9001968 Rounded: 81 Computed: 80.85 Non Adjusted: 80.85
My Oracle version is 10.2.0.4.
As far as the predicate is concerned the only conditions against this table are JOIN conditions, no other predicates are specified against this table.
I have Jonathan Lewis' CBO Fundamentals book and I have been studying it closely. However, I am not able to gather enough information from the histogram chapters to be able to accurately reproduce this cardinality.
If you need me to post the histograms I can (they are kind of lengthy).
Thanks!
Edited by: Centinul on May 15, 2009 9:54 AM
Added extra information...