hangs on index rebuilt

Giganews Newsgroups
Subject: hangs on index rebuilt
Posted by:  Marco Vannini (m.vanni…@tinCANCELLA.it)
Date: Mon, 19 Apr 2004

Hi all.
Session  hang with a  'waited too long for a row cache enqueue lock!' during
an index rebuilt after a direct load (with skip_index_maintenance) into a
large subpartition-composite table. I realized that  the problem concerned
2 newly created function-based bitmap indexes, built on the same field:
index1: 'create bitmap index fbx_tab1_1  on tab1(substr((fieldx),1,1)) local
tablespace  ts_index;'
index 2: 'create bitmap index fbx_tab1_2 on  tab1(substr((fieldx),2,1))
local tablespace  ts_index;'

I didn't get any problem in creating them, but when I load new data in a new
empty partition and then perform 'alter table tab1 modify subpartition
new_subpartition rebuild unusable local indexes' the process hangs and the
alert reports the message above. After having  dropped those indexes
everything it's ok;  but since it would be useful to have them, how can
avoid this problem?
thanks

Replies