1

Hi Teradata colleagues!

I have to do some modifications in production: 1) Modify existing collumns 2) Add some new collumns 3) Drop and re-create primary index

Can anyone help me in 1) case - modifying column segment with addition of compress part:

segment CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('AM ','CAM ','KAM ','KO ','PSHC ','RES ','SBS ')

How to add this column: pot_klasse CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('B','U')

How to make new index? (Do I have to drop it first and then make a new one?)

Dantes
  • 2,693
  • 5
  • 24
  • 33

1 Answers1

3

NOTE: All of this information can be found in the Teradata Manuals available for download from here. From there you can select the release which you are interested in on the upper right of the screen.

Adding Value List Compression to Existing Column

Depending on the table size and duration which you can tolerate the table being exclusively locked you can add value list compression using ALTER TABLE. If there are sufficient presence bits in the table header for the compressed value list the operation can be fairly quick otherwise it will be fairly intensive: 

ALTER TABLE MyDB.MyTable ADD SEGMENT CHAR(10) 
      COMPRESS ('AM', 'CAM', 'KAM', 'KO', 'PSHC', 'RES', 'SBS');

Otherwise you can create a new table with the value list compression defined on it, copy the statistics from the original production table to the new table, insert the data into the new table, and then rename the tables to introduce the new table as the production table.

Adding Column to Existing Table

If there are unused presence bits in the table header adding this column using ALTER TABLE can be a fairly quick operation. If there are not sufficient presence bits for the compressed value list to be captured ALTER TABLE would not be the recommended approach:

ALTER TABLE MyDB.MyTable ADD pot_klasse CHAR(1) 
      CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('B','U');

Changing the Primary Index

It depends.

There are situations where you can modify the PI of the table without having to create a new table using ALTER TABLE. (e.g. non-unique to unique) However, more often than not you will find yourself having to create a new table and inserting the data from the original table to the new table. (e.g. Adding a column, changing the data type of a column in the primary index, etc.)

zinking
  • 5,018
  • 4
  • 43
  • 76
Rob Paller
  • 7,506
  • 23
  • 23
  • Thx Rob! This is what I was looking for. – Dantes Aug 09 '12 at 14:21
  • Rob, can you please help me concerning this matter (I didn`t found it in the documentation, which is honestly little overwhelming): My table had this column -BAN DECIMAL(9,0)-, and now I want to change it to - BAN DECIMAL(15,0) COMPRESS 0.- How can I do it? What does COMPRESS constraint 0. or any other mean anyway? Thanks, D. – Dantes Aug 10 '12 at 08:18
  • I hope this is possible, and I don`t have to create a new table and then copy the data form the old table. The table is very very big - when I do COUNT(*) form that table I get this error: 2616 numeric overflow occurred during computation – Dantes Aug 10 '12 at 09:12
  • I see your question about CONUT(*) was addressed in a separate SO thread. – Rob Paller Aug 10 '12 at 13:27