Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 15 Oct 2013 @ 21:47:59 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Adding a Name to Primary Index without recreating it
 
From:   Prescott, Kyle R

I am not aware of any easy was outside of the CREATE TABLE AS...

It's very doable, but you will need to script the index portion.

For NUPI

     CREATE TABLE MY_NEW_TABLE as MY_EXISTING_TABLE WITH DATA AND STATS
     PRIMARY INDEX  (Col1....);

For UPI

     CREATE TABLE MY_NEW_TABLE as MY_EXISTING_TABLE WITH DATA AND STATS
     UNIQUE PRIMARY INDEX  (Col1....);

Other method:

1) Capture your DDL using show table commands (script it)... export them into single file to edit.

2) Capture STATS on existing tables via TSET tool.

3) create new tables with edited primary index names in different (new) database (with space to capture the data)

4) insert/select from old tables to new ones

5) Import STATS via TSET tool to new tables changing the target databasename on the import (saves time/resources recollecting stats)

6) delete/drop all the old tables (or delete database if you can to them all at once)

7) create table .tablename as .tablename with data and stats (do for all tables)

8) delete/drop all the tables in the


Kyle Prescott
DBA Manager
Unum-GHDS
Chattanooga, TN



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023