Drop table versus truncate and drop index

Drop table versus truncate and drop index

Post by michae » Sat, 17 May 2003 01:12:57



I have a DTS package that bulk copies data from text files
into a staging table and then adds an index to speed the
parsing out of rows into another destination table. Then
indexes are adding to the destination table to speed some
data quality queries that run against it. At the moment
the package truncates the tables and drops the indexes
before moving the data.

Would it be more efficient to drop the tables and then re-
create them as opposed to truncating and then waiting on
the several drop index statements?

 
 
 

Drop table versus truncate and drop index

Post by Ray » Sat, 17 May 2003 08:14:27


Best thing to do is test for yourself. I used to use
delete statements but truncate since it is non(or
mininally) logged is extremely faster. Went from 7
seconds to less than a second on some tables

Quote:>-----Original Message-----
>I have a DTS package that bulk copies data from text
files
>into a staging table and then adds an index to speed the
>parsing out of rows into another destination table. Then
>indexes are adding to the destination table to speed
some
>data quality queries that run against it. At the moment
>the package truncates the tables and drops the indexes
>before moving the data.

>Would it be more efficient to drop the tables and then
re-
>create them as opposed to truncating and then waiting on
>the several drop index statements?

>.


 
 
 

1. Problems dropping indexes when dropping tables

Hi,

I have tried to drop a table which executes fine, but on recreating the
table (obviously slightly differently) I've found that it doesn't always
successfully drop the indexes.  The message I am getting at he moment is
as follows:

Table Corrupt: Object does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#29184 extent#29280
object id on extent=0 (object name = 0) object id in Sysindexes;
1465108310 (object name=adr)

has anybody ever had this error?  And even if not, does anybody know
what it's asking me to do?

I would be grateful for nay help as I am at a complete loss

Thanks

Jane

2. A database connection

3. Relation between DROP TABLE and DROP INDEX

4. Problems in MDB to ADP Conversion

5. Drop Vs (Truncate & Drop)

6. Naming Conventions

7. Problem with Drop Table and truncate table

8. Possible tuse of EXISTS in PL/SQL?

9. Truncate table vs drop table and fragmentation

10. Scripting DROP Table *and* DROP (related) Foreign Keys...

11. drop table failed to drop associated sequence

12. Unable to drop or truncate table