Re: Question about index generation

Question 1: probably it is advisable to recreate the indexes Question 2: you should be able to execute an alter index rebuild command, you don't need to drop/recreate the primary key. It may well worth adding the unrecoverable clause, other wise everything will go through redo log also.
The temporary segment you need, the size of it is between 1.5 and 2 times the number of rows in the table times the total length of the key. You don't need to take into account the size of the table. During import every index is created right after the import of the table. Of course you can avoid this by issuing two command imp ... indexfile=<filename>
This will dump all create index statements. and
imp ... indexes=n. This will skip the index creation step during import.

Hth,
Sybrand Bakker, Oracle DBA

Ian & Chris wrote:

> Hi,
> Can anybody help me please.
> I'm using a DEC Alpha running VMS 7.1 and Oracle 7.3.2......
> We have an on-line application the does enquiry vetting for a home
> shopping catalogue.
> As a result of this process several tables are populated with
> anything from 1 to 370 rows per enquiry.
> What we are now doing is sending this info to another Oracle
> database on another Alpha to be kept for upto a year and a half.
> This info will be used for subsequent vetting of enquiries, for
> Operational Research to tune scorecards and for reporting
> purposes.
> Some of the tables will end up having 300 - 500 million rows on
> then.
> I have a couple of questions
> 1. When we come to archive/extract (to tape) the info and delete
> it from the database will this have an adverse effect on
> subsequent (days / weeks later) data retrieval times.
> 2. Can I rebuild / drop re-create the Primary keys on the tables
> considering one of the tables has been sized at 13.93 Gb.
> I've only got a temporary tablespace of 524288 M (Would I need
> a temporary tablespace of at least the same size as the
> biggest table of doesn't Index recreation work that way).
> How does EXPORT / IMPORT deal with re-creating / re-builing
> indexes ??
>
> Thanks for any help in advance
>
> Ian (totally confused by the lack of help from technical support at
> work) Downham
>
> A relatively newbie DBA
>
> --
> to return e-mail remove NOSPAM from address......

Received on Wed Nov 25 1998 - 13:53:50 CST