Overblog Suivre ce blog
Administration Créer mon blog
30 avril 2011 6 30 /04 /avril /2011 07:44

On est samedi matin, 7h : j'ai commencé à lire du asktom.

... je pense que quelque chose tourne pas rond, je vais me faire une choucroute pour me calmer un peu.

 

Quoi qu'il en soit, notons que ce n'est pas la peine de chercher les paramètres non documentés dans v$parameters, et que les noms des tables non documentées correspondantes sont extrêmement faciles à retenir... c'est pour ça qu'on va les afficher ici comme aide mémoire.

(Sachant que bien sûr, les paramètres non documentés, personnellement, ça me fait vraiment de belles jambes... pour le retour du printemps, c'est parfait).

 

Puis au passage, le thread complet est assez rigolo, c'est dans la série du gros sac texan charmant personnage, qui n'est ni sénile ni d'une mauvaise foi totale, qui se fait ramoner par Tom :

 

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:47466211228419

 


Followup   September 12, 2005 - 2pm Central time zone:

 in order to see undocumented parameters, you must use an undocumented table ;)


select x.ksppinm name, y.ksppstvl value
from x$ksppi x , x$ksppcv y
where x.indx = y.indx
/


Repost 0
Published by Pacman - dans SGBD
commenter cet article
29 avril 2011 5 29 /04 /avril /2011 13:49

La récursivité pure SQL sous Oracle, c'est pas évident avant la 11g.
Souvent lors de petits défis entre amis, je me suis bien pris la tête, sans grands résultats.
... jusqu'aujourd'hui, où j'ai dépilé une importante tâche de mon énooooorme to-do list : me documenter sur la CLAUSE MODEL de 10g.

 

A vrai dire, je ne suis toujours pas sûr de ce qu'on peut en faire dans la vraie vie... mais en tous cas on peut bien rigoler avec !

 

1) La clause MODEL en quelques mots

 

S'appliquant sur le résultat d'un SELECT, elle permet de considérer le résultat comme un tableau (éventuellement multi-dimensionnel), et de faire joujou avec.
 

Pour avoir les détails, je vous renvoie vers l'excellent article de l'excellent Antoun qui est en fait une excellente traduction d'un non moins excellent Neerlandais dont j'ai oublié le très excellent nom (je me souviens juste qu'il fait plein de points au scrabble) :
http://antoun.developpez.com/oracle/model/

 

2) Les suites numériques !

 

Donc voilà moi ce que j'en ai retenu, c'est qu'on peut créer des nouvelles "cases" dans le "tableau", et ce de manière itérative en faisant référence aux cases précédentes.

 

Du coup, mon grand fantasme est réalisé : sortir la sutie de Fibonacci en "pur" SQL :


  SELECT n, u
  FROM (
    SELECT 0 n, 0 U FROM DUAL
    UNION ALL SELECT 1 n, 1 U FROM DUAL
    ) t
  MODEL
   dimension by (n)
   measures (u)
   rules upsert
   iterate(10)
   (u[2+iteration_number] = u[1+iteration_number] + u[iteration_number])

 

Petite explication pour ceux qui ont eu la flemme de suivre le lien :
- Le résultat du SELECT sans le MODEL, c'est le tableau initial.

 

- J'y mets n et U(n), j'y initialise les premiers éléments de la suite : U0 = 0, U1 = 1

 

- Dimension, c'est ce qui indexe le tableau : n, puisqu'on fait du U(n)

 
- Measures, ça doit lister toutes les grandeurs du tableau, ici juste U(n)

 
- Rules : c'est là que commencent les règles de calcul

 


- Upsert : c'est pour dire que si pour un n donné U(n) existe déjà dans le tableau, on le met à jour, sinon on crée une nouvelle ligne. On peut spécifier Update ou Upsert All (ce dernier étant un truc obscur, je le rajoute à ma to-do list)
Iterate(10) : ca dit que la règle qui va suivre, faudra l'appliquer 10 fois (en fait 11, parce qu'on commence à 0 je dirais)

 
- Et la dernière ligne, c'est la règle de calcul à appliquer. Ici c'est la définition de la récurrence : Un+2   =   Un+1   +   Un.

 

