Suivre ce blog
Administration Créer mon blog
15 janvier 2014 3 15 /01 /janvier /2014 11:21
Nouvelle année, nouvelles résolutions.
La première : coder un échiquier pour jouer sous SQL*Plus.

J'ai posté le script complet ici : 

1) Le modèle de données

Le modèle de données est simple : c'est la liste des coups joués, ni plus ni moins.

CREATE TABLE cs_mouv (
    x number, 
    y number,
    x_dest number, 
    y_dest number,
    n_mouv number, 
    piece char(1),
    col char(1), 
    move_str varchar2(10),
    fic char(1) default 'N'
);

Pour cela : 
- on enregistre la case de départ (x, y), la case d'arrivée (x_dest, y_dest), la pièce et sa couleur
- le numéro du coup (n_mouv)
- Le flag "fic", comme fictif, permet de décomposer les coups spéciaux en plusieurs mouvements.
Comme le roque, la prise en passant, la promotion...
- Le "move_str" ne sert qu'à conserver la châne saisie par l'utilisateur, pour des raisons pratiques.

2) Reconstituer la position à partir de la liste des coups

Quand on a la liste des coups, et si on n'est pas complètement stupide, on peut en déduire la position.

CREATE OR REPLACE VIEW pieces AS
SELECT x_dest x, y_dest y, max(piece) keep(dense_rank last order by n_mouv) piece
    , max(col) keep(dense_rank last order by n_mouv) col 
FROM (
    SELECT x_dest, y_dest, n_mouv
         , piece 
         , col
    FROM cs_mouv
    WHERE x_dest IS NOT NULL
    UNION ALL
    SELECT x, y, n_mouv
         , null 
         , null
    FROM cs_mouv
    )
GROUP BY x_dest, y_dest
/

Il s'agit là de se baser sur les cases de l'échiquier, et de chercher quelle est la pièce qui s'y situe actuellement.
La première partie de l'UNION ALL liste les pièces arrivant sur une case donnée, la deuxième partie les cases abandonnées... on retient dans tout ça la pièces correspondant au plus récent numéro de coup pour chaque case.

3) Afficher la position

La procédure pkg_csboard.display affiche la position actuelle. La vue "pieces" donnant déjà toutes les positions, il s'agit juste de metre en forme le résultat.
Le paramètre en entrée permet d'orienter l'affichage (noirs ou blancs) par l'inversion du système de coordonnées.

A noter que par défaut, SQL*Plus justifie les "leading spaces" (les premiers espaces à gauche), ce qui fout en l'air les efforts de mise en forme.
Pour empêcher cela, il faut entrer l'option :

SET SERVEROUTPUT ON FORMAT WRAPPED

4) Initialiser l'échiquier

La procécdure pkg_csboard.init supprime la liste des coups, puis insère toutes les pièces dans leur position initiale.


cs1-copie-1.jpg
 

5) Jouer des coups

La procédure cs_move prend en paramètre la chaîne de caractères représentant la position initiale et la destination de la pièce à déplacer, contrôle la validité du coup, l'exécute, puis affiche la position : 

SQL> exec pkg_csboard.csmove('e2e4');

cs1-copie-2.jpg

La sous-procédure parse_move vérifie la validité du coup, et retourne le type de coup effectué (erreur, coup normal, petit roque, ...). J'ai pas eu la motivation d'implémenter la prise en passant et la promotion, mais ça doit pas être bien compliqué.
La sous-procédure exec_move est appelée ensuite pour effectuer l'insertion effective.

SQL> exec pkg_csboard.csmove('oo');

cs1-copie-4.jpg
6) Afficher la liste des coups.

C'est la procédure display_list. J'aurais pu m'amuser à mettre des tirets, mais j'avais la flemme. 

SQL> exec pkg_csboard.display_list;
  1. e2e4    e7e5
  2. g1f3    b8c6
  3. f1c4    g8f6
  4. oo
  
7) Annuler des coups

Comme on a la liste des coups, ce n'est pas bien compliqué non plus de revenir en arrière...
La procédure cancel_move supprime le nombre de coups passés en paramètres, en rassemblant au préalable les coups fictifs avec leur coup réel associé.

SQL> exec pkg_csboard.cancel_move(2);

cs1-copie-5.jpg

Voilà, il resterait encore plein de choses à faire :
- implémenter les coups spéciaux manquants
- vérifier la validité des coups
- insérer le concept de partie
- utiliser si possibles des queues pour que deux personnes puissent jouer "sur le réseau"
- encapsuler ça dans du shell pour pouvoir faire des boucles
- ...

Mais je préfère faire les choses à moitié, c'est mieux.

Partager cet article

Published by Pacman - dans SQL cosmétique
commenter cet article
10 décembre 2013 2 10 /12 /décembre /2013 16:38

Dans l'exemple type des row generators (par exemple ici), on utilise parfois une construction étrange qui est "CONNECT BY level <= N".


C'est certes étrange et abusé, mais ça se conçoit : "connecte moi avec n'importe quoi et récursivement, mais pas plus loin que le 3ème étage".

 

