Archives of the TeradataForum

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

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

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>>