Tadaaaam !

 

         N          U
---------- ----------
         0          0
         1          1
         2          1
         3          2
         4          3
         5          5
         6          8
         7         13
         8         21
         9         34
        10         55
        11         89

 

12 rows selected.

 

(Va falloir que je change de froc, je me suis fait dessus)

 

3) Produit cumulatif

 

Je sais pas si ça se fait dans la vraie vie, mais parmi les nombreuses applications de la récursivité, il y a le produit cumulatif.

Et la requête MODEL, je sens qu'elle est encore plus grottesque que la version Exp(Sum(Ln(x))

 

Alors, un jeu de données :

SQL> CREATE TABLE testcumproduct
  2  (grp NUMBER,
  3   ratio NUMBER);

 

C'est parti !

 

SQL> select grp, ratio, rn, nb
  2  from (
  3    select grp, ratio, row_number() over(partition by grp order by null) rn, count(*) over(partition by grp) nb
  4    from testcumproduct
  5  ) t
  6  model
  7    return updated rows
  8    partition by (grp)
  9    dimension by (rn)
 10    measures(ratio, nb)
 11    rules upsert
 12    iterate (100) until (iteration_number = nb[1]-1)
 13    ( ratio[1] = ratio[1] * coalesce(1+ratio[2+iteration_number], 1) );

 

       GRP      RATIO         RN         NB
---------- ---------- ---------- ----------
         1      .3432          1          4
         0     1.0296          1          5

 

A noter que :
- j'ai multiplié par 1+ ratio, pour faire genre ce sont des taux
- La clause partition permet d'émuler le group by
- J'itère en mettant à jour la première ligne de chaque groupe
- La clause return updated rows permet de compléter l'émulation du group by
- J'aurais bien voulu mettre directement mon nb dans en paramètre d'itération, mais il veut pas...

 

Voilà voilà, encore un article complètement inutile, vous êtes bien sur le PacBlog !

 

 

Repost 0
Published by Pacman - dans SQL cosmétique
commenter cet article
22 avril 2011 5 22 /04 /avril /2011 12:13

