Suppression de beaucoup de données dans Oracle

le moyen Le plus rapide est de créer un nouveau avec CREATE TABLE AS SELECT en utilisant NOLOGGING option. Je veux dire:

ALTER TABLE table_to_delete RENAME TO tmp;
CREATE TABLE table_to_delete NOLOGGING AS SELECT .... ;

bien sûr il faut recréer des contraintes sans validation, index avec nologging ,Grant,... mais il est très très rapide.

Si vous avez de la difficulté dans la production, vous pouvez effectuer les opérations suivantes:

ALTER TABLE table_to_delete RENAME to tmp;
CREATE VIEW table_to_delete AS SELECT * FROM tmp;
-- Until there can be instantly
CREATE TABLE new_table NOLOGGING AS SELECT .... FROM tmp WHERE ...;
<create indexes with nologging>
<create constraints with novalidate>
<create other things...>
-- From here ...
DROP VIEW table_to_delete;
ALTER TABLE new_table RENAME TO table_to_delete;
-- To here, also instantly

Vous devez prendre soin de:

• les procédures stockées peuvent être invalidées, mais elles seront recompilées la deuxième fois qu'elles sont appelées. Vous devez le tester.
• NOLOGGING signifie que minimum redo sont générés. Si vous avez un rôle DBA, exécuter un ALTER SYSTEM CHECKPOINT pour s'assurer qu'aucune donnée n'est perdue en cas de plantage de l'instance.
• NOLOGGING le tablespace être aussi dans NOLOGGING.

une autre option meilleure que create milions of inserts est:

-- Create table with ids
DELETE FROM table_to_delete
 WHERE ID in (SELECT ID FROM table_with_ids WHERE ROWNUM < 100000);
DELETE FROM table_with_ids WHERE ROWNUM < 100000;
COMMIT;
-- Run this 50 times ;-)

le choix PLSQL n'est pas conseillé car il peut créer le Instantané trop vieux message en raison que vous êtes la validation d' (et clôturant la transaction) avec un curseur ouvert (celui en boucle) vous voulez continuer à l'utiliser. Oracle l'autorise, mais ce n'est pas une bonne pratique.

mise à jour: pourquoi je peux m'assurer que le dernier bloc PLSQL va fonctionner? Parce que je supose que:

• personne d'autre n'utilise cette table temporaire pour quelque raison que ce soit (DBA ou jobs collecting statistics, DAB tasks like move, insering records, and so on). Cela peut être assuré car est une table auxiliaire seulement pour ce.
• Puis, avec la dernière affirmation, la requête va être exécuté exactement avec le même plan et va retourner les lignes avec le même ordre.

Lors de l'exécution de massives suppressions Oracle, assurez-vous que vous n'êtes pas à court de UNDO SEGMENTS.

en exécutant DML,Oracle tout d'abord écrit tous les changements dans le REDO log (les anciennes données avec les nouvelles données).

quand le REDO journal est plein ou que le délai d'attente se produit, Oracle effectue log synchronization: il écrit new données dans les datafiles( dans votre cas, marque les blocs datafile comme libres), et écrit les anciennes données dans le UNDO tablespace (de sorte que il reste visible aux transactions simultanées jusqu'à ce que vous commit vos modifications).

Lorsque vous validez vos modifications, l'espace UNDO segments occupés par la transaction yuor est libéré.

Cela signifie que si vous supprimez 5M lignes de données, vous aurez besoin d'avoir de l'espace pour all ces lignes dans votre UNDO segments, de sorte que les données peuvent être déplacées en premier (all at once) et supprimé qu'après validation.

Cela signifie également que le simultanées des requêtes (le cas échéant) devront lire à partir de REDO journaux ou UNDO segments lors de l'exécution de balayages de table. Ce n'est pas la façon la plus rapide d'accéder aux données.

Cela signifie également que si l'optimiseur sélectionnez HASH JOIN pour votre requête de suppression (ce qui sera très probablement le cas), et la table temp ne tiendra pas dans le HASH_AREA_SIZE (ce qui sera probablement le cas), alors la requête aura besoin de several scanne la grande table, et certaines parties de la table seront déjà déplacées dans REDO ou UNDO.

compte tenu de tout ce qui précède, vous feriez probablement mieux de supprimer les données dans 200,000 fragmente et commit les changements entre les deux.

ainsi vous allez, d'abord, vous débarrasser des problèmes décrits ci-dessus, et, deuxièmement, optimiser votre HASH_JOIN, que vous aurez le même nombre de lectures, mais le lit eux-mêmes seront plus efficaces.

Dans votre cas, cependant, je voudrais essayer de forcer l'optimiseur d'utiliser NESTED LOOPS, comme je m'y attendais sera plus rapide dans votre cas.

pour ce faire, assurez-vous que votre table temporaire a une touche primaire sur ID, et réécrivez votre requête comme suit:

DELETE  
FROM   (
       SELECT  /*+ USE_NL(tt, tn) */
               tn.id
       FROM    temp_table tt, table_name tn
       WHERE   tn.id = tt.id
       )

