Overblog Suivre ce blog
Administration Créer mon blog
15 août 2013 4 15 /08 /août /2013 14:21

- Moi : "Salut, ça te dérange si je crée des JOB sur la base de test ? Ca n'ira jamais en prod"

 

- Mon DBA : "Bah pas de problème, je t'ai attribué le rôle DBA, fais toi plais'. Cela dit, si c'est pour des rapports XXXX, ils sont déjà déposés en automatique [...je vous passe les détails]"

 

- Moi : "Non, non, c'est pour Rémy, qui m'a demandé de lui rappeler tous les jours qu'il doit commander le repas"

 

- Mon DBA : "Euh... ouais. Dans ce cas, j'ai une 11GR2 de test pour préparer la migration, je te crée un compte dessus"

 

Bref, je me retrouve avec une 11gR2 et je vais faire joujou avec dbms_schudeler et utl_mail.

 

1) La structure

 

Bon, histoire de faire un peu de paramétrage sans faire d'usine à gaz non plus, on se limite : 

 

Aux données personnes : 

 

PACMANN>CREATE TABLE personne (

  2  id_personne NUMBER NOT NULL PRIMARY KEY,

  3  nom VARCHAR2(50) NOT NULL,

  4  prenom VARCHAR2(50) NOT NULL,

  5  email VARCHAR2(50));

 

Table created.

 

Aux notifications, identifiées fonctionnellement par un code

 

PACMANN>CREATE TABLE message (

  2  id_message NUMBER NOT NULL PRIMARY KEY,

  3  objet VARCHAR2(100) NOT NULL,

  4  corps VARCHAR2(4000),

  5  code_message VARCHAR2(50) NOT NULL UNIQUE);

 

Table created.

 

Une table de lien pour abonner les gens aux notifications : 

 

PACMANN>CREATE TABLE message_destinataire (

  2  id_message NUMBER NOT NULL,

  3  id_personne NUMBER NOT NULL,

  4  CONSTRAINT pk_mess_dest PRIMARY KEY (id_message, id_personne),

  5  CONSTRAINT fk_md_message FOREIGN KEY (id_message) REFERENCES message(id_message),

  6  CONSTRAINT fk_md_personne FOREIGN KEY (id_personne) REFERENCES personne(id_personne));

 

Table created.

 

2) Les données

 

J'insère Rémy et moi. Anonymisés bien sûr dans le post de blog.

On note aussi que la syntaxe INSERT ALL est tout à fait superflue ici, l'esprit étant d'insérer dans plusieurs fois (potentiellement dans des tables différentes) à partir d'une même source.

(Ici dual, puisque comme dit, il s'agit juste d'épater les newbs)

 

PACMANN>INSERT ALL

  2  INTO personne VALUES(1, 'PACMANN', 'Pacmann', 'yyyy@yyyy.com')

  3  INTO personne VALUES(2, 'ANONYMOUS', 'Rémy', 'xxxx@xxxx.com')

  4  SELECT * FROM dual;

 

2 rows created.

 

PACMANN>commit;

 

Commit complete.

 

Dans le message, on voit apparaître une variable $1, qui permettra ensuite de personnaliser les messages à l'exécution.

 

PACMANN>INSERT INTO message

  2  VALUES (1, 'Notification repas',

  3  'N''oublies pas de commander ton repas de midi, $1 !',

  4  'NOTIF_REPAS_MIDI');

 

1 row created.

 

PACMANN>commit;

 

Commit complete.

 

PACMANN>INSERT INTO message_destinataire

  2  SELECT id_message, id_personne

  3  FROM message

  4    CROSS JOIN personne;

 

2 rows created.

 

PACMANN>commit;

 

Commit complete.

 

3) Le package d'envoi des mails : 

 

Dans le package de notification, la procédure send_notif_perso envoie une notification à tous les abonnés.

Comme dit, il suffit d'inclure des $1 et $2 pour personnaliser les messages avec les noms et prénoms des abonnés.

 

J'avais hésité à faire un envoi global à tous les abonnés, ce qui m'aurait permis, pour la première fois de ma vie, d'utiliser listagg pour constituer la chaîne aggrégée des destinataires... tant pis

(C'est pour cela que la procédure s'appelle send_notif_perso, ça appelle un send_notif_global)

 

PACMANN>CREATE OR REPLACE PACKAGE pkg_notif AS

  2  PROCEDURE send_notif_perso(p_code_message IN VARCHAR2);

  3  END pkg_notif;

  4  /

 

Package created.

 

PACMANN>CREATE OR REPLACE PACKAGE BODY pkg_notif

  2  AS

  3

  4    PROCEDURE send_notif_perso(p_code_message IN VARCHAR2) IS

  5

  6    BEGIN

  7        FOR crow IN (

  8            SELECT c.nom, c.prenom, c.email, a.objet, a.corps

  9            FROM message a

 10              INNER JOIN message_destinataire b on a.id_message = b.id_message

 11              INNER JOIN personne c on b.id_personne = c.id_personne

 12            WHERE a.code_message = p_code_message)

 13        LOOP

 14

 15          UTL_MAIL.SEND (

 16              sender => 'Mr_Notif',

 17              recipients => crow.email,

 18              subject => crow.objet,

 19              message => replace(replace(crow.corps, '$1', crow.prenom), '$2', crow.nom));

 20

 21        END LOOP;

 22    END send_notif_perso;

 23

 24  END pkg_notif;

 25  /

 

Package body created.

 

4) La planification

 

Puis on enpaquette tout ça dans un job, qu'on programme du lundi au vendredi à 10h30, sachant que les commandes se ferment à 11h.

 

