Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: drop index or export table

Re: drop index or export table

From: Suresh Bhat <suresh.bhat_at_mitchell-energy.com>
Date: Fri, 12 Nov 1999 14:18:38 GMT
Message-ID: <01bf2d28$9888b0e0$a504fa80@mndnet>


Correction:

My previoous post should say SYS's temporary tablespace not SYSTEM's.

Also, if the indexed constraints are foreign keys then you may have to disable them before recreating.

Further, Conner suggested rebuilding, this will require twice as much disk space as recreating,
because the old index is still present until the new one is finished. So you will need 600M which is a lot.
Except having the old index for quesries, there is no great advantage in rebuilding index if you are not moving the index to another tablespace.

Suresh

Suresh Bhat <suresh.bhat_at_mitchell-energy.com> wrote in article <01bf2c8b$ae5efc00$a504fa80_at_mndnet>...
> Hi,
>
> Steven <SCHEN_at_prodigy.net> wrote in article
> <80es3p$3ep0$1_at_newssvr04-int.news.prodigy.com>...
> > I have a table with 200M and 8 extents, but its 2 indexes are 250M with
> over
> > 30 extents each.
> >
> > Do we drop and rebuild indexes or export/import table?
> Drop and recreate indexes. Make sure that you have 275m extra space in
> your SYSTEM's temporary tablespace where the index is temporarily built
by
> SYS account before it is moved to the specified tablespace.
>
> > Would table
> > export/import also rearrange index extent?
>
> I believe that is correct. For the following to work you must have
> exported with compress=y.
>
> If you want to see what those storage parameters are then use:
> imp indexfile=create_script.sql
>
> This will not import any data, it simply creates a script for indexes and
> tables (commented out).
>
> While you are at it, you may want to try
>
> imp show=y log=create_script.sql
>
> With the same effect, but creates script for almost every object
including
> packages, functions,triggers,
> constraints,grants etc.
>
> > On the other hand, some indexes are associated with constraint columns.
> > Haven't tried if they can be dropped.
> I don't believe that matters.
>
> Suresh Bhat
> Oracleguru
> www.oraclegur.net
>
Received on Fri Nov 12 1999 - 08:18:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US