Les histogrammes

Le calcul des statistiques est destiné à aider l’optimiseur à trouver le nombre d’enregistrements pour décider du chemin qui choisi pour l’interprétation de la requête. Suivant le mode de stockage dans le dictionnaire de données il existe dans les versions antérieures à Oracle 12c deux types d’histogrammes : FREQUENCY et HEIGHT BALANCED

A partir de la version Oracle 12c le type « HEIGHT BALANCED » est remplacée par deux nouveaux types : HYBRID ou TOP-FREQUENCY

Le type FREQUENCY

Le premier type d’histogramme utilisé par Oracle est « FREQUENCY » qui signifie que chaque bouquet de valeurs (la traduction exacte du terme « BUCKET » est seau le terme de bouquet de valeurs me pares plus approprié en français) contient les fréquences d’apparition d’une valeur unique du champ.

STAGB@rubis>BEGIN

2 DBMS_STATS.GATHER_TABLE_STATS(

3 OWNNAME => USER ,

4 TABNAME => 'IND01',

5 ESTIMATE_PERCENT => NULL,

6 METHOD_OPT =>

7 'FOR COLUMNS ACHETE SIZE 254');

8 END;

9 /

STAGB@rubis>select column_name,num_distinct n,num_buckets b,

2 histogram, sample_size

3 from user_tab_columns

4 where table_name = 'IND01' and column_name = 'ACHETE';

COLUMN_NAME N B HISTOGRAM SAMPLE_SIZE

-------------- ---------- ---------- --------------- -----------

ACHETE 29 29 FREQUENCY 7643024

Chaque fois que vous avez dans le champ « HISTOGRAM » le type « FREQUENCY », le nombre de bouquets de valeurs est égal aux nombres de valeurs distinctes. La collecte des statistiques peut être effectuée avec des histogrammes de ce type uniquement si le nombre de valeurs distinctes du champ est inférieur à « 254 ».

A partir de la version Oracle 12c, le nombre maximal de valeurs distinctes doit être inférieur à « 2048 ».

La requête qui permet de retrouver les bouquets de valeurs est la suivante :

STAGB@rubis> select ACHETE, count(*) as nb from IND01 group by ACHETE order by ACHETE;

ACHETE NB

---------- ----------

1 190179

2 117813

7 190820

8 381809

10 184434

16 444809

26 63663

31 375662

32 456477

33 267282

34 391948

35 247313

37 242517

40 195000

41 313629

55 130891

59 253179

61 323334

67 128654

69 190538

70 328596

73 255803

75 249599

77 188349

80 130498

82 258606

85 317945

88 572168

90 251509

Dans l’image précédente vous pouvez retrouver les « 29 » bouquets de valeurs, chacun représentant la fréquence d’apparition de chaque valeur distincte présente en abscisse que vous retrouvez dans le champ « FREQUENCY » de la requête suivante.

select endpoint_value,endpoint_number,

2 endpoint_number - lag(endpoint_number,1,0)

3 over(order by endpoint_number) frequency

4 from user_tab_histograms

5 where table_name = 'IND01'

6 and column_name = 'ACHETE'

7 order by endpoint_number;

ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY

-------------- --------------- ----------

1 190179 190179

2 307992 117813

7 498812 190820

8 880621 381809

10 1065055 184434

16 1509864 444809

26 1573527 63663

31 1949189 375662

...

STAGB@rubis>set autotrace trace exp

STAGB@rubis>select * from IND01 where ACHETE = 26;

Plan d'exécution

----------------------------------------------------------

Plan hash value: 2750765725

-------------------------------------------

-------------------------------------------

| 0 | SELECT STATEMENT | | 63663 |

|* 1 | TABLE ACCESS FULL| IND01 | 63663 |

-------------------------------------------

Le type HEIGHT BALANCED

Le mode de calcul des histogrammes de ce champ est de type « HEIGHT BALANCED ». Ce type est automatiquement utilisé par Oracle quand le nombre de bouquets de valeurs est inférieur au nombre de valeurs distinctes du champ. Il ne peut créer qu’un nombre limité de bouquets de « 254/2048 » et il faut alors distribuer les fréquences d’apparition des valeurs dans ces bouquets.

Pour comprendre le mode de remplissage des bouquets de valeurs voici un exemple ou le nombre de bouquets est limité à cinq. Ainsi dans notre exemple chaque bouquet de valeurs à une taille égale au cinquième du nombre des enregistrements de la table pour les 29 valeurs distincts de la colonne « ACHETE ». La requête suivante permet d’afficher la dispersion des valeurs distinctes dans les bouquets.

STAGB@rubis> select round(count(*) / 5) "taille bouquet"

2 from ind01;

taille bouquet

--------------

1528605

STAGB@rubis>select ACHETE, b, count(*) n,

