Oracle FAQ Your Portal to the Oracle Knowledge Grid
 HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Join cardinality and query tuning. (join selectivity works!)

# Re: Join cardinality and query tuning. (join selectivity works!)

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Sat, 13 Nov 2004 21:26:24 +0100
Message-ID: <011101c4c9bf\$0d86c890\$3c02a8c0@JARAWIN>

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_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)

• 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]

```--
http://www.freelists.org/webpage/oracle-l
```
Received on Sat Nov 13 2004 - 14:29:39 CST

Original text of this message

 HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US