Cela dit, il y a des moments où on veut générer N lignes pour chaque lignes appartenant à une sélection (de plus d'une ligne cette fois).

 

CREATE TABLE test_rowgen AS                

SELECT 'pacmann' c, 3 n FROM DUAL UNION ALL

SELECT 'robert' c, 2 n FROM DUAL UNION ALL 

SELECT 'squelettor' c, 1 n FROM DUAL       

 

Par exemple ici, on voudrait générer 3 lignes pacmann, deux lignes robert, et 1 ligne squelettor.

 

Et là, si on tente : 

 

SELECT *             

FROM test_rowgen     

CONNECT BY level <= n

 

... on obtient toutes les combinaisons possibles récursivement, tant que le niveau est inférieur à "n", ce qui n'est pas bon. La différence avec le générateur à partir de dual, c'est que la ligne de dual ne peut se connecter qu'avec elle-même, alors que là, on a une tripotée d'autres arbres distincts à générer combinatoirement.

 

Si, pour empêcher de connecter n'importe quoi, on tente de lui préciser qu'il doit se connecter qu'avec lui-même... 

 

SELECT c, n          

FROM test_rowgen     

CONNECT BY level <= n

    AND c = prior c  

 

... on obtient : 

ORA-01436: CONNECT BY loop in user data

 

La boucle est détectée au niveau de l'analyse de la requête, et non au niveau de l'exécution (pour preuve la première requête, une autre preuve un peu plus loin dans cet article)

 

Si on ajoute NOCYCLE, on ne va pas bien loin : 

 

SELECT c, n                  

FROM test_rowgen             

CONNECT BY NOCYCLE level <= n

    AND c = prior c          

 

C                   N

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

pacmann             3

robert              2

squelettor          1

 

Bien entendu, NOCYCLE évite l'erreur, mais pour autant, lorsque le cycle est détecté, aucune connexion n'est tentée pour les chemins suspectés d'induire des cycles.

 

Pour cela, des petits génies (dont Laurent Schneider, mais lui seul pourra nous dire s'il l'avait repompé ailleurs à l'époque :)) ont hacké le système et ont tenté : 

 

SELECT c, n                         

FROM test_rowgen                    

CONNECT BY level <= n               

    AND c = prior c                 

    AND prior sys_guid() IS NOT NULL

 

C                   N

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

pacmann             3

pacmann             3

pacmann             3

robert              2

robert              2

squelettor          1

 

Alors oui, ça marche... mais qu'est ce que ça veut dire ?

 

Testons de réécrire un peu la requête : 

WITH u AS (                  

    SELECT c, n, sys_guid() t

    FROM test_rowgen         

    )                        

SELECT *                     

FROM  u                      

CONNECT BY level <= n        

    AND c = prior c          

    AND prior t IS NOT NULL  

 

C                   N T                               

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

pacmann             3 ED2ED377E71B437AE0436203650A0428

pacmann             3 ED2ED377E71D437AE0436203650A0428

pacmann             3 ED2ED377E71F437AE0436203650A0428

robert              2 ED2ED377E721437AE0436203650A0428

robert              2 ED2ED377E723437AE0436203650A0428

squelettor          1 ED2ED377E725437AE0436203650A0428

 

Ca marche !

Mais si on force la matérialisation : 

 

WITH u AS (                                  

    SELECT /*+materialize*/c, n, sys_guid() t

    FROM test_rowgen                         

    )                                        

SELECT *                                     

FROM  u                                      

CONNECT BY level <= n                        

    AND c = prior c                          

    AND prior t IS NOT NULL                  

 

... Paf !

ORA-01436: CONNECT BY loop in user data

 

Et moi, les résultats qui varient en fonction du plan d'exécution... hum. (Je dis ça, mais faudrait encore vérifier dans quel ordre les choses se passent...)

 

Par ailleurs la condition "prior sys_guid() is not null" n'empêche en rien les loop...

 

SELECT c, n                                           

FROM test_rowgen                                 

CONNECT BY prior sys_guid() IS NOT NULL

 

... boucle infinie tuée à la main après plusieurs millions de lignes fetched.

 

Donc voilà, cette astuce est démoniaque et plutôt dangereuse.

 

Je propose donc une méthode encore plus nulle, parce que je le vaux bien : 

 

SELECT c,n                                                                     

FROM (                                                                         

    SELECT c, n, coalesce(sum(n-1)                                             

    over(order by c desc rows between unbounded preceding and 1 preceding),0) p

    FROM test_rowgen                                                           

    )                                                                          

CONNECT BY rownum <= n + p                                                     

 

C                   N

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

squelettor          1

robert              2

pacmann             3

pacmann             3

robert              2

pacmann             3

 

Pourquoi est-ce que ça marche ?

Parce qu'au moment de connecter, on se base sur rownum pour estimer le nombre de connexion qui ont été faites sur les lignes précédentes, et en déduire combien il faut en faire la ligne présente.


Pourquoi est-ce que c'est lamentable et à ne surtout pas refaire ?

Parce que d'une part, je n'ai pas la moindre idée de ce qu'est sensé être l'exécution de CONNECT BY, et (surtout ?) parce qu'on ne programme pas en fonction de l'algorithme interne d'Oracle, qui peut très bien changer d'un patch à l'autre (l'introduction du hash group by en étant certainement le plus bel exemple)

 

Comme dans tout article nihiliste, il faut finir par la clause MODEL (qui marche, n'est pas officiellement prohibée, mais vous mettra à dos vos collègues et votre hiérarchie) : 

 

SELECT c, n                                     

FROM test_rowgen                                

MODEL                                           

PARTITION BY (c c0)                             

DIMENSION BY (1 i)                              

MEASURES (c,n)                                  

RULES (                                         

    c[FOR i FROM 1 TO n[1] INCREMENT 1] = c[1]  

    , n[FOR i FROM 1 TO n[1] INCREMENT 1] = n[1]

    )                                           

 

C                   N

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

robert              2

squelettor          1

robert              2

pacmann             3

pacmann             3

pacmann             3

 

C'est bien la première fois que je trouve MODEL moins obscure qu'une quelconque autre méthode...

Partager cet article

Published by Pacman - dans SQL
commenter cet article
5 décembre 2013 4 05 /12 /décembre /2013 10:53

Pour faire suite à mon article précédent, je vais quand même lâcher une solution viable, spécifique à Oracle : 

 

SELECT ville_etp                                                                

FROM t_entrepot                                                                 

WHERE rayon_ryn IN (SELECT rayon_ryn                                            

                    FROM t_rayon)                                               

GROUP BY ville_etp                                                              

HAVING collect(DISTINCT rayon_ryn) = (SELECT collect(rayon_ryn) FROM t_rayon)   

 

VILLE_ETP

---------

MARSEILLE

TOULOUSE 

 

Collect, à partir de 10g, permet de créer un collection par agrégation. 

Dans une collection, il n'y a pas de problématique d'ordre, par contre il peut bien y avoir des doublons (d'où l'ajout du DISTINCT).


Dans cette requête, on vérifie donc que la collection des rayons proposés par un entrepot (en limitant la liste de ces rayons à ceux présent dans la table t_rayon), correspond exactement à la liste des rayons à fournir.

 

Si on veut la correspondance exacte entre les rayons de t_entrepot et ceux de t_rayon, on peut virer la sous-requête IN.

Partager cet article

Published by Pacman - dans SQL
commenter cet article
4 décembre 2013 3 04 /12 /décembre /2013 16:49

Ma grand-mère me disait toujours, "Pacman, n'oublie jamais le côté artistique du SQL, ni le côté obscur de la force".

Et là, force est de constater que depuis un moment, je m'y crois trop : j'ai oublié les valeurs essentielles dans lesquelles mes ancêtres et moi croyons, et dans lesquelles ma propre descendance devra croire : l'absurdité, l'inutilité, la créativité non applicable à la vraie vie.

 

On va donc aborder aujourd'hui : la division relationnelle avec l'opérateur BITAND !

 

Pour un vrai article sérieux voire boursoufflé sur la division relationnelle, cherchez sur le net.

Par exemple, l'article de SQLPro (vous savez, le mec qui, quand vous lui posez une question sur le forum developpez.net, commence par vous insulter, puis vous recommande d'acheter ses bouquins...), dont nous allons d'ailleurs reprendre le script de tests : 

http://sqlpro.developpez.com/cours/divrelationnelle/

 

1) La division relationnelle

 

Il s'agit de repérer dans une table un ensemble de ligne. 

C'est comme faire un filtre where, mais sur tout un groupe dont les lignes doivent matcher, partiellement ou complètement, un certain nombre de valeurs.

Ces valeurs cibles étant par exemple répertoriées dans une autre table.

 

2) Jeu de données

 

Comme dit, c'est repompé de chez SQLPro. Il s'agit de dépôts (identifiés par leur ville) qui servent des rayons.

Lesquels sont capables de déservir tous les rayons ?

 

/* table des rayons */                                                          

CREATE TABLE T_RAYON                                                            

(RAYON_RYN  CHAR(16));                                                          

/* tables des entrepôts */                                                      

