Overblog Suivre ce blog
Administration Créer mon blog
28 octobre 2011 5 28 /10 /octobre /2011 16:23

Die Softwareentwickler mögen Schleife und Cursors über alles, und finden desshalb die unwahrscheinlichsten Rechtfertigungen um die globale DML query zu vermeiden.

Unter denen gibt's die Handlung der Fehlerfällen :

"Neee, die ganze Anfrage darf nicht Rollbacked werden, nur wegen einer verdammten Zeile"

 

Aber seit 10gR2 gilt dieses Argument (fast) nicht mehr, denn es gibt jetzt die ERROR LOGGING !

 

Versuchen wir's mal mit verschiedenen Fehler Gründen...

 

1) Die Testtabelle :


 CREATE TABLE testlog (

 id NUMBER UNIQUE,

 n NUMBER CHECK(n > 0),

 c VARCHAR2(10) NOT NULL);

 

2) Die Errorlogtabelle :

 

EXEC dbms_errlog.create_error_log('TESTLOG', 'TESTERR')

 

3) Einfügen

 

SQL> INSERT INTO testlog

  2  SELECT CASE level WHEN 2 THEN 1 ELSE level END, --die zweite Zeile wird unique contraint verletzen

  3         CASE level WHEN 4 THEN -1 ELSE level END, --die vierte verletzt die positiv check bedingung

  4         CASE level WHEN 6 THEN null WHEN 8 THEN lpad('x', 12, 'x') ELSE 'd' END --die sechte die NOT NULL bedingung und die achte überschreitet die maximale Länge

  5  FROM DUAL

  6  CONNECT BY level <= 12

  7  LOG ERRORS INTO TESTERR ('instest') REJECT LIMIT UNLIMITED;

 

8 ligne(s) créée(s).

 

SQL> SELECT *

  2  FROM testlog

  3  /

 

  ID    N C

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

   1    1 d

   3    3 d

   5    5 d

   7    7 d

   9    9 d

  10   10 d

  11   11 d

  12   12 d

 

=> 8 der 12 zeilen sind erfolgreich hinzufügen worden !

 

SQL> SELECT * FROM testerr;


  PrtScr capture 3(1)

 

Und natürlich klappt  es auch mit UPDATE, MERGE UND DELETE !

 

SQL> MERGE INTO testlog a

  2  USING (SELECT level l,

  3             CASE level WHEN 4 THEN -1 ELSE level + 1 END n,

  4             CASE level WHEN 5 THEN null ELSE 'c' END c

  5         FROM DUAL CONNECT BY LEVEL <= 13) t

  6  ON (a.id = t.l)

  7  WHEN MATCHED THEN UPDATE SET a.n = t.n, a.c = t.c

  8  WHEN NOT MATCHED THEN INSERT (a.id, a.n, a.c) VALUES(t.l, t.n, t.c)

  9  LOG ERRORS INTO TESTERR('testmerge') REJECT LIMIT UNLIMITED;

 

11 lignes fusionnées.

 

SQL> SELECT * FROM testlog;

 

  ID    N C

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

   1    2 c

   3    4 c

   5    5 d

   7    8 c

   9   10 c

  10   11 c

  11   12 c

  12   13 c

   2    3 c

   6    7 c

   8    9 c

  13   14 c

 

SQL> SELECT ORA_ERR_MESG$, ORA_ERR_ROWID$, ORA_ERR_TAG$

  2  FROM testerr;

 

PrtScr-capture_3-1--copie-1.jpg

 

 

4) Trotzdem ein Paar Schertze

 

Manchmal werden die unique contraints nur "am Ende" überprüft : in diese Fälle scheitert die ganze Anfrage.

Zum beispiel den direct path load :

 

SQL> INSERT INTO testlog

  2  SELECT /*+APPEND*/ CASE level WHEN 10 THEN 1 ELSE level END, level, 'c'

  3  FROM DUAL

  4  CONNECT BY level <= 10

  5  LOG ERRORS INTO testerr ('rarghl') REJECT LIMIT UNLIMITED;

INSERT INTO testlog

*

ERREUR à la ligne 1 :

ORA-00001: unique constraint (EDGE_ADM.SYS_C00356572) violated

 

Ohne den append hint klappt's prima :

 

SQL> INSERT INTO testlog

  2  SELECT CASE level WHEN 10 THEN 1 ELSE level END, level, 'c'

  3  FROM DUAL

  4  CONNECT BY level <= 10

  5  LOG ERRORS INTO testerr ('rarghl') REJECT LIMIT UNLIMITED;

 

1 ligne créée.

Repost 0
Published by Pacman - dans SQL
commenter cet article
20 juillet 2011 3 20 /07 /juillet /2011 13:03

C'est l'anniversaire du PacBlog ! (en fait c'était il y a deux jours)

 

Pour ses deux ans, son "Blog Rank" est retombé à 2.

(C'est en gros un indicateur qui dit que quand il est proche de 0, le blog est à chier)

 

Voilà, pour ne pas s'arrêter sur ce caca d'anniversaire, je vais quand même ajouter un complément sans aucune valeur ajoutée sur l'article précédent.

 

Donc, pour garantir l'ordre sur le résultat d'un ORDER BY, définissez un champ fictif sur chacune des requêtes composant le UNION ALL :

 

SQL> SELECT *
  2  FROM (
  3     SELECT a.*, 1 o
  4     FROM paral1 a
  5     UNION ALL
  6     SELECT b.*, 2 o
  7     FROM paral2 b)
  8  ORDER BY o
  9  /

 

Ah là là, c'est pas ça qui va faire remonter mon BlogRank...

Repost 0
Published by Pacman - dans SQL
commenter cet article
27 mai 2010 4 27 /05 /mai /2010 12:12

Aujourd'hui, un thème assez banal : tester si une chaîne de caractères est un nombre !
C'est à priori pas super passionnant, mais l'objectif ici est surtout de détailler l'une des méthodes qui est souvent baclée.


On va donc explorer rapidement trois possibilités :
1) Essayer... et rattraper l'exception
2) Tester avec une expression régulière
3) La méthode translate

 

 

