La suppression d'un grand nombre de données dans Oracle

La première approche est la meilleure, parce que vous donnez l'optimiseur de requête une image claire de ce que vous essayez de faire, au lieu d'essayer de le cacher. Le moteur de base de données peut prendre une approche différente de la suppression de 5,5 m (soit 5,5% de la table) à l'interne qu'à l'effacement de 200k (soit 0,2%).

Voici aussi un l'article massif SUPPRIMER dans Oracle qui vous voudrez peut-être lire.

OriginalL'auteur Jiri Klouda

Lors de l'exécution de massives suppressions dans Oracle, assurez-vous que vous n'êtes pas en cours d'exécution hors de UNDO SEGMENTS.

Lors de l'exécution de DML, Oracle écrit d'abord tous les changements dans le REDO journal (le vieux de données ainsi que les nouvelles données).

Lorsque le REDO journal est rempli ou un délai d'attente se produit, Oracle effectue log synchronization: il écrit new des données dans les fichiers de données (dans votre cas, les marques de la forme de blocs libres), et les écritures anciennes données dans le UNDO tablespace (afin qu'il reste visible pour les transactions simultanées jusqu'à ce que vous commit vos modifications).

Lorsque vous validez vos modifications, de l'espace dans UNDO segments occupés par yuor transaction 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 les requêtes simultanées (le cas échéant) devront lire à partir de REDO journaux ou UNDO segments lors de l'exécution d'analyses de la table. Ce n'est pas le moyen le plus rapide pour accéder à des données.

Cela signifie également que si l'optimiseur de sélectionner HASH JOIN pour votre suppression de la requête (ce qui sera probablement le faire), et la table temporaire ne rentre pas dans le HASH_AREA_SIZE (qui sera très probablement être le cas), alors la requête devra several scanne sur la grande table, et de certaines parties de la table sera déjà déménagé dans REDO ou UNDO.

Donné tous dit ci-dessus, vous feriez probablement mieux de supprimer des données dans 200,000 morceaux et valider les modifications entre les deux.

Donc, vous, d'abord, se débarrasser des problèmes décrits ci-dessus et, d'autre part, d'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 à utiliser NESTED LOOPS, que je pense qu'il sera plus rapide dans votre cas.

Pour ce faire, assurez-vous que votre temp table possède une clé primaire sur ID, et de réécrire 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 cette requête au travail.

Comparer avec les éléments suivants:

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'en tenir à cela.

OriginalL'auteur Quassnoi

Si votre version d'origine de SQL prend un temps très long, certains simultanées Sql peut s'exécuter lentement qu'ils ont à utiliser la commande ANNULER pour reconstruire une version des données sans votre les modifications non validées.

Un compromis pourrait ê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 que nécessaire. Un petit ROWNUM moyens de plus en plus fréquentes s'engage et (probablement) une réduction de l'impact sur d'autres sessions en termes de devoir demander une annulation. Toutefois, selon les plans d'exécution, il peut y avoir d'autres incidences, et il prendra probablement plus de temps ensemble.
Techniquement, le "POUR" une partie de la boucle est inutile, car la SORTIE ce sera la fin de la boucle. Mais je suis paranoïaque à propos illimité boucles que c'est une douleur pour tuer la session si elles ne se coincent.

OriginalL'auteur Gary Myers

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 tous autour de la performance et surtout le grand supprime (millions de lignes de plus dans un tableau) ce script fonctionnait bien.

Vous aurez à modifier légèrement (ie: examiner les utilisateurs/mots de passe, ainsi que d'obtenir des rollback segments de droite). Aussi vous avez vraiment besoin d'en discuter avec votre DBA et l'exécuter dans un environnement de TEST en premier. 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 est la suppression de 500 enregistrements à la fois à partir de la table MSF170 basé sur une instruction sql.

Si vous avez besoin de supprimer des données à partir de plusieurs tables, il suffit d'inclure supplémentaires exec 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. Ça va être lent et en fonction de la table peut prendre un certain temps d'arrêt. Les tests, le calendrier et le réglage sont votre meilleur ami ici.

OriginalL'auteur Mark Nold

Le moyen le plus facile pour moi, c'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;

OriginalL'auteur sandeep