Optimierung von delete statement

[Jones]

Ich habe folgendes Statement:

DELETE FROM TABLE1 WHERE ID NOT IN ((SELECT DISTINCT CID FROM TABLE2) UNION (SELECT DISTINCT CID FROM TABLE3))

CID in TABLE2 und TABLE3 verweist auf eine ID in TABLE1 (nicht als Fremdschlüssel, ist in meinem Fall nicht möglich).
TABLE1 enthalt sehr viele Einträge. Ich möchte alle aus TABLE1 löschen, auf die in TABLE2 und TABLE3 nicht mehr verwiesen wird.

Obiges Statement dauert sehr lange (ich benutze Oracle 10 XE). Wie kann ich den Löschvorgang beschleunigen?

[dimitri]

Wie kann ich den Löschvorgang beschleunigen?

Indem Du auf zumindest 2 deiner 3 Sortiervorgänge verzichtest:

DELETE FROM TABLE1 
 WHERE ID NOT IN ((SELECT CID FROM TABLE2) UNION (SELECT CID FROM TABLE3))

Ein UNION dampft impliziet doppelte Datensätze ein.

Ansonsten kommts natürlich drauf an, wiele Datensätze Du zu löschen hast.

Evtl ist es auch schneller, die Datensätze die man behalten möchte in einer zwischentabelle zu kopieren, die ursprüngliche Tabelle zu truncaten und dann wieder zurückzukopieren. Das kommt aber auf Deinen Anwendungsfall drauf an.

Dim

PS: Können CID in Tabelle2 oder 3 NULL sein?

[O. Helper]

Wenn CID indiziert ist, müsste die Lösung schneller sein:
[highlight=SQL]DELETE FROM TABLE1
WHERE NOT EXISTS (SELECT 1
FROM TABLE2
WHERE TABLE1.CID = TABLE2.CID)
AND
NOT EXISTS (SELECT 1
FROM TABLE3
WHERE TABLE1.CID = TABLE3.CID) [/highlight]

[dimitri]

Wäre das nicht eher eine OR Bedingung? Um zu beurteilen ob NOT IN oder NOT EXISTS schneller ist, müsste man die Mengenverhältnisse kennen.

Wenn CID indiziert ist, müsste die Lösung schneller sein:

Ein Indexzugriff ist nicht unbedingt immer schneller.

Dim

[O. Helper]

eine OR Bedingung

Wenn ich nicht allzu falsch liege, stimmt AND.
Den oben werden sie mit UNION verbunden und dann mit NOT IN geprüft; CID darf also weder nicht in TABLE 1 und nicht in TABLE2 vorhanden sein (ForeignKey)

Mit dem Rest hast Du aber Recht (und ich schreibe zukünftig könnte statt solte oder gar müsste ).

[Jones]

danke

Danke für eure schnelle Antworten.

@dimitri: Das mit dem Umkopieren kann ich tatsächlich mal probieren...
Was genau meinst du mit Sortiervorgängen?
Um es mal zu konkretisieren die CID-Felder in TABLE2 und TABLE3 sind eindeutig - es kann also, von null-Werten abgesehen, hier keine Dopplungen auftreten. Genauer: die CID-Felder von TABLE2 und TABLE3 verweisen auf den Primärschlüssel von TABLE1.

Mein Anwendungsfall ist der, dass ich aus TABLE2 und TABLE3 Einträge entfernen muss und dann aus TABLE1 die Einträge entfernen will, auf die die aus TABLE2 und TABLE3 gelöschten verwiesen haben. Da TABLE2 und TABLE3 aber einen unterschiedlichen Aufbau haben komme ich mit kaskadierendem Löschen nicht weiter; oder irre ich mich da?

[dimitri]

Wenn ich nicht allzu falsch liege, stimmt AND.

Ja doch hast natürlich recht.

Was genau meinst du mit Sortiervorgängen?

Du hast zwei DISTINCT und ein UNION im Statement. Beide dampfen doppelte Datensätze ein - dazu müssen diese aber vorher sortiert werden. Und das machst du eben 3 mal. Wenn du dir den Auführungsplan ansiehst, wirst du auch 3 Sort Unique Operationen feststellen können.

Mein Anwendungsfall ist der, dass ich aus TABLE2 und TABLE3 Einträge entfernen muss und dann aus TABLE1 die Einträge entfernen will, auf die die aus TABLE2 und TABLE3 gelöschten verwiesen haben. Da TABLE2 und TABLE3 aber einen unterschiedlichen Aufbau haben komme ich mit kaskadierendem Löschen nicht weiter; oder irre ich mich da?

Wenn ich das so richtig verstanden habe, dann ja: Du irrst dich. Der Aufbau der Tabellen hat nichts mit Referentieller Integrietät zu tun. Leg von den beiden Tabellen jeweils einen FK Constraint mit der Option ON DELETE CASCADE auf die übergeordnete Tabelle an, und Deine Probleme sollten der Vergangenheit angehören. Achte aber darauf, dass die FK Felder indiziert sind. Das geschieht, im Gegensatz zu einem PK Constraint, nicht implizit.

Dim