Heute bin ich um 3 Uhr morgens aufgewacht, und dieses Mal nicht wegen meines Kinds Schreien, sondern wegen der Frage im Titel.
(Ansonsten geht's meiner 4 Monaten alten Tochter ganz gut : sie schläft wie ein Sack, jede Nacht 12 Stunden)

 

Insofern eine IOT (index organized table) als Baum strukturiert ist (B-Tree), wie sieht den Ausführungsplan aus ?

 

Die Testtabellen :

 

SQL> CREATE TABLE testiot1 AS

  2  SELECT level key, level / 2 val

  3  FROM DUAL

  4  CONNECT BY level <= 100000;

 

Table crÚÚe.

 

SQL> ALTER TABLE testiot1 ADD CONSTRAINT testiot1 PRIMARY KEY (key);

 

Table modifiÚe.

 

SQL> CREATE TABLE testiot2 (

  2  key,

  3  val,

  4  CONSTRAINT testiot2pk PRIMARY KEY (key))

  5  ORGANIZATION INDEX

  6  AS SELECT * FROM testiot1;

 

Table crÚÚe.

 

Jetzt den Ausführungsplan :

 

SQL> EXPLAIN PLAN FOR

  2  SELECT * FROM testiot2

  3  /

 

ExplicitÚ.

 

EcoulÚ : 00 :00 :00.01

SQL> SELECT *

  2  FROM table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

 

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

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

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

|   0 | SELECT STATEMENT |            | 90755 |  2304K|    51   (0)|

|   1 |  INDEX FULL SCAN | TESTIOT2PK | 90755 2304K|    51   (0)|

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

 

 

Keine Uberraschung, die ganze Tabelle ist in dem PK Index gespeichert... (Na ja, vielleicht hätten wir ein FAST FULL SCAN erwarten dürfen...)

Also das war jetzt ein ganz überflüssiges blödes Post, und darauf bin ich sehr Stolz.

 

Noch eine weitere nutzlose Bemerkung :
Eine IOT ist unoptimiziert für FULL SCANS, den die Baumstruktur soll entweder gereinigt werden (FAST FULL SCAN), oder durchgelesen werden (mit überflüssige dateien).

 

SQL> set autotrace traceonly statistics

SQL> SELECT * FROM testiot1;

 

100000 ligne(s) sÚlectionnÚe(s).

 

EcoulÚ : 00 :00 :00.03

 

Statistiques

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

          0  recursive calls

          0  db block gets

        413  consistent gets

          0  physical reads

          0  redo size

    1130447  bytes sent via SQL*Net to client

       1869  bytes received via SQL*Net from client

        201  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

     100000  rows processed

 

SQL> SELECT * FROM testiot2;

 

100000 ligne(s) sÚlectionnÚe(s).

 

EcoulÚ : 00 :00 :00.05

 

Statistiques

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

          0  recursive calls

          0  db block gets

        440  consistent gets

          0  physical reads

          0  redo size

    1130448  bytes sent via SQL*Net to client

       1869  bytes received via SQL*Net from client

        201  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

     100000  rows processed

 

Also, falls ihr echt was über IOT wissen wollt : 

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg07iot.htm

 

 

 

Repost 0
Published by Pacman - dans SGBD
commenter cet article
14 avril 2011 4 14 /04 /avril /2011 15:13

Cher journal intime,

 

Je dois te faire un terrible aveux :

J'ai commencé à te tromper avec un autre média d'exhibitionnisme mental :

http://twitter.com/indexffs

 

Eh oui, avec toi, c'est toujours pleins d'attentions, du temps, de l'argent...

 

... alors que Twitter, c'est à la pulsion, du jet instantanné !

 

Plus concrètement, sur le PacTwitt, ce sont des liens vers des sites qui documentent des fonctionnalités que je trouve sympa, ou juste un petit commentaire sur des sujets pour lesquels je n'ai ni le temps, ni la patience pour en faire un vrai article.

 

L'autre différence, c'est que contrairement au PacBlog, le PacTwitt n'est pas obligatoire inutile !

Repost 0
Published by Pacman - dans Journal intime
commenter cet article
15 octobre 2010 5 15 /10 /octobre /2010 13:22

Cher journal intime,

 

Comme tu le sais déjà, Z/Zn est un anneau, quoi qu'il arrive.
Mais qu'est ce qui peut assurer l'inversibilité de chacun de ses éléments ?
Je veux essayer de répondre, sans utiliser les théorèmes d'intellos autistes que je ne comprends pas !


1) Si n n'est pas premier, c'est vite réglé :


On peut trouver p > 1 qui divise n...
... et tu fais comment maintenant pour trouver des candidats à :
p * q = s * n + 1 ???
(Oui, on reconnaît l'identité de bezout... comme quoi ce n'est pas idiot)

 

Partons de p.
p = n * 0 + p, et p > 1

 

Ensuite, on colle successivement (itérativement) des p :
p + p = 0 * n + 2p, et 2p > 1 bien sûr

 

On continue à coller des morceaux jusqu'à ce qu'à atteindre n (puisque p divise n)
p * q = n + 0

 

En collant le suivant, on revient au début et ça boucle !
p * (q+1) = n + p

 

Donc le plus petit reste qu'on peut obtenir en multipliant p par quelque chose et divisant par n... c'est p > 1 !

 

Donc, si n est non premier, il existe au moins un élément de Z/Zn* non inversibe.

 

2) Si n est premier :

 

