Update auf grosse Tabelle

[trustno1]

Hallo Zusammen

Ich habe ein Problem mit einem Update auf eine relativ grosse Tabelle.

Tabelle a enthält rund 2 mio records
Tabelle b enthält rund 5,5 mio records

Die Verlinkung ist wie folgt:
Tabelle a hat 5 Attribute, wobei 2 wichtig sind
a.userkey
a.planfk
(ein Index existiert über beide Attribute)

Tabelle b hat 80 Attribute, wobei 4 wichtig sind
b.userkey
b.planfk
b.date
b.lineflag
(ein Index existiert über userkey, date, lineflag)

Mein Statement ist nun wie folgt:

UPDATE b SET b.planfk = (
SELECT a.planfk FROM a WHERE a.userkey = b.userkey)
b.date > '31-12-2006' AND
b.lineflag = 0;

Ich habe das Statement jetzt über Nacht laufen gelassen und es war immer noch nicht fertig. Ich weiss, die Verschachtelung ist ungünstig. Gibt es eine andere Möglichkeit ein solches Update vorzunehmen? Oder irgendwie eine Einstellung die einen Commit nach einer bestimmten Anzahl Records absetzt, damit der Cache zurückgesetzt wird?

Im voraus besten Dank für eure Hilfe.
Viele Grüsse
Andrea

[fanderlf]

Irgendwie ist das SQL Statement falsch entweder fehlt ein WHERE nach dem SubSelect oder die Klammer ist falsch

[trustno1]

oups, ja, das WHERE fehlt:

UPDATE b SET b.planfk = (
SELECT a.planfk FROM a WHERE a.userkey = b.userkey)
WHERE b.date > '31-12-2006' AND
b.lineflag = 0;

sorry

[ralfb]

Ein Datum sollte auch immer als solches übergeben werden:

Also nicht: b.date > '31-12-2006', sondern d.date > date '2006-12-31' oder to_date('31-12-2006','dd-mm-yyyy').

Damit werden alle möglichen Effekte und evtl. auch das Nichtnutzen des Index vermieden.

MfG

Ralf

[trustno1]

Ich hatte das Format eigentlich vor der Ausführung gesetzt:

alter session set nls_date_format = 'dd-mm-yyyy';

Das sollte ja eigentlich den gleichen Effekt haben, nicht? Aber ich werde es trotzdem mal so probieren. Ich danke dir!

MfG
Andrea

[ralfb]

Ich hatte das Format eigentlich vor der Ausführung gesetzt:

alter session set nls_date_format = 'dd-mm-yyyy';

Das sollte ja eigentlich den gleichen Effekt haben, nicht?

Nicht ganz. Das sorgt nur dafür, dass bei einem internen Umwandeln von Zeichenkette nach Datum (und umgekehrt) das Richtige entsteht. Aber ob bei dem Vergleich von Datum und Zeichenkette nun das linke Datum in eine Zeichenkette umgewandelt wird (und damit der Index nicht greift) oder die rechte Zeichenkette in ein Datum (was zu begrüßen wäre), weiß ich ehrlich gesagt auch nicht ;-) Aber vielleicht kann dazu noch einer was sagen.

Gruß

Ralf

[dimitri]

Hi,

in diesem fall wird der Index verwendet, allerdings bin ich noch auf der Suche, nach welchen regeln Oracle die Konvertierungen durchführt.

select * from tab where number_field >'a'

Schlägt fehl, da Oracle versucht ein to_number um das 'a' zu legen.

select * from tab where varchar_fiel >1

Schlägt fehl wenn sich in varchar_field ein nicht nummerisches Zeichen befindet, da Oracle versucht ein to_number um varchar_field zu legen.

Zur eigentlichen Frage: Sind deine Tabellen analysiert, wieviele Datensätze sollen denn geändert werden? Also prozentual gesehen nur ein paar oder fast alle?

Dim

[trustno1]

Hi Dim

Danke für deine Nachforschungen.

Die Tabelle beinhaltet zurzeit 5'584'138 Records (täglich steigend), davon muss ich ca. 25% updaten.

Ich habe mittels oracle explain plan geschaut, es wird kein Index genommen, stattdessen wird ein full scann gemacht, egal in welcher Reihenfolge ich die Attribute im Index setze.

Ich denke aber inzwischen, dass nicht das Datum das Problem ist.

Ich habe in der Where-Clause ja nur zwei Attribute:

b.date > '31-12-2006' AND
b.lineflag = 0;

Für den select count(*) mit diesen Bedingungen habe ich einen Index über diese beide Attribute erstellt. Dann wurde der Index genommen.

Wenn ich aber das Update-Statement ausführe (mit der gleichen Where-Clause), dann wird der Index nicht genommen (auch nicht für den Select, nicht nur für den Update).

Für den Sub-Select wird der Index über table a genommen.

Vielen Dank
MfG
Andrea

[dimitri]

Hi,

bei 25% ist es durchaus verständlich, dass kein Index verwendet wird, da dies evtl. noch länger dauern würde.
Für deinen Count muss die Datenbank nicht noch zusätzlich in der Tabelle das Feld planfk nachlesen, sondern kann einen fast Full Scan über den Index durchführen (das ist das Full Tablescan Äquivalent beim Index)

Voraussetzung dafür ist aber, dass mindestens eines der Felder einen NOT NULL Constraint besitzt ansonsten würde Oracle auch hier einen FTS durchführen müssen, da andernfalls das Ergebnis falsch sein könnte.

Du kannst ja mal einen Hint versuchen, um Oracle dazu zu bringen den Index auf jeden fall zu verwenden:

UPDATE /*+INDEX(b INDEXNAME)*/ SET ...

Des weiteren ist da noch der Punkt mit den aktuellen Statistiken. Was bringt denn

select num_rows,blocks,last_analyzed from user_tables where table_name='B'

Dim

[trustno1]

Hi Dim

Der select num_rows,blocks,last_analyzed from user_tables where table_name='B' ergibt:

NUM_ROWS=5615037
BLOCKS=275210
LAST_ANALYZED=24-02-2010

Das mit dem Hint an Oracle zur Benutzung des Index funktioniert gemäss explain table. Ich lass das Script mal laufen, mal schauen, wie lange es geht.

Vielen Dank!
mfg
andrea