1) La grande classique

 

La méthode que l'on retrouve le plus souvent consiste à créer une fonction qui tente simplement de caster la chaîne en nombre.
Si l'opération échoue, on rattrappe l'exception.

 

La fonction :

  

CREATE OR REPLACE FUNCTION IS_NUMBER ( p_str IN VARCHAR2 ) RETURN NUMBER

IS

v_dummy NUMBER;

 

BEGIN

  

   v_dummy := TO_NUMBER(p_str);

 

  

Le test :

  

SQL> SELECT is_number('robert') as notnum, is_number('123,43') as num

  2  FROM DUAL

  3  /

 

    NOTNUM        NUM

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

        -1          0

 

 

 

Cependant, on entend souvent rétorquer à cette méthode "Mais euh, je n'ai pas le droit de créer de fonction sur ma base !"
D'où la suite...

 

2) Les expression régulières

 

Avec les expression régulières, on peut tester la "tronche" de la chaîne : plus que les caractères qu'elle contient, l'expression régulière teste également "l'agencement"...
A partir d'Oracle 10g, on a droit à REGEXP_LIKE !

 

 

 

Note importante : REGEXP_LIKE renvoie un booléen, qui n'est pas un type qui peut être renvoyé par l'instruction SELECT d'Oracle.
C'est pour cela qu'il faut la traiter avec un CASE WHEN...


3) Translate !

 

La méthode TRANSLATE, c'est simple :
En gros, on prend la chaîne, on remplace tous les chiffres par des blancs, et s'il ne reste que des blancs à la fin, c'est un nombre.

 
   