Prenons un p quelconque entre 2 et n - 1, et cherchons si on lui trouve un inverse dans Z/Zn.
Pour cela, regardons juste ce qu'il se passe quand on tente de le multiplier par chacun des éléments :

r = 0 * p mod n
r = 1 * p mod n
r = 2 * p mod n
...
r = n-1 * p mod n

 

Sur un exemple, p = 7, n = 11

 

i 0 1 2 3 4 5 6 7 8 9 10
p * i 0 7 14 21 28 35 42 49 56 63 70
r 0 7

3

10 6 2 9 5 1 8 4

 

 

(Ho, ça m'a bien fait réviser mes tables de 7 et de 11 !)

 

=> Si on trouve un inverse, c'est bien parce que l'opération * p réalise une permutation de Z/Zn !
(En clair, si en itérant 11 fois, je trouve 11 résultats différents entre 0 et 10, je vais forcément tomber une fois sur le gros lot...)

 

Ca se montre et s'explique aisément :
Si jamais on trouvait deux fois le même résultat, pour deux multiplicateurs différents q' > q, on sait que la différence aurait un reste nul par la division par n :
p * q' - p * q = k n
p * (q' - q) = k n

 

Et comme q' - q est compris entre 1 et n-1, on aurait :
- soit q' - q = 1 et donc p = k n : ouh là, pas possible
- soit q' - q > 1 et là on a trouvé un diviseur à n qui est premier : pas possible non plus !

 

C'est donc impossible, et on retrouve en fait sur la fameuse boucle qu'on avait eu plus haut : si on retombe deux fois sur le même reste, c'est qu'on est en train de boucler... et donc on a du passer par zéro.

 

Donc, si n est premier, tout p de Z/Zn* est inversible.

 

3) Allez, pour ne pas piéger les petits jeunes de terminale qui doivent vraiment démontrer le truc au bac, on va quand même rappeler la démonstration instantannée par l'identité de bezout.

 

Soit n entier strictement supérieur à 0.
Z/Zn est un corps si et seulement :
Pour tout p différent de 0 appartenant à Z/Zn, il existe q appartenant Z/Zn tel que p * q = 1 (multiplication dans Z/Zn)
<=> il existe k entier naturel tel que :
p * q = k * n + 1
p * q + (-k) * n + 1

 

Z/Zn est un corps si et seulement si quelque soit p dans [1,n-1], p et n sont premiers entre eux.
... ce qui veut exactement dire que n est premier !


 

Repost 0
Published by Pacman - dans Journal intime
commenter cet article
8 octobre 2010 5 08 /10 /octobre /2010 18:23

Cher journal intime,

Ces jours-ci, une terrible phrase me hante :
"Z/Zn est un corps, si et seulement si n est premier"

Plus que la réponse basique "parce que Bezout", je voudrais saisir l'essence même de l'inversibilité de ces éléments.
C'est mon prochain objectif dans la vie.

Mais en attendant, le Pacblog inaugure ses nouvelles couleurs.
J'en ai profité également pour écrire mon premier pense bête PL / SQL.

... avec une astuce lamentable pour faire du syntax-highlight :
- J'écris le code sous TOAD qui me fait la coloration syntaxique
- Je le colle dans Word qui garde la mise en forme
- Je sauvegarde en HTML
- Je copie / colle le source du HTML sur le PacBlog...

(Oui, c'est honteux)




Repost 0
Published by Pacman - dans Journal intime
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
9 mars 2010 2 09 /03 /mars /2010 17:24

Recently, i've read an article from Steven Feuerstein about stripping strings :
http://www.oracle.com/technology/oramag/oracle/06-jan/o16plsql.html

Starting with Oracle 10g, you can use REGEXP_REPLACE to strip any string from a list of given chars.
However, in earlier version, he illustrates the use of the TRANSLATE function for this purpose.

The issues can be summed up as :
- Passing a NULL second argument to the translate function nullyfies the result :

SELECT translate('Steven Feuerstein', 'x', '') FROM dual;
T
-
 
1 row selected.

- The trick : use a joker char which you replace by... itself

SELECT translate('Steven Feuerstein', 'xe', 'x') FROM dual
TRANSLATE('S
------------
Stvn Furstin

1 row selected.

... but you cannot use a constant joker character for all of you strippings, because this character might be the one you want to strip off !
That's why Uncle Steven suggest you should have this joker char beeing as well an argument of your stripping function.

Today in this article, i'll just show a funny way to do it without joker char argument ;)
Disclaimer : i don't want to compete with master Steven, just having a little fun with Oracle SQL !


1) CONNECT BY LEVEL forever !

Let's use the CONNECT BY LEVEL bug to "spell" the string in rows :

SELECT substr('Steven Feuerstein', level, 1) as c, level as rk
FROM dual
CONNECT BY level <= length('Steven Feuerstein')

C         RK
- ----------
S          1
t          2
e          3
v          4
e          5
n          6
           7
F          8
e          9
u         10
e         11
r         12
s         13
t         14
e         15
i         16
n         17

17 rows selected.

Now the stripping just looks like a filter :

SELECT c, row_number() over(order by rk) rk from
    (SELECT substr('Steven Feuerstein', level, 1) as c, level as rk
    FROM dual
    CONNECT by level <= length('Steven Feuerstein')) inp
 WHERE inp.c <> 'e'

C         RK
- ----------
S          1
t          2
v          3
n          4
           5
F          6
u          7
r          8
s          9
t         10
i         11
n         12


12 rows selected.

2) Re-aggregate it... using replace ?!

You sure noticed the ranking of the lines in the previous query. The purspose was obvious : beeing able to re-aggregate the chars after filtering !

In Oracle 11g version, the build in function LISTAGG would make it quite easy.

I choose the XMLAGG method that works in 9i :
- you put your string in single XML tags
- you aggregate the tags...
... then you strip the tags from the resulting string !


Really funny, was there really an issue from the beginning ?


SELECT replace('Steven Feuerstein', 'e', '') as A, translate('Steven Feuerstein', 'e', '') AS b
FROM dual;

REPLACE('STE T
------------ -
Stvn Furstin 


1 row selected.


Yeah, just as expected !
But you can only give one character to strip (unless you want to strip it from a string, which differs from stripping several different chars) as argument of the REPLACE function.


Let's change our example to prove the worthiness of our trick and strip the input string from two chars : "e" and "t"
... and re-aggregate the result :


SELECT replace(replace(xmlagg(xmlelement("marcel", c) order by rk), '<marcel>', ''), '</marcel>', '')
FROM
(SELECT c, row_number() over(order by rk) rk from
    (SELECT substr('Steven Feuerstein', level, 1) as c, level as rk
    FROM dual
    CONNECT by level <= length('Steven Feuerstein')) inp
 WHERE inp.c NOT IN (SELECT substr('et', level, 1) FROM dual CONNECT BY level <= length('et'))
 )

REPLACE(REPLACE(XMLAGG(XMLELEMENT("MARCEL",C)ORDERBYRK),'<MARCEL>',''),'</MARCEL
--------------------------------------------------------------------------------
Svn Fursin                                        
                             

1 row selected.

Great...
...But if you really want to use this underperforming method that could become obsolete whenever the CONNECT BY LEVEL bug would be fixed, you have to put it in a function !
Because you cannot "pass" arguments to the deeper levels of a nested query expression.


Cosmetic SQL Rules !


 

Repost 0
Published by Pacman - dans SQL cosmétique
commenter cet article
9 janvier 2010 6 09 /01 /janvier /2010 11:55
Cher journal intime,

Depuis maintenant trois semaines, j'ai rejoint le côté obscur de l'informatique, qui n'est presque plus de l'informatique d'ailleurs... et je m'éloigne du monde d'Oracle après presque 2 ans d'acharnement.

Par la même, c'est également mon lointain rêve de devenir un jour DBA qui perd de sa consistence...

Cela dit, je ne lacherai pas l'affaire si facilement !

C'est donc le moment de faire la liste des bonnes résolutions pour 2010 :

- S'efforcer de poster une fois toutes les deux semaines
- Lire plus de docs Oracle, et plus de Asktom !
- Créer des pages pour expliquer / décrire les concepts Oracle afin de pouvoir y faire référence
- Vaincre Dark Sidious, et dominer l'empire à sa place.

Ah oui au passage : Bonne année !
Repost 0
Published by Pacman - dans Journal intime
commenter cet article
10 décembre 2009 4 10 /12 /décembre /2009 15:13
Il y a une problématique assez connue et plutôt sérieuse : le comportement des sous-requêtes NOT IN face aux valeurs NULL.
Mais l'autre jour, je me suis aperçu qu'il y a un autre point rigolo : la nullabilité des colonnes a aussi un impact sur l'algorithme de jointure utilisé !

Et disons le : entre le HASH JOIN ANTI, et le FILTER, il y a vraiment un monde !

Allez, on va regarder ça d'un peu plus près :
1) NOT IN et NULL : pourquoi les résultats sont "faussés".
2) HASH JOIN ANTI, ça arrache
3) Pas de NULL ? Dites-le à Oracle !
4) Petits bonus