CREATE TABLE T_ENTREPOT                                                         

(VILLE_ETP  CHAR(16),                                                           

RAYON_RYN  CHAR(16));                                                           

/* alimentation de la table des rayons */                                       

INSERT INTO T_RAYON (RAYON_RYN) VALUES ('frais');                               

INSERT INTO T_RAYON (RAYON_RYN) VALUES ('boisson');                             

INSERT INTO T_RAYON (RAYON_RYN) VALUES ('conserve');                            

INSERT INTO T_RAYON (RAYON_RYN) VALUES ('droguerie');                           

/* alimentation de la table des entrepots */                                    

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('PARIS', 'boisson');      

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('PARIS', 'frais');        

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('PARIS', 'conserve');     

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('LYON', 'boisson');       

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('LYON', 'conserve');      

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('LYON', 'droguerie');     

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'boisson');  

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'frais');    

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'conserve'); 

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'droguerie');

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('ANGER', 'boisson');      

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('ANGER', 'frais');        

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('ANGER', 'droguerie');    

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('TOULOUSE', 'boisson');   

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('TOULOUSE', 'frais');     

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('TOULOUSE', 'conserve');  

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('TOULOUSE', 'droguerie'); 

/* modif de l'état initial */                                                   

INSERT INTO T_RAYON VALUES ('conserve');                                        

 

3) La méthode qui perd

 

La représentation binaire, c'est comme la représentation "usuelle" décimale, sauf qu'au lieu d'avoir des nombres composés de chiffres de 0 à 9, on a des nombres composés de 0 et de 1.


Par exemple, si on prend le nombre décimal 153, c'est : 

- En décimal 1 * 10^2 + 5 * 10^1 + 3 * 10^0 = 153 [decimal]

- En binaire 1 * 2^7 + 0 * 2^6 + 0 * 2^5 + 1 * 2^4 + 1 * 2^2 + 1 * 2^0 = 100111 [binaire]

 

Pour deux nombres donnés n1 et n2, bitand(n1, n2) est le nombre résultant de l'opération "pour chaque position, si le chiffre correspondant dans n1 et dans n2 vaut 1, on renvoie 1, sinon 0"

Par exemple, 

bitand(153 [décimal], 15 [décimal])

= bitand (100111[binaire], 001111[binaire])

= 000111 [décimal]

= 7

 

Cette représentation colle parfaitement à une liste de flags : si on affecte à : 

- frais la valeur 1

- boisson la valeur 2

- conserve la valeur 4

- droguerie la valeur 8

 

... et qu'on fait la somme, on obtient 15, qui s'écrit 1111 en binaire.

 

Si ensuite on somme ces valeurs pour chaque dépôt, on pourra chercher quels sont les dépôt de somme n2, tels que bitand(n2, 15) = 15.

 

En SQL, ça donne : 

 

WITH t AS (                                                                       

SELECT distinct rayon_ryn, power(2, dense_rank() over(order by rayon_ryn) - 1) flg

FROM t_rayon)                                                                     

, u as (                                                                          

SELECT sum(flg) sumflg                                                            

FROM t)                                                                           

SELECT ville_etp                                                                  

FROM t_entrepot a                                                                 

  JOIN t ON a.rayon_ryn = t.rayon_ryn                                             

  CROSS JOIN U                                                                    

GROUP BY ville_etp, sumflg                                                        

HAVING bitand(sum(distinct t.flg), sumflg) = sumflg                               

 

Voilà voilà, ne pas appliquer ce genre de méthode au boulot, bien entendu...

 

Partager cet article

Published by Pacman - dans SQL cosmétique
commenter cet article
4 décembre 2013 3 04 /12 /décembre /2013 15:02

L'un des moyens d'étudier l'exécution d'une requête SQL, c'est l'autotrace.

L'autotrace s'appuie sur les statistiques mesurées sur la session, pour en sortir un différentiel des grandeurs en question avant / après exécution de la requête.

 

set autot traceonly statistics

SELECT count(*)               

FROM all_objects              

 

Statistics                                                

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

          8  recursive calls                              

          0  db block gets                                

     102739  consistent gets                              

        140  physical reads                               

          0  redo size                                    

        337  bytes sent via SQL*Net to client             

        350  bytes received via SQL*Net from client       

          2  SQL*Net roundtrips to/from client            

        842  sorts (memory)                               

          0  sorts (disk)                                 

          1  rows processed                               

 

Mais ça ne marche pas sur l'exécution d'un bloc ou procédure PL/SQL : 

 

DECLARE            

  i number;        

BEGIN              

  SELECT count(*)  

  INTO i           

  FROM all_objects;

END;               

/                  

 

PL/SQL procedure successfully completed.

 

Il faut donc faire le boulot soit-même !

 

1) Le modèle de données : 

 

A. Le vue v$statname donne la liste des grandeurs mesurées : 

 

STATISTIC# : identifiant de la grandeur

NAME : nom de la statistique

CLASS : identifiant de classe

STAT_ID : identifiant numérique

 

Parmi toutes les grandeurs mesurées, on retrouve celles de l'autotrace (à l'exception du 'rows processed') : 

 

SELECT *                                                  

FROM v$statname                                           

WHERE name IN ( 'recursive calls',                        

                'db block gets',                          

                'consistent gets',                        

                'physical reads',                         

                'redo size',                              

                'bytes sent via SQL*Net to client',       

                'bytes received via SQL*Net from client', 

                'SQL*Net roundtrips to/from client',      

                'sorts (memory)',                         

                'sorts (disk)',                           

                'rows processed');                        

 

STATISTIC# NAME                                    CLASS    STAT_ID

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

         7 recursive calls                             1 2656001462

        47 db block gets                               8 1480709069

        50 consistent gets                             8 4162191256

        54 physical reads                              8 2263124246

       134 redo size                                   2 1236385760

       343 bytes sent via SQL*Net to client            1 2967415760

       344 bytes received via SQL*Net from client      1  161936656

       345 SQL*Net roundtrips to/from client           1  159846526

       349 sorts (memory)                             64 2091983730

       350 sorts (disk)                               64 2533123502

 

B. La colonne CLASS quant à elle corresond à une liste des classes suivantes : 

 

1 - User

2 - Redo

4 - Enqueue

8 - Cache

16 - OS

32 - Real Application Clusters

64 - SQL

128 - Debug

 

Comment ça une liste dans une seule colonne, le modèle ne serait-il pas normalisé ??

 

WITH classname AS (                                       

SELECT 1 class, 'User' class_name FROM DUAL UNION ALL     

SELECT 2, 'Redo' FROM DUAL UNION ALL                      

SELECT 4, 'Enqueue' FROM DUAL UNION ALL                   

SELECT 8, 'Cache' FROM DUAL UNION ALL                     

SELECT 16, 'OS' FROM DUAL UNION ALL                       

SELECT 32, 'Real Application Clusters' FROM DUAL UNION ALL

SELECT 64, 'SQL' FROM DUAL UNION ALL                      

SELECT 128, 'Debug' FROM DUAL                             

)                                                         

SELECT *                                                  

FROM v$statname                                           

