Indexaufbau / Indexrebuild

[sayso]

Hallo Jungs,

gibt es irgendein Dokument wo beschrieben ist, wenn Datensätze beim Indexaufbau/rebuild gesperrt werden?

Es geht um folgende Szenarien:

1) Es gibt eine Tabelle mit 2.5 Millionen Datensätzen. Um einen schnelleren Zugriff auf die Sätze zu ermöglichen, wollen wir zusätlich einen Index anlegen, der andere Felder beinhaltet.

2) Es existiert bereits ein Index auf die 2.5 Millionen Datensätze, den wir aber auch neu aufbauen wollen.

Da die Tabelle soviele Einträge hat stellt sich nun die Frage ob bzw. wie die Datensätze beim Indexaufbau/rebuild gesperrt werden?

Meiner Meinung nach passiert folgendes beim Indexrebuild. Es wird ein 2ter temporärer Index auf die Felder erstellt und wenn dieser fertig erstellt ist, wird einfach geswitcht und der alte entsorgt. Ist das soweit richtig?

Werden dabei Datensätze gesperrt, etc..?

Datenbanksystem: Oracle 9i & 10g

Vielen Dank für euere Hilfe.

[Amstelchen]

Da die Tabelle soviele Einträge hat stellt sich nun die Frage ob bzw. wie die Datensätze beim Indexaufbau/rebuild gesperrt werden?

Meiner Meinung nach passiert folgendes beim Indexrebuild. Es wird ein 2ter temporärer Index auf die Felder erstellt und wenn dieser fertig erstellt ist, wird einfach geswitcht und der alte entsorgt. Ist das soweit richtig?

Werden dabei Datensätze gesperrt, etc..?

AFAIK wird auf tabellenbasis gesperrt, während mittels ALTER INDEX ein rebuild stattfindet oder mittels CREATE INDEX ebendieser erstellt wird.

der index wird dabei im TEMP tablespace erstellt und wie du richtig geschrieben hast, erst dann der basistabelle zugeordnet. das geschieht im data dictionary.

die klausel ONLINE erlaubt allerdings, DML in einer journalingtabelle (intern realisiert als IOT) abzulegen; die änderungen werden im anschluss in den originären index hineinintegriert - während dieser zeit kann also problemlos mittels INSERT, UPDATE und DELETE daten verändert werden, ohne vom locking betroffen zu sein.

natürlich sind wie immer diverse feinheiten zu beachten, z.b. allenfalls vorhandene (sub)partitionierung, art des index/der indizes (domain, bitmap, cluster), verwendung von parallel DML (da geht AFAIR kein online index rebuild), etc.

s'Amstel

s'Amstel

[sayso]

AFAIK wird auf tabellenbasis gesperrt, während mittels ALTER INDEX ein rebuild stattfindet oder mittels CREATE INDEX ebendieser erstellt wird.

die klausel ONLINE erlaubt allerdings, DML in einer journalingtabelle (intern realisiert als IOT) abzulegen; die änderungen werden im anschluss in den originären index hineinintegriert - während dieser zeit kann also problemlos mittels INSERT, UPDATE und DELETE daten verändert werden, ohne vom locking betroffen zu sein.

Hallo Amstelchen,

d.h. wenn ich einen Index ohne die Klausel ONLINE erstelle, wird die komplette Tabelle dabei gesperrt? Das ist für diese zentrale Tabelle bei uns leider nicht akzeptabel.

Was hat die Klausel ONLINE für "Nachteile", denn sonst würde ja jeder den Index immer online erstellen, wenn dadurch keine Sperrung erfolgt.

Vielen Dank für deine Hilfe :e@sy

[geloescht_JesterDay]

Ohne jetzt von Oracle Ahnung zu haben... aber ein Index bezieht sich doch immer nur auf den Zugriff auf einen Datensatz bzw. auf das Finden eben dieses bestimmten Satzes. Wieso sollte dann überhaupt eine Tabelle gesperrt werden? An den Daten in der Tabelle wird doch gar nichts geändert.