PACMANN>BEGIN

  2  DBMS_SCHEDULER.create_job (

  3    job_name        => 'notification_repas_midi',

  4    job_type        => 'PLSQL_BLOCK',

  5    job_action      => 'BEGIN pkg_notif.send_notif_perso(''NOTIF_REPAS_MIDI''); END;',

  6    start_date      => sysdate,

  7    repeat_interval => 'freq=weekly;byday=mon,tue,wed,thu,fri;byhour=10;byminute=30',

  8    end_date        => NULL,

  9    enabled         => TRUE,

 10    comments        => 'La notif quotidienne pour ne pas oublier de commander le repas de midi !');

 11  END;

 12  /

 

PL/SQL procedure successfully completed.

 

Un petit lancement manuel du job pour vérifier que ça marche :

 

PACMANN>exec DBMS_SCHEDULER.RUN_JOB('notification_repas_midi');

 

PL/SQL procedure successfully completed.

 

Il n'aura plus d'excuses pour mourir de faim, Rémy. Enfin si, si le job est shooté, si la base joujou est dézinguée, ... bref, si ça arrive, cela sera clairement son destin.

 

5) Quelques compléments

 

J'ai un peu lutté sur quelques points : 

- UTL_MAIL non installé sur la base. En sys : 

@$ORACLE_HOME/rdbms/admin/utlmail.sql

@$ORACLE_HOME/rdbms/admin/prvtmail.plb

alter system set smtp_out_server = ton_serveur scope=both

 

- A partir de 11g, les utilitaires du type UTL_MAIL sont restreint par des ACL (access control list), j'ai bidouillé par l'intermédiaire de ce site : 

http://www.oracleflash.com/36/Oracle-11g-Access-Control-List-for-External-Network-Services.html

(A noter que la première fois, j'ai connement exécuté les exemples sans remplacer par le nom de mon smtp... erghl)

 

- Comme je manquais de patience avec la doc oracle, je suis allé chercher les exemples de définitions de planifications chez Donald (ne le dites pas trop fort, ma fille va vouloir y aller aussi)

http://www.dba-oracle.com/t_dbms_scheduler_examples.htm

 

- Comme j'ai des goûts de chiotte, je change régulièrement de mise en forme pour les requêtes que je poste ici. Vous pouvez laisser un message pour me dire que j'ai vraiment des goûts de chiotte.

Repost 0
Published by Pacman - dans SGBD
commenter cet article
8 août 2013 4 08 /08 /août /2013 09:43

Let's have a look at Laurent Schneider's funny query (that he had already posted on several blogs :))


select from dual where lnnvl(1=0) and lnnvl(1=0) is null;

 

D

-

X

 

Does it mean that lnnvl(1=0) is both true and unknown ?

 

Of course not.

Actually, it is the same issue as the REGEXP_LIKE "function" i played with a few days ago : 

http://pacmann.over-blog.com/article-why-regexp_like-is-more-than-a-function-119416964.html

 

lnnvl Should be used as a condition... and when doing so, it does behave as expected : 

 

SELECT CASE WHEN lnnvl(1=0) THEN 'True' WHEN NOT lnnvl(1=0) THEN 'False' ELSE 'Null' END "1=0"

, CASE WHEN lnnvl(1=1) THEN 'True' WHEN NOT lnnvl(1=1) THEN 'False' ELSE 'Null' END "1=1"

, CASE WHEN lnnvl(1=null) THEN 'True' WHEN NOT lnnvl(1=null) THEN 'False' ELSE 'Null' END "1=null"

, CASE WHEN lnnvl(null=null) THEN 'True' WHEN NOT lnnvl(null=null) THEN 'False' ELSE 'Null' END "null=null"

FROM dual;

 

1=0   1=1   1=nul null=

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

True  False True  True

 

 

 

Remember, lnnvl(cond) is evaluated to true whenever cond is false or null and evaluated to false otherwise, making a simpler syntax for handling null cases in predicates.

 

But now, try this : 

 

SELECT lnnvl(1=0) "1=0"

, lnnvl(1=1) "1=1"

, lnnvl(1=null) "1=null"

, lnnvl(null=null) "null=null"

FROM dual;

 

       1=0        1=1     1=null  null=null

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


 

lnnvl always returns NULL !

 

But i would not expect lnnvl to return something else... but rather raise an exception, since it cannot return a boolean value within a SQL statement !

 

Also in my opinion, Oracle documentation is wrong when stating : 

 

The function can be used only in the WHERE clause of a query. It takes as an argument a condition and returns TRUE if the condition is FALSE or UNKNOWN and FALSE if the condition is TRUE.

 

It just behaves, and should be used, the same way as REGEXP_LIKE, LIKE, (1 = 0) does : these are logical conditions being evaluated, not functions that return any value.

 

SELECT regexp_like('a', 'a')

FROM dual;

 

SELECT regexp_like('a', 'a')

       *

ERROR at line 1:

ORA-00904: "REGEXP_LIKE": invalid identifier


 

 

Anyway, I wish Oracle SQL logical algebra would just work as any logical algebra does, have REGEXP_LIKE and LNNVL really return a boolean, and having both statements being equivalent : LNNVL(cond) or LNNVL(cond) = true...

 

Lnnvl has always been kind of myth in Oracle, until it became documented in 10gR1 release... but buggy !

Funnily enough, even Tom Kyte was not aware of lnnvl beeing documented (read the comments) : 

http://awads.net/wp/2006/10/11/previously-undocumented-lnnvl-sql-function-buggy/

Repost 0
Published by Pacman - dans SGBD
commenter cet article
6 août 2013 2 06 /08 /août /2013 11:18

Last week, i was quite confused when i read on a forum that Oracle SQL does not support boolean type. Posted question looked like this : 

 

CREATE OR REPLACE FUNCTION test_bool(i number) RETURN boolean AS

BEGIN

return true;

END;

 

Function created.

 

SELECT *

FROM dual

WHERE test_bool(1) = true

 

ERROR at line 3:

ORA-00904: "TRUE": invalid identifier

 

I was extremely confident that it could be working this way : 


SELECT *

FROM dual

WHERE test_bool(1);         

 

... or also like this :  

 

 