WHERE class not in (SELECT class                          

                    FROM classname)                       

 

STATISTIC# NAME                                 CLASS    STAT_ID

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

        80 DBWR fusion writes                   40 2313150541   

       146 gc cr blocks served                  40 1075941831   

...                                                             

       328 buffer is pinned count               72 1300470380   

       329 buffer is not pinned count           72 2670431739   

       330 no buffer to keep pinned count       72 3528454686   

 

Les classes 40 et 72 représentes respectivement 32 + 8 et 64 + 8... c'est de l'information aggrégée codée en binaire !

Pour la jointure avec les noms de classe, il suffit donc d'utiliser BITAND :

 

WITH classname AS (                                         

SELECT 1 class, 'User' class_name FROM DUAL UNION ALL       

SELECT 2, 'Redo' FROM DUAL UNION ALL                        

SELECT 4, 'Enqueue' FROM DUAL UNION ALL                     

SELECT 8, 'Cache' FROM DUAL UNION ALL                       

SELECT 16, 'OS' FROM DUAL UNION ALL                         

SELECT 32, 'Real Application Clusters' FROM DUAL UNION ALL  

SELECT 64, 'SQL' FROM DUAL UNION ALL                        

SELECT 128, 'Debug' FROM DUAL                               

)                                                           

SELECT name, (SELECT wm_concat(class_name)                  

             FROM classname b                               

             WHERE bitand(a.class, b.class) > 0) class_names

FROM v$statname a                                           

WHERE class IN (40,72)                                      

 

NAME                                  CLASS_NAMES                    

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

DBWR fusion writes                    Cache,Real Application Clusters

gc cr blocks served                   Cache,Real Application Clusters

...                                                                  

buffer is pinned count                Cache,SQL                      

...                                                                  

 

Au passage, la représentation binaire + BITAND est un moyen rigolo, illisible et sous performant de faire des opérations ensemblistes, jusqu'à la division relationnelle... mais passons !

 

C. Les valeurs effectivement mesurée sont disponibles via la vue v$mystat (v$sesstat représentant l'ensemble des mesures pour toutes les sessions)


SID : le SID de la session concernée (toujours le même dans v$mystat)

STATISTIC# : le numéro de statistique, pointant sur la colonne homonyme de la table v$statname

VALUE : la valeur mesurée

 

2) Consolider des snapshots : runstats_pkg

 

L'idée est maintenant de prendre une photo de la valeur des statistiques avant exécution, d'en reprendre une autre après, et d'afficher la différence.

Pour cela, reprenons le travail de l'oncle Tom et son package runstats_pkg : 

 

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551378329289980701

 

.. qui s'appuie sur une vue qui lie les vues v$statname et v$mystat, et d'un GTT qui permet de stocker les photos de statistiques.

 

Ce package par contre, est construit pour comparer deux exécutions différentes : 

rs_start : prise de photo initiale

rs_middle : prise de photo après la première exécution

rs_stop : prise de photo après la deuxième exécution et comparaison des deux photos (ou plutôt des différences entre photo 2 et 1, et photo 3 et 2)

 

Pour mesurer les stats sur une seule exécution, on va donc juste ajouter une procédure de fin supplémentaire rs_stop_single : 

 

create or replace package runstats_pkg                              

as                                                                  

    procedure rs_start;                                             

    procedure rs_middle;                                            

    procedure rs_stop( p_difference_threshold in number default 0 );

    procedure rs_stop_single;                                       

end;                                                                

/                                                                   

 

create or replace package body runstats_pkg                         

as                                                                  

[...]                                                               

 

procedure rs_stop_single is                                         

begin                                                               

    rs_middle;                                                      

    dbms_output.put_line                                            

    ( 'Bloc ran in ' || g_run1 || ' cpu hsecs' );                   

 

    dbms_output.put_line( chr(9) );                                 

 

    dbms_output.put_line                                            

    ( rpad( 'Name', 30 ) || lpad( 'Value', 12 ));                   

 

    for x in                                                        

    ( select rpad( replace(a.name,'STAT...'), 30 ) ||               

             to_char( b.value-a.value, '999,999,999' ) data         

        from run_stats a                                            

          join run_stats b on a.name = b.name                       

       where a.runid = 'before'                                     

         and b.runid = 'after 1'                                    

         and replace(a.name, 'STAT...') in ( 'recursive calls',     

                'db block gets',                                    

                'consistent gets',                                  

                'physical reads',                                   

                'redo size',                                        

                'bytes sent via SQL*Net to client',                 

                'bytes received via SQL*Net from client',           

                'SQL*Net roundtrips to/from client',                

                'sorts (memory)',                                   

                'sorts (disk)',                                     

                'rows processed')                                   

    ) loop                                                          

        dbms_output.put_line( x.data );                             

    end loop;                                                       

end;                                                                

[...]                                                               

 

 