Wenn auf eine Tabelle zugegriffen wird, wird vom DBMS geprüft, ob für die Anforderung ein passender Index zur Verfügung steht. Wenn ja wird der zum Zugriff genommen, wenn nicht, werden die Sätze in der Eingangsreihenfolge (so wie sie eben angelegt wurden) durchsucht.

So ein Zugriff sollte auch bei dieser Anzahl an Datensätzen nicht ewig brauchen, einen geeigneten DB-Server vorrausgesetzt.

Im großen und ganzen passiert doch nicht so viel mehr als wenn ein "Select DatensatzIndex FROM Tablelle ORDER BY Feld" gemacht werden würde und dann dies gespeichert wird.

Oder liege ich da jetzt so falsch?

EDIT:

Hab hier gerade mal (IBM iSeries DBMS) eine Abfrage auf eine Tabelle gemacht, die auch ca. 2,5 Mio Sätze hat (paar mehr).

Ein "Select * ..." hat für den Zugriff in Eingangsreihenfolge 52 Millisekunden gedauert (laut Optimierungsmonitor), inkl. Optimierungszeit des DBMS (1 Millisek.). Verfügbare Zeit für die anweisung waren 83104 Mikrosekunden.

Anweisungsbeginn: 2006-10-30-09.37.34.953888

Anweisungsende: 2006-10-30-09.37.35.036992

Ich weiß nicht ganz, wo da so ein Problem bestehen soll...

[sayso]

hmm was ist denn nun richtig?

[Jasper]

ein offline-rebuild setzt ein exclusives DML-lock (kein insert, update, delete, merge möglich) auf die tabelle. die tabelle während des indexaufbaus zu ändern ist generell keine gute idee. der index wird aber nicht im TEMP-tablespace erstellt, dort landet nur das temporäre sort-segment. wenn der rebuild den vorhandenen index verwenden kann (ist die regel), dann steht der index auch während des rebuilds für queries zur verfügung.

ein online-rebuild dagegen baut parallel einen zweiten index in form einer IOT auf. alle änderungen auf den vorhandenen während des rebuild werden in einem journal vermerkt. deshalb kann auch nicht der vorhandene index für den rebuild genutzt werden. die tabelle wird nur kurz zu beginn und am ende für DML gesperrt.

offline geht schnell, vor allem, wenn der vorhandene index genutzt werden kann und benötigt weniger resourcen. nachteil ist das DML-lock für die dauer des rebuilds.

online hat zwar kein DML-lock, benötigt aber bedeutend mehr resourcen und dauert länger als offline.

wenn möglich immer offline-rebuild bevorzugen, auch wenn DML vielleicht für die dauer des rebuild 'hängen'. wenn es gar nicht anders geht, online verwenden.

-j

[mme]

bei uns dauert offline und online genauso schnell...

Online braucht mehr Ressourcen, weil der paralell, wo der alte noch besteht, den neuen schon speichern muss...