SELECT CASE WHEN test_bool(1) THEN 'hello world' END

FROM dual;

 

... but it ends up with the same error message : 

 

ORA-00920: invalid relational operator

 

Why would i expect such a syntax to work ?


SELECT *

FROM dual

WHERE regexp_like('a', 'a');

 

D

-

X

 

SELECT CASE WHEN regexp_like('a', 'a') THEN 'hello world' END

FROM dual;

 

CASEWHENREG

-----------

hello world

 

I often read that regexp_like returns a boolean. While it clearly returns a boolean in PL/SQL, it does not look like it does in SQL...

 

DECLARE

b boolean;

BEGIN

b:=regexp_like('a', 'a');

if b then

dbms_output.put_line('I love UMP !');

end if;

END;

 

I love UMP !

 

By the way, just have a look into the standard package : 

 

SELECT *

FROM dba_source

WHERE line BETWEEN 2312 AND 2318

  AND owner = 'SYS'

  AND name = 'STANDARD'

  AND type = 'PACKAGE'

ORDER BY line

 

TEXT

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

  -- REGEXP_LIKE --

  function REGEXP_LIKE (srcstr   VARCHAR2 CHARACTER SET ANY_CS,

                        pattern  VARCHAR2 CHARACTER SET srcstr%CHARSET,

                        modifier VARCHAR2 DEFAULT NULL)

    return BOOLEAN;

    pragma FIPSFLAG('REGEXP_LIKE', 1452);


 

I presume there is a specific implementation of these functions for SQL use, probably through another object wrapping the basic regexp_like function, while the standard function only works in PL/SQL : 


DECLARE

b boolean;

BEGIN

b:=sys.standard.regexp_like('a', 'a');

if b then

dbms_output.put_line('I love JF Copé !');

end if;

END;

 

I love JF Copé !

 

... but it does not in SQL !


SELECT *

FROM dual

WHERE sys.standard.regexp_like('a', 'a');

 

ERROR at line 3:

ORA-00920: invalid relational operator

 

Unlike for REGEXP_SUBSTR, Oracle documentation actually does not describe REGEXP_LIKE as a function that returns a boolean, but rather as a condition that evaluates to true or false.

 

REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE. This condition evaluates strings using characters as defined by the input character set.

 

http://docs.oracle.com/cd/B12037_01/server.101/b10759/conditions001.htm

A condition could be said to be of a logical datatype, although Oracle Database does not formally support such a datatype.

 

Oracle SQL implements boolean algebra without booleans (by replacing them with condition evaluations !)... kind of creepy :)

Repost 0
Published by Pacman - dans SGBD
commenter cet article
30 juillet 2013 2 30 /07 /juillet /2013 15:44

L'autre jour, allez savoir pourquoi, je jouais avec des Index Organized Tables (IOT).

Ces créatures ont cela de spécial qu'il n'y a pas de table à proprement parler, ou plutôt que l'index clef primaire contient toutes les données.

 
Cette structure est particulièrement sympa quand la table n'est pas très large (c'est à dire des lignes pas très grosses), et que l'accès par la clef primaire est très courant. 
En effet, le clustering factor de la PK est par construction parfait pour une IOT.

 
 
Par contre, on a des petits effets rigolos quant on parle d'adressage des lignes : Quand vous insérez une ligne dans une table, on s'en tape un peu de l'endroit physique : soit tout à la fin (aux environs du high water mark), soit quelque part où il y a de la place à réutiliser.

 
 
Dans un index en revanche, les entrées sont ordonnées selon la clef de l'index, puisque l'objectif est quand même à la base d'accélérer la recherche d'une (plage de) valeur donnée.... la suite tout au long de la démo.


1) Le logical rowid
 
Allez, une petite IOT et son index secondaire. 

J'ajoute une colonne m pour pouvoir l'indexer, et une une autre m pour avoir une colonne totalement non indexée.

 

CREATE TABLE testiot (l primary key, m, n) 
 ORGANIZATION INDEX AS
 SELECT 1, 1, 1 
 FROM dual 
 UNION ALL 
 SELECT 10000, 10000, 10000
 FROM dual;

Table created.

CREATE INDEX testiotii ON TESTIOT(m);
Index created.

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

 PL/SQL procedure successfully completed.

Pour une IOT, Oracle maintient un logical rowid. Ce logical rowid est stocké dans les indexes secondaires, et permet de faire un accès à l'IOT, comme dans un ACCESS BY INDEX ROWID classique.

Vous remarquerez au passage qu'il est bien plus laid de frangliser avec "logical rowid" que de traduire rowid logique. C'ezt mon choix.
SELECT l, m, n, rowid 
FROM testiot;

         L          M          N ROWID
---------- ---------- ---------- -----------------------------------------
         1          1          1 *BAEACwQCwQL+
     10000      10000      10000 *BAEACwQCwwL+


2) Pas fiable à requêter !

Mais si on ajoute une foultitude de clefs entre les deux premières valeurs, que va-t-il arriver à ce rowid ?  

INSERT INTO testiot
SELECT level + 1, level + 1, level + 1
FROM dual
CONNECT BY level <= 9900;

commit;

Commit complete.

set autot on explain

SELECT l, m, n, rowid
FROM testiot
WHERE m in (1, 10000);

        L          M          N ROWID
---------- ---------- ---------- -----------------------------------------
         1          1          1 *BAEACwgCwQL+
     10000      10000      10000 *BAEAWT8CwwL+

-----------------------------------------------------------------------------
| Id  | Operation        | Name                | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                     |     2 |    18 |     1   (0)|
|*  1 |  INDEX FULL SCAN | SYS_IOT_TOP_1443683 |     2 |    18 |     1   (0)|
-----------------------------------------------------------------------------

La deuxième ligne a été déplacée, comme attendu.

Maintenant, premier fun fact : 

SELECT
/*+index(testiot testiotii)*/ l, m, rowid
FROM testiot
WHERE m in (1, 10000);

         L          M ROWID