Cette procédure est copiée - collée - légèrement modifiée de la procédure rs_stop (au lieu de faire la différence sur 2 runs, simplement afficher les résultas de l'unique run et nettoyer ce qui ne sert à rien).

Allez, on teste : 

 

 

exec runstats_pkg.rs_start;

 

DECLARE                    

  i number;                

BEGIN                      

  SELECT count(*)          

  INTO i                   

  FROM all_objects;        

END;                       

/                          

 

PL/SQL procedure successfully completed.

 

exec runstats_pkg.rs_stop_single;

 

Bloc ran in 46 cpu hsecs

 

Name                          Value

bytes sent via SQL*Net          592

bytes received via SQL*       1,005

SQL*Net roundtrips to/f           4

sorts (memory)                1,344

sorts (disk)                      0

recursive calls                   4

db block gets                    20

consistent gets             112,153

physical reads                    0

redo size                     3,036

 

PL/SQL procedure successfully completed.

 

Voilà !

Bien entendu, une fois que ces résultats sont consolidés dans la GTT, on peut chercher n'importe quelle valeur parmi la tripotée de statistiques mesurées...

 

Et de manière plus générale, le package peut être retouché à souhait. 

Pour ma part, je n'aime pas trop le rs_stop qui affiche les différences selon un seuil exprimé en valeurs absolues... (sachant qu'on mesure des statistiques qui n'ont pas même unité)

Partager cet article

Published by Pacman - dans SGBD
commenter cet article
18 novembre 2013 1 18 /11 /novembre /2013 13:08

 

Quand on veut savoir pourquoi une procédure rame, on peut par exemple la tracer.

"alter session set events 10046 trace name context forever, level 8" est en ce sens un outil efficace, car il permet de déterminer ce qui a fait attendre l'exécution.

 

C'est donc ce que j'ai fait l'autre jour, et voilà le résultat : 

 

INSERT INTO MA_TABLE

VALUES (...)

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute  47914     60.45     573.76      93619      11588    1221462       47914

Fetch        0      0.00       0.00          0          0          0           0

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

total    47915     60.45     573.76      93619      11588    1221462       47914

 

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 189     (recursive depth: 1)

 

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  db file sequential read                     93619        0.83        517.81

  latch free                                      1        0.00          0.00

  latch: shared pool                              1        0.00          0.00

  buffer busy waits                               1        0.00          0.00

  undo segment extension                          1        0.00          0.00

  latch: object queue header operation            2        0.00          0.00

********************************************************************************

 

On a donc attendu environ 9 minutes en lecture d'index pour insérer 47K lignes.

 

C'est bien beau tout ça, mais quel(s) index ? La répartition du temps d'attente est homogène ?

 

Quand on regarde dans le fichier trace brut, on voit plein de lignes de ce genre : 


WAIT #78: nam='db file sequential read' ela= 8907 file#=26 block#=1865617 blocks=1 obj#=1717754 tim=1352309684636336

 

Et l'obj#, quand on vérifie dans dba_objects, c'est MA_TABLE_FK_ART.

 

On va donc travailler sur cette lueur d'espoir.

 

A. Tout d'abord, extraire les waits : 

 

grep '^WAIT' mon_fichier_trace.trc > trc_dir/my_trc_file.trc

 

B. Ensuite, créer une table externe pour lire ce fichier en SQL (des compétences correctes en shell Unix m'auraient permis de parser tout ça en live)

 

CREATE DIRECTORY trc_dir AS '/home/oracle/trc_dir';

 

CREATE TABLE trc_load  

      (msg varchar2(255))

    ORGANIZATION EXTERNAL

      (TYPE ORACLE_LOADER

      DEFAULT DIRECTORY trc_dir

      ACCESS PARAMETERS

        (RECORDS DELIMITED BY NEWLINE

         FIELDS (msg char(255))

        )

      LOCATION ('my_trc_file.trc')

      );  

 

C. L'analyse de la trace

 

select object_name

  , regexp_replace(msg, '([[:print:]]*nam=)|ela[[:print:]]*') evt

  , sum(regexp_substr(regexp_substr(msg, 'ela= ([[:alnum:]]+) file'), '[[:digit:]]+')) / 1000000 totelaela 

  , count(*) cnt

from trc_load a

  join dba_objects b on regexp_substr(regexp_substr(msg, 'obj#=([[:alnum:]]+) tim'), '[[:digit:]]+') = b.object_id

group by regexp_replace(msg, '([[:print:]]*nam=)|ela[[:print:]]*'), object_name  

order by sum(regexp_substr(regexp_substr(msg, 'ela= ([[:alnum:]]+) file'), '[[:digit:]]+')) / 1000000 desc     

 

OBJECT_NAME  EVT  TOTELAELA  CNT
MA_TABLE_PK  'db file sequential read' 261.571248 45877
MA_TABLE_ART_FK  'db file sequential read' 254.222979 47453
... 'db file sequential read'    
MA_TABLE_DEV_FK  'db file sequential read' 1.097991 174
... 'db file sequential read'    
MA_TABLE_PHO_FK  'db file sequential read' 0.951472 123
MA_TABLE_DEP_FK  'db file sequential read' 0.820889 4555

 

A présent, on peut enfin comprendre et envisager des solutions : 

- Cette table consolide pour une occurence donnée, la photo du stock d'un magasin. 

L'index PK est ordonné ainsi (article, courleur, taille, photo, magasin). Ce qui veut donc dire que le clustering factor est particulièrement mauvais, puisque les insertions se font par le module qui rame, par photo sur un magasin.

=> En réordonnançant la PK, (photo, magasin, article, courleur, taille), il n'y a plus aucune attente pour cet index

- L'index FK article est extrêmement redondante, puisque le nombre de références distinctes est finalement relavitement restreinte, mais dupliquées pour chaque photo de chaque magasin.

=> La compression de l'index divise sa taille part 3, réduisant également le nombre de blocs lus et mis à jour.

Partager cet article

Published by Pacman - dans SGBD
commenter cet article
22 octobre 2013 2 22 /10 /octobre /2013 15:02

J'avais lu, il y a très longtemps de cela, une astuce de l'oncle Tom pour indexer uniquement quelques valeurs très sélectives d'une colonne.

Typiquement un flag ou statut de traitement.

 

1) FBI is watching you

La vieille méthode avant 11G : 

 

CREATE TABLE test_ind AS

SELECT level n, CASE WHEN level <= 100 then 1 ELSE 0 END flag

FROM DUAL

CONNECT BY LEVEL <= 1000000;

 

CREATE INDEX test_ind_ix ON test_ind(flag);

CREATE INDEX test_ind_ix2 ON test_ind(nullif(flag, 0));

 

exec dbms_stats.gather_table_stats(null, 'TEST_IND', cascade => true)

 

La mécanique se base sur le fait que lorsque toutes les valeurs de la clef à indexer sont nulles, l'entrée n'est pas stockée dans l'index.

Créer un Function Based Index avec NULLIF permet donc d'exclure les valeurs non désirables de l'index.

 

Regardons maintenant quelques différences entre l'index "normal" et l'index custom : 

 

SELECT index_name, blevel, leaf_blocks, index_type

FROM dba_indexes

WHERE table_name = 'TEST_IND';

 

INDEX_NAME                         BLEVEL LEAF_BLOCKS INDEX_TYPE

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

TEST_IND_IX                             2        1812 NORMAL

TEST_IND_IX2                            0           1 FUNCTION-BASED NORMAL

 

- Très peu de blocs pour l'index custom, donc très rapide à scanner et optimal en consommation de ressources

- B-tree level à 0 pour l'index custom, donc deux lectures d'économisées à chaque accès

- Index_type = FUNCTION-BASED NORMAL pour l'index custom

 

Pour utiliser l'index, il faut par contre requêter la colonne après application de la fonction qui définit l'index : 

 

EXPLAIN PLAN FOR

SELECT *

FROM test_ind

WHERE nullif(flag, 0) = 1;

 

SELECT *

FROM TABLE(dbms_xplan.display);

 

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

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)|

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

|   0 | SELECT STATEMENT            |              |   100 |   800 |     2   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_IND     |   100 |   800 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | TEST_IND_IX2 |   100 |       |     1   (0)|

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

 

Côté plan d'exécution, on constate que ça torche sévère, et qu'en plus la cardinalité est estimée correctement même sans histogramme !

 

EXPLAIN PLAN FOR

SELECT *

FROM test_ind

WHERE flag = 1;

 

SELECT *

FROM TABLE(dbms_xplan.display);

 

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

| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |          |   500K|  3906K|   472   (3)| 00:00:06 |

|*  1 |  TABLE ACCESS FULL| TEST_IND |   500K|  3906K|   472   (3)| 00:00:06 |

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

 

