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

0031A 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

 

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

| Id  | Operation         | Name  | Rows  |

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

|   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

 

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

| Id  | Operation         | Name  | Rows  |

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

|   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;

 

R

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

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;

 

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

| Id  | Operation         | Name  |  Rows  |

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

|   0 | SELECT STATEMENT  |       |   458K |

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

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

 

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

 

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

| Id  | Operation         | Name  |  Rows  |

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

|   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

 

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

| Id  | Operation         | Name  | Rows  |

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

|   0 | SELECT STATEMENT  |       | 88372 |

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

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

0031Le 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;

 

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

| Id  | Operation         | Name  | Rows  |

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

|   0 | SELECT STATEMENT  |       |   456K|

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

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

 

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

 

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

| Id  | Operation         | Name  | Rows  |

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

|   0 | SELECT STATEMENT  |       |   263K|

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

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

 

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

 

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

| Id  | Operation         | Name  | Rows  |

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

|   0 | SELECT STATEMENT  |       |   263K|

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

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

0031Le 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;

 

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

| Id  | Operation         | Name | Rows  |

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

|   0 | SELECT STATEMENT  |      |  1717K|

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

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

 

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

 

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

| Id  | Operation         | Name | Rows  |

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

|   0 | SELECT STATEMENT  |      |   929 |

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

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