Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 29 Mar 2000 @ 08:37:13 GMT


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


Subj:   Re-designing Tables
 
From:   Barbara George

We currently have 3 transaction tables that are to be re-designed, some unused columns dropped and new columns added.

I am looking for the most efficient way to design the indexes, and would appreciate any suggestions. What is the performance significance of having the primary key different to the primary index?

Table definitions:

     Table A - header table:
     Size: 200,000,000+ records.  Grows slowly. Oldest data deleted each month.
     Unique column: tranid, generated internally during load process.
     Other significant columns: region, store, date
     Current Indexes:
     (UPI) - region, store, date, tranid
     (NUSI) - region, store, date
     (NUSI) - region, store, tranid
     (NUSI) - region, store
     (NUSI) - tranid
     (NUSI) - date

Users will access table using any combination of region, store, date.

     Table B - detail1
     Size: 2,000,000,000+ records. Oldest data deleted each month.
     Unique columns: tranid & seq
     Other significant columns: region, store, date
     Current indexes:
     (UPI) region, store, date, tranid
     (NUSI) region, store, date

Users seldom access this table directly, usually from header via inner join.

     Table C - detail2
     Size: 220,000,000+ records. Oldest data deleted each month
     Unique columns: tranid & seq
     Other significant columns: region, store, date, refno (20 character
     reference number)
     Current indexes:
     (UPI) region, store, date, tranid
     (NUSI) region, store, date
     (NUSI) region, store, tranid
     (NUSI) region, store
     (NUSI) refno

Users usually access this table via a join to header. Also accessed directly, usually by region, store and either date or refno.

Tables B & C are linked via the header table only.


Thanks for your time.

Barbara George
Manager, Decision Support, Pick 'n Pay Information Systems
Cape Town, South Africa



     
  <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: 27 Dec 2016