... ce qui n'est pas le cas de l'index classique. Il faudrait calculer un histogramme, mais même dans ce cas, il peut y avoir des suprises. (Je montrerais un de ces jours un exemple de prod que j'ai recontré il y a peu)

 

Côté dictionnaire, on constate qu'Oracle a créé une colonne virtuelle cachée, pour stocker la valeur de la fonction, et l'indexer : 

 

SELECT column_name, hidden_column, virtual_column, data_default

FROM dba_tab_cols

WHERE table_name = 'TEST_IND';

 

COLUMN_NAME               HID VIR DATA_DEFAULT

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

SYS_NC00003$              YES YES CASE "FLAG" WHEN 0 THEN NULL ELSE "FLAG" END

FLAG                      NO  NO

N                         NO  NO

 

Au passage, c'est la majeure différence entre DBA_TAB_COLUMNS et DBA_TAB_COLS : 

l'affichage des colonnes cachées. 

(Pour ma part, je requête évidemment toujours dba_tab_cols parce que c'est plus court à taper...)


2) 11g et virtual column

 

L'autre jour, l'ami Waldar faisait remarquer que c'est une pratique obscure, et qu'on pouvait, à partir de 11g (si je ne me trompe pas), créer une colonne virtuelle et l'indexer.

"Big up" à lui, comme diraient les jeunes aujourd'hui : 

 

ALTER TABLE test_ind ADD ghost_flag NUMBER GENERATED ALWAYS AS (nullif(flag, 0));

 

ALTER TABLE test_ind ADD ghost_flag NUMBER GENERATED ALWAYS AS (nullif(flag, 0))

                         *

ERROR at line 1:

ORA-54015: Duplicate column expression was specified

 

Paf. Oui, au passage, on ne peut créer deux colonnes virtuelles de même définition.

 

DROP INDEX test_ind_ix2;

ALTER TABLE test_ind ADD ghost_flag NUMBER AS (nullif(flag, 0));

CREATE INDEX test_ind_ix3 ON test_ind(ghost_flag);

exec dbms_stats.gather_table_stats(null, 'TEST_IND', cascade => true)

 

EXPLAIN PLAN FOR

SELECT *

FROM test_ind

WHERE ghost_flag = 1;

 

SELECT *

FROM TABLE(dbms_xplan.display);

 

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

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)|

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

|   0 | SELECT STATEMENT            |              |   100 |   800 |     2   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_IND     |   100 |   800 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | TEST_IND_IX3 |   100 |       |     1   (0)|

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

 

Effectivement, cela donne le même résultat.

Si ce n'est que la colonne n'est plus cachée bien que virtuelle : 

 

SELECT column_name, hidden_column, virtual_column, data_default

FROM dba_tab_cols 

 WHERE table_name = 'TEST_IND';

 

 

COLUMN_NAME                    HID VIR DATA_DEFAULT

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

GHOST_FLAG                     NO  YES CASE "FLAG" WHEN 0 THEN NULL ELSE "FLAG" END

FLAG                           NO  NO

N                              NO  NO

Partager cet article

Published by Pacman - dans SGBD
commenter cet article
20 septembre 2013 5 20 /09 /septembre /2013 14:27

Il y a des fois, on agit avant de penser.

Au moment même où je validais ma requête, j'attendais un "ORA-00918: column ambiguously defined".

Mais ce n'est pas arrivé.

 

1) Les données de test

 

Précision : on est en version 10.2.0.4.0

 

create table testeuh as 

select 'a' id, 1 val from dual union all

select 'b' id, 2 val from dual;

 

create table testeuh1 as 

select 'a' id, 1 val from dual union all

select 'b' id, 2 val from dual;

 

create table testeuh2 as

select 'a' ida, 'b' idb, 0 val from dual

 

2) Ce qu'il nous arrive en général

 

Quand dans une requête sur plusieurs tables, on ne préfixe pas par l'alias de la table une colonne présente dans plusieurs tables, cela donne : 

 

SELECT val

FROM testeuh2 a

JOIN testeuh b on a.ida = b.id;

 

SELECT val

       *

ERROR at line 1:

ORA-00918: column ambiguously defined

 

Normal, car comment savoir quelle colonne val doit être renvoyée ?

Il faut donc préfixer les colonnes avec l'alias de la table : 

 

SELECT a.val

FROM testeuh2 a

  JOIN testeuh b on a.ida = b.id;

 

       VAL

----------

         0

 

3) Parce que plus on est de fous, plus on rit

 

SELECT val

FROM testeuh2 a

  JOIN testeuh b on a.ida = b.id

  JOIN testeuh1 c on a.idb = c.id;

 

       VAL

----------

         2

 

=> Ce coup-ci, la requête ne plante pas, et Oracle me renvoie arbitrairement le val de la table testeuh1... comment est-il choisi ?

 

On ne peut pas affirmer grand chose, mais on peut faire joujou : 

 

SELECT val

FROM testeuh2 a

  JOIN testeuh b on a.ida = b.id

  JOIN testeuh1 c on a.idb = c.id

  JOIN testeuh1 d on a.ida = d.id;

 

       VAL

----------

         1

 

=> Quand on rajoute une jointure de plus, ça ne plante toujours pas. De plus, c'est la dernière table citée dans la clause FROM qui remporte la mise.

 

Est-ce que cela dépend du plan d'exécution ?

 

SELECT /*+leading(b a d c)*/val

FROM testeuh2 a

  JOIN testeuh b on a.ida = b.id

  JOIN testeuh1 c on a.idb = c.id

  JOIN testeuh1 d on a.ida = d.id;

 

       VAL

----------

         1

 

Elapsed: 00:00:00.01

 

Execution Plan

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

Plan hash value: 162124344

 

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

| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT     |          |     1 |    43 |    14  (15)| 00:00:01 |

|*  1 |  HASH JOIN           |          |     1 |    43 |    14  (15)| 00:00:01 |

|*  2 |   HASH JOIN          |          |     1 |    37 |    10  (10)| 00:00:01 |

|*  3 |    HASH JOIN         |          |     1 |    18 |     7  (15)| 00:00:01 |

|   4 |     TABLE ACCESS FULL| TESTEUH  |     2 |    12 |     3   (0)| 00:00:01 |

|   5 |     TABLE ACCESS FULL| TESTEUH2 |     1 |    12 |     3   (0)| 00:00:01 |

|   6 |    TABLE ACCESS FULL | TESTEUH1 |     2 |    38 |     3   (0)| 00:00:01 |

|   7 |   TABLE ACCESS FULL  | TESTEUH1 |     2 |    12 |     3   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - access("A"."IDB"="C"."ID")

   2 - access("A"."IDA"="D"."ID")

   3 - access("A"."IDA"="B"."ID")

 

=> Et ben non, la table c est accédée en dernier, mais pour autant val ne vaut pas 2.

 

4) Conclusions

 

a. C'est une bonne pratique de préfixer systématiquement les colonnes (et en plus ça apporte énormément de lisibilité pour toute personne qui lit le code sans connaître le modèle par coeur)

 

b. "Ambiguously" provient probablement du préfixe latin amb-, qui porte l'idée de double selon wiktionary.org. Donc à partir de 3, on n'est plus ambigu, et il ne s'agit donc pas d'un bug

Partager cet article

Published by Pacman - dans SQL
commenter cet article
29 août 2013 4 29 /08 /août /2013 15:10

Comment rassembler des intervalles qui se chevauchent ?

Dans ma jeunesse, j'ai vu (et proposé) des solutions tirées par les cheveux.

Et l'autre jour sur OTN, j'ai trouvé la méthode de FrankKulash particulièrement élégante.

 

Petit article hommage pour lui : 

 

