Oracle: Fehler bei CREATE BITMAP INDEX

[Claudia_72b2c4]

Hallo an alle

Ich habe in einer Testtabelle 1.000.000 Reihen und wollte auf die Spalte „geschlecht“ einen Bitmap Index anlegen:
CREATE BITMAP INDEX bmiGeschlecht ON Umfrage(Geschlecht);

Leider krieg ich jedesmal folgende Fehlermeldung mit der ich nicht wirklich etwas anfangen kann:
ORA-28604: Tabelle zu fragmentiert, um Bitmap-Index (4261794,304,72) zu generieren.

Einen normalen B-Tree Index kann ich auf dagegen problemlos auf die Spalte anwenden.

Kann mir irgendjemand Tipps geben, was ich bei dem Bitmap-Index genau falsch mache ?

Danke, Gruss
Claudia

[muzel_c91186]

Hallo Claudia,

zunächst kannst Du ja mal versuchen, die Tabelle zu defragmentieren (vorausgesetzt, Du hast DBA-Recht - aber offenbar kannst Du ja auch nen Index anlegen, und genug Plattenplatz).
Also

Alter Table testtabelle move tablespace temp;
alter tablespace (original-tablespace) coalesce;
Alter Table testtabelle move tablespace (original-tablespace);

Und dann noch alle evtl. vorhandenen Indexe (Indizes oder wie auch immer ) zu der Tabelle mit

Alter index index1 rebuild;
Alter index index2 rebuild;
...

reparieren.
Das sollte funktionieren, wenn Du keine ausgefallenen Objekttypen in der Tabelle hast.
Ich bin mir allerdings nicht sicher, ob ein Bitmap-Index auf so einer Spalte etwas bringt - ich vermute mal, da stehen nur Werte „m“ oder „w“ drin? Da geht die Suche ohne Index möglicherweise schneller.
Außerdem gab es bei bestimmten Oracle-Versionen einen ernsthaften Bug bei Bitmap-Indexen. Leider hast Du die Version nicht dazugeschrieben.

Gruß, Uwe

[Claudia_72b2c4]

Hallo Uwe
Danke fuer deine Antwort.
Sorry, ich vergesse immer die Version dazuzuschreiben. Also, es handelt sich um Oracle 9.2.0.1.0.
Da das ganze keine produktive Datenbank ist, hab ich vorhin mal ganz brutal die komplette Tabelle nochmal geschloescht und neu angelegt. Da hat es dann geklappt. Das komische ist, dass sich die Bitmap Indizes nur anlegen lassen wenn die Tabelle ganz neu ist. Waren da vorher schon andere Indizes drauf krieg ich wieder diese Fehlermeldung.
Was macht denn so ein B-Tree Index mit meiner Tabelle dass der Bitmap Index dann streikt ?
Ach so, das ganze wird eine Studienarbeit darueber in welchen Faellen ein Bitmap Index einem B-Tree Index vorzuziehen ist. Daher diese etwas seltsame Indizierung der Spalte „geschlecht“
Gruss Claudia

[muzel_c91186]

Was macht denn so ein B-Tree Index mit meiner Tabelle dass der
Bitmap Index dann streikt ?

Da wäre vielleicht mal ein komplettes Create-script
(jedenfalls das für die Tabelle und die zugehörigen Indizes) interessant…
Streiten sich da vielleicht zwei Indizes um die gleiche Spalte oder Spaltenliste?

Uwe

[Claudia_72b2c4]

Was macht denn so ein B-Tree Index mit meiner Tabelle dass der
Bitmap Index dann streikt ?

Da wäre vielleicht mal ein komplettes Create-script
(jedenfalls das für die Tabelle und die zugehörigen Indizes)
interessant…
Streiten sich da vielleicht zwei Indizes um die gleiche Spalte
oder Spaltenliste?

OK, hier das CREATE TABLE Skript:

CREATE TABLE Umfrage
(nr INT NOT NULL,
geschlecht CHAR(1) NOT NULL,
familienstand CHAR(15) NULL,
beruf CHAR(20) NULL,
geburtsjahr INT NOT NULL,
automarke CHAR(15) NOT NULL,
getraenk CHAR(15) NOT NULL,
waschmittel CHAR(25) NOT NULL,
urlaubsziel CHAR(25) NOT NULL,
wohnort CHAR(20) NOT NULL);

Anschliessend hab ich die Tabelle mit folgender Prozedur gefuettert:

CREATE OR REPLACE PROCEDURE einfuegen_umfrage(anzahl INT) IS
nr INTEGER := 0;
geschlecht CHAR(1) := ‚w‘;
familienstand CHAR(15) := null;
beruf CHAR(20) := null;
geburtsjahr INTEGER := 1930;
automarke CHAR(15) := ‚auto1‘;
getraenk CHAR(15) := ‚getraenk1‘;
waschmittel CHAR(25) := ‚waschmittel1‘;
urlaubsziel CHAR(25) := ‚urlaubsziel1‘;
wohnort CHAR(20) := ‚wohnort1‘;

BEGIN
LOOP
– Nummern von 1 bis 1.000.000
nr := nr + 1;

– Selektivitaet 2: maennlich, weiblich
IF MOD(nr, 2) = 0
THEN geschlecht := ‚w‘;
ELSE
geschlecht := ‚m‘;
END IF;

