Re: dropping an index inside a transaction

On Sat, Mar 17, 2012 at 8:37 PM, Andy Chambers <achambers(at)mcna(dot)net> wrote:
> Hi All,
>
> If one drops an index inside a transaction, is that index still usable
> by other transactions?  My use-case is loading millions of records
> into a heavily indexed table.  I'd like to speed up the load by
> temporarily dropping the indices.  I'm wondering if I can do this
> inside a transaction so that the rest of the application can still
> benefit from the indices.

If another transaction is already relying on the index you can't drop
it. Once you drop it in a transaction all other queries against the
tables will be waiting behind the exclusive lock on the transaction to
finish to access the table.