1) Les données

 

Robert, Marcel et Squelettor travaillent dans la même équipe. Voici la table non normalisée de leur planning : 

 

CREATE TABLE planning AS

SELECT 'Robert' nom, to_date('20130810 10', 'YYYYMMDD hh24') date_deb, to_date('20130810 14', 'YYYYMMDD hh24') date_fin FROM DUAL

UNION ALL SELECT 'Marcel' nom, to_date('20130810 12', 'YYYYMMDD hh24') date_deb, to_date('20130810 20', 'YYYYMMDD hh24') date_fin FROM DUAL

UNION ALL SELECT 'Squelettor' nom, to_date('20130810 11', 'YYYYMMDD hh24') date_deb, to_date('20130810 18', 'YYYYMMDD hh24') date_fin FROM DUAL

UNION ALL SELECT 'Robert' nom, to_date('20130811 08', 'YYYYMMDD hh24') date_deb, to_date('20130811 10', 'YYYYMMDD hh24') date_fin FROM DUAL

UNION ALL SELECT 'Marcel' nom, to_date('20130811 12', 'YYYYMMDD hh24') date_deb, to_date('20130811 14', 'YYYYMMDD hh24') date_fin FROM DUAL

 UNION ALL SELECT 'Squelettor' nom, to_date('20130811 09', 'YYYYMMDD hh24') date_deb, to_date('20130811 13', 'YYYYMMDD hh24') date_fin FROM DUAL;

 

2) Interval UNION !

 

La requête suivante répond à la question : "quelles sont les périodes disjointes sur lesquelles il y a au moins une personne présente ?"

 

SELECT to_char(min(date_deb), 'DD/MM/YYYY hh24:mi') deb_union, to_char(max(date_fin), 'DD/MM/YYYY hh24:mi') fin_union

FROM (

  SELECT date_deb, date_fin, sum(brk) OVER(ORDER BY date_deb) grp

  FROM (

      SELECT date_deb, date_fin

        , CASE WHEN max(date_fin) OVER(ORDER BY date_deb ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) < date_deb

               THEN 1

               ELSE 0 END brk

      FROM planning)

      )

GROUP BY grp

ORDER BY grp;

 

DEB_UNION        FIN_UNION

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

10/08/2013 10:00 10/08/2013 20:00

11/08/2013 08:00 11/08/2013 14:00

 

Le raisonnement : pour une ligne donnée, si parmi les lignes précédentes triées par date de début, la plus grande date de fin est strictement inférieure à ma date de début, la ligne courante crée un nouvel intervalle.


Par défaut, la fenête (le périmètre) d'une fonction analytique comprend toutes les lignes de la partition de dates inférieures où égale à la ligne courante.

Le '1 PRECEDING' permet de s'arrêter un cran avant, et on doit préciser le début de la fenête à UNBOUNDED PRECEDING pour indiquer qu'on prend tout depuis le début de la partition.

 

Une fois qu'on a ces flags 'BRK' pour chaque ligne, en faire la somme cumulative sur chaque ligne, permet de compter le nombre de ruptures présentes avant la ligne courante.

Ce nombre 'GRP' est un critère de groupement pour rassembler les intervalles, et il ne reste plus qu'à choper le début et la fin de l'intervalle.

 

3) Interval INTERSECTION !

 

Histoire d'apporter quand même une touche personnelle, j'ai cherché à répondre à une autre question : 

"Quel est le nombre de personnes présentes par tranches horaires distinctes ?"

 

J'ai fait des petits dessins où je projetais des intervalles sur un axe, pour finir par me dire : si on colle toutes les extrêmités d'intervalles les unes aux autres, on peut ensuite considérer chacun des segments minimaux et compter le nombre de "segments" du planning dans lesquels ils sont inclus.

 

SELECT to_char(t.deb_int, 'DAY hh24:mi') deb, to_char(t.fin_int, 'DAY hh24:mi') fin, count(*) nb, LISTAGG(nom, ',') WITHIN GROUP(ORDER BY nom) lstnom

FROM planning p JOIN 

    (SELECT borne_date deb_int, lead(borne_date, 1) OVER(ORDER BY borne_date) fin_int

    FROM planning

    UNPIVOT (borne_date

             FOR type_borne

             IN (date_deb, date_fin))

    GROUP BY borne_date) t

    ON t.deb_int between p.date_deb and p.date_fin AND t.fin_int IS NOT NULL

    AND t.fin_int between p.date_deb and p.date_fin AND t.fin_int IS NOT NULL

GROUP BY t.deb_int, t.fin_int    

ORDER BY t.deb_int

 

DEB                  FIN                          NB LSTNOM

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

SATURDAY  10:00      SATURDAY  11:00               1 Robert

SATURDAY  11:00      SATURDAY  12:00               2 Robert,Squelettor

SATURDAY  12:00      SATURDAY  14:00               3 Marcel,Robert,Squelettor

SATURDAY  14:00      SATURDAY  18:00               2 Marcel,Squelettor

SATURDAY  18:00      SATURDAY  20:00               1 Marcel

SUNDAY    08:00      SUNDAY    09:00               1 Robert

SUNDAY    09:00      SUNDAY    10:00               2 Robert,Squelettor

SUNDAY    10:00      SUNDAY    12:00               1 Squelettor

SUNDAY    12:00      SUNDAY    13:00               2 Marcel,Squelettor

SUNDAY    13:00      SUNDAY    14:00               1 Marcel

 

J'ai profité de ma nouvelle 11gR2 pour utiliser UNPIVOT, mais cela se fait bien sûr également (et entre autres) de la manière artisanale suivante : faire le produit cartésien avec une "table" à deux lignes, puis choisir alternativement la bonne colonne.

 

SELECT to_char(t.deb_int, 'DAY hh24:mi') deb, to_char(t.fin_int, 'DAY hh24:mi') fin, count(*) nb, LISTAGG(nom, ',') WITHIN GROUP(ORDER BY nom) lstnom

FROM planning p JOIN 

    (SELECT CASE n WHEN 1 THEN date_deb WHEN 2 then date_fin END deb_int

            , lead(CASE n WHEN 1 THEN date_deb WHEN 2 then date_fin END, 1) OVER(ORDER BY CASE n WHEN 1 THEN date_deb WHEN 2 then date_fin END) fin_int

    FROM planning

      CROSS JOIN (SELECT 1 n FROM DUAL

                  UNION ALL SELECT 2 FROM DUAL)

    GROUP BY CASE n WHEN 1 THEN date_deb WHEN 2 then date_fin END) t

    ON t.deb_int between p.date_deb and p.date_fin AND t.fin_int IS NOT NULL

    AND t.fin_int between p.date_deb and p.date_fin AND t.fin_int IS NOT NULL

GROUP BY t.deb_int, t.fin_int    

ORDER BY t.deb_int

 