---------- ---------- -----------------------------------------
         1          1 *BAEACwQCwQL+
     10000      10000 *BAEACwQCwwL+

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     2 |    18 |     1   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR  |           |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| TESTIOTII |     2 |    18 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

La pseudo colonne rowid ne renvoit pas la même chose en fonction du chemin d'accès !
En fait, comme le logical rowid est susceptible de varier souvent, Oracle ne remet pas à jour tous les indexes à chaque changement d'adressage dans l'IOT.

On se retrouve donc avec un rowid pas frais stocké avec la clef de l'index secondaire TESTIOTII.
Et c'est pour cela qu'en plus du logical rowid, la clef primaire de l'IOT est également stockée dans l'index secondaire !

On le voit d'ailleurs très bien dans le plan de cette dernière requête : on SELECT la colonne l, qui n'est pas dans l'index TESTIOTII, mais pourtant il n'y a pas besoin d'accès à l'IOT pour la récupérer.

3) Et un de plus.

Comme je n'a plus de colonne à indexer, je fais un FBI bidon, puis rajoute quelques lignes : 

CREATE INDEX
testiotii2 ON TESTIOT(m * 1);

Index created.

INSERT INTO
testiot
SELECT level + 1.5, level + 1.5, level + 1.5
FROM dual
CONNECT BY level <= 9900;

9900 rows created.

commit;

Commit complete.

Et là, ça devient n'importe quoi, on a 3 trois rowid différents pour la même ligne : 

SELECT
/*+index(testiot testiotii)*/ 'ii' accesstype, rowid, 10000
FROM testiot
WHERE m = 10000
UNION ALL
SELECT /*+index(testiot testiotii2)*/ 'ii2', rowid, 10000
FROM testiot
WHERE m * 1 = 10000
UNION ALL
SELECT /*+index(testiot testiotii2)*/ 'tab', rowid, n
FROM testiot
WHERE m = 10000; 

ACC ROWID                                          10000
--- ----------------------------------------- ----------
ii  *BAEACwQCwwL+                                  10000
ii2 *BAEAWT8CwwL+                                  10000
tab *BAEAGj0CwwL+                                  10000

-------------------------------------------------------------------------------
| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |     3 |    27 |    31  (97)|
|   1 |  UNION-ALL         |                     |       |       |            |
|*  2 |   INDEX RANGE SCAN | TESTIOTII           |     1 |     9 |     1   (0)|
|*  3 |   INDEX RANGE SCAN | TESTIOTII2          |     1 |     9 |     1   (0)|
|*  4 |   INDEX UNIQUE SCAN| SYS_IOT_TOP_1443683 |     1 |     9 |    29   (0)|
|   5 |    INDEX FULL SCAN | TESTIOTII2          |  9902 |       |    29   (0)|
-------------------------------------------------------------------------------

Le problème, quand on a des estimations fausses du rowid, c'est qu'on commence par lire un bloc pour rien, puis on déplie l'index PK pour récupérer la ligne finale... plein de boulot dans le vent, en somme.
Les accès directs réussis par rowid sont d'ailleurs recencés dans les stats de l'index : 

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

PL/SQL procedure successfully completed.

set autot off

SELECT
index_name, blevel, num_rows, leaf_blocks, pct_direct_access
FROM dba_indexes
WHERE table_name = 'TESTIOT';

INDEX_NAME                         BLEVEL   NUM_ROWS LEAF_BLOCKS PCT_DIRECT_ACCESS
------------------------------ ---------- ---------- ----------- -----------------
SYS_IOT_TOP_1443683                     1      19802         209                 0
TESTIOTII                               1      19802         234                50
TESTIOTII2                              1      19802         110                53

4) Re-link-er correctement

Du coup, quand ça devient trop le foutoir, il faut y remettre de l'ordre : 

ALTER INDEX
testiotii UPDATE BLOCK REFERENCES;

Index altered.

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

PL/SQL procedure successfully completed.

SELECT
index_name, blevel, num_rows, leaf_blocks, pct_direct_access
FROM dba_indexes
WHERE table_name = 'TESTIOT';

INDEX_NAME                         BLEVEL   NUM_ROWS LEAF_BLOCKS PCT_DIRECT_ACCESS
------------------------------ ---------- ---------- ----------- -----------------
SYS_IOT_TOP_1443683                     1      19802         209                 0
TESTIOTII                               1      19802         234               100
TESTIOTII2                              1      19802         110                50

5) Ou rebuild !

Un dernier petit test, où on va claquer la moitié de l'IOT : 

DELETE FROM
testiot
WHERE l <= 5000;

9998 rows deleted.

commit
;

Commit complete.

On réorg l'IOT pour ramener tout le monde vers le début : 

ALTER TABLE
testiot MOVE;

Table altered.

... et on rebuild l'index : 

ALTER INDEX
testiotii2 REBUILD ONLINE;

Index altered.

SELECT
/*+index(testiot testiotii)*/ 'ii' accesstype, rowid, 10000
FROM testiot
WHERE m = 10000
UNION ALL
SELECT /*+index(testiot testiotii2)*/ 'ii2', rowid, 10000
FROM testiot
WHERE m * 1 = 10000
UNION ALL
SELECT /*+index(testiot testiotii2)*/ 'tab', rowid, n
FROM testiot
WHERE m = 10000; 

ACC ROWID                                          10000
--- ----------------------------------------- ----------
ii  *BAEAGj0CwwL+                                  10000
ii2 *BAEAGqsCwwL+                                  10000
tab *BAEAGqsCwwL+                                  10000

Voilà, le rebuild a réaligné son estimation de rowid de l'index sur le rowid logique de l'IOT !

A noter que les stats de l'index sont recalculés au rebuild (c'est vrai que tant qu'à ce repalucher toutes les données, pourquoi ne pas en profiter...)