1) True, false, ou... on n'en sait rien !

On va prendre un exemple tout simple : une table contenant les valeurs 2 NULL, une autre table avec deux lignes prenant les valeurs 1 et NULL.

CREATE TABLE testnotin1
AS SELECT 2 nb FROM DUAL UNION ALL
SELECT null FROM DUAL;

CREATE TABLE testnotin2
AS SELECT 1 nb FROM DUAL UNION ALL
SELECT null FROM DUAL;


Quelles sont les lignes de la première table dont la valeur n'est pas dans la deuxième ?

SELECT *
FROM testnotin1
WHERE nb NOT IN (SELECT nb FROM testnotin2);

no rows selected


Explication :
Pour qu'une ligne de testnotin1 (a) soit retenue, il faut que pour chaque ligne de testnotin2 (b) :
le prédicat (affirmation) a.nb = b.nb soit fausse.
Or, a.nb = NULL (de même que NULL = b.nb) ne retourne ni vrai, ni faux, mais "indéterminé" !

Ici, il faut encore noter la différence de logique de l'approche "Il n'existe pas de ligne dans b, telle que a.nb = b.nb" :

SELECT *
FROM testnotin1 a
WHERE NOT EXISTS (SELECT 1
FROM testnotin2 b
WHERE a.nb = b.nb);

        NB