(Et la première fois de ma vie que j'utilise LISTAGG pour mettre dans un même sac Marcel, Robert et Squelettor...)

Partager cet article

Published by Pacman - dans SQL
commenter cet article
21 août 2013 3 21 /08 /août /2013 15:19

L'utilisateur : "Salut, je voudrais un filtre à sélection multiple sur les pays  pour mon rapport XYZ... idéalement avec des cases à cocher" Notez que ce n'est pas un rapport XXX, on fait pas ça chez nous.

Moi :            "Désolé, notre framework est pourri, ça va pas être jouable. Par contre si ça te dit, je te laisse saisir ta sélection, par exemple sous forme de liste de code pays sur 2 caractères, séparés par des virgules. En bonus, un bouton pour afficher la liste des correspondances libellé / code pays."

L'utilisateur : "Vendu !"

 

Donc je me mets à transformer des chaînes de caractères représentant des listes en lignes, afin de pouvoir faire des filtres "IN (SELECT ...)"

Sujet classique, juste l'occasion de passer en revue les méthodes, et "expliquer" mes choix.

 

Spliter une chaîne de caractères, ça se fait de manière itérative.

 

1) Implémenter sa boucle avec CONNECT BY level

 

Toujours la même astuce : CONNECT BY level sur une sélection d'une seule ligne, ça génère des lignes à la volée, et elles sont "indexables" par level ou rownum : 

 

define p_str = 'FR,DE,PL,BE'

 

SELECT substr(str,  instr(str, del, 1, level) + 1, instr(str, del, 1, level+1) - instr(str, del, 1, level) - 1)

FROM (select '&p_str' p_str, ',' || '&p_str' || ',' str, ',' del from dual)

CONNECT BY level <= length(p_str) - length(replace(p_str, del, '')) + 1; 

 

SUBSTR(STR,IN

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

FR

DE

PL

BE

 

On rajoute un délimiteur en début en fin de liste (mais on pourrait aussi s'embourber dans plein de CASE pour traiter le début et la fin de liste), puis on peut extraire avec substr en s'appuyant sur la position des délimiteurs dans la chaîne.

 

A partir de 10g, on a la version bien plus élégante avec REGEXP_SUBSTR : 

 

SELECT regexp_substr('&p_str',  '[^,]+', 1, level)

FROM dual

CONNECT BY level <= length(regexp_replace('&p_str', '[^,]+')) + 1; 

 

La regexp [^,]+ matche toutes les occurences multiples de caractères qui sont différents de ','... les deux derniers paramètres sont équivalents à INSR, c'est à dire position de départ pour la recherche, et numéro d'occurence à traiter.

 

Pourquoi je n'ai pas choisi cette solution ?

- Parce que CONNECT BY level, c'est du bugusing (CONNECT BY est sensé faire des arbres, pas du café...)

- Parce que ça fait des mochetés à recoder à chaque fois, et que ça n'arrange pas mes collègues non autistes du SQL 

 

2) Implémenter sa boucle avec MODEL

 

A présent, on reprend l'extraction regexp_substr parce que c'est joli, mais on itère avec la clause MODEL

 

SELECT tk 

FROM (

  SELECT 1 n, p_str, p_str tk, length(regexp_replace(p_str, '[^,]+')) + 1 n_item

  FROM (SELECT  '&p_str' p_str FROM dual) t

)

MODEL 

DIMENSION BY (n)

MEASURES (p_str, tk, n_item)

RULES (

tk[FOR n FROM 1 TO n_item[1] INCREMENT 1] = regexp_substr(p_str[1],  '[^,]+', 1, cv(n))

);

 

Cependant, je me demande si utiliser la clause model dans du code de production ne pourrait pas être un motif valable de licenciement...

Y a un gars (qui devrait se reconnaître s'il passe par là), qui disait : "As 10g introduced MODEL, which is mostly used to impress your colleagues but seldom used in production, ..."

 

3) XML Table

 

On peut faire plein de trucs assez loufoques avec XMLTable sous Oracle.

Notamment tout simplement : 

 

SELECT *

FROM xmltable('&p_str');

 

C'est tout simple, tout compact, mais j'ai décidé de ne pas l'utiliser parce qu'il y a "XML" dans l'instruction. (Oui oui, c'est purement psychologique)

 

 

4) Pipelined function

 

Une fonction pl/sql, ça sait très bien faire des boucles, et ça sait aussi renvoyer des tableaux.

Et puis, ça peut aussi aussi retourner des lignes au fil de l'eau en étant "pipelined".

C'est tout mignon, on a l'impression de cracher des petites aiguilles empoisonnées avec une sarbacane : 

 

 

CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF varchar2(4000);

/

CREATE OR REPLACE FUNCTION split_string2(p_str IN VARCHAR2, p_del IN VARCHAR2) 

RETURN varchar2_ntt

PIPELINED

IS

n_item NUMBER;

   s_str VARCHAR2(32767);

   s_ret VARCHAR2(4000);

BEGIN

  s_str := trim(both p_del from p_str);

n_item := length(s_str) - coalesce(length(replace(s_str, p_del, '')), 0) + 1;

  s_str := p_del || trim( both p_del from p_str) || p_del;

 

FOR i IN 1..n_item LOOP    

s_ret := substr(s_str, instr(s_str, p_del, 1, i) + 1, instr(s_str, p_del, 1, i + 1) - instr(s_str, p_del, 1, i) -1);

    pipe row(s_ret);

END LOOP;

END split_string2;

/

 

 

SELECT *

FROM table(split_string('&p_str',','))

 

Cela dit, bien qu'étant un grand fan de pipes, je trouve ça stupide de context switch n fois alors que l'exécution de la boucle en elle-même prend un temps négligeable.

 

 

5) Function

 

Bon ben voilà, j'ai choisi au final de faire une petite fonction toute simple (j'ai cependant gardé la méthode d'extraction pourrie à base de substr/instr), qui renvoit tout un tableau : 

 

CREATE OR REPLACE FUNCTION split_string(p_str IN VARCHAR2, p_del IN VARCHAR2) 

RETURN varchar2_ntt

IS

t_ret varchar2_ntt;

n_item NUMBER;

   s_str VARCHAR2(32767);

BEGIN

  s_str := trim(both p_del from p_str);

n_item := length(s_str) - coalesce(length(replace(s_str, p_del, '')), 0) + 1;

  s_str := p_del || trim( both p_del from p_str) || p_del;

t_ret := varchar2_ntt();

  t_ret.extend(n_item);

 

FOR i IN 1..n_item LOOP    

t_ret(i) := substr(s_str, instr(s_str, p_del, 1, i) + 1, instr(s_str, p_del, 1, i + 1) - instr(s_str, p_del, 1, i) -1);

END LOOP;

RETURN t_ret;

END split_string;

/

 

SELECT *

FROM table(split_string2('&p_str',','))

 

Il y a des gens qui vous diront qu'appeler des fonctions PL/SQL dans du SQL c'est mal parce que ça context switch.

Mais ici, dans une requête comme celle-ci, la fonction est à priori appelée une seule fois, donc sincèrement, je m'en cogne.

 

SELECT *

FROM ma_table 

WHERE code_pays IN 

  (SELECT column_value

    FROM table(split_string2('&p_str',',')))

 

 

 

 

Partager cet article

Published by Pacman - dans SQL
commenter cet article