Die Frage warum dann nicht alle online machen ist einfach... Früher gab es noch nicht die Option online (kam glaube ich erst mit 8i (8.1.7).

Um abwärtskompatibel zu sein gibt es die Unterscheidung.

Ausserdem ist das online nur in der Enterprise Version verfügbar, d.h. wenn du die Standardversion hast, stellt sich die Frage nach online gar nicht...

[Jasper]

bei uns dauert offline und online genauso schnell...

Online braucht mehr Ressourcen, weil der paralell, wo der alte noch besteht, den neuen schon speichern muss...

wenn offline und online genauso lang dauert, kann es sich nicht um grosse tabellen handeln. offline kann den vorhandenen index nutzen, online macht _immer_ ein full table scan:

> alter index dummy1 rebuild;

----------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost |

----------------------------------------------------------------------

| 0 | ALTER INDEX STATEMENT | | | | |

| 1 | INDEX BUILD NON UNIQUE| DUMMY1 | | | |

| 2 | SORT CREATE INDEX | | | | |

| 3 | INDEX FAST FULL SCAN| DUMMY1 | | | |

----------------------------------------------------------------------

> alter index dummy1 rebuild online;

----------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost |

----------------------------------------------------------------------

| 0 | ALTER INDEX STATEMENT | | | | |

| 1 | INDEX BUILD NON UNIQUE| DUMMY1 | | | |

| 2 | SORT CREATE INDEX | | | | |

| 3 | TABLE ACCESS FULL | DUMMY | | | |

----------------------------------------------------------------------

und zumindest bei mir ist ein index fast full scan _immer_ schneller als ein table scan, wenn die tabelle grösser als ein paar blöcke ist.

-j

[Sigi]

@JesterDay es macht ja wohl nen Unterschied ob ich nen Select ausfuehre wo ich nur den Datenzeiger auf der Tabelle rumschubse oder ob ich auch die Daten lese und weiterverarbeite.

[mme]

wenn offline und online genauso lang dauert, kann es sich nicht um grosse tabellen handeln. offline kann den vorhandenen index nutzen, online macht _immer_ ein full table scan....

Ja stimmt, theoretisch hast du recht, aber bei uns braucht es offline und auch online 6 Stunden alle indexe zu rebuilden. Die größten Tabellen haben 25 Mio Datensätze...

Von der theorie her eigentlich nicht nachvollziehbar... dem muss ich wohl mal auf den grund gehen...

[geloescht_JesterDay]

@JesterDay es macht ja wohl nen Unterschied ob ich nen Select ausfuehre wo ich nur den Datenzeiger auf der Tabelle rumschubse oder ob ich auch die Daten lese und weiterverarbeite.

Kommt drauf an wie du sie liest und wie du sie verarbeitest. Bei DB Abfragen ist der Flaschenhals immer die ÜBertragung zum Client. Da das in dem Fall aber nicht gemacht werden muss, passiert da nicht viel mehr als den Satzzeiger rumzuschubbsen. Die Daten werden ja sofort auf dem Server wieder verarbeitet.

Ich hatte hier schon ein Programm wo ich erstmal alles über SQL und auf Clientseite gemacht habe. Bei vielen Tabellen und vielen Sätzen (u.A. die mehr als 2,5 Mio von oben, allerdings schon mit ner entspr. Einschränkung) hat das schon lange gedauert. Das ganze dann umgesetzt per RPG direkt auf dem Server war um ... bestimmt 1-2 Zehnerpotenzen schneller, evtl noch mehr Und das schon hier, wo ich eine mehr oder weniger direkte Anbindung an die DB habe. Will gar nicht wissen wie das beim Kunden aussah

Die SQL Abfrage muss ja auch erstmal umgesetzt und abgearbeitet werden. Die DB versteht SQL nicht native. Zumindest ist das bei unserer DB2 so. RPG ist SQL zu Fuß, sag ich immer (Naja schon noch was mehr, aber man arbeitet da direkt an den Dateien und mit dem Satzzeiger)

[johnhaltonx]

Blöde frage, warum wird der Index überhaupt rebuilded??

Soll der auf einen anderen Tablespace verschoben werden ?

Alles andere ist nämlich in 99% der Fälle sinnlos

AskTom "Rebuilding Indexes"

[mme]

Blöde frage, warum wird der Index überhaupt rebuilded??

Soll der auf einen anderen Tablespace verschoben werden ?

Alles andere ist nämlich in 99% der Fälle sinnlos

AskTom "Rebuilding Indexes"

Das ist eine alte Frage der Philosophie... und eine Frage der Applikation. Bei OLTP-System ist es meistens nicht nötig, interessanter weise schlägt der Tuning-Assistent von Oracle dies jedoch nicht selten vor...