SELECT
index_name, blevel, num_rows, leaf_blocks, pct_direct_access
FROM dba_indexes
WHERE table_name = 'TESTIOT';

INDEX_NAME                         BLEVEL   NUM_ROWS LEAF_BLOCKS PCT_DIRECT_ACCESS
------------------------------ ---------- ---------- ----------- -----------------
SYS_IOT_TOP_1443683                     1      19802         209                 0
TESTIOTII                               1      19802         234                 0
TESTIOTII2                              1       9804          29               100

COMPUTE STATISTICS Clause

This clause has been deprecated. Oracle Database now automatically collects statistics during index creation and rebuild. 
This clause is supported for backward compatibility and will not cause errors.

Bref, notons surtout que COALESCE, qui permet également de recompacter l'index, ne remet pas à jour les rowid (normal, on re-façonne juste l'arbre)

ALTER INDEX
TESTIOTII COALESCE;

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

SELECT
index_name, blevel, num_rows, leaf_blocks, pct_direct_access
FROM dba_indexes
WHERE table_name = 'TESTIOT';

INDEX_NAME                         BLEVEL   NUM_ROWS LEAF_BLOCKS PCT_DIRECT_ACCESS
------------------------------ ---------- ---------- ----------- -----------------
SYS_IOT_TOP_1443683                     1       9804          29                 0
TESTIOTII                               1       9804         116                 0
TESTIOTII2                              1       9804          29               100
Repost 0
Published by Pacman - dans SGBD
commenter cet article
18 juillet 2013 4 18 /07 /juillet /2013 10:58

Un des grands problèmes dans ma vie, c'est ma passion pour le NATURAL FULL OUTER JOIN. Parce qu'il y a plein de mots, c'est full, le côté naturel, les joints et tout ça.


Et cette lubie m'a encore piégée hier lorsque je faisais une simple comparaison de données avant / après traitement.


Allez, démonstration par étape : 


1) Deux tables correspondant aux données avant / après


SQL>CREATE TABLE tab_avant AS

  2  SELECT level l

  3  FROM dual

  4  CONNECT BY level <= 5;


Table created.


Elapsed: 00:00:00.09

SQL>CREATE TABLE tab_apres AS

  2  SELECT level + 2 l

  3  FROM dual

  4  CONNECT BY level <= 5;


Table created.


Elapsed: 00:00:00.03


2) Pour comparer, minus ou natural machin : 


La version minus me barbe un peu, parce qu'il faut écrire A - B et B - A : 


SQL>SELECT '+ap', a.*

  2  FROM tab_apres a

  3  MINUS

  4  SELECT '+ap', b.*

  5  FROM tab_avant b

  6  UNION ALL

  7  SELECT '+av', c.*

  8  FROM tab_avant c

  9  MINUS

 10  SELECT '+av', d.*

 11  FROM tab_apres d;


'+A          L

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

+ap          6

+ap          7

+av          1

+av          2


En revanche, la version natural full outer join me semble beaucoup plus drôle :


SQL>SELECT case when b.rowid is null then '+av' when a.rowid is null then '+ap' end, l

  2  FROM tab_avant a

  3    NATURAL FULL OUTER JOIN tab_apres b

  4  WHERE a.rowid is null OR b.rowid is null;


CAS          L

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

+av          1

+av          2

+ap          6

+ap          7


Elapsed: 00:00:00.03


"Natural" pour dire qu'on match sur toutes les colonnes de même nom (à n'utiliser que pour rigoler sous peine de se faire virer), outer join parce qu'on prend aussi ce qui ne matche pas, et full parce que c'est outer des deux côtés.


3) Une défaite supplémentaire


Seulement voilà, il y a des choses qui ne sont pas comparables... ajoutons une colonne vide dans chaque table : 


SQL>ALTER TABLE tab_avant ADD c number;


Table altered.


Elapsed: 00:00:00.06

SQL>ALTER TABLE tab_apres ADD c number;


Table altered.


Elapsed: 00:00:00.03


... et relançons la comparaison :


SQL>SELECT case when b.rowid is null then '+av' when a.rowid is null then '+ap' end, l, c

  2  FROM tab_avant a

  3    NATURAL FULL OUTER JOIN tab_apres b

  4  WHERE a.rowid is null OR b.rowid is null;


CAS          L          C

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

+av          1

+av          4

+av          3

+av          2

+av          5

+ap          6

+ap          7

+ap          5

+ap          4

+ap          3


10 rows selected.


Patatra, ça devient n'importe quoi. Le "natural" ne doit pas faire oublier qu'on teste "colonne a" = "colonne b" dans la jointure.

Et la comparaison NULL = NULL est toujours aussi peu constructive...


On n'a pas ce problème avec minus : 


SQL>SELECT '+ap', a.*

  2   FROM tab_apres a

  3   MINUS

  4   SELECT '+ap', b.*

  5   FROM tab_avant b

  6   UNION ALL

  7   SELECT '+av', c.*

  8   FROM tab_avant c

  9   MINUS

 10   SELECT '+av', d.*

 11   FROM tab_apres d;


'+A          L          C

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

+ap          6

+ap          7

+av          1

+av          2


Elapsed: 00:00:00.04

 

Z'imaginez même pas quels trucs tordus j'étais allé chercher pour expliquer les différences...

Repost 0
Published by Pacman - dans SQL
commenter cet article
4 juillet 2013 4 04 /07 /juillet /2013 16:38

Dans la vie, il y a ceux qui sont géniaux, et ceux qui répètent connement ce que disent les gens géniaux.


Je fais partie de la deuxième catégorie...


Ayant passé mes 32 dernières années (j'ai commencé le SQL Oracle à la sortie de la maternité) à doubler les quotes pour les échapper, et sans que personne ne se moque jamais de moi, je me dois de partager ce que j'ai appris aujourd'hui sur OTN : 


 

SQL> SELECT q'!J'aime les '!' AS e1, q'{Ah c'qu'on rigole avec l'UMP}' AS e2 FROM dual;

 