2 sum(count(*)) over (partition by ACHETE) n_b,

3 sum(count(*)) over (partition by b

4 order by ACHETE) sc_b

5 from (select ACHETE, ntile(5) over ( order by ACHETE) b

6 from ind01 )

7 group by ACHETE, b;

ACHETE B N N_B SC_B

---------- ---------- ---------- ---------- ----------

1 1 190179 190179 190179

2 1 117813 117813 307992

7 1 190820 190820 498812

8 1 381809 381809 880621

10 1 184434 184434 1065055

16 1 444809 444809 1509864

26 1 18741 63663 1528605

26 2 44922 63663 44922

31 2 375662 375662 420584

32 2 456477 456477 877061

33 2 267282 267282 1144343

34 2 384262 391948 1528605

34 3 7686 391948 7686

35 3 247313 247313 254999

37 3 242517 242517 497516

40 3 195000 195000 692516

41 3 313629 313629 1006145

55 3 130891 130891 1137036

59 3 253179 253179 1390215

61 3 138390 323334 1528605

61 4 184944 323334 184944

67 4 128654 128654 313598

69 4 190538 190538 504136

70 4 328596 328596 832732

73 4 255803 255803 1088535

75 4 249599 249599 1338134

77 4 188349 188349 1526483

80 4 2122 130498 1528605

80 5 128376 130498 128376

82 5 258606 258606 386982

85 5 317945 317945 704927

88 5 572168 572168 1277095

90 5 251509 251509 1528604

Il faut comprendre que dans le cas des histogrammes de type « HEIGHT BALANCED » une valeur distincte de la colonne peut se trouver sur un ou plusieurs bouquets de valeurs à la fois.

La distribution des fréquences dans les bouquets de valeurs de b1÷b5, est présentée dans l’image précédente. Chaque bouquet a une taille prédéfinie qui est « 1/5 » du nombre d’enregistrements distincts de la table. Le premier commence à être rempli par les fréquences d’apparition des valeurs distinctes du champ « ACHEMINE » jusqu’à concurrence de sa taille, par la suite le suivant est rempli de la même manière et ainsi de suite jusqu’à la fin.

La colonne « B » c’est le numéro du bouquet de valeurs. La colonne « N_B » représente le nombre des enregistrements correspondent à la valeur distincte de la colonne « ACHEMINE ». La colonne « N » représente le nombre des enregistrements correspondent à la valeur distincte de la colonne « ACHEMINE » dans le bouquet de valeurs correspondant. La dernière colonne « SC_B » est une somme cumulative dans le bouquet des valeurs des la colonne « N » et montre le mécanisme de remplissage de chaque bouquet de valeurs.

Ce premier exemple n’a le but que de montrer le remplissage de bouquets mais il n’est pas pertinent du point de vue statistiques.

STAGB@rubis> select column_name,num_distinct n,num_buckets b,

2 histogram, sample_size

3 from user_tab_columns where table_name = 'IND01'

4 and column_name = 'ACHETE';

COLUMN_NAME N B HISTOGRAM SAMPLE_SIZE

-------------- ----- --- --------------- -----------

ACHETE 29 5 HEIGHT BALANCED 7643024

Vous pouvez voir que pour le champ « ACHETE » la collecte retrouve bien « 29 » enregistrements tous stockés dans cinq bouquets de valeurs.

STAGB@rubis> select ACHETE, count(*) as nb

2 from IND01 group by ACHETE order by ACHETE;

ACHETE NB

---------- ----------

1 190179

2 117813

7 190820

8 381809

10 184434

16 444809

26 63663

...

STAGB@rubis>set autotrace trace exp

STAGB@rubis>select * from IND01 where ACHETE = 26;

Plan d'exécution

----------------------------------------------------------

Plan hash value: 2750765725

-------------------------------------------

-------------------------------------------

| 0 | SELECT STATEMENT | | 263K|

|* 1 | TABLE ACCESS FULL| IND01 | 263K|

-------------------------------------------

STAGB@rubis>set autotrace off

STAGB@rubis>select trunc(count(*) /

2 count(distinct ACHETE),-3)/1000||'k' r

3 from IND01;

------------------

263k

L’optimiseur dans ce cas utilise un calcul simple pour trouver la valeur moyenne d’enregistrements pour une des valeurs du champ.

Pour le deuxième exemple on augmente le nombre des bouquets de valeurs à 50 pour la colonne « ACHEMINE » qui a 69 valeurs distincts.

STAGB@razvan> select endpoint_value, endpoint_number,

2 endpoint_number - lag(endpoint_number,1,0) over (order by endpoint_number) frequency

3 from user_tab_histograms

4 where table_name = 'IND01' and column_name = 'ACHEMINE' and endpoint_number > 0

