Re: Join cardinality and query tuning. (join selectivity works!)
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 13 Nov 2004 21:26:24 +0100
Hi Charu,
There are two views on your problem there: a theoretical and a practical
Theory first..
I suppose the formula you applied to calculate the join cardinality
(Metalink 68992.1; popularised by the Wolfgang's 10053 paper) is not general
enough. The formula works fine for join cardinality (NOT for join
selectivity!) only in case there are no additional where conditions in the
join (i.e. both FF's are equal to 1).
Let's illustrate it on a simple example:
Select * from A, B where A.id = B.id;
A num_rows = 1000 /* unique key */
A.id NDV = 1000; A.id NUM_NULLS = 0
B num_rows = 1000 /* mod(x,200) with 100 NULLs */
B.id NDV = 200; B.id NUM_NULLS = 100
CBO (I tested with 9.2.0.5) gives
Join_selectivity = 1/1000 --- 1 / max (A.id NDV, B.id NDV)
Join cardinality = 1/1000 * (1000 - 0) * --- A num_rows - A.id NUM_NULLS
(1000 - 100) --- B num_rows -
B.id NUM_NULLS
= 900
Compare with the result of the formel:
Join_selectivity = 1/ max (A.id NDV, B.id NDV) *
[(A num_rows - A.id NUM_NULLS)/ A num_rows] *
[(B num_rows - B.id NUM_NULLS)/ B num_rows]
In this case you get 9/10000, this is a difference to CBO, but the join
cardinality.
Join_cardinality = join_selectivity * 1000 * 1000 = 900
. is correct again!
In your case as you use constraint on W_LOW_D with VAL IN ('Save-No',.) the
formula must be extended. In my opinion (with no guarantee) the more general
form of this formula is as follows:
Join_cardinality = join_selectivity * cardinality_row_set_1 *
cardinality_row_set_2
where
Cardinality_row_set = (num_distinct - num_nulls) * FF
and (here the tricky part)
join_selectivity = 1 / MAX(NDV Table 1, NDV table 2) --- nothing new
but sometimes
join_selectivity = 1 / MIN(NDV Table 1, NDV table 2) --- use MIN instead of
MAX
and sometimes
join_selectivity = even something else
(This is simple a result of observation, by no means a general definition)
In your case was chosen the MIN option. Consider:
Cardinality_row_set_W_LOW_D = (24410 -0) * 2/9478 = 5
(The density of VAL is applied twice in FF as there are two members in IN
list)
Cardinality_row_set_W_ACTIVITY_F = (18340960 - 13414260) * 1 = 4926K
join_selectivity = 1/13 (the MIN NDV was selected to calculate join
selectivity)
join_cardinality = 5 * 4926K * 1/13 =~ 1900K
HTH, but as I already said (and you discovered in between) the join
cardinality is not the problem here. Also independent of the definitive
variation of the formula, in reality (as a result of the statement
execution) you get something very distinctly different from the prediction
(except for some very trivial cases).
On the practical part ..
1) please check the table and index statistics - I suppose they could be out
of sync. Compare the NDV for column ROW_WID (=24410) with DK in index
W_LOW_D_P1 (=21100).
2) This is possible more critical for the second table, where in index
W_ACTIVITY.. DK = 5 (contrary to NDV 13 for W_ACTIVITY_F.X_BT_OUTCO..).This
could have influence on CBO.
Please make sure that the index statistics
a) are fresh or
b) are manipulated as required
before complaining with execution plans.
Regards
Jaromir D.B. Nemec
http://www.db-nemec.com
----- Original Message -----
From: "Charu Joshi" <joshic@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Cc: <lex.de.haan@xxxxxxxxxxxxxx>
Sent: Monday, November 08, 2004 11:10 AM
Subject: RE: Join cardinality and query tuning.
Hi all,
Apologies for the delay in getting back. Had a tough time getting the 10053
trace (it being production environment). Ultimately I replicated the
So the join selectivity = (1/MAX(24410,13))* ( (18340960 -
13414260)/18340960)*((24410 - 0)/24410)
= 1.10043976277393E-5
and the join cardinality = 1.10043976277393E-5 * 5 * 4926K -- Please refer
the plan.
= 272
Yet the CBO calculates it as 1952K.
I hope I have picked up all the important statistics. If I have missed out
on something then please let me know. To highlight the most significant
statistic from the trace:
Join cardinality: 1952063 = outer (5) * inner (4926700) * sel (7.6923e-02)
[flag=0]
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Re: Join cardinality and query tuning. (join selectivity works!)
