| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Join cardinality and query tuning. (join selectivity works!)
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
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 ..
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_at_mahindrabt.com>
To: <oracle-l_at_freelists.org>
Cc: <lex.de.haan_at_naturaljoin.nl>
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)
and the join cardinality = 1.10043976277393E-5 * 5 * 4926K -- Please refer the plan.
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]
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Nov 13 2004 - 14:29:39 CST
 |
 |