E1           E2

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

J'aime les ' Ah c'qu'on rigole avec l'UMP

 

 

(le caractère après la quote sert de seconde couche de délimiteur, on referme la citation dans le sens inverse. Bref comme < >... dans un sens puis dans l'autre quoi)

 

Merci BluShadow !


Repost 0
Published by Pacman - dans SQL
commenter cet article
3 juillet 2013 3 03 /07 /juillet /2013 16:08

Comment est-ce qu'après une journée d'archéologie PL/SQLienne je me suis demandé pourquoi j'ai étudié une proc qui n'est jamais appelée ?

 

1) Création d'une proc inutile


SQL> r ismagic

  1  create or replace procedure ismagic

  2  IS

  3  BEGIN

  4          --Etape n°31

  5          dbms_output.put_line('Magic is all around');

  6  END;

  7*

 

Procedure created.

 

2) Lire la proc dans SQLDeveloper

 

 

magic

 

3) L'astuce ? 

  
ismagic2.JPG
  
Oui je sais, mon vim a des couleurs infâmes...
Repost 0
Published by Pacman - dans SQL cosmétique
commenter cet article
1 juillet 2013 1 01 /07 /juillet /2013 10:22

De retour sur Oracle après 3 ans dans le monde du pipo, je me refais un peu la main en participant au forum OTN.

 

Et là, sur une question classique "comment vérifier qu'une chaîne de caractères est un nombre sous Oracle ?", je m'aperçois que les intervenants d'OTN à priori chevronnés sont en fait un peu à la masse par rapport aux amis de developpez.net d'il y a 3-5 ans.

 

Notamment, pour parser une chaîne potentiellement numérique, il ne faut pas simplement vérifier qu'elle est composée de chiffres !

 

Je m'étais amusé à l'époque sur ce même non-blog à faire un is_numeric imbuvable : 

http://pacmann.over-blog.com/article-yet-another-oracle-isnumeric-function-51158452.html

 

Je profite de l'occasion pour enfin compléter quelques points : 

- Quite à simplement vérifier la présence de chiffres uniquement la fonction rtrim fait très bien le boulot, pas besoin de translate + trim !

  => Un grand merci à McM de developpez.net

- Faire une vraie regexp bien touffue, c'est idéal

  => Un grand merci à Bloon de developpez.net

 

SQL> SELECT to_number(c)

  2      , case when regexp_like (c,'^(-|\+)?(\d*|\d+,|,\d+|\d+,\d+)(E(-|\+)?\d+)?$','i')then 1 else 0 end win

  3      , case when rtrim(c, '1234567890') is null then 1 else 0 end mcm

  4      , case when regexp_like (c, '^[[:digit:]]$') then 1 else 0 end loosexp

  5  FROM (

  6    SELECT '1' c FROM DUAL UNION ALL

  7    SELECT '+2'FROM DUAL UNION ALL

  8    SELECT '3,4' FROM DUAL UNION ALL

  9    SELECT '-5' FROM DUAL UNION ALL

 10    SELECT '6E78' FROM DUAL UNION ALL

 11    SELECT '9E-5' FROM DUAL UNION ALL

 12    SELECT '3,E6' FROM DUAL UNION ALL

 13    SELECT '-,45E7' FROM DUAL UNION ALL

 14    SELECT '4,' FROM DUAL

 15  );

 

TO_NUMBER(C)        WIN        MCM    LOOSEXP

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

           1          1          1          1

           2          1          0          0

         3,4          1          0          0

          -5          1          0          0

  6,0000E+78          1          0          0

      ,00009          1          0          0

     3000000          1          0          0

    -4500000          1          0          0

           4          1          0          0

 

9 rows selected.  

 

Voilà, voilà, vous constaterez qu'il est toujours très facile de faire un article en repompant les solutions des autres... mais bon vu comme j'ai galéré à retrouver la regexp, ça vaut le coup de la rediffuser, hein !

Et puis pour s'y remettre, faut bien commencer quelque part...

 

EDIT : 

 

Prise en compte du NLS_NUMERIC_CHARACTERS, un grand merci à notre guess star Laurent.

Pas prise en compte par contre des nombres de R barre, on va dire que ça en rentre pas dans les données 'business"