----------

         2


(on ne voit pas très bien le NULL, mais le résultat comporte bien deux lignes !)
A présent, on garde la ligne de A s'il n'y a aucune ligne de b pour laquelle le prédicat a.nb = b.nb retourne "vrai".
Or le cas indéterminé n'étant pas "vrai", il n'est pas une entrave pour la sélection de la ligne.

2) Le HASH, c'est bon !


Pour effectuer la requête NOT IN, Oracle a bien entendu plusieurs solutions.
Ici, je veux juste faire un peu de pub pour la méthode de jointure HASH JOIN ANTI.

Pour cela, on va créer deux tables "idéales" : l'une contenant beaucoup de lignes, l'autre peu :

CREATE TABLE test_hash_aj (id NOT NULL, val) AS
SELECT level id, lpad('x', 255, 'x') as val
FROM dual
CONNECT by level <= 1000000;

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

CREATE TABLE test_hash_aj2 (id primary key, val) AS
SELECT level * 1000 id, lpad('x', 255, 'x') as val
FROM dual
CONNECT by level <= 1000
/

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


a. La méthode perdante :
Pour chaque ligne de A, on utilise l'index unique de B pour voir s'il y a ou non une correspondance (on force avec le hint nl_aj) :

SELECT *
FROM test_hash_aj a
WHERE id NOT IN (SELECT /*+nl_aj*/ id FROM test_hash_aj2 b)
/

