{"id":801,"date":"2017-01-16T15:22:17","date_gmt":"2017-01-16T15:22:17","guid":{"rendered":"http:\/\/blog.tiran.info\/?p=801"},"modified":"2017-01-16T15:22:17","modified_gmt":"2017-01-16T15:22:17","slug":"fonctions-approximatives-oracle-12-2-2","status":"publish","type":"post","link":"https:\/\/blog.tiran.stream\/?p=801","title":{"rendered":"Fonctions approximatives Oracle 12.2 (#2)"},"content":{"rendered":"

L’objectif de cet article est de tester les nouvelles fonctions d’agr\u00e9gation approximatives offertes par Oracle 12cR2<\/a>. On utilisera les donn\u00e9es charg\u00e9es\u00a0dans le billet pr\u00e9c\u00e9dent<\/a>.<\/p>\n

Avec la version 12.2, la fonction APPROX_COUNT_DISTINCT<\/a> (introduite en\u00a012.1.0.2)\u00a0est compl\u00e9t\u00e9e par APPROX_COUNT_DISTINCT_DETAIL, APPROX_COUNT_DISTINCT_AGG et TO_APPROX_COUNT_DISTINCT<\/a> qui offrent\u00a0notamment la possibilit\u00e9 de r\u00e9aliser des agr\u00e9gations hi\u00e9rarchiques.<\/p>\n

Cette fonction de comptage s’appuie sur\u00a0l’algorithme Hyperloglog<\/a>\u00a0qui permet d’obtenir des gains de performance impressionnants par rapport \u00e0 un comptage exact mais au prix\u00a0d’une pr\u00e9cision approximative (erreur de comptage g\u00e9n\u00e9ralement inf\u00e9rieure \u00e0\u00a05%).<\/p>\n

Dans ce billet, l’id\u00e9e est d’estimer le nombre de contributeurs \u00e0 Wikipedia<\/a> par p\u00e9riodes (mois, ann\u00e9e etc…). Le dataset utilis\u00e9 est relativement volumineux 77 millions de lignes (~4GB).<\/p>\n

Ci-dessous, on calcule le nombre de contributeurs distincts sur l’ensemble du dataset (ann\u00e9es 2005 \u00e0 2016). On emploie d’abord un comptage approximatif puis un comptage exact:<\/p>\n

SQL> SELECT banner FROM v$version;\n\nBANNER\n--------------------------------------------------------------------------------\nOracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production\nPL\/SQL Release 12.2.0.1.0 - Production\nCORE    12.2.0.1.0      Production\nTNS for Linux: Version 12.2.0.1.0 - Production\nNLSRTL Version 12.2.0.1.0 - Production\n\nSQL> SET TIMING ON\nSQL>\nSQL> SELECT approx_count_distinct (uname) FROM log_contribs;\n\nAPPROX_COUNT_DISTINCT(UNAME)\n----------------------------\n                    30284800\n\nElapsed: 00:00:09.16\nSQL> SELECT a.name, b.VALUE\n  2    FROM v$statname a, v$mystat b\n  3   WHERE a.statistic# = b.statistic# AND a.name = 'session pga memory max';\n\nNAME                                                                  VALUE\n---------------------------------------------------------------- ----------\nsession pga memory max                                              4262248\n\nElapsed: 00:00:00.03\nSQL>\nSQL> SELECT COUNT (DISTINCT uname) FROM log_contribs;\n\nCOUNT(DISTINCTUNAME)\n--------------------\n            28839444\n\nElapsed: 00:00:50.40\nSQL> SELECT a.name, b.VALUE\n  2    FROM v$statname a, v$mystat b\n  3   WHERE a.statistic# = b.statistic# AND a.name = 'session pga memory max';\n\nNAME                                                                  VALUE\n---------------------------------------------------------------- ----------\nsession pga memory max                                           1733560680\n\nElapsed: 00:00:00.03\nSQL>\n<\/pre>\n
On peut voir que l’erreur d’approximation n’est que de 4.7%<\/strong> (100*(30284800-28839444)\/30284800) alors qu’on obtient une diminution de 82% du temps de calcul<\/strong> (100*(50-9)\/50).
\nEncore mieux: le besoin m\u00e9moire du comptage approximatif ne repr\u00e9sente qu’une portion infinit\u00e9simale de celui li\u00e9 au comptage exact: 4MB contre 1.7GB<\/strong>!<\/p>\n
Cette approche est tr\u00e8s prometteuse lorsqu’on travaille sur des datasets massifs avec des exigences de pr\u00e9cision pouvant s’accommoder d’une erreur de l’ordre de 5%.<\/p>\n
Outre le gain de performance pur, Oracle 12.2 permet gr\u00e2ce aux fonctions APPROX_COUNT_DISTINCT_DETAIL<\/a>\u00a0& APPROX_COUNT_DISTINCT_AGG<\/a> de proc\u00e9der \u00e0 des agr\u00e9gations hi\u00e9rarchiques (le d\u00e9tail d’une agr\u00e9gation peut \u00eatre recombin\u00e9 pour constituer une agr\u00e9gation plus large).<\/p>\n
C’est plus facile \u00e0 expliquer sur un exemple\u00a0; consid\u00e9rons les donn\u00e9es suivantes:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
CATEGORIE<\/td>\nID<\/td>\n<\/tr>\n
1<\/td>\n1<\/td>\n<\/tr>\n
1<\/td>\n2<\/td>\n<\/tr>\n
1<\/td>\n3<\/td>\n<\/tr>\n
1<\/strong><\/td>\n4<\/strong><\/td>\n<\/tr>\n
1<\/strong><\/td>\n5<\/strong><\/td>\n<\/tr>\n
2<\/strong><\/td>\n4<\/strong><\/td>\n<\/tr>\n
2<\/strong><\/td>\n5<\/strong><\/td>\n<\/tr>\n
2<\/td>\n6<\/td>\n<\/tr>\n
2<\/td>\n7<\/td>\n<\/tr>\n
2<\/td>\n8<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Les ID 4 et 5 existent \u00e0 la fois pour les valeurs de CATEGORIE 1 et 2.<\/p>\n
Si je souhaite connaitre le nombre d’ID distincts par cat\u00e9gorie, je peux ex\u00e9cuter la requ\u00eate suivante:<\/p>\n
select CATEGORIE, count(distinct ID) cd_ID from T1 group by CATEGORIE;<\/em><\/p>\n\n\n\n\n\n
CATEGORIE<\/td>\ncd_ID<\/td>\n<\/tr>\n
1<\/td>\n5<\/td>\n<\/tr>\n
2<\/td>\n5<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Maintenant, si je souhaite connaitre le nombre d’ID distinct globalement (c’est \u00e0 dire, toutes cat\u00e9gories confondues), il se trouve que le r\u00e9sultat ne peut pas \u00eatre d\u00e9duit du r\u00e9sultat de la requ\u00eate pr\u00e9c\u00e9dente. En effet, le nombre de valeurs distinctes globalement (8) ne correspond pas \u00e0 la somme des valeurs distinctes de chaque cat\u00e9gorie (5+5). Pour obtenir le r\u00e9sultat, cela oblige \u00e0 l’ex\u00e9cution d’une nouvelle requ\u00eate:<\/p>\n
select count(distinct ID) cd_ID from T1;<\/em><\/p>\n
 <\/p>\n
Ici, le dataset est minuscule mais quand on doit r\u00e9aliser ce genre d’agr\u00e9gations \u00e0 plusieurs niveaux de d\u00e9tail sur des milliards de lignes, la r\u00e9p\u00e9tition de requ\u00eates s’av\u00e8re prohibitive.<\/p>\n
C’est dans ce genre de contexte que la fonction d’agr\u00e9gation approximative APPROX_COUNT_DISTINCT_DETAIL<\/a> prend tout son sens. Elle permet de fournir un r\u00e9sultat d’agr\u00e9gation interm\u00e9diaire (dans un format binaire) \u00ab\u00a0r\u00e9-agr\u00e9geable\u00a0\u00bb \u00e0 un niveau moins fin.<\/p>\n
 <\/p>\n
En reprenant l’exemple pr\u00e9c\u00e9dent, pour connaitre le nombre d’ID distinct par cat\u00e9gorie, on peut stocker le r\u00e9sultat de la requ\u00eate \u00e0 l’aide d’une vue mat\u00e9rialis\u00e9e:<\/p>\n
create materialized view MV_CNT_CAT as select CATEGORIE, approx_count_distinct_detail(ID) cd_ID_bin from T1 group by CATEGORIE;<\/em><\/p>\n
La MView pr\u00e9c\u00e9dente est alors exploitable directement via la fonction TO_APPROX_COUNT_DISTINCT<\/a>:<\/p>\n
select CATEGORIE, to_approx_count_distinct(cd_ID_bin) from MV_CNT_CAT group by CATEGORIE;<\/em><\/p>\n
Mais, avec APPROX_COUNT_DISTINCT_AGG<\/a>, on peut aussi proc\u00e9der \u00e0 une r\u00e9-aggregation pour obtenir le nombre (approch\u00e9) d’ID toute cat\u00e9gories confondues:<\/p>\n
select to_approx_count_distinct(APPROX_COUNT_DISTINCT_AGG(cd_ID_bn)) from MV_CNT_CAT;<\/em><\/p>\n
On \u00e9vite ainsi de re-balayer la table source!<\/p>\n
 <\/p>\n
Faisons un petit essai sur la table LOG_CONTRIBS:\u00a0je souhaite connaitre le nombre de contributeurs distincts \u00e0 Wikipedia par mois et par ann\u00e9e. Avec un comptage exact j’aurai normalement recours \u00e0 deux vues mat\u00e9rialis\u00e9es:<\/p>\n
SQL> CREATE MATERIALIZED VIEW MV_CONTRIB_AN_MOIS_EXACT\n  2  AS\n  3        SELECT EXTRACT (YEAR FROM tmstamp) an,\n  4               EXTRACT (MONTH FROM tmstamp) mois,\n  5               COUNT (DISTINCT uname)     cd\n  6          FROM log_contribs\n  7      GROUP BY EXTRACT (YEAR FROM tmstamp), EXTRACT (MONTH FROM tmstamp);\n\nMaterialized view created.\n\nElapsed: 00:01:26.80\nSQL>\nSQL> CREATE MATERIALIZED VIEW MV_CONTRIB_AN_EXACT\n  2  AS\n  3        SELECT EXTRACT (YEAR FROM tmstamp) an,\n  4               COUNT (DISTINCT uname)     cd\n  5          FROM log_contribs\n  6      GROUP BY EXTRACT (YEAR FROM tmstamp);\n\nMaterialized view created.\n\nElapsed: 00:01:06.32\nSQL>\nSQL> BEGIN\n  2      DBMS_MVIEW.refresh ('MV_CONTRIB_AN_MOIS_EXACT,MV_CONTRIB_AN_EXACT', 'C');\n  3  END;\n  4  \/\n\nPL\/SQL procedure successfully completed.\n\nElapsed: 00:02:39.87\nSQL>\n<\/pre>\n
Avec une approche bas\u00e9e sur les fonctions approximatives, on cr\u00e9\u00e9e une MView au niveau de d\u00e9tail le plus fin en utilisant APPROX_COUNT_DISTINCT_DETAIL:<\/p>\n
SQL> CREATE MATERIALIZED VIEW MV_CONTRIB_AN_MOIS_APPROX_DET\n  2  AS\n  3        SELECT EXTRACT (YEAR FROM tmstamp)        an,\n  4               EXTRACT (MONTH FROM tmstamp)       mois,\n  5               approx_count_distinct_detail (uname) acdd\n  6          FROM log_contribs\n  7      GROUP BY EXTRACT (YEAR FROM tmstamp), EXTRACT (MONTH FROM tmstamp);\n\nMaterialized view created.\n\nElapsed: 00:00:52.21\nSQL>\nSQL> desc MV_CONTRIB_AN_MOIS_APPROX_DET\n Name                                      Null?    Type\n ----------------------------------------- -------- ----------------------------\n AN                                                 NUMBER\n MOIS                                               NUMBER\n ACDD                                               BLOB\n\nSQL>\n<\/pre>\n
La MView ainsi construite n’est pas directement utilisable, les infos de comptages sont dans un champ BLOB. En revanche, on peut utiliser cette MView comme source de MViews d\u00e9pendantes:<\/p>\n
SQL> CREATE MATERIALIZED VIEW MV_CONTRIB_AN_MOIS_APPROX\n  2  AS\n  3      SELECT an, mois, to_approx_count_distinct (acdd) acd\n  4        FROM MV_CONTRIB_AN_MOIS_APPROX_DET;\n\nMaterialized view created.\n\nElapsed: 00:00:00.28\nSQL>\nSQL> CREATE MATERIALIZED VIEW MV_CONTRIB_AN_APPROX\n  2  AS\n  3        SELECT an,\n  4               to_approx_count_distinct (approx_count_distinct_agg (acdd)) acd\n  5          FROM MV_CONTRIB_AN_MOIS_APPROX_DET\n  6      GROUP BY an;\n\nMaterialized view created.\n\nElapsed: 00:00:00.11\nSQL>\n<\/pre>\n
La cr\u00e9ation de ces derni\u00e8res est extr\u00eamement rapide dans la mesure ou l’essentiel du travail d’agr\u00e9gation a d\u00e9j\u00e0 \u00e9t\u00e9 r\u00e9alis\u00e9.<\/p>\n
Le refresh de l’ensemble est significativement plus rapide que celui des MViews avec comptage exact:<\/p>\n
SQL> BEGIN\n  2      DBMS_MVIEW.refresh (\n  3          'MV_CONTRIB_AN_MOIS_APPROX_DET,MV_CONTRIB_AN_MOIS_APPROX,MV_CONTRIB_AN_APPROX',\n  4          'C',\n  5          nested   => TRUE);\n  6  END;\n  7  \/\n\nPL\/SQL procedure successfully completed.\n\nElapsed: 00:00:57.34\nSQL>\n<\/pre>\n
On peut alors s’int\u00e9resser \u00e0 la pr\u00e9cision des donn\u00e9es approch\u00e9es. Afin d’obtenir des \u00e9l\u00e9ments statistiques sur l’erreur d’approximation, j’utilise ma fonction stat_summary pr\u00e9sent\u00e9e dans un billet pr\u00e9c\u00e9dent<\/a>:<\/p>\n
SQL> set long 10000\nSQL> set longc 10000\nSQL> set pages 0\nSQL> set timing off\nSQL> CREATE OR REPLACE VIEW err_approx\n  2  AS\n  3      SELECT ROUND (100 * (ABS (cd - acd) \/ cd), 2) pct\n  4        FROM MV_CONTRIB_AN_MOIS_APPROX a, MV_CONTRIB_AN_MOIS_EXACT b\n  5       WHERE a.an = b.an AND a.mois = b.mois;\n\nView created.\n\nSQL>\nSQL> SELECT stat_summary ('err_approx', 'pct') FROM DUAL;\n\nC##RAF.ERR_APPROX [PCT]\n----------------------\n\nN: 145\nMin\/Max: 0\/4.56 - Delta: 4.56\nVar.: 1.298825 - e.t.: 1.13966\n\nQuantiles\n----------------------\n     5%: .112\n    25%: .55\nMediane: 1.28\n    75%: 2.19\n    95%: 3.856\n\nPrincipale(s) modalite(s):\n   [1]: .46\n   [2]: 1.33\n   [3]: .03\n   [4]: 1.83\n   [5]: .63\n\nCinq valeurs les plus grandes [Outliers (*) >4.65]:\n   [1]: 4.56\n   [2]: 4.45\n   [3]: 4.4\n   [4]: 4.33\n   [5]: 4.28\n\nCinq valeurs les plus petites [Outliers (*) <-1.91]:\n   [5]: .04\n   [4]: .03\n   [3]: .03\n   [2]: .03\n   [1]: 0\nSQL>\n<\/pre>\n
L’erreur d’approximation reste syst\u00e9matiquement sous la barre des 5%. On a m\u00eame 95% des approximations pr\u00e9sentant une erreur inf\u00e9rieure \u00e0 3.9%<\/p>\n
La pr\u00e9cision des approximations est encore meilleure pour l’estimation ann\u00e9e par ann\u00e9e (entre 0.03% et 2.18%):<\/p>\n
SQL> set pages 50\nSQL>   SELECT a.an, ROUND (100 * (ABS (cd - acd) \/ cd), 2) pct\n  2      FROM MV_CONTRIB_AN_APPROX a, MV_CONTRIB_AN_EXACT b\n  3     WHERE a.an = b.an\n  4  ORDER BY 2 DESC;\n\n        AN        PCT\n---------- ----------\n      2010       2.18\n      2011       2.16\n      2012        1.9\n      2013          1\n      2014        .79\n      2016        .65\n      2004         .4\n      2007        .35\n      2005        .31\n      2009        .28\n      2008        .26\n      2006         .2\n      2015        .03\n\n13 rows selected.\n\nSQL>\n<\/pre>\n
Dans les situation ou un comptage approximatif est suffisant, l’utilisation de ces fonctions pr\u00e9sente de s\u00e9rieux avantages: gain de performance, faible besoin m\u00e9moire, hi\u00e9rarchie d’agr\u00e9gation r\u00e9utilisable. Le param\u00e8tre d’initialisation APPROX_FOR_COUNT_DISTINCT<\/a> permet en outre de convertir automatiquement les appels COUNT(DISTINCT xxx) en APPROX_COUNT_DISTINCT(xxx)!<\/p>\n","protected":false},"excerpt":{"rendered":"
L’objectif de cet article est de tester les nouvelles fonctions d’agr\u00e9gation approximatives offertes par Oracle 12cR2. On utilisera les donn\u00e9es<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"colormag_page_container_layout":"default_layout","colormag_page_sidebar_layout":"default_layout","footnotes":""},"categories":[6,15],"tags":[],"class_list":["post-801","post","type-post","status-publish","format-standard","hentry","category-oracle","category-statistique-exploratoire"],"_links":{"self":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/801","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=801"}],"version-history":[{"count":0,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/801\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=801"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=801"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=801"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}