Fighting ORA-00600[12421]: DROP+CREATE INDEX works, REBUILD INDEX not ...

Hello,

I wonder if somebody else had encountered something similar to the following concerning
ORA-00600 [12421][][][][]...: Background:
Oracle EE 8.1.7.3 on AIX 4.3.3
Oracle Report, Query consisting of 7 SELECTS with UNIONs

The story:
The report runs well over weeks and is used ca. 10 users. Then it throws ORA-00600 [12421]. The user phones to us.

A developer in my team said
"Oh, that one again ... drop and recreate that index ABC !"
-

Because I thought that DROP and CREATE was just their old habit as they did not know about REBUILD, I made a rebuild of index ABC. BTW, it is a very simple index: one column only

Start Report, crash: still ORA-00600 [12421].
-

Analyzed ABC
Seeing execution plan. Index is taken.

Start Report, crash: still ORA-00600 [12421].
-

My developer starts nagging at me "Why donīt You just DROP ... etc.pp"

Ok. I do DROP INDEX ABC. CREATE INDEX ABC lalala to make him content.

The Report runs normally again.
All are content - but me ... I did not understand what was happening.
-

Aftermath:
Report problem is solved, but I am left back with the recognition that DROP+CREATE is obviously _not_ equal to REBUILD ! Before this, I thought that REBUILD (+NOLOGGING) would be most beneficial, just because it speeds up my work. Many docs and books suggest that.

I knew the metalink doc about ORA-00600 [12421], it is a reported bug (fixed in 9i). Some made a work around for their special problems.
But it did not say anything that could help me understand my problem; the index ABC was not UNUSABLE, in fact the index ABC was found in the execution plan.
But when I tried to start the query, I immediatly saw ORA-00600.

OK. we migrate to 9.2. within a few weeks anyway, but I must say, it shaked my picture of the world a little ...

My developers may have the luxory not to understand something, I dont. Is it a _general_ misconception that I have about an index REBUILD, or is this based only on a special query with many UNIONs ? This is why I post this.
I googled a while and found nothing in my direction.

Any comments are greatly appreciated.

TIA, Jan Received on Mon Feb 09 2004 - 08:24:24 CST