5 order by endpoint_number;

ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY

-------------- --------------- ----------

1 0 1

3 1 1

5 2 1

9 3 1

12 4 1

13 5 1

15 6 1

17 7 1

19 8 1

21 11 3

22 13 2

24 14 1

25 15 1

26 18 3

28 19 1

30 20 1

37 21 1

39 22 1

41 23 1

43 24 1

45 25 1

47 26 1

49 27 1

51 28 1

53 29 1

54 30 1

57 31 1

60 32 1

61 33 1

62 34 1

63 35 1

65 36 1

68 37 1

69 40 3

71 41 1

74 42 1

78 43 1

81 44 1

83 45 1

84 46 1

86 47 1

89 48 1

92 50 2

STAGB@razvan>select distinct * from

2 (select t.num_rows "Enregistrements",

3 c.num_distinct "Valeurs",

4 round(t.num_rows/c.num_buckets)"taille bouquet",

5 c.num_buckets "bouqets",

6 round((c.num_buckets -

7 (sum(case frequency when 1 then 0

8 else frequency end ) over ()))

9 *

10 (t.num_rows/c.num_buckets) /

11 (c.num_distinct - sum(case frequency when 1

12 then 0 else 1 end ) over ())) "taille autres"

13 from

14 (select num_rows from user_tab_statistics

15 where table_name = 'IND01') t cross join

16 (select column_name, num_distinct, num_buckets, histogram

17 from user_tab_col_statistics

18 where table_name = 'IND01'

19 and column_name = 'ACHEMINE') c join

20 (select column_name,

21 endpoint_value,

22 endpoint_number,

23 endpoint_number

24 - lag(endpoint_number,1,0)

25 over(order by endpoint_number) frequency

26 from user_tab_histograms

27 where table_name = 'IND01'

28 and column_name = 'ACHEMINE'

29 and endpoint_number > 0

30 order by endpoint_number

31 ) s on (c.column_name=s.column_name));

Enregistrements Valeurs taille bouquet bouqets taille autres

--------------- ---------- -------------- ---------- -------------

7643024 69 152860 50 88372

STAGB@razvan> select round(count(*) / 50) "taille bouquet",

2 round((count(*) / 50)*2,-3)/1000||'k' "x2",

3 round((count(*) / 50)*3,-3)/1000||'k' "x3"

4 from IND01;

taille bouquet x2 x3

-------------- ------ ------

152860 306k 459k

STAGB@razvan>set autotrace trace exp

STAGB@razvan>select * from IND01 where ACHEMINE = 21;

--------------------------------------------

--------------------------------------------

| 0 | SELECT STATEMENT | | 458K |

|* 1 | TABLE ACCESS FULL| IND01 | 458K |

--------------------------------------------

STAGB@razvan>select * from IND01 where ACHEMINE = 22;

--------------------------------------------

--------------------------------------------

| 0 | SELECT STATEMENT | | 305K |

|* 1 | TABLE ACCESS FULL| IND01 | 305K |

--------------------------------------------

STAGB@razvan> select * from IND01 where ACHEMINE = 1;

Plan d'exécution

-------------------------------------------

Plan hash value: 2750765725

-------------------------------------------

-------------------------------------------

| 0 | SELECT STATEMENT | | 88372 |

|* 1 | TABLE ACCESS FULL| IND01 | 88372 |

-------------------------------------------

Le type HYBRID

Le type d’histogramme « HYBRID » est une combinaison de deux types qu’on a vus jusqu'à présent, « HEIGHT BALANCED » et « FREQUENCY ».

Le premier bouquet de valeurs est constitue de la première valeur distincte. Toutes les valeurs qui finissent un bouquet ont une cardinalité connue comme dans le mode « FREQUENCY » pour les autres dispersés dans le bouquet on applique la même règle que pour le type « HEIGHT BALANCED ».

STAGB@rubis>BEGIN

2 DBMS_STATS.GATHER_TABLE_STATS(

3 OWNNAME => USER ,

4 TABNAME => 'IND01',

5 ESTIMATE_PERCENT => NULL,

6 METHOD_OPT =>

7 'FOR COLUMNS ACHETE SIZE 5');

8 END;

9 /

STAGB@rubis> select column_name,num_distinct n,num_buckets b,

2 histogram, sample_size,

3 trunc(sample_size / num_distinct, -3)/1000||'K'

4 "Moyenne"

5 from user_tab_columns

6* where table_name = 'IND01' and column_name = 'ACHETE'

COLUMN_NAME N B HISTOGRAM SAMPLE_SIZE Moyenne

------------ -- -- --------------- ----------- ---------------

ACHETE 29 5 HYBRID 7643024 263K

STAGB@rubis>SELECT ROWNUM BOUQET,

2 ENDPOINT_NUMBER "Taille BOUQET",