Vous aurez besoin de la clé primaire sur temp_table pour que cette requête fonctionne.

Comparez avec ce qui suit:

DELETE  
FROM   (
       SELECT  /*+ USE_HASH(tn tt) */
               tn.id
       FROM    temp_table tt, table_name tn
       WHERE   tn.id = tt.id
       )

, voir ce qui est plus rapide et s'y tenir.

C'est mieux de tout faire à la fois, comme dans votre premier exemple. Mais j'en parlerai certainement avec votre DBA d'abord car ils pourraient vouloir récupérer les blocs que vous n'utilisez plus après la purge. De plus, il peut y avoir des problèmes d'ordonnancement qui ne sont normalement pas visibles du point de vue de l'utilisateur.

si votre SQL original prend très longtemps, certains SQL concurrents peuvent fonctionner lentement car ils doivent utiliser UNDO pour reconstruire une version des données sans vos changements non engagés.

un compromis peut être quelque chose comme

FOR i in 1..100 LOOP
  DELETE FROM table_name WHERE id IN (SELECT id FROM temp_table) AND ROWNUM < 100000;
  EXIT WHEN SQL%ROWCOUNT = 0;
  COMMIT;
END LOOP;

Vous pouvez ajuster ROWNUM au besoin. Un ROWNUM plus petit signifie des propagations plus fréquentes et (probablement) un impact réduit sur les autres sessions en termes de nécessité d'appliquer undo. Toutefois, selon les plans d'exécution, il peut y avoir d'autres incidences, et il prendra probablement plus de temps ensemble. Techniquement, la partie "pour" de la boucle est inutile car la sortie mettra fin à la boucle. Mais je suis paranoïaque à propos des boucles illimitées car c'est une douleur de tuer la session si elles sont bloquées.

je recommande d'exécuter ceci comme une seule suppression.

existe-il des tables enfants de l'un à supprimer? Si oui, assurez-vous que la clé étrangère dans ces tableaux est indexé. Sinon, vous pourriez faire un balayage complet de la table enfant pour chaque ligne que vous supprimez ce qui pourrait rendre les choses très lentes.

vous pourriez vouloir quelques façons de vérifier la progression de la suppression au fur et à mesure qu'elle s'exécute. Voir Comment vérifier la base de données oracle depuis longtemps les requêtes?

Comme d'autres personnes l'ont suggéré, si vous voulez tester le l'eau, vous pouvez mettre: rownum < 10000 sur la fin de votre requête.

j'ai fait quelque chose de similaire dans le passé avec Oracle 7, où j'ai dû supprimer des millions de lignes de milliers de tables. Pour toutes les performances rondes et en particulier les grandes suppressions (millions de lignes plus dans une table) ce script a bien fonctionné.