– Selektivitaet 5: ledig, verheiratet, geschieden, verwitwet, null
IF MOD(nr, 5) = 0
THEN familienstand := ‚ledig‘;
ELSIF MOD(nr, 5) = 1
THEN familienstand := ‚verheiratet‘;
ELSIF MOD(nr, 5) = 2
THEN familienstand := ‚geschieden‘;
ELSIF MOD(nr, 5) = 3
THEN familienstand := ‚verwitwet‘;
ELSE
familienstand := null;
END IF;

– Selektivitaet 20: beruf1 … beruf20
beruf := ‚beruf‘ || (MOD(nr, 20) + 1);

– Selektivitaet 50: 1930 … 1980
geburtsjahr := 1930 + MOD(nr, 50);

– Selektivitaet 300: automarke1 … automarke300
automarke := ‚automarke‘ || (MOD(nr, 300) + 1);

– Selektivitaet 1.000: getraenk1 … getraenk1000
getraenk := ‚getraenk‘ || (MOD(nr, 1000) + 1);

– Selektivitaet 5.000: waschmittel1 … waschmittel5000
waschmittel := ‚waschmittel‘ || (MOD(nr, 5000) + 1);

– Selektivitaet 20.000: urlaubsziel1 … urlaubsziel20000
urlaubsziel := ‚urlaubsziel‘ || (MOD(nr, 20000) + 1);

– Selektivitaet 50.000: wohnort1 … wohnort50000
wohnort := ‚wohnort‘ || (MOD(nr, 50000) + 1);

EXIT WHEN nr > anzahl;

INSERT INTO umfrage
VALUES (nr, geschlecht, familienstand, beruf, geburtsjahr,
automarke, getraenk, waschmittel, urlaubsziel, wohnort);

END LOOP;

END einfuegen_umfrage;
/

Dann hab ich zuerst einen normalen Index auf jede einzelne Spalte erstellt und meine SELECT Anweisungen ausgefuehrt. Anschliessend hab ich die Indizes mit DROP INDEX wieder geloescht und wollte dann die Bitmap Indizes erstellen. Und an der Stelle spuckt er dann die Fehlermeldung aus.

Hilft dir das weiter oder soll ich die CREATE INDEX Anweisung auch noch posten ? Interessiert mich brennend warum das nicht funktioniert !!!

Um den Fehler zu umgehen, hab ich jetzt einfach 3 identische Tabellen (mit unterschiedlichen Namen) angelegt so dass ich die Indizes nicht immer loeschen muss. Nur bin ich mir noch nicht ganz so sicher, ob ich damit nicht die Ergebnisse der Zeitmessung der SELECT-Anweisungen zerstoere weil’s halt nicht mehr ein und diesselbe Tabelle ist.

Gruss Claudia

[muzel_c91186]

Habs auf der 9.2.0.5 probiert, keine Probleme.
Vielleicht hilft ein Update?
Gruß Uwe

[TheBeast]

Hallo Claudia!

Die einfachste Lösung als erstes: Den Index anlegen, bevor du die Tabelle füllst, dann dürfte der Fehler nie auftreten.

Die zweite Lösung ist etwas komplizierter (und für reine Testdaten wohl auch _etwas_ zu aufwändig), aber wenn das mit Echtdaten passiert, dann vielleicht doch gangbar:

1. Den (ersten) Block identifizieren, der den Fehler verursacht

   SELECT max(substr(rowid,10,4)) FROM [table];

2. Die Rows aus diesem Block in eine temporäre Tabelle exportieren

   CREATE TABLE my_temp AS SELECT * FROM [table] WHERE rowid IN
   (SELECT rowid FROM [table] WHERE substr(rowid,10,4)=[resultat aus 1.]);

3. Ursprüngliche Daten löschen

   DELETE FROM [table] WHERE rowid IN
   (SELECT rowid FROM [table] WHERE substr(rowid,10,4)=[resultat aus 1.]);

4. Bitmap Index anlegen
   Falls es weitere Blocks gibt, die den Fehler verursachen, dann bei 1 von vorne beginnen und Daten in die temporäre Tabelle auslagern.

5. Daten aus der temporären Tabelle in die Originaltabelle zurückschaufeln

   INSERT INTO [table] SELECT * FROM my_temp;

6. Temporäre Tabelle löschen

   DROP TABLE my_temp;

Das sollte das Problem lösen, ist aber wie gesagt etwas aufwändig. Die Schritte 1 und 2 liessen sich übrigens auch noch zusammenfassen (noch ein Subselect mehr…).

ACHTUNG:
.) Das Ganze hab’ ich jetzt mehr oder weniger aus dem Stegreif hingeschrieben, möglicherweise ist das Script also nicht 100 %-ig sauber.
.) Wenn eine Applikation rowids direkt verwaltet dann ändern sich diese dadurch natürlich und müssen angepasst werden.
.) Für den Fall dass Oracle die Struktur der Rowids umstellt stimmt der substr nicht mehr (der hier sollte für alle aktuellen Versionen funktionieren)

Zu deinen Performance-Auswertungen: Du solltest sichergehen, dass bei jeder Messung die Ausgangsbedingungen gleich sind (Cache!) und ausserdem, dass der jeweilige Index auch wirklich verwendet wird (analyze table, explain plan bzw. optimizer hints), sonst sind die Messdaten eher für den Hugo.

Liebe Grüße,
Martin