3 ENDPOINT_ACTUAL_VALUE "Valeur fin",

4 ENDPOINT_NUMBER -

5 LAG(ENDPOINT_NUMBER,1,0) OVER

6 (ORDER BY ENDPOINT_ACTUAL_VALUE) -

7 ENDPOINT_REPEAT_COUNT "HEIGHT BALANCED",

8 ENDPOINT_REPEAT_COUNT FREQUENCY

9 FROM USER_TAB_HISTOGRAMS

10 WHERE TABLE_NAME = 'IND01'

11 AND COLUMN_NAME = 'ACHETE'

12 ORDER BY ENDPOINT_NUMBER;

BOUQET Taille BOUQET Valeur fin HEIGHT BALANCED FREQUENCY

------- ------------- ---------- --------------- ----------

1 190 179 1 0 190 179

2 2 405 666 32 1 759 010 456 477

3 4 447 425 59 1 788 580 253 179

4 6 501 402 82 1 795 371 258 606

5 7 643 024 90 890 113 251 509

STAGB@rubis>set autotrace trace exp

STAGB@rubis>select * from IND01 where ACHETE = 32;

-------------------------------------------

-------------------------------------------

| 0 | SELECT STATEMENT | | 456K|

|* 1 | TABLE ACCESS FULL| IND01 | 456K|

-------------------------------------------

STAGB@rubis>select * from IND01 where ACHETE = 21;

-------------------------------------------

-------------------------------------------

| 0 | SELECT STATEMENT | | 263K|

|* 1 | TABLE ACCESS FULL| IND01 | 263K|

-------------------------------------------

STAGB@rubis>select * from IND01 where ACHETE = 59;

-------------------------------------------

-------------------------------------------

| 0 | SELECT STATEMENT | | 263K|

|* 1 | TABLE ACCESS FULL| IND01 | 263K|

-------------------------------------------

Le type TOP-FREQUENCY

Le type d’histogramme « TOP-FREQUENCY » est une combinaison de deux types qu’on a vu jusqu'à présent, « HEIGHT BALANCED » et « FREQUENCY » choisi par Oracle dans le cas ou une petite partie des valeurs distinctes occupent une grande volumétrie de la table. La règle est la suivante :

P = (1-(1/n))*100

N est le nombre des valeurs distinctes et P est le pourcentage d’enregistrements qui doivent être occupés par le Top N des valeurs. Ainsi les valeurs du Top N sont présentées dans le format « FREQUENCY » les autres valeurs sont dans le bouquet final en format « HEIGHT BALANCED ».

STAGB@rubis> SELECT RETOURNE,

2 COUNT(*) FREQUENCY,

3 ROUND(RATIO_TO_REPORT(COUNT(*)) OVER() *100,2) "%",

4 SUM(COUNT(*)) OVER( ORDER BY RETOURNE) SC,

5 SUM(COUNT(*)) OVER( ORDER BY RETOURNE) /

6 SUM(COUNT(*)) OVER( ) * 100 "%"

7 FROM IND GROUP BY RETOURNE ORDER BY 1;

RETOURNE FREQUENCY % SC %

---------- ---------- ------- ---------- -------

0 4347020 56.88 4347020 56.88

1 809072 10.59 5156092 67.46

2 1717955 22.48 6874047 89.94

3 764332 10.00 7638379 99.94

4 713 .01 7639092 99.95

5 758 .01 7639850 99.96

6 808 .01 7640658 99.97

7 825 .01 7641483 99.98

8 765 .01 7642248 99.99

9 776 .01 7643024 100.00

STAGB@rubis>BEGIN

2 DBMS_STATS.GATHER_TABLE_STATS(

3 OWNNAME => USER,

4 TABNAME => 'IND',

5 METHOD_OPT => 'FOR COLUMNS RETOURNE SIZE 5');

6 END;

7 /

STAGB@rubis>SELECT COLUMN_NAME, NUM_DISTINCT N,

2 NUM_BUCKETS,HISTOGRAM

3 FROM USER_TAB_COL_STATISTICS

4 WHERE TABLE_NAME = 'IND';

COLUMN_NAM N NUM_BUCKETS HISTOGRAM

---------- ---------- ----------- ---------------

RETOURNE 10 5 TOP-FREQUENCY

STAGB@rubis>select * from ind where RETOURNE = 2;

------------------------------------------

------------------------------------------

| 0 | SELECT STATEMENT | | 1717K|

|* 1 | TABLE ACCESS FULL| IND | 1717K|

------------------------------------------

STAGB@rubis>select * from ind where RETOURNE = 8;

------------------------------------------

------------------------------------------

| 0 | SELECT STATEMENT | | 929 |

|* 1 | TABLE ACCESS FULL| IND | 929 |

------------------------------------------