Discussion :

[tibal]

Bonjour,

J'aimerai bien comprendre comment Oracle calcule la densité d'une colonne d'une table.

J'ai lu la doc de Marius : http://marius-nitu.developpez.com/tu...cule-cout/#LII

Le problème est que la formule qu'il fourni :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1/NDV = densité avec NDV est le nombre des valeurs distinctes de la colonne

ne correspond pas à la colonne DENSITY de la vue USER_TAB_COL_STATISTICS après passage des statistiques avec un estimate_percent => 100

Quelqu'un a t'il une petite idée ?

[pachot]

Bonjour,
Est-ce que tu peux donner un exemple (avec des valeurs).
Est-ce que tu comptes les NULL comme valeurs distinctes ou non ?
Franck.

[tibal]

Bonjour Franck,

Oui, en fait j'ai créé une table, ses indexes et passé les stats (voir le code ci-dessous) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
CREATE TABLE t
AS
SELECT
   rownum AS id,
   round(dbms_random.normal*1000) AS val1,
   100+round(ln(rownum/3.25+2)) AS val2,
   100+round(ln(rownum/3.25+2)) AS val3,
   dbms_random.string('p',250) AS pad
FROM 
   all_objects
WHERE 
   rownum <= 1000
ORDER BY 
   dbms_random.value;
 
 
UPDATE 
   t 
SET
   val1 = NULL 
WHERE 
   val1 < 0;
 
 
ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);
 
 
CREATE INDEX t_val1_i ON t (val1);
 
 
CREATE INDEX t_val2_i ON t (val2);
 
 
BEGIN
  dbms_stats.gather_table_stats(ownname          => user,
                                tabname          => 'T',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size skewonly',
                                cascade          => TRUE);
END;
/

Là, j'interroge la vue user_tab_col_statistics :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
set lines 180 pages 10000
 
column name       format a10
column #dst       format 999,999,999
column low_value  format a10
column high_value format a10
column dens       format 0.99999
column #null      format 999,999
column avglen     format 999,999
 
SELECT
   column_name AS "NAME",
   num_distinct AS "#DST",
   low_value,
   high_value,
   density AS "DENS",
   num_nulls AS "#NULL",
   avg_col_len AS "AVGLEN",
   histogram,
   num_buckets AS "#BKT"
FROM 
   user_tab_col_statistics
WHERE 
   table_name = 'T';

dont voici le résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
NAME               #DST LOW_VALUE  HIGH_VALUE     DENS    #NULL   AVGLEN HISTOGRAM             #BKT
---------- ------------ ---------- ---------- -------- -------- -------- --------------- ----------
ID                1,000 C102       C20B        0.00100        0        4 NONE                     1
VAL1                459 C103       C2205E      0.00233      489        3 HEIGHT BALANCED        254
VAL2                  6 C20202     C20207      0.00050        0        4 FREQUENCY                6
VAL3                  6 C20202     C20207      0.00050        0        4 FREQUENCY                6
PAD               1,000 20206E204A 7E57353D32  0.00100        0      251 HEIGHT BALANCED        254
                        7851557539 696E245C28
                        726E687A5E 5461347B75
                        293F32264A 7927635920
                        69777B7C63 67595A7962
                        69725B2464 602A263336
                        242D       3970

Alors que si j'utilise pour la densité 1 / Valeurs non NULL, j'obtiens le résultat suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select
   1 / count(distinct id) dst_id,
   1 / count(distinct val1) dst_val1,
   1 / count(distinct val2) dst_val2,
   1 / count(distinct val3) dst_val3,
   1 / count(distinct pad) dst_pad
from
t
where
   id is not null
or
   val1 is not null
or
   val2 is not null
or
   val3 is not null
or
   pad is not null
/

Résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
    DST_ID   DST_VAL1   DST_VAL2   DST_VAL3    DST_PAD
---------- ---------- ---------- ---------- ----------
      ,001 ,002178649 ,166666667 ,166666667       ,001

La formule densité = 1 / Valeurs non NULL est pourtant confirmée par Tom Kyte : http://asktom.oracle.com/pls/asktom/f?p=100:11:0:::11_QUESTION_ID:2969235095639

La, je ne vois pas d'ou provient cette différence.

[pachot]

D'après le lien asktom et la note metalink correspondante, lorsqu'il y a des histograms:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
 Density =  Number of non-popular values
               ----------------------------
                  total number of values

Car la densité n'est utilisée que calculer la selectivité des valeurs qui se retrouvent que dans une seule tranche de l'histogramme.

[tibal]

A quoi correspond

Number of non-popular VALUES

, et qu'entends tu par

une seule tranche de l'histogramme ?

Merci pour tes lumières.

[mnitu]

La formule en question est valable quand les histogrammes ne sont pas présents, l’article le précise. Join over histograms écrit par Alberto del’Era donne plus des détails.

[tibal]

Merci Marius, mais je ne sais toujours pas à quoi correspond exactement

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Number of non-popular VALUES

[mnitu]

Merci Marius, mais je ne sais toujours pas à quoi correspond exactement

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Number of non-popular VALUES

J’ai plutôt séché les cours des statistiques mais en gros voilà ce que j’ai compris.
Il y a 10 billes avec les valeurs suivantes 1, 2, 3, 4, 9, 10 et quatre fois 5. Il y a 5 seaux et chaque seau peut contenir seulement 2 billes. Les seaux sont remplis de la façon suivante :

• S1 contient les billes numérotées 1 et 2
• S2 contient les 2 billes numérotées 3 et 4
• S3 contient deux billes numérotées 5
• S4 contient deux billes numérotées 5
• S5 contient les billes numérotées 9 et 10

On peut constater que les billes numérotés 5 se trouvent dans deux seaux différentes : S3 et S4. La valeur 5 est une valeur populaire. Les autres valeurs sont des valeurs non-populaires.

[pachot]

excellent l'explication.

[Marc Musette]

dans le cadre d'un frequency histogram, la densité est égale à 1/(2*nombre_de_rows), ce qui explique le .0005 (1/2000)

les concepts de valeurs populaires n'entrent en ligne de compte que pour les HB histograms et la formule de la densité se complique (somme des carrés de la fréquence des valeurs NON populaires / (nbre de rows not null * nbre de rows non populaires)

si tu souffres d'insomnies, tu peux toujours lire le brevet déposé à ce sujet (http://www.freepatentsonline.com/6732085.html)

ou compter les moutons (c'est plus sain je pense ;o)

[tibal]

Marius, merci pour ton explication simple et claire.

Marc,

Pourtant je n'obtiens pas ces valeurs là :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
NAME               #DST LOW_VALUE  HIGH_VALUE     DENS    #NULL   AVGLEN HISTOGRAM             #BKT
---------- ------------ ---------- ---------- -------- -------- -------- --------------- ----------
VAL2                  6 C20202     C20207      0.00050        0        4 FREQUENCY                6
VAL3                  6 C20202     C20207      0.00050        0        4 FREQUENCY                6

Même en divisant par deux :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
    DST_ID   DST_VAL2   DST_VAL3
---------- ---------- ----------
      ,001 ,083333333 ,083333333

D'ou peut provenir cette différence ?

[Marc Musette]

1/(2*1000)=0.0005

c'est bien le nombre de rows et non le nombre de valeurs distinctes (1/2*6)

[tibal]

Merci Marc.

Je cherche maintenant pour les HEIGHT BALANCED où Oracle s'appuie sur un échantillonage de valeur lorsque la colonne en contient beaucoup trop.