vous devrez le modifier légèrement (c'est-à-dire: examiner les utilisateurs/mots de passe, plus obtenir vos segments de retour à droite). Aussi, vous avez vraiment besoin de discuter de cela avec votre DBA et de l'exécuter dans un environnement de TEST d'abord. Ayant dit tout cela, c'est assez facile. La fonction delete_sql () cherche un lot de rowids dans la table que vous spécifiez puis les supprime lot par lot. Par exemple,

exec delete_sql('MSF710', 'select rowid from msf710 s where  (s.equip_no, s.eq_tran_date, s.comp_data, s.rec_710_type, s.seq_710_no) not in  (select c.equip_no, c.eq_tran_date, c.comp_data, c.rec_710_type, c.seq_710_no  from  msf710_sched_comm c)', 500);

l'exemple ci-dessus supprime 500 enregistrements à la fois de la table MSF170 basée sur un énoncé sql.

si vous avez besoin de supprimer des données de plusieurs tables, il suffit d'inclureexec delete_sql(...) lignes dans le fichier de supprimer les tables.sql

Oh, et n'oubliez pas de mettre un rollback segments de nouveau en ligne, il n'est pas dans le script.

spool delete-tables.log;
connect system/SYSTEM_PASSWORD
alter rollback segment r01 offline;
alter rollback segment r02 offline;
alter rollback segment r03 offline;
alter rollback segment r04 offline;

connect mims_3015/USER_PASSWORD

CREATE OR REPLACE PROCEDURE delete_sql (myTable in VARCHAR2, mySql in VARCHAR2, commit_size in number) is
  i           INTEGER;
  sel_id      INTEGER;
  del_id      INTEGER;
  exec_sel    INTEGER;
  exec_del    INTEGER;
  del_rowid   ROWID;

  start_date  DATE;
  end_date    DATE;
  s_date      VARCHAR2(1000);
  e_date      VARCHAR2(1000);
  tt          FLOAT;
  lrc         integer;


BEGIN
  --dbms_output.put_line('SQL is ' || mySql);
  i := 0;
  start_date:= SYSDATE;
  s_date:=TO_CHAR(start_date,'DD/MM/YY HH24:MI:SS');


  --dbms_output.put_line('Deleting ' || myTable);
  sel_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(sel_id,mySql,dbms_sql.v7);
  DBMS_SQL.DEFINE_COLUMN_ROWID(sel_id,1,del_rowid);
  exec_sel := DBMS_SQL.EXECUTE(sel_id);
  del_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(del_id,'delete from ' || myTable || ' where rowid = :del_rowid',dbms_sql.v7);
 LOOP
   IF DBMS_SQL.FETCH_ROWS(sel_id) >0 THEN
      DBMS_SQL.COLUMN_VALUE(sel_id,1,del_rowid);
      lrc := dbms_sql.last_row_count;
      DBMS_SQL.BIND_VARIABLE(del_id,'del_rowid',del_rowid);
      exec_del := DBMS_SQL.EXECUTE(del_id);

      -- you need to get the last_row_count earlier as it changes.
      if mod(lrc,commit_size) = 0 then
        i := i + 1;
        --dbms_output.put_line(myTable || ' Commiting Delete no ' || i || ', Rowcount : ' || lrc);
        COMMIT;
      end if;
   ELSE 
       exit;
   END IF;
 END LOOP;
  i := i + 1;
  --dbms_output.put_line(myTable || ' Final Commiting Delete no ' || i || ', Rowcount : ' || dbms_sql.last_row_count);
  COMMIT;
  DBMS_SQL.CLOSE_CURSOR(sel_id);
  DBMS_SQL.CLOSE_CURSOR(del_id);

  end_date := SYSDATE;
  e_date := TO_CHAR(end_date,'DD/MM/YY HH24:MI:SS');
  tt:= trunc((end_date - start_date) * 24 * 60 * 60,2);
  dbms_output.put_line('Deleted ' || myTable || ' Time taken is ' || tt || 's from ' || s_date || ' to ' || e_date || ' in ' || i || ' deletes and Rows = ' || dbms_sql.last_row_count);

END;
/

CREATE OR REPLACE PROCEDURE delete_test (myTable in VARCHAR2, mySql in VARCHAR2, commit_size in number) is
  i integer;
  start_date DATE;
  end_date DATE;
  s_date VARCHAR2(1000);
  e_date VARCHAR2(1000);
  tt FLOAT;
BEGIN
  start_date:= SYSDATE;
  s_date:=TO_CHAR(start_date,'DD/MM/YY HH24:MI:SS');
  i := 0;
  i := i + 1;
  dbms_output.put_line(i || ' SQL is ' || mySql);
  end_date := SYSDATE;
  e_date := TO_CHAR(end_date,'DD/MM/YY HH24:MI:SS');
  tt:= round((end_date - start_date) * 24 * 60 * 60,2);
  dbms_output.put_line(i || ' Time taken is ' || tt || 's from ' || s_date || ' to ' || e_date);
END;
/

show errors procedure delete_sql
show errors procedure delete_test

SET SERVEROUTPUT ON FORMAT WRAP SIZE 200000; 

exec delete_sql('MSF710', 'select rowid from msf710 s where  (s.equip_no, s.eq_tran_date, s.comp_data, s.rec_710_type, s.seq_710_no) not in  (select c.equip_no, c.eq_tran_date, c.comp_data, c.rec_710_type, c.seq_710_no  from  msf710_sched_comm c)', 500);






spool off;

Oh, et un dernier conseil. Il va être lent et en fonction de la table peut nécessiter un certain temps d'arrêt. Tester, chronométrer et ajuster sont vos meilleurs amis ici.

Toutes les réponses ici sont très bien, juste une chose à ajouter: si vous souhaitez supprimer des enregistrements dans une table, et sont bien sûr vous n'aurez pas besoin de la restauration, alors vous voulez utiliser l' table tronquée la commande.

(Dans votre cas, vous ne voulait supprimer un sous-ensemble, mais pour quelqu'un qui rôde avec un problème similaire, j'ai pensé ajouter cet)

la façon la plus facile pour moi est: -

DECLARE
L_exit_flag VARCHAR2(2):='N';
L_row_count NUMBER:= 0;

BEGIN
   :exit_code        :=0;
   LOOP
      DELETE table_name
       WHERE condition(s) AND ROWNUM <= 200000;
       L_row_count := L_row_count + SQL%ROWCOUNT;
       IF SQL%ROWCOUNT = 0 THEN
          COMMIT;
          :exit_code :=0;
          L_exit_flag := 'Y';
       END IF;
      COMMIT;
      IF L_exit_flag = 'Y'
      THEN
         DBMS_OUTPUT.PUT_LINE ('Finally Number of Records Deleted : '||L_row_count);
         EXIT;
      END IF;
   END LOOP;
   --DBMS_OUTPUT.PUT_LINE ('Finally Number of Records Deleted : '||L_row_count);
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE ('Error Code: '||SQLCODE);
      DBMS_OUTPUT.PUT_LINE ('Error Message: '||SUBSTR (SQLERRM, 1, 240));
      :exit_code := 255;
END;