SQL>   SELECT c

  2         , case when regexp_like (c,replace('^(-|\+)?(\d+|\d+X|X\d+|\d+X\d+)(E(-|\+)?\d+)?$', 'X', '\'||d),'i') then to_number(c) end tnb

  3         , case when regexp_like (c,replace('^(-|\+)?(\d+|\d+X|X\d+|\d+X\d+)(E(-|\+)?\d+)?$', 'X', '\'||d),'i')then 1 else 0 end win

  4         , case when rtrim(c, '1234567890') is null then 1 else 0 end mcm

  5         , case when regexp_like (c, '^[[:digit:]]$') then 1 else 0 end loosexp

  6     FROM (

  7       SELECT '1' c FROM DUAL UNION ALL

  8       SELECT '+2'FROM DUAL UNION ALL

  9       SELECT '3,4' FROM DUAL UNION ALL

 10       SELECT '-5' FROM DUAL UNION ALL

 11       SELECT '6E20' FROM DUAL UNION ALL

 12       SELECT '9E-5' FROM DUAL UNION ALL

 13       SELECT '3,E6' FROM DUAL UNION ALL

 14       SELECT '-,45E7' FROM DUAL UNION ALL

 15       SELECT 'E7' FROM DUAL UNION ALL

 16       SELECT '4,' FROM DUAL UNION ALL

 17       SELECT '4.' FROM DUAL UNION ALL

 18       SELECT '3.4' FROM DUAL UNION ALL

 19       SELECT '-.45E7' FROM DUAL UNION ALL

 20       SELECT '3.E6' FROM DUAL

 21     )

 22     CROSS JOIN (SELECT substr(value, 1, 1) d

 23                 FROM nls_session_parameters

 24                 where parameter='NLS_NUMERIC_CHARACTERS');

 

C             TNB        WIN        MCM    LOOSEXP

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

1               1          1          1          1

+2              2          1          0          0

3,4           3,4          1          0          0

-5             -5          1          0          0

6E20   6,0000E+20          1          0          0

9E-5       ,00009          1          0          0

3,E6      3000000          1          0          0

-,45E7   -4500000          1          0          0

E7                         0          0          0

4,              4          1          0          0

4.                         0          0          0

3.4                        0          0          0

-.45E7                     0          0          0

3.E6                       0          0          0

 

14 rows selected.

 

Elapsed: 00:00:00.09

SQL> alter session set nls_numeric_characters = '.,'

  2  /

 

Session altered.

 

Elapsed: 00:00:00.01

SQL>   SELECT c

  2         , case when regexp_like (c,replace('^(-|\+)?(\d+|\d+X|X\d+|\d+X\d+)(E(-|\+)?\d+)?$', 'X', '\'||d),'i') then to_number(c) end tnb

  3         , case when regexp_like (c,replace('^(-|\+)?(\d+|\d+X|X\d+|\d+X\d+)(E(-|\+)?\d+)?$', 'X', '\'||d),'i')then 1 else 0 end win

  4         , case when rtrim(c, '1234567890') is null then 1 else 0 end mcm

  5         , case when regexp_like (c, '^[[:digit:]]$') then 1 else 0 end loosexp

  6     FROM (

  7       SELECT '1' c FROM DUAL UNION ALL

  8       SELECT '+2'FROM DUAL UNION ALL

  9       SELECT '3,4' FROM DUAL UNION ALL

 10       SELECT '-5' FROM DUAL UNION ALL

 11       SELECT '6E20' FROM DUAL UNION ALL

 12       SELECT '9E-5' FROM DUAL UNION ALL

 13       SELECT '3,E6' FROM DUAL UNION ALL

 14       SELECT '-,45E7' FROM DUAL UNION ALL

 15       SELECT 'E7' FROM DUAL UNION ALL

 16       SELECT '4,' FROM DUAL UNION ALL

 17       SELECT '4.' FROM DUAL UNION ALL

 18       SELECT '3.4' FROM DUAL UNION ALL

 19       SELECT '-.45E7' FROM DUAL UNION ALL

 20       SELECT '3.E6' FROM DUAL

 21     )

 22     CROSS JOIN (SELECT substr(value, 1, 1) d

 23                 FROM nls_session_parameters

 24                 where parameter='NLS_NUMERIC_CHARACTERS');

 

C             TNB        WIN        MCM    LOOSEXP

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

1               1          1          1          1

+2              2          1          0          0

3,4                        0          0          0

-5             -5          1          0          0

6E20   6.0000E+20          1          0          0

9E-5       .00009          1          0          0

3,E6                       0          0          0

-,45E7                     0          0          0

E7                         0          0          0

4,                         0          0          0

4.              4          1          0          0

3.4           3.4          1          0          0

-.45E7   -4500000          1          0          0

3.E6      3000000          1          0          0

 

14 rows selected.

 

 

Elapsed: 00:00:00.04

 

Repost 0
Published by Pacman - dans SQL
commenter cet article
1 avril 2012 7 01 /04 /avril /2012 14:02

Ne serait-ce que par esprit de contradiction, le premier avril est le parfait moment pour aborder le sujet le moins drôle de l'univers :voir les données avant modifications, et les comparer aux données présentes !

 

0) Un jeu d'essai simple :

 

SQL> CREATE TABLE testflash AS
  2  SELECT level id, mod(level, 5) n
  3  FROM DUAL
  4  CONNECT BY level <= 2000;

Table créée

 

... et quelques modifications :

 

SQL> UPDATE testflash
  2  SET n = 99
  3  WHERE id = 1000;

1 ligne mise à jour.


SQL> INSERT INTO testflash VALUES(2001, 99);

1 ligne créée.

SQL> DELETE FROM testflash WHERE id = 2000;

1 ligne supprimée.

SQL> commit;

Validation effectuée.

 

On vérifie que les modifications sont bien là :

 

SQL> SELECT *
  2  FROM testflash
  3  WHERE id IN (1000, 2000, 2001);

        ID          N
---------- ----------
      1000         99
      2001         99

 

 

1) AS OF TIMESTAMP :

 

Par principe, Oracle est capable de livrer une vue dans le passée des données, afin de garantir la consistence de la lecture. C'est fait de manière automatique, par exemple lorsque ta requête ultra longue ne voit pas les modifications qui sont commitées par ailleurs depuis le début de son executions.

 

En 9i FLASHBACK QUERY permet d'exploiter la mécanique pour demander explicitement, à un instant donné, de lire les données du passé. (Je passe les détails parce ça m'intéresse pas plus que ça)

 

Et en 9iR2, on va un peu plus loin en permettant, à l'intérieur d'une requête, de préciser quelles tables doivent être lues dans le passé. C'est la clause AS OF TIMESTAMP ajoutée après le nom de la table :

 

SQL> SELECT *
  2  FROM testflash AS OF TIMESTAMP sysdate - 1/24/12
  3  WHERE id IN (1000, 2000, 2001);

        ID          N
---------- ----------
      1000          0
      2000          0

 

Tadam ! Ce sont les données avant modifications.

1/24/12 pour dire que c'était "il y a 5 minutes", mais on peu bien sûr spécifier directement une vraie date (et c'est ce qu'on va faire dans la suite) :

 

SQL> SELECT *
  2  FROM testflash AS OF TIMESTAMP to_date('20120401 13:00', 'yyyymmdd hh24:mi')
  3  WHERE id IN (1000, 2000, 2001);

        ID          N
---------- ----------
      1000          0
      2000          0

 

2) Full outer Compare !

 

Et donc maintenant, pour voir exactement ce qui a changé, une jointure full externe sur la table avant / après permet de faire un diff sympa :


SQL> SELECT CASE WHEN a.id is NULL THEN 'added'
  2              WHEN b.id IS NULL THEN 'removed'
  3     ELSE 'modified' END status,
  4         a.id, a.n, b.id newid, b.n newn
  5  FROM testflash a
  6    FULL OUTER JOIN testflash AS OF TIMESTAMP to_date('20120401 13:00', 'yyyymmdd hh24:mi') b
  7     ON a.id = b.id
  8  WHERE a.id IS NULL OR b.id IS NULL OR a.n <> b.n;

STATUS           ID          N      NEWID       NEWN
-------- ---------- ---------- ---------- ----------
modified       1000         99       1000          0
removed        2001         99
added                                2000          0

 

3) Entre autres

 

Il y a quelques pré-requis :

- ëtre post 9.2 bien sûr

- l'utilisateur doit avoir le grant execute sur le package dbms_flashback

- les données doivent être disponibles, un peu dans l'esprit ORA-1555

- pas possible lorsqu'il y a eu un DDL entre temps sur la table (notamment TRUNCATE !)

 

TIens sinon, la semaine dernière chuis passé OCM !

(On n'échappe pas aux traditions stupides...)

 

Allez, zou, je vais aller m'occuper de ma gamine qui a bu entre temps toute l'eau de Javel...

(Pas la peine d'appeler la DASS, c'est encore un poisson, hein !)

Repost 0
Published by Pacman - dans SQL
commenter cet article
24 janvier 2012 2 24 /01 /janvier /2012 08:44

Hier c'étaient des replays de starcraft 2, une autre fois c'était Jack Bauer, ... Aujourd'hui au réveil, une soudaine envie de jouer avec des index.

Un index, c'est une structure arborescente qui, à une clef, potentiellement composite, associe une adresse (rowid) dans une table.


Le nombre d'étages de cet arbre, ou niveau de l'arbre, ou encore "b-tree level", dépend du nombre de blocs feuilles à référencer (le bloc de départ peut contenir n références vers des blocs au niveau inférieur, qui eux même peuvent contenir m références, ...), mais pas seulement !


 

tmp.jpg


1) Un petit exemple rigolo :

On va illustrer avec une table à deux colonnes : l'une constante, l'autre identifiant unique.  

CREATE TABLE t AS SELECT lpad('x', 255, 'X') c, level l FROM DUAL CONNECT BY level <= 1000;

On construit deux index composites sur ces deux colonnes en intervertissant l'ordre

CREATE INDEX idxt1 ON t(c, l) COMPUTE STATISTICS;

CREATE INDEX idxt2 ON t(l, c) COMPUTE STATISTICS;

Et là, on vérifie le niveau du b-tree index :

SELECT index_name, blevel
FROM dba_indexes
WHERE table_name = 'T';

INDEX_NAME                         BLEVEL
------------------------------ ----------
IDXT1                                   2
IDXT2                                   1

Quand on met la colonne la plus sélective en premier, ça baisse le niveau de l'index !

2) Ca marche aussi pour les index concaténés au lieu de composites

CREATE INDEX IDXT3 ON t(c||l) COMPUTE STATISTICS;

CREATE INDEX IDXT4 ON t(l||c) COMPUTE STATISTICS;

SELECT index_name, blevel
FROM dba_indexes
WHERE table_name = 'T';

INDEX_NAME                         BLEVEL
------------------------------ ----------
IDXT1                                   2
IDXT2                                   1
IDXT3                                   2
IDXT4                                   1

Même sentence.

L'idée en fait, c'est que pour référencer un niveau inférieur de l'arbre, vous n'avez pas besoin de toute la clef.


Supposez que dans le premier niveau d'indirection, vous référenciez un bloc feuille de la manière suivante :
Pour les valeurs entre '1;xxx...x' à '500;xxx...x', rendez-vous bloc 4233 !
... se reformule aisément en :
Pour les valeurs entre '1*' à '500*', rendez-vous bloc 4233 !
=> Les références sont beaucoup plus courtes, on peut en coincer beaucoup plus dans un bloc, et on a donc besoin de moins de niveaux d'indirection !

3) La même bien sûr sans concaténation de l'index

Cette fois, le pattern est présent naturellement dans la colonne.

CREATE TABLE u AS SELECT level || lpad('x', 255, 'X') c FROM DUAL CONNECT BY level <= 1000;

CREATE INDEX idxu1 ON u(c) COMPUTE STATISTICS;

CREATE TABLE W AS SELECT lpad('x', 255, 'X') ||level c FROM DUAL CONNECT BY level <= 1000;

CREATE INDEX idxw1 ON w(c) COMPUTE STATISTICS;

SELECT index_name, blevel
FROM dba_indexes
WHERE table_name IN ('U', 'W');

INDEX_NAME                         BLEVEL
------------------------------ ----------
IDXU1                                   1
IDXW1                                   2

Exactement la même... comme on pouvait s'y attendre.
C'est dans le mécanisme même de construction de l'index.

4) Juste pour déconner

Sur un index à forte redondance sur les premières positions de la clef composite, on peut (depuis 9i si je ne me trompe pas), compresser :

ALTER INDEX idxt1 REBUILD COMPRESS 1 COMPUTE STATISTICS;


SELECT index_name, blevel
FROM dba_indexes
WHERE table_name = 'T';

INDEX_NAME                         BLEVEL LEAF_BLOCKS
------------------------------ ---------- -----------
IDXT1                                   1           3
IDXT2                                   1          39
IDXT3                                   2          39
IDXT4                                   1          39

IDXT1 est devenu tout petit non seulement quant à son niveau, mais aussi pour les blocs feuilles qui sont elles aussi compressés.... c'est Imbattable :)

Repost 0
Published by Pacman - dans SGBD
commenter cet article