SELECT CASE WHEN trim(translate('1234,5', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as num

, CASE WHEN trim(translate('robert', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as notnum

FROM DUAL

/

 

       NUM     NOTNUM

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

         1          0

 

Seulement voilà, le résultat est assez inexact, car on teste les caractères contenus, mais pas le séquencement !


Et c'est en fait la seule raison plus ou moins valable de s'étendre sur ce sujet maintes fois traité... faisons donc l'effort de détailler cela en détail.

 

a. Les espaces


Dans le premier exemple de translate, on ne teste pas les espaces.
Pourtant, les formats disponibles pour to_number ne permettent pas d'intercaler des espaces entre les chiffres...

 

Par exemple, le translate donne :

 

SQL> ed

Úcrit fichier afiedt.buf

 

  1  SELECT CASE WHEN trim(translate('12 345', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as res

  2* FROM DUAL

SQL> /

 

       RES

----------

         1

 

Alors que le to_number vautre :

  

 

SQL> SELECT to_number('12 345') FROM dual;

SELECT to_number('12 345') FROM dual

                 *

ERREUR Ó la ligne 1 :

ORA-01722: invalid number

 

En fait, les espaces "trailing" et "leading" sont les seuls acceptés.
On peut donc simplement comparer la taille de la chaîne "trimée" à la taille de la chaine dépouillée de tous les espaces.

 

 

b. Nombre d'occurences des caractères spéciaux

 

La version basique de la méthode translate se contente d'accepter une liste de caractères.
Cependant, certains caractères tels que le séparateur de décimales ou le signe doivent apparaître au maximum une fois.

 

SQL> ed

Úcrit fichier afiedt.buf

 

  1  SELECT CASE WHEN trim(translate(' 12 345 ', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as res,

  2  CASE coalesce(length(trim(' 12 345 ')), 0) - coalesce(length(replace(' 12 345 ', ' ', '')), 0)

  3  WHEN 0 THEN 1 ELSE 0 END as res2

  4* FROM DUAL

SQL> /

 

       RES       RES2

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

         1          0

   

Pour tester le nombre d'occurences d'un caractère, il suffit de comparer la longueur de la chaîne de départ à la longueur de la chaîne épurée des dits caractères :

 

  

 

SQL> SELECT CASE WHEN trim(translate('12,34,5 ', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as res,

  2  CASE WHEN coalesce(length('12,34,5'), 0) - coalesce(length(replace('12,34,5', ',', '')), 0) > 1 THEN 0 ELSE 1 END as res3

  3  FROM DUAL

  4  /

 

       RES       RES3

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

         1          0

  

c. Restrictions sur la position d'un caractère spécifique

 

Les restrictions suivantes doivent être appliquées :
- le caractère "e" de l'écriture scientifique ne doit pas être en dernière position
- les caractères + et - doivent se trouver en première position

  

 

SQL> SELECT CASE WHEN trim(translate('12,34,5 ', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as res,

  2  CASE coalesce(length('12345e'), 0) - coalesce(instr('12345e', 'e'), 0)  WHEN 0 THEN 0 ELSE 1 END as res4,

  3  CASE WHEN coalesce(instr('123+45', '+'), 0)  > 1 THEN 0 ELSE 1 END as res5

  4  FROM DUAL

  5  /

 

       RES       RES4       RES5

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

         1          0          0

 

d. La position relative de certains caractères

 

Le séparateur de décimales doit apparaître avant le caractère "e" :

 

 

 

SQL> SELECT CASE WHEN trim(translate('12,34,5 ', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as res,

  2  CASE WHEN coalesce(instr('12e3,45', ','), 0) <= coalesce(instr('12e3,45', 'e'), 0)  THEN 1 ELSE 0 END as res4

  3  FROM DUAL;

 

       RES       RES4

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

         1          0

 
Allez, si on combine toutes ces conditions, on devrait y arriver :

 

SQL> WITH testeuh AS
  2  (SELECT '123' AS c FROM DUAL UNION ALL
  3  SELECT '12,3' AS c FROM DUAL UNION ALL
  4  SELECT '+12,3' AS c FROM DUAL UNION ALL
  5  SELECT '+123,1e5' AS c FROM DUAL UNION ALL
  6  SELECT '123+5' AS c FROM DUAL UNION ALL
  7  SELECT '125,5,' AS c FROM DUAL UNION ALL
  8  SELECT '125e,5' AS c FROM DUAL UNION ALL
  9  SELECT '1 125,5' AS c FROM DUAL
 10  )
 11  SELECT c,
 12  CASE WHEN trim(translate(c, '1234567890,+-e', ' ')) IS NULL THEN 1 ELSE 0 END
 13  * CASE coalesce(length(trim(c)), 0) - coalesce(length(replace(c, ' ', '')), 0) WHEN 0 THEN 1 ELSE 0 END
 14  * CASE WHEN coalesce(length(c), 0) - coalesce(length(replace(c, ',', '')), 0) > 1 THEN 0 ELSE 1 END
 15  * CASE WHEN coalesce(length(c), 0) - coalesce(length(replace(c, '+', '')), 0) > 1 THEN 0 ELSE 1 END
 16  * CASE WHEN coalesce(length(c), 0) - coalesce(length(replace(c, '-', '')), 0) > 1 THEN 0 ELSE 1 END
 17  * CASE WHEN coalesce(length(c), 0) - coalesce(length(replace(c, 'e', '')), 0) > 1 THEN 0 ELSE 1 END
 18  * CASE coalesce(length(c), 0) - coalesce(instr(c, 'e'), 0)  WHEN 0 THEN 0 ELSE 1 END
 19  * CASE WHEN coalesce(instr(c, '+'), 0)  > 1 THEN 0 ELSE 1 END
 20  * CASE WHEN coalesce(instr(c, '-'), 0)  > 1 THEN 0 ELSE 1 END
 21  * CASE WHEN coalesce(instr(c, ','), 0) <= coalesce(instr(c, 'e'), 0) OR coalesce(instr(c, 'e'), 0) = 0 THEN 1 ELSE 0 END AS isnumeric
 22  FROM testeuh
 23  / 

  

C         ISNUMERIC
-------- ----------
123               1
12,3              1
+12,3             1
+123,1e5          1
123+5             0
125,5,            0
125e,5            0
1 125,5           0 

 

Une grande dédicace à Mr X pour sa contribution.
(Je n'ai pas le droit de le citer...)

 

 

 

 

 

Repost 0
Published by Pacman - dans SQL
commenter cet article
1 décembre 2009 2 01 /12 /décembre /2009 13:22

Dans un article précédent, je disais que grâce à l'évolution de l'instruction MERGE sous 10g, on pouvait enfin faire des UPDATE de jointure efficaces sous Oracle.

Ce n'est pas tout à fait vrai. Car Oracle permet de mettre à jour des vues... sous certaines conditions seulement !
Nous allons donc dans la suite illustrer les limitations de l'in-line view update, et donc l'avantage certain de l'instruction MERGE.

1) L'exemple basique d'update d'in-line view

Reprenons les jeux de test créés dans l'article précédent et la requête de Mnitu (cf commentaire) :


SQL> set autotrace on
SQL> UPDATE (
  2    SELECT a.val oldval, b.val newval
  3    FROM test_merge a
  4      JOIN test_merge_source b ON a.id = b.id
  5  )
  6   SET oldval = newval;

1000 rows updated.

------------------------------------------------------------------------
| Id  | Operation           | Name              | Rows  | Bytes | Cost |
-----------------------------------------------------------------------|
|   0 | UPDATE STATEMENT    |                   |  1000 | 13000 |   527|
|   1 |  UPDATE             | TEST_MERGE        |       |       |      |
|*  2 |   HASH JOIN         |                   |  1000 | 13000 |   527|
|   3 |    TABLE ACCESS FULL| TEST_MERGE_SOURCE |  1000 |  7000 |     3|
|   4 |    TABLE ACCESS FULL| TEST_MERGE        |  1010K|  5918K|   501|
------------------------------------------------------------------------


C'est parfait, exactement ce qu'on souhaite !
Mais ça ne marche pas toujours...


2) Le cas de défaite

Nous étions dans le cas d'une relation 1-1.
Considérons à présent une relation maître-esclave.

SQL> CREATE TABLE acct  (acct_num primary key, some_amount, acct_desc) AS
  2  SELECT level, 0, lpad('x', mod(level, 10), 'x')
  3  FROM DUAL
  4  CONNECT BY level <= 100
  5  /

Table created.

SQL> CREATE TABLE ecr(acct_num, ecr_num, ecr_amt, some_desc) AS
  2  SELECT a.acct_num, b.ecr_num, b.ecr_amt, ' '
  3  FROM acct a
  4    CROSS JOIN (SELECT level as ecr_num, level * 100 as ecr_amt
  5                FROM DUAL
  6                CONNECT BY level <= 10) b
  7  /

Table created.

SQL> ALTER TABLE ecr ADD CONSTRAINT pk_ecr PRIMARY KEY(acct_num, ecr_num)
  2  /

Table altered.

SQL>  exec dbms_stats.gather_table_stats('PACMAN', 'ACCT', cascade => true)

PL/SQL procedure successfully completed.

SQL>  exec dbms_stats.gather_table_stats('PACMAN', 'ECR', cascade => true)

PL/SQL procedure successfully completed.



On passera sur le fait que cet exemple doit forcément piétiner au moins une des formes normales...

Bref, faisons un premier essai, relativement idiot : mettre à jour la colonne some_amount de la table acct avec le montant associé dans ecr(ecr_amt) (ma formulation est volontairement floue, la punition sera immédiate).


SQL> UPDATE
  2    (SELECT some_amount, ecr_amt
  3     FROM acct a
  4       JOIN ecr b ON a.acct_num = b.acct_num)
  5  SET some_amount = ecr_amt;
SET some_amount = ecr_amt
    *
ERROR at line 5:
ORA-01779: cannot modify a column which maps to a non key-preserved table



Bah ouais, ça plante... voyons pourquoi.


3) Les limitations de l'UPDATE d'in-line views

Lorsqu'on met à jour une vue, qu'elle soit explicitement nommée, ou 'in-line' c'est à dire sous forme de "table dérivée" :
- on ne peut mettre à jour qu'une table à la fois


- la table mise à jour doit être "key-preserved"
Cela signifie que pour une ligne de la table à mettre à jour, il doit y avoir une ligne au plus dans le résultat de la vue.

Le terme "Key-preserved" s'applique bien à une table au sein d'une vue. Essayons par exemple de mettre à jour la table ecr avec les valeurs de acct en utilisant la même jointure :


SQL>  ALTER TABLE ecr MODIFY some_desc VARCHAR2(4000) --ptit ajustement à cause du CTAS raté :)
  2  /

Table altered.
SQL> UPDATE
  2    (SELECT some_desc, acct_desc
  3     FROM acct a
  4       JOIN ecr b ON a.acct_num = b.acct_num)
  5  SET some_desc= acct_desc;

1000 rows updated.

Ca marche, bien sûr, car une seule ligne est associée à chaque ligne de ecr.

La raison pour laquelle il faut que la table mise à jour doit être key-preserved se comprend aisément :
Si on a plusieurs lignes dans la jointure pour une même clef, on va sensément faire plusieurs mises à jour... et c'est la dernière qui gagne !

Mais la notion de dernière mise à jour dépend du plan d'exécution, ce qui est inadmissible.


Rassurez-vous, une telle aberration n'est pas non plus permise avec le MERGE :

SQL> MERGE INTO acct a
  2  USING ecr b
  3  ON (a.acct_num = b.acct_num)
  4  WHEN MATCHED THEN UPDATE SET a.some_amount = b.ecr_amt;
USING ecr b
      *
ERROR at line 2:
ORA-30926: unable to get a stable set of rows in the source tables



Jusque là, pas d'avantage concret pour le MERGE.

Sauf que la définition d'une key-preserved table est assez stricte !

Je trouve la doc Oracle un peu floue sur ce point, mais on note quand même :

The key-preserving property of a table does not depend on the actual data in the table. It is, rather, a property of its schema. For example, if in the emp table there was at most one employee in each department, then deptno would be unique in the result of a join of emp and dept, but dept would still not be a key-preserved table.


Par contre, l'Oncle Tom est un peu plus précis sur asktom :
You NEED a primary key/unique constraint on
object_id in T1 to ensure that each row in T joins to AT MOST 1 row in T1.


Et là, on a un problème : avoir besoin d'une contrainte d'unicité, ça limite les possibilités.
Par exemple, il est assez courant de vouloir mettre à jour une table avec un résultat d'aggrégation d'une autre table !

Voyons voir ce que ça donne...


4) After all, MERGE is good for you !

Supposons à présent que l'on veuille affecter pour chaque compte de la table acct, la somme des montants associés dans la table ecr :

SQL> UPDATE
  2    (SELECT a.some_amount, b.amt
  3     FROM acct a
  4       JOIN (SELECT acct_num, sum(ecr_amt) amt
  5             FROM ecr
  6             GROUP BY acct_num) b ON a.acct_num = b.acct_num
  7    )
  8  SET some_amount= amt;
SET some_amount= amt
    *
ERROR at line 8:
ORA-01779: cannot modify a column which maps to a non key-preserved table



Non key-preserved !

Et pourtant, en faisant la jointure avec un GROUP BY, on est sûr de n'avoir qu'une ligne !

Par contre avec le MERGE :

SQL> MERGE INTO acct a
  2  USING (SELECT acct_num, sum(ecr_amt) sumecr
  3         FROM ecr
  4         GROUP BY acct_num) b
  5  ON (a.acct_num = b.acct_num)
  6  WHEN MATCHED THEN UPDATE SET a.some_amount = b.sumecr;

100 rows merged.



Ca marche !


Un grand merci quoi qu'il en soit à Mnitu de m'emêcher de dire des conneries impunément...

Repost 0
Published by Pacman - dans SQL
commenter cet article
30 octobre 2009 5 30 /10 /octobre /2009 11:04

La question du jour :
Est-il équivalent de placer son prédicat (mono table) de jointure externe dans la clause LEFT OUTER JOIN, ou dans le WHERE en permettant les NULL ?
(En fait, c'est même la question du mois vu que je n'ai pas été capable de finir un article depuis longtemps...)


Ben non !

Et il y a plusieurs raisons, que nous illustrerons par l'exemple.

Dans la suite de l'article, il y aura :

0) Mise en jambes, jeux de tests

1) Si la colonne que vous voulez filtrer sur le résultat est nullable (peut prendre la "valeur" NULL), alors il peut y avoir confusion entre le NULL de "il n'y a pas de lignes qui matchent la correspondance" et "la colonne de la ligne répondant au critère de jointure est NULL"

2) Si une et une seule ligne de la table de droite (dans une jointure LEFT) répond au critère de jointure mais prend une valeur non NULL pour la colonne filtrée : 0 lignes renvoyées au lieu d'une !

3) Ca n'a rien à voir, mais un petit plébiscite pour la syntaxe ANSI contre la version dépréciée Oracle


0) C'est parti !


Les jeux de tests : une table maître, une table détail. Une seule ligne de détail pour Marcel, 3 lignes de détail pour Robert, 0 pour Squelettor.


CREATE TABLE TEST_LJ_MAITRE AS
SELECT 1 AS idm, 'Robert' as nom FROM DUAL UNION ALL
SELECT 2 AS idm, 'Marcel' as nom FROM DUAL UNION ALL
SELECT 3 AS idm, 'Squelettor' as nom FROM DUAL;

CREATE TABLE TEST_LJ_DETAIL AS
SELECT 1 AS ids, 1 as idm, 'M' as s FROM DUAL UNION ALL
SELECT 2 AS ids, 1 as idm, 'F' as s  FROM DUAL UNION ALL
SELECT 3 AS ids, 1 as idm, NULL as s  FROM DUAL UNION ALL
SELECT 4 AS ids, 2 as idm, 'F' as s  FROM DUAL;


La jointure externe, c'est comme une jointure normale, sauf que les lignes de gauche (pour LEFT, et droite pour RIGHT...) sont dans le résultat même si aucune ligne de la table de droite ne correspond au critères spécifiés dans la clause "ON".


SQL>  SELECT *
  2   FROM TEST_LJ_MAITRE a
  3     LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
  4   /

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         1 Robert              2          1 F
         1 Robert              3          1
         2 Marcel              4          2 F
         3 Squelettor

Au passage, on rappelle que :
- INNER JOIN est strictement équivalent à JOIN
- LEFT OUTER JOIN est strictement équivalent à LEFT JOIN

Personnellement, j'utilise JOIN et LEFT OUTER JOIN. Non pas par paresse, mais parce que dans la théorie des ensembles, une "relation" est naturellement "INNER"... alors que la jointure externe est plus difficile à rapprocher d'un concept mathématique simple.
Ainsi, je trouve que LEFT JOIN peut donner l'illusion pour les débutants que l'ordre des tables dans une jointure "INNER" à une signification, alors que ce n'est bien évidemment pas le cas. C'est pour cela que je précise le OUTER.

Après, c'est une histoire de goûts...


1) Il y a NULL et NULL !



On veut à présent récupérer les relations des MAITREs avec des DETAILs masculins (DETAIL.s = 'M'), et ceux qui ne sont pas en relation avec des DETAILs masculins (qui du coup auront les colonnes DETAIL à NULL)

Première tentative :


SQL> SELECT *
  2  FROM TEST_LJ_MAITRE a
  3    LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
  4  WHERE b.s = 'M'
  5  /

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M


Mon dieu, tout a disparu !
Ben oui, pour les MAITRE sans détail, le prédicat b.s = 'M' est faux !

Deuxième tentative :

SQL> SELECT *
  2   FROM TEST_LJ_MAITRE a
  3     LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
  4   WHERE b.s <> 'F'
  5  /

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M


Ca ne marche pas mieux. Ben ouais, NULL, ce n'est pas vraiment une valeur.
Quand b.s IS NULL, la question b.s <> 'F' a pour réponse : indéterminé !


Troisième tentative :

SQL> SELECT *
  2   FROM TEST_LJ_MAITRE a
  3     LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
  4   WHERE b.s = 'M' OR b.s IS NULL
  5  /

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         1 Robert              3          1
         3 Squelettor

On a certes ramené Squelettor... mais on ramené une deuxième ligne pour Robert, avec une relation de s. indéterminé !


2) Mon dieu, ils ont encore tué ma jointure externe !


Quatrième tentative :
On se dit que ce qui caractérise le côté ouvert, c'est que l'identifiant de la table externe soit NOT NULL. Et donc, on va compléter la condition précédente :

SQL> SELECT *
  2    FROM TEST_LJ_MAITRE a
  3      LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
  4    WHERE b.s = 'M' OR (b.s IS NULL AND b.ids IS NULL)
  5  /

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         3 Squelettor


Presque !
Mais le pauvre Marcel est passé à la trappe. Et je trouve que c'est le plus gros piège, car on pense souvent à inclure le NULL dans la clause WHERE... mais c'est vraiment insuffisant.
Ici, Marcel est en relation avec un seul DETAIL, mais de s = F.
Et donc, il passe la jointure
  "LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm"
Mais est refoulé dans le WHERE, quelle que soit la manière dont on le fasse !


Et du coup, quand un critère est vraiment "externe", il faut le mettre dans la clause ON qui est faite pour cela !

SQL>  SELECT *
  2   FROM TEST_LJ_MAITRE a
  3     LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
  4                      AND b.s = 'M';

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         2 Marcel
         3 Squelettor

Pas de discrimination, quelle que soit l'orientation !


3) Suivez la norme, tout le monde le dit !



Pour finir, la syntaxe "old school" :
Mettre un (+) dans les prédicats du WHERE.

SQL>  SELECT *
  2   FROM TEST_LJ_MAITRE a, TEST_LJ_DETAIL b
  3   WHERE a.idm = b.idm(+)
  4     AND b.s(+) = 'M';

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         2 Marcel
         3 Squelettor

Et effectivement, ça marche aussi.

Mais il faut rappeler que si cette syntaxe est disponible sous Oracle, ce n'est pas juste pour se démarquer des autres... mais simplement parce qu'elle a été mise en place avant la norme SQL 92 qui a standardisé les JOIN. (Attention, je n'ai pas dit légalisé, ni dépénalisé)


La preuve, c'est que même Oracle recommande la syntaxe normalisée :

Oracle Corporation recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause join syntax:[...]



(Ok, c'est un peu agressif le rouge, mais c'est pour bien insister !)

Tiré d'ici :
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/queries7.htm#2054625


Voilà, Voilà !

 

Repost 0
Published by Pacman - dans SQL
commenter cet article
14 août 2009 5 14 /08 /août /2009 16:58
The following article will be written in german.
Why ? No reason at all, i just hope that there are german people who are interested in SQL queries...

Na dann, los geht's.
Das heutige Thema handelt ein rekurrenter Bedürfnis :
Wie kann man Strings in einer grouppierung konkatinieren ?
Zum Beispliel, nehmen wir an, es gibt eine Tabelle der Fussballmannschaftenspieler : jede Zeile steht also für einen Spieler.
Team_mitglieder (Club, Spielername, Nummer)
Aus irgend einenem Grund, soll für jede Mannschaft eine einzige Zeile alle Namen der Mitglieder auslisten (getrennt mit komma zum Beispiel).

MySQL bietet die Funktion GROUP_CONCAT an. Und dadurch ist die Query ganz einfach :

SELECT Club, GROUP_CONCAT(Spielername, ',') ORDER BY Nummer
FROM Team_mitglieder
GROUP BY Club

MySQL ist leider das einzige DBMS, das diese Funktion direkt anbietet.
Im folgendem Artikel werden desshalb zwei verschiedene Wege (eigentlich gibt es noch einige anderen) präsentiert, das selbe in Oracle zu erreichen :
- durch die Verwendung der Baumstrukture Funktionen (CONNECT BY)
- mit den Oracle XML Funktionen

1) CONNECT BY Methode

Ihr wisst bestimmt schon, wofür mann dies gebrauchen kann :
Stellt euch vor, die Hierarchie in euren Firma wird von der Folgende Tabelle verkörpert :

CREATE TABLE Angestellte (
Id NUMBER,
Name VARCHAR(20),
LeiterId NUMBER) ;

-- die Oberleiterin
INSERT INTO Angestellte(Id, Name, LeiterId) VALUES (1, 'Angela', NULL);
-- Angela's erster Sklave
INSERT INTO Angestellte(Id, Name, LeiterId) VALUES (2, 'Franz', 1);
-- Angela's zweiter Sklave
INSERT INTO Angestellte(Id, Name, LeiterId) VALUES (3, 'Ulrich', 1);
-- Ulrich's Helfer
INSERT INTO Angestellte(Id, Name, LeiterId) VALUES (4, 'Klöden', 3);
-- Und heidi klum, nur so...
INSERT INTO Angestellte(Id, Name, LeiterId) VALUES (5, 'Heidi', 3);


Versuchen wir mal jetzt die ganze Hierarchie zu bilden :

SELECT path
FROM (
SELECT sys_connect_by_path(name, '->') AS path, CONNECT_BY_ISLEAF AS flg, CONNECT_BY_ROOT id rid
FROM Angestellte
CONNECT BY PRIOR id = leiterid
)
WHERE rid = 1
 
PATH
--------------------------------
->Angela
->Angela->Franz
->Angela->Ulrich
->Angela->Ulrich->Klöden
->Angela->Ulrich->Heidi


Jedes Wert wird rekursiv mit dem Vorgängern verknüpft, und die ganze Kette steht zur verfügung durch die Funktion SYS_CONNECT_BY_PATH.

Zurück zum Hauptthema : man braucht nur eine Einkantenbaumstruktur bestimmen, dessen Knoten die Spielernamen sind.
Erste Stufe : Nummerierung der Spieler
Zweite Stufe : Bildung des Baumes

CREATE TABLE test AS
SELECT    'Bayern' as team, 'Kahn' as name FROM DUAL UNION ALL
SELECT    'Bayern','Lucio'        FROM DUAL UNION ALL
SELECT    'Bayern','Traore'        FROM DUAL UNION ALL
SELECT    'Bayern','Philipp Lahm'        FROM DUAL UNION ALL
SELECT    'Bayern','Valerien Ismael'    FROM DUAL UNION ALL
SELECT    'Bayern','Michael Ballack'    FROM DUAL UNION ALL
SELECT    'Bayern','Jens Jeremies'    FROM DUAL UNION ALL
SELECT    'Bayern','Andreas Ottl'        FROM DUAL UNION ALL
SELECT    'Bayern','Johan Micoud'        FROM DUAL UNION ALL
SELECT    'Bayern','Ronaldo'        FROM DUAL UNION ALL
SELECT    'Bayern','Guerrero'        FROM DUAL UNION ALL
SELECT    'Bayern','Robbie Fowler'    FROM DUAL UNION ALL
SELECT    'Dortmund','Roman Weidenfeller'    FROM DUAL UNION ALL
SELECT    'Dortmund','Ashley Cole'    FROM DUAL UNION ALL
SELECT    'Dortmund','John Terry'        FROM DUAL UNION ALL
SELECT    'Dortmund','Ricardo Carvalho'    FROM DUAL UNION ALL
SELECT    'Dortmund','Phillip Lahm'    FROM DUAL UNION ALL
SELECT    'Dortmund','Sergio Ramos'    FROM DUAL UNION ALL
SELECT    'Dortmund','Patrick Vieira'    FROM DUAL UNION ALL
SELECT    'Dortmund','Bastian Schweinsteiger'    FROM DUAL UNION ALL
SELECT    'Dortmund','Steven Gerrard'    FROM DUAL UNION ALL
SELECT    'Dortmund','Cristiano Ronaldo'    FROM DUAL UNION ALL
SELECT    'Dortmund','Thierry Henry' FROM DUAL


SELECT  team, ltrim(path, ',') as path
FROM (
   SELECT team, SYS_CONNECT_BY_PATH(name, ',') as path, CONNECT_BY_ISLEAF flg
   FROM (
           SELECT team, name, ROW_NUMBER() OVER(PARTITION BY team ORDER BY NULL) rk
           FROM test
   )
   CONNECT BY PRIOR rk = rk - 1 AND PRIOR team = team
   START WITH rk = 1
)
WHERE flg = 1

TEAM
--------
PATH
------------------------------------------------------------------------------------------------------------------------------------
Bayern
Kahn,Lucio,Robbie Fowler,Guerrero,Ronaldo,Johan Micoud,Andreas Ottl,Jens Jeremies,Michael Ballack,Valerien Ismael,Philipp Lahm,Traor
e

Dortmund
Cristiano Ronaldo,Steven Gerrard,Bastian Schweinsteiger,Patrick Vieira,Sergio Ramos,Thierry Henry,Ricardo Carvalho,John Terry,Ashley
 Cole,Roman Weidenfeller,Phillip Lahm


- ROW_NUMBER() nummeriert die Zeilen in Jeder Gruppe definiert in der PARTITION BY Klause
- CONNECT_BY_LEAF ergibt 1, wenn das Wert ein Endknoten ist


2) XML Methode :

Seit Oracle 9i gibt es den Datentyp XMLTYpe und verschiedene passende Funktionen.
Unter anderen wertvollen Möglichkeiten, kann man :
- XML Elemente schaffen : XMLElem(Wert, Knotenname)
- Eine Gruppe XML Elemente aggregieren : XMLAgg(Element ORDER BY Reihenfolge)

Natürlich muss man danach die XML Markierungen reinigen... aber trotzdem bleibt das ganze ziemlich einfach :

SELECT team, RTRIM(REPLACE(REPLACE(XMLAgg(XMLElement("x", name )), '<x>', ' '), '</x>', ','), ',') as Agg
FROM test
GROUP BY team

TEAM
--------
AGG
------------------------------------------------------------------------------------------------------------------------------------
Bayern
 Kahn, Lucio, Traore, Valerien Ismael, Jens Jeremies, Johan Micoud, Guerrero, Robbie Fowler, Ronaldo, Andreas Ottl, Michael Ballack,
 Philipp Lahm

Dortmund
 Roman Weidenfeller, Sergio Ramos, Bastian Schweinsteiger, Cristiano Ronaldo, Thierry Henry, Steven Gerrard, Patrick Vieira, Phillip
 Lahm, Ashley Cole, John Terry, Ricardo Carvalho


Diese XML Methode finde ich intuitiver als die Baummethode.
Ausserdem mag sie aus dem sichtpunkt der Leistungen viel besser abschneiden, wenigstens bevor 10g.

Das war's !
Repost 0
Published by Pacman - dans SQL
commenter cet article
24 juillet 2009 5 24 /07 /juillet /2009 16:29
Un exercice assez courant : mettre à jour une table A avec les valeurs d'une table B, en faisant la correspondance sur un certain nombre de colonnes.
MySQL permet l'UPDATE de jointure... mais pas Oracle !
J'ai toujours trouvé ça assez naze, car la requête doit exécuter les mêmes accés deux fois pour :
- Rechercher les valeurs dans le SET
- délimiter le périmètre de mise à jour

Mais avec l'évolution de l'instruction MERGE sous Oracle 10g, le problème est enfin résolu !

Gros Erratum :
Sous 9i, il existe déjà une syntaxe pour le faire ! Merci à Mnitu pour l'info : son commentaire donne la syntaxe.
Cela dit, il faut que la table à modifier soit "key preserved" (et surtout le démontrer à Oracle...).
Vous trouverez un petit complément sur le sujet ici.


Allez, un petit exemple pour illustrer la chose.
Création des jeux de tests :

SQL> CREATE TABLE test_merge AS
  2  SELECT level AS id, 0 as val
  3  FROM DUAL
  4  CONNECT BY level <= 1000000
  5  /

Table créée.

SQL> CREATE UNIQUE INDEX test_merge_ii ON test_merge(id)
  2  /

Index créé.

SQL> CREATE TABLE test_merge_source AS
  2  SELECT id, id as val
  3  FROM test_merge
  4  WHERE mod(id, 1000) = 0
  5  /

Table créée.

SQL> CREATE UNIQUE INDEX test_merge_source_ii ON test_merge_source(id)
  2  /

Index créé.

Un petit coup de stats :
SQL> exec dbms_stats.gather_table_stats(NULL, 'TEST_MERGE')

Procédure PL/SQL terminée avec succès.

SQL> exec dbms_stats.gather_table_stats(NULL, 'TEST_MERGE_SOURCE')

Procédure PL/SQL terminée avec succès.



On active l'autotrace, et c'est parti !
SQL> set autotrace on

Première méthode, l'UPDATE simple :
SQL> UPDATE test_merge a
  2  SET val = (SELECT val
  3          FROM test_merge_source b
  4          WHERE a.id = b.id)
  5  /

1000000 ligne(s) mise(s) à jour.


Plan d'exécution
----------------------------------------------------------                                                                         
Plan hash value: 1824631898       
                                  
-----------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost
-----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                      |   996K|  5839K|   501
|   1 |  UPDATE                      | TEST_MERGE           |       |              
|   2 |   TABLE ACCESS FULL          | TEST_MERGE           |   996K|  5839K|   501
|   3 |   TABLE ACCESS BY INDEX ROWID| TEST_MERGE_SOURCE    |     1 |     7 |     2
|*  4 |    INDEX UNIQUE SCAN         | TEST_MERGE_SOURCE_II |     1 |       | 
         1
-----------------------------------------------------------------------------------
                                                                                                                                   
Predicate Information (identified by operation id):                                                                                
---------------------------------------------------                                                                                
                                                                                                                                   
   4 - access("B"."ID"=:B1)                                                                                                        


Statistiques
----------------------------------------------------------                                                                         
       2089  recursive calls                                                                                                       
    2073432  db block gets                                                                                                         
    1192599  consistent gets                                                                                                       
          0  physical reads                                                                                                        
  444612940  redo size                                                                                                             
        681  bytes sent via SQL*Net to client                                                                                      
        620  bytes received via SQL*Net from client                                                                                
          4  SQL*Net roundtrips to/from client                                                                                     
          6  sorts (memory)                                                                                                        
          0  sorts (disk)                                                                                                          
    1000000  rows processed                                                                                                        

SQL> rollback

  2  /


Comme on n'a pas délimité le périmètre, toutes les valeurs sans correspondance sont mises à NULL !

C'est pourquoi on est obligé d'ajouter :

SQL> UPDATE test_merge a
  2  SET val = (SELECT val
  3          FROM test_merge_source b
  4          WHERE a.id = b.id)
  5  WHERE EXISTS (SELECT NULL
  6             FROM test_merge_source b
  7             WHERE a.id = b.id)
  8  /

1000 ligne(s) mise(s) à jour.


Plan d'exécution
----------------------------------------------------------                                                                         
Plan hash value:2830137269                                                                                                        
                                                                                                                                   
-----------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost
-----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                      |  1000 | 10000 |   508
|   1 |  UPDATE                      | TEST_MERGE           |       |       |      
|   2 |   NESTED LOOPS               |                      |  1000 | 10000 |   508
|   3 |    SORT UNIQUE               |                      |  1000 |  4000 |     2
|   4 |     INDEX FAST FULL SCAN     | TEST_MERGE_SOURCE_II |  1000 |  4000 |     2
|*  5 |    INDEX UNIQUE SCAN         | TEST_MERGE_II        |     1 |     6 |     1
|   6 |   TABLE ACCESS BY INDEX ROWID| TEST_MERGE_SOURCE    |     1 |     7 |     2
|*  7 |    INDEX UNIQUE SCAN         | TEST_MERGE_SOURCE_II |     1 |       |     1
-----------------------------------------------------------------------------------

                                                                                                                                   
Predicate Information (identified by operation id):                                                                                
---------------------------------------------------                                                                                
                                                                                                                                   
   5 - access("A"."ID"="B"."ID")                                                                                                   
   7 - access("B"."ID"=:B1)                                                                                                        


Statistiques
----------------------------------------------------------                                                                         
         10  recursive calls                                                                                                       
       1032  db block gets                                                                                                         
       4013  consistent gets                                                                                                       
          0  physical reads                                                                                                        
     247328  redo size                                                                                                             
        686  bytes sent via SQL*Net to client                                                                                      
        700  bytes received via SQL*Net from client                                                                                
          4  SQL*Net roundtrips to/from client                                                                                     
          2  sorts (memory)                                                                                                        
          0  sorts (disk)                                                                                                          
       1000  rows processed                                                                                                        

SQL> rollback
  2  /

Annulation (rollback) effectuée.


En regardant le plan d'exécution, on voit bien que la jointure est réalisée 2 fois, ce qui est un véritable drame humain...
Du coup, sous 9i ou moins, il était plus efficace de faire CURSOR UPDATE en parcourant la jointure, un gros aveu d'échec.

Mais depuis 10g, il est possible d'utiliser MERGE (merci SQL 2003 !)pour faire faire un UPSERT... sans le SERT !
(Il n'est plus obligatoire d'implémenter les deux clauses MATCHED et NOT MATCHED.

La petite démo :
SQL> MERGE INTO test_merge a
  2  USING test_merge_source b
  3  ON (a.id = b.id)
  4  WHEN MATCHED THEN UPDATE SET a.val = b.val
  5  /
1000 lignes fusionnées.

Plan d'exécution
----------------------------------------------------------                                                                         
Plan hash value: 2484597143                                                                                                        
                                                                                                                                   
------------------------------------------------------------------------
| Id  | Operation            | Name              | Rows  | Bytes | Cost
------------------------------------------------------------------------                                         
|   0 | MERGE STATEMENT      |                   |  1000 | 15000 |   526
|   1 |  MERGE               | TEST_MERGE        |       |       |      
|   2 |   VIEW               |                   |       |       |      
|*  3 |    HASH JOIN         |                   |  1000 | 13000 |   526
|   4 |     TABLE ACCESS FULL| TEST_MERGE_SOURCE |  1000 |  7000 |     3
|   5 |     TABLE ACCESS FULL| TEST_MERGE        |   996K|  5839K|   501
------------------------------------------------------------------------

                                                                                                                                   
Predicate Information (identified by operation id):                                                                                
---------------------------------------------------                                                                                
                                                                                                                                   
   3 - access("A"."ID"="B"."ID")                                                                                                   


Statistiques
----------------------------------------------------------                                                                         
          1  recursive calls                                                                                                       
       1021  db block gets                                                                                                         
       1667  consistent gets                                                                                                       
          0  physical reads                                                                                                        
     248796  redo size                                                                                                             
        688  bytes sent via SQL*Net to client                                                                                      
        640  bytes received via SQL*Net from client                                                                                
          4  SQL*Net roundtrips to/from client                                                                                     
          1  sorts (memory)                                                                                                        
          0  sorts (disk)                                                                                                          
       1000  rows processed                                                                                                        

SQL> COMMIT
  2  /

Validation effectuée.


Ils ne sont pas mignons, le plan d'exécution / les stats ?



Repost 0
Published by Pacman - dans SQL
commenter cet article