Re: disabling an index without deleting it?

["Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>]

On Tue, Feb 26, 2008 at 8:48 PM, Markus Bertheau
<mbertheau(dot)pg(at)googlemail(dot)com> wrote:
> 2008/2/27, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>
>
> > "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>  >  > "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> wrote:
>  >
>  > >> begin;
>  >  >> drop index abc_dx;
>  >  >> select ....
>  >  >> rollback;
>  >  >>
>  >  >> and viola, your index is still there.  note that there are likely some
>  >  >> locking issues with this, so be careful with it in production.  But on
>  >  >> a test box it's a very easy way to test various indexes.
>  >
>  >  > Wouldn't you also bloat the index?
>  >
>  >
>  > No, what makes you think that?  The index won't change at all in the
>  >  above example.  The major problem is, as Scott says, that DROP INDEX
>  >  takes exclusive lock on the table so any other sessions will be locked
>  >  out of it for the duration of your test query.
>
>  Why is the exclusive lock not taken later, so that this method can be
>  used reasonably risk-free on production systems? From what I
>  understand the later would be either a statement that would
>  (potentially) be modifying the index, like an UPDATE or an INSERT, or
>  actual transaction commit. If none of these occur and the transaction
>  is rollbacked, the exclusive lock doesn't have to be taken at all.

It would rock to be able to do that on a production database.  Any
Oracle DBA looking over your shoulder would fall to the floor and need
resuscitation.