999000 rows selected.

Elapsed: 00:00:03.42

-----------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost
-----------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |   997K|   251M| 10891
|   1 |  NESTED LOOPS ANTI |              |   997K|   251M| 10891
|   2 |   TABLE ACCESS FULL| TEST_HASH_AJ |   998K|   247M| 10478
|*  3 |   INDEX UNIQUE SCAN| SYS_C007206  |     1 |     4 |     0
-----------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    1041999  consistent gets
         52  physical reads
          0  redo size
   10225277  bytes sent via SQL*Net to client
      22362  bytes received via SQL*Net from client
       1999  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     999000  rows processed



b. Deuxième méthode : on crée une table de hashage, qui a chaque valeur de id dans la table B, associe une adresse mémoire (ou index de tableau).
Puis on lit la table A, et pour chaque ligne, on regarde si la fonction de hashage renvoie quelque chose pour l'id.

SELECT *
FROM test_hash_aj a
WHERE id NOT IN (SELECT  id FROM test_hash_aj2 b)
/

999000 rows selected.

Elapsed: 00:00:01.90


--------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost
--------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |   997K|   251M| 10502
|*  1 |  HASH JOIN RIGHT ANTI |              |   997K|   251M| 10502
|   2 |   INDEX FAST FULL SCAN| SYS_C00720   |  1000 |  4000 |     2
|   3 |   TABLE ACCESS FULL   | TEST_HASH_AJ |   998K|   247M| 10478
--------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      40005  consistent gets
          0  physical reads
          0  redo size
   10225277  bytes sent via SQL*Net to client
      22362  bytes received via SQL*Net from client
       1999  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     999000  rows processed



Eh oui, la deuxième méthode fait moins de consistent gets, et s'exécute plus rapidement.
Il faut quand même préciser qu'ici, la différence est limitée par le fait que la distribution des données est parfaite pour le parcours par index !

3) NULL ou bon ? Le CBO ne peut pas le deviner !

Reprenons nos deux tables, mais nettoyons un peu les contraintes :

SQL> ALTER TABLE test_hash_aj MODIFY id NULL;

Table altered.

Elapsed: 00:00:00.04
SQL> ALTER TABLE test_hash_aj2 DROP CONSTRAINT SYS_C00720 KEEP INDEX;

Table altered.



Et retentons l'expérience :

SELECT *
FROM test_hash_aj a
WHERE id NOT IN (SELECT  id FROM test_hash_aj2 b)
/

999000 rows selected.

Elapsed: 00:01:40.93

Execution Plan
----------------------------------------------------------
Plan hash value: 4041281780

-------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |   998K|   247M|    12M
|*  1 |  FILTER            |               |       |       |      
|   2 |   TABLE ACCESS FULL| TEST_HASH_AJ  |   998K|   247M| 10482
|*  3 |   TABLE ACCESS FULL| TEST_HASH_AJ2 |     1 |     4 |    13
-------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TEST_HASH_AJ2" "B" WHERE
              LNNVL("ID"<>:B1)))
   3 - filter(LNNVL("ID"<>:B1))


Statistics
----------------------------------------------------------
        189  recursive calls
          0  db block gets
   41021041  consistent gets
          0  physical reads
          0  redo size
   10225277  bytes sent via SQL*Net to client
      22362  bytes received via SQL*Net from client
       1999  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
     999000  rows processed


Le HASH JOIN ANTI n'est plus choisi... et l'index ne peut être utilisé !
Du coup, on se retrouve avec un opération filter qui coute vraiment très cher : on réexécute la recherche brutale dans B pour chaque ligne de A.

=> Oracle ne sait pas que les colonnes ne contiennent pas de NULL. Or les indexes ne stockent pas les entrées dont toutes les colonnes sont nulles.
... et ils semble donc également que les NULL posent des problèmes à la table de hashage.

