Optimierung der Abfrage Löschen mit einer großen Anzahl von Daten auf oracle

Arbeite ich auf oracle 9i. Ich habe eine Tabelle mit 135,000,000 records, partitioniert, wobei jede partition mit ca. Zu 10.000.000 Zeilen. alle indizierten und alles.

Muss ich löschen um 70,000,000 Zeilen aus dieser die neue business-Anforderung.

So habe ich eine Sicherung der zu löschenden Zeilen als separate Tabelle.

Table1 <col1, col2........> -- main table (135,000,000 rows)

Table2 <col1, col2........> -- backup table (70,000,000 rows)

Versucht, die unterhalb Abfrage löschen.

Delete from table1 t1 where exists (select 1 from table2 t2 where t2.col1 = t1.col1)

aber es dauert unendlich Stunden.

dann versucht 

declare
cursor c1 is 
select col1 from table2;
c2 c1%rowtype;
cnt number;
begin
cnt :=0;
open c1;
loop
    fetch c1 into c2;
    exit when c1%notfound;

    delete from table1 t1 where t1.col1 = c2.col1;
    if cnt >= 100000 then
        commit;
    end if;
    cnt:=cnt+1;
end loop;
close c1;
end;

sogar noch seine schon seit mehr als 12 Stunden. und immer noch nicht abgeschlossen.

Bitte beachten Sie, dass es mehrere Indizes auf Tabelle1 und ein index über col1 auf Tabelle2. alle Tabellen und Indizes werden analysiert.

Bitte beraten, ob es eine Möglichkeit der Optimierung für dieses Szenario.

Danke Jungs.

  • Wenn der code wirklich so aussieht, das Sie Begehen werden, für jede Zeile nach Zeile 100000, das ist wahrscheinlich nicht das, was Sie wollen und werden langsam natürlich. Stellen Sie Ihre cnt:=0 nach dem commit
  • Ya-Zähler zurücksetzen muss, ich glaube, das war ein Tippfehler ausgelassen. Aber ich zog es vor, fallen die Indizes und simulieren, wie die top-Antwort
InformationsquelleAutor Jude F'do | 2012-03-20
  1. 4

    Löschen aller Indizes (Sicherung der create-Anweisungen)
    Verwenden Sie die select-Anweisung, die verwendet wird, um die backup-Tabelle, erstellen Sie einen Befehl LÖSCHEN
    Neu alle index

    InformationsquelleAutor Peter Kiss
  2. 4

    Ich erinnere mich, vor diesem Problem früher. In diesem Fall Griffen wir, um dies zu tun, da lief es schneller als alle anderen zu löschen-operation:

    1) Erstellen Sie eine andere Tabelle mit der gleichen Struktur

    2) Einfügen in die neue Tabelle die Datensätze, die Sie behalten möchten (verwenden Sie den Direkten Pfad einfügen, um diese Fahrt)

    3) Fallen die alte Tabelle

    4) Benennen Sie die neue Tabelle

    InformationsquelleAutor Nikhil
  3. 1

    Sagen Sie, dass die Tabelle partitioniert ist. Ihre Absicht, die fallen alle Daten, die in bestimmten Partitionen? Wenn dem so ist, sollten Sie in der Lage, ziehen Sie einfach die 7 Partitionen, die die 70 Millionen Zeilen, die Sie löschen möchten. Ich nehme jedoch an, dass Ihr problem ist nicht so einfach.

    Wenn Sie tun können interim verpflichtet, dass bedeutet, dass Sie kümmern sich nicht um die transaktionskonsistenz, die meisten effizienter Ansatz ist wahrscheinlich etwas entlang der Linien von 

    CREATE TABLE rows_to_save
    AS SELECT *
         FROM table1
        WHERE <<criteria to select the 65 million rows you want to keep>>

TRUNCATE TABLE table1;

INSERT /*+ append */
  INTO table1
SELECT *
  FROM rows_to_save;

    Abgesehen, dass anstatt der backup-Tabelle, wäre es effizienter, einfach die DELETE Anweisung

    DELETE FROM table1
 WHERE <<criteria to select the 70 million rows you want to keep>>

    Können Sie auch profitieren von dem löschen oder deaktivieren von Indizes und Einschränkungen vor dem ausführen des DELETE.

    InformationsquelleAutor Justin Cave
  4. 0

    Werde ich beantworten diese Annahme, dass es billiger ist, filter gegen die backup-Tabelle, aber es wäre wahrscheinlich billiger, nur die negation der Kriterien, die Sie verwendet, zum Auffüllen der backup-Tabelle.

    1) erstellen Sie eine neue Tabelle mit der gleichen Struktur. Keine Indizes, constraints, Trigger.

    2) 

     wählen Sie " insert /*+ append nologging */into new_table-partition (' || n.partition_name || ') select * from old_table-partition (' || o.partition_name || ') minus select * from bak_table-partition (' || b.partition_name || ');' 
von all_tab_partitions o, all_tab_partitions n, all_tab_partitions b 
wo o.partition_no = alle( n.partition_no, b.partition_no) 
und o.table_name = 'OLD_TABLE' und o -.table_owner = 'BESITZER' 
und n.table_name = 'NEW_TABLE' und n.table_owner = 'BESITZER' 
und b.table_name = 'BAK_TABLE' und b.table_owner = 'BESITZER'; 
- beachten Sie, ich habe Sie nicht führen können, müssen kleinere Korrekturen neben den offensichtlichen Ersetzungen

    3) zu überprüfen, und führen Sie das Ergebnis der vorherigen Abfrage

    4) bauen Sie die Indizes, Einschränkungen und Trigger, die, wenn nötig

    Dies vermeidet massive Mengen von redo-und undo im Vergleich zu den "löschen".
    append-Hinweis für direct-path inserts
    keine Protokollierung zur weiteren Reduzierung redo - stellen Sie sicher, dass Sie backup danach
    nutzt Ihr Partitionierung zu brechen, die die Arbeit in Blöcken, die sortiert werden können, weniger geht

    Könnten Sie wahrscheinlich schneller gehen mit parallel-Einsatz + parallel wählen, aber es ist wahrscheinlich nicht nötig. Nur nicht parallel select ohne den insert-und ein "alter session enable parallel dml"

    • nologging ist kein echter Hinweis, Sie müssen nur append.
    InformationsquelleAutor llayland
Schreibe einen Kommentar