Faster way to delete and rebuild a table

Database

Faster way to delete and rebuild a table

Hello everyone.  This is my first post.  I have a question about deleting and rebuilding tables.  I have inherited some code that is structured like

1.  create an empty table in the sandbox  (I'll call it table A)

2.  insert all data from an existing table (I'll call it table B) to table A

3.  drop table B

4.  create table B again with no data in it (table structure only)  (I'll call this table B new)

5.  insert all data from table A into table B new

6.  drop table A

7.  add new month's data to table B new

I am wondering if it is really necessary to have so many steps?  My thinking is that there is another way to keep a safety copy of the data and eliminate some of the steps, like 1 and 2.  I could be totally wrong here but does this current way of doing things seem like the best (i.e. fastest) way?  Thank you

Tags (1)
5 REPLIES
Teradata Employee

Re: Faster way to delete and rebuild a table

So both step 2 and step 5 insert ALL the data (no WHERE clause)? And table A is dropped before applying the new data to B? (which would mean it's not really a "safety copy")

Why not skip 1 through 6 and just insert the new data?

Enthusiast

Re: Faster way to delete and rebuild a table

Hi,

Even if you skip all the steps and do only step 7, the result will be same.

Khurram

Re: Faster way to delete and rebuild a table

Thanks for your replies.  To answer your question Fred, table A is dropped after it is copied to table B new (step 5).    What is really happening is the original table B is being appended to.  Step 2 backs up table B (temporarily) as table A, table B is then dropped, an empty copy of table B is created (table B new), the data is put back in from table A, table A is dropped, then a query is ran to get the new month's data and append it to table B new.  Just seemed like a few unncecessarys steps in there, but I could be wrong.  Thanks.  

Enthusiast

Re: Faster way to delete and rebuild a table

Hi,

Thank you for clarification, but these all first 6 steps will lead the table B in same state as it is in initial state. So you can directly add the new data to table B without these steps. Just comment out these steps and execute only step 7. the result will be same.

Br,

Khurram

Khurram
Teradata Employee

Re: Faster way to delete and rebuild a table

1 Use the Vproc Manager utility to set the VprocState of the AMP that will be rebuilt to FATAL.

 

SET (AMP) =FATAL;

 

2 Restart the Teradata Database.

 

Tpareset –x

 

Note: This step is not necessary if the rebuilding AMP state was FATAL before the last

Teradata Database restart.

 

3 Use the Vproc Manager utility to boot the AMP that will be rebuilt. Messages will be displayed on the system console to indicate the status of the boot. If the boot is successful, this AMP is ready for an ALL TABLES rebuild.

 

 

Note: The BOOT command will re-initialize the disk of the AMP in anticipation of alltables table rebuild and start the DBS partitions on the specified AMP. This applies only to vprocs with a VprocState of FATAL and a ConfigStatus of Down. A confirmation input is necessary to process the initialization. Valid VprocIds are decimal numbers in the range of 0...16383.

 

Note: Hex numbers can also be specified by appending a trailing “x” (for example, 0x, 3FFx).

 

4 Start Table Rebuild and run an ALL TABLES rebuild on this AMP.

 

Start rebuild

 

Rebuild amp (1*) all tables all data;

 

5 When the rebuild is done, use the Vproc Manager utility to set the VprocState of this AMP to ONLINE.

 

SET (AMP)=Online:

Note: Table Rebuild automatically sets the VprocState of this AMP from UTILITY to OFFLINE when complete.

 

6 Restart the Teradata Database.

 

Tpareset -x