Disable/Drop Indexes

[hubud]

I work with a DSS system that has a number of different processes using the same tables at different times of the day.

Recently due to various problems these processes tend to be running at the same time. This has caused conflicts when similar sessions try to run procs to either create or drop indexes at the same time.

E.g.

Session 1 drops index(prior to load, locking table) and then creates index(post load, locking table).

Session 2 attempts to do the same thing however due to table locks held by session 1 returns an error and terminates the session due to NOWAIT being specified. We often get the message "Create Index failure...index already exists" or similar.

Is there a better way than to drop and then recreate an index during a large load? I am aware that this is necessary as the index would slow down the whole process. Could Disabling the index work better?

I am aware that you can disable, enable novalidate, enable validate which does not cause dml locks. However when an index is disabled does this mean that the index is inaccessible to other processes (i.e. can other processes use the index in a disabled state, I'm guessing no).

Following the index being re-enabled I was thinking that it could be rebuilt online thus only holding full table locks for a short period of time which should reduce contention with other processes doing what they need to do. I have not been able to find if "nologging" can be specified during online rebuilds.

I would appreciate any direction on this.

simmo

 

[alexhu]

Can you leave the indexes off until all the processes have finished ?

I realise this will slow down normal table access but will it be acceptable to do this ?

Alex

 

[sem]

In fact you need some kind of dispatcher to manage all your tasks. I think this is the only NORMAL way to handle such situations. Errors with dropping/creating indexes are in fact the less dangerous that may occur.

Regards, Dima

 

[hubud]

Not possible leave the indexes off because the processes need to create indexes to run updates during the session.

It is not possible to slow down the load updates/inserts as they are run daily and already last 5-6 hours.

there would not normally be a problem however, as I said, they are being run concurrently and need to be nursed through the process manually. Occasionally they are re-run if certain procs fall down because of the table locks of other sessions.

thanks anyway

simmo