13 septembre 2002

Boucle de mise a jour SQL avec COMMITs intermédiaires


Ci joint un modèle de boucle PL/SQL pour faire des mises à jour SQL avec des COMMITs intermédiaires (ici toutes les 100 mises à jour). Ceci est utile quand les volumes sont trop importants et provoquent des erreurs sur les tablespaces temporaires ou d'undo.

begin
loop
    update/insert/delete ... where ... and rownum < 100;
    exit when sql%rowcount < 99;
    commit;
end loop;
end;
/


Par exemple pour supprimer les travaux Opération de ZO de plus de 90 jours avec COMMIT tous les 10 DELETE :

begin
loop
    delete from ZO00 where TISOUM > '0001-01-01' and TISOUM < SYSDATE - 90 and rownum < 10;
    exit when sql%rowcount < 9;
    commit;
end loop;
end;
/


PS : ce faisant, vous avez libéré de l'espace dans chacune des tables ZO**, mais pas dans les tablespaces HRZO et HRZOI (faire un DELETE ne modifie pas l'espace alloué aux tables, mais libère de l'espace "à l'intérieur" des tables). Si vous souhaitez récupérer l'espace libéré dans chacune des tables pour le rendre disponible à toutes, vous devrez utiliser la commande :
ALTER {TABLE/INDEX} object_name DEALLOCATE UNUSED;

5 commentaires:

  1. Hi Damien,

    I found your blog 1 month ago, and i'm following it since then.

    I work in Portugal for a company owned by IBM who resell HR Access.

    Please continue your excellent job sharing your posts with us.

    RépondreSupprimer
  2. Bonjour,

    J'ai déjà rencontré ce type de problème, notamment par rapport au PRDB qui prend pas mal de place (par exemple lors de rechargement). Cela faisait éventuellement planter le chargement, car les tables étaient pleines. J'avais alors essayé de supprimer des enregistrements, mais effectivement cela ne changeait pas grand chose. j'avais lu plus tard que le delete ne permettaient pas de gagner de la place, mais je n'ai jamais vu clairement ce qu'il fallait faire dans ce cas.
    Si j'ai bien compris, cette commande (ALTER {TABLE/INDEX} object_name DEALLOCATE UNUSED;
    ) doit permettre de faire le ménage, c'est bien cela ? Il faut la faire en "préventif" (avant que le dépassement de capacité) ? Ou bien si on lance cette commande après un dépassement, cela permet de libérer tout de suite de la place ?
    Enfin, c'est-ce bien au niveau de chaque table qu'il faut le faire (ZO00, ZO01...) ? Et non pas au niveau de tablspace ?
    Merci pour vos précisions.

    RépondreSupprimer
  3. - Prenez le tablespace FRIGO
    - Créez la table LAIT et la table JUS
    - Oracle va attribuer a chacune une première Bouteille (un "Extent")
    - Faites des INSERT dans LAIT
    - Au fur et à mesure du remplissage, Oracle va ajouter de nouvelles bouteilles de LAIT dans le FRIGO
    - Quand le FRIGO sera plein il ne sera plus possible d'ajouter de bouteilles - ni de LAIT ni de JUS

    Un DELETE dans LAIT va permettre de vider les Bouteilles de LAIT, mais pas de les mettre à la poubelle, FRIGO reste plein. il ne sera toujours pas possible d'ajouter de bouteilles de JUS

    Le TRUNCATE REUSE STORAGE vide intégralement les bouteilles de LAIT sans retour arrière possible, mais ne les jette pas à la poubelle. Il ne sera toujours pas possible d'ajouter des bouteilles de JUS.

    Le DROP va mettre toutes les bouteilles de LAIT à la poubelle - Le TRUNCATE DROP STORAGE vide intégralement les bouteilles et les met toutes à la poubelle sauf la première - donc il sera possible d'ajouter des bouteilles LAIT comme de JUS.

    Le DEALLOCATE UNUSED met à la poubelle les bouteilles vides jusqu'à rencontrer le niveau haut du LAIT. Il sera donc possible d'ajouter des bouteilles LAIT comme de JUS ... sous réserve que le niveau haut du LAIT ait permis de mettre des Bouteilles à la Poubelle ...

    Je vous laisse digérer cela

    RépondreSupprimer
  4. Merci pour ces explications qui me permettent de mieux appréhender cette problématique.

    RépondreSupprimer
  5. Une version pour DB2, trouvée sur "http://www.daharveyjr.com/delete-using-commit-count-db2-stored-procedure/", a exécuter avec un "db2 -td@" ...

    BEGIN
    -- DECLARE Statements
    DECLARE v_DELETE_QUERY VARCHAR(1024);
    DECLARE v_DELETE_STATEMENT STATEMENT;
    SET v_DELETE_QUERY = 'DELETE FROM (SELECT 1 FROM ... WHERE ... FETCH FIRST 99 ROWS ONLY) AS DELETE_TABLE';
    PREPARE v_DELETE_STATEMENT FROM v_DELETE_QUERY;
    DEL_LOOP:
    LOOP
    EXECUTE v_DELETE_STATEMENT;
    IF SQLCODE = 100 THEN
    LEAVE DEL_LOOP;
    END IF;
    COMMIT;
    END LOOP;
    COMMIT;
    END@

    RépondreSupprimer