Index problems

[dreameR25]

Hi there,

I have a table of 8000+ records but when I try and do a simple update such as,

update bpd
set title = upper(title)

it takes a very long time.. in fact it took 1 minute to update 4 records, so at that pace it would take over 30 hours for the whole set of records.
I tried creating an index on the member_key field but it says that there is already an index on that field.

My question is this, is it possible for indexes to get damaged and if so, how do I drop them if I don't even know the name of the index?

Please help because this is urgent.

Thanks.

 

[stefanhei]

Hi.
To get the name of the indexes issue:
select index_name from user_indexes
where table_name = 'BPD'
to get the columns the indexes are on:
select index_name,column_name from user_ind_columns
where table_name = 'BPD'
If there is an index on your title-column i'd drop it before the update, then recreate it afterwards.

Stefan

 

[jimbopalmer]

update bpd
set title = upper(title)
where title <> upper(title)

may be much faster, if most are correct already I tried to remain child-like, all I acheived was childish.

 

[Wonboodoo]

The index won't help your update because you don't have a &quot;where&quot; clause. Even the &quot;where&quot; clause jimbopalmer provides won't use an index. The index will actually slow the query down because you're updating the data as well as the index. Try drop the index and run the update again.

That said, four records updated a minute is ridiculous, with an index or not. You probably have something wrong with your database, or maybe you are getting locked by another process.

 

[stefanhei]

My guess is that there's a compound index (1 index on more than 1 column) that contains the title-column giving you such bad performance.

Stefan

 

[jimbopalmer]

Yes, mine speeds the update by doing fewer disk writes, not by indexing I tried to remain child-like, all I acheived was childish.

 

[sem]

I suppose you have some huge table connected to this one via foreign key. If this is the case, check whether the connected fields are indexed. Do you have such problems with other tables. Regards, Dima

 

[DaPi]

Are there triggers on this table? They could be taking time (but 4 per minute . . . !) DaPi - no silver bullet