Par contre, la méthode NOT EXISTS ne souffre pas du même problème :

 SELECT *
 FROM test_hash_aj a
 WHERE NOT EXISTS (SELECT  1 FROM test_hash_aj2 b WHERE a.id = b.id)
/
--------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost
--------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |   997K|   251M| 10502
|*  1 |  HASH JOIN RIGHT ANTI |              |   997K|   251M| 10502
|   2 |   INDEX FAST FULL SCAN| SYS_C00720   |  1000 |  4000 |     2
|   3 |   TABLE ACCESS FULL   | TEST_HASH_AJ |   998K|   247M| 10478
--------------------------------------------------------------------


Ce qui se comprend assez bien, puisque selon la logique décrite dans le premier paragraphe, la comparaison à une valeur NULL laisse le résultat global inchangé.

Mais tentons quand même d'expliquer clairement à Mr CBO que notre requête ne contient vraiment pas de NULL :

 SELECT *
 FROM test_hash_aj a
 WHERE id NOT IN (SELECT  id FROM test_hash_aj2 b WHERE id IS NOT NULL)
   AND id IS NOT NULL
/

--------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost
--------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |   997K|   251M| 10506
|*  1 |  HASH JOIN RIGHT ANTI |              |   997K|   251M| 10506
|*  2 |   INDEX FAST FULL SCAN| SYS_C00720   |  1000 |  4000 |     2
|*  3 |   TABLE ACCESS FULL   | TEST_HASH_AJ |   998K|   247M| 10482
--------------------------------------------------------------------



Ca marche !
A noter qu'ajouter COALESCE n'aide pas, et qu'il faut restreindre les NULL des deux côtés.

Cette histoire est assez traitre, parce que lorsqu'on a conscience du problème de "faux" résultats pour cause de NULL, on peut oublier que même lorsqu'il n'y a pas de NULL, les performances peuvent être lamentables en dépit d'une requête qui semble bien écrite...


4) Au passage...

a. A propos des indexes et des valeurs NULL
Ne pensez pas que les clefs ne sont pas stokées dans un index si une valeur est nulle. En fait, c'est uniquement lorsque toutes les colonnes composant l'index sont nulles que la clef n'est pas stockée.

Petit test :

CREATE INDEX test_hash_aj2_i ON test_hash_aj2(id, 1) COMPUTE STATISTICS
/

SELECT *
FROM test_hash_aj a
WHERE id NOT IN (SELECT  id FROM test_hash_aj2 b);

------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Bytes | Cost
------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |   998K|   247M|  3013K
|*  1 |  FILTER               |                 |       |       |
|   2 |   TABLE ACCESS FULL   | TEST_HASH_AJ    |   998K|   247M| 10482
|*  3 |   INDEX FAST FULL SCAN| TEST_HASH_AJ2_I |     1 |     4 |     3
------------------------------------------------------------------------

b. NOT IN (NULL)... lnnvl !
Reprenons le plan d'exécution de la requête NOT IN, plus précisément aux informations sur les prédicats :

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TEST_HASH_AJ2" "B" WHERE
              LNNVL("ID"<>:B1)))
   3 - filter(LNNVL("ID"<>:B1))


La fonction LNNVL, renvoie la négation du résultat du prédicat en paramètre lorsque celui-ci vaut TRUE ou FALSE, et renvoie également TRUE lorsque le résutat est indéterminé :

WITH tt AS (SELECT 1 nb FROM dual
  UNION ALL SELECT null FROM dual
  UNION ALL SELECT 2 FROM DUAL)
SELECT nb, 'ouais !'
FROM tt
WHERE lnnvl(nb = 2)
/

 NB 'OUAIS!
--- -------
  1 ouais !
    ouais !


Et donc dans le filtre de l'EXPLAIN, on voit bien que le NULL est traité de la même manière que l'égalité, donc comme un critère de disqualification de la ligne.



Repost 0
Published by Pacman - dans SGBD
commenter cet article