Re: Compression Algorithm [From David Clough: Thu, 25 Oct 2007 @ 18:13 GMT]

Message Posted: Thu, 25 Oct 2007 @ 18:13:44 GMT

Subj:

Re: Compression Algorithm

From:

David Clough

Thanks for the comments and input. For your trouble in replying here's where I am ...

Taking those comments into account, I've now finished the job. I've now got one Stored Procedure which, for a specified table, provides all the values worth compressing on, column by column, writing the data to a table.

To complement this, I've written (finished today) a front end (written in Python) that gets the values from Teradata and constructs a full, intact, Create Table statement.

The gathering of the data could take hours - we'll run that part overnight - but the construction of the output takes around 1 second !

Here's an example output :

CREATE MULTISET TABLE example_tab_NEW ( COM_ID CHAR ( 02 ) CHARACTER SET LATIN NOT NULL COMPRESS ('WW'), CON_ID CHAR ( 15 ) CHARACTER SET LATIN NOT NULL, BUL_ID_ORIG CHAR ( 05 ) CHARACTER SET LATIN NOT NULL COMPRESS ('EIN','SCL','ARH','ZRB','BRU','IST','KOR','RTM','ZWS','MAD','SIN','HKG','BCN','BT8', 'BH8','DX7','KUL','SP8','BIE','FRT','BSL','HAM','ZMU','MHN','ANR','LIS','BKK','NBE', 'CN2','TPY','SEL','MOW','QDU','TYO','GNO','HGH','DTM','NGB','BER','CP9','BUH','WAW', 'BLL','DUS','HA3','CN1','COL','LGE','VIE','SAO','BRE','STO','OK1','FBG','LYS','HMK', 'AGB','GVA','HNJ','DUB','ULM','SZX','SCN','VGN','JKT','JNB','BK2','BUD','CP1','WB4', 'CPH','SYD','FR1','LP1','CCU','WZB','CN3','BL1','SGE','ATH','SGN','MNL','MMA','BUE', 'QFC','MRS','YYZ','ZRH','EF3','LUG','NTE','MIL','HYD','GT2','LPZ','GLM','GOT','DRS', 'SBG','LIL','LUX','BRQ','LCY','BOG','CA3','BRT','KLE','ORB','ORK','CDG','LY9','TAO', 'TO1','CVT','HEL','TKU','KRK','KBZ','MEL'), CON_CREATE_DT DATE NOT NULL COMPRESS (date '2007-06-26',date '2007-06-27',date '2007-07-10',date '2007-06-28',date '2007-07-11',date '2007-07-18',date '2007-07-12',date '2007-07-17',date '2007-07-25',date '2007-06-29',date '2007-07-24',date '2007-07-19',date '2007-07-31',date '2007-07-13', date '2007-07-20',date '2007-07-27',date '2007-05-03',date '2007-08-01',date '2007-08-02',date '2007-08-07',date '2007-08-08',date '2007-07-09',date '2007-07-23',date '2007-08-03',date '2007-08-14',date '2007-08-09',date '2007-07-16',date '2007-07-30', date '2007-08-16'), CON_CREATE_TM SMALLINT NOT NULL, CON_SRKY_ID INTEGER NOT NULL, CON_WHOUSE_CD CHAR ( 05 ) CHARACTER SET LATIN NOT NULL COMPRESS (''), CON_DG_TRANMODE_CD CHAR ( 01 ) CHARACTER SET LATIN NOT NULL COMPRESS (''), CON_DUTIABLE_IN CHAR ( 01 ) CHARACTER SET LATIN NOT NULL COMPRESS ('D'), CON_COLL_DT DATE COMPRESS (date '2007-06-27',date '2007-06-26',date '2007-06-28',date '2007-07-10',date '2007-07-11',date '2007-07-18',date '2007-07-12',date '2007-07-17',date '2007-07-25',date '2007-07-31',date '2007-06-29',date '2007-07-13',date '2007-07-24',date '2007-07-19', date '2007-07-26',date '2007-07-20',date '2007-07-23',date '2007-08-02',date '2007-08-01',date '2007-05-03',date '2007-08-07',date '2007-07-09',date '2007-08-08',date '2007-07-16',date '2007-08-03',date '2007-08-09',date '2007-08-14',date '2007-07-30', date '2007-08-16'), CON_COLL_TM TIME(0), CON_COMM_DELIV_DT DATE COMPRESS (date '2007-07-30',date '2007-07-23',date '2007-07-16',date '2007-07-02',date '2007-08-06',date '2007-08-13',date '2007-08-20',date '2007-07-19',date '2007-07-26',date '2007-07-13',date '2007-08-02',date '2007-07-20',date '2007-07-17',date '2007-07-12', date '2007-06-29',date '2007-07-27',date '2007-07-25',date '2007-07-24',date '2007-07-31',date '2007-08-03',date '2007-08-01',date '2007-08-10',date '2007-08-09',date '2007-08-16',date '2007-06-28',date '2007-08-07',date '2007-08-08',date '2007-08-17', date '2007-07-11'), CON_MAX_DEL_DT DATE COMPRESS (date '2007-07-23',date '2007-07-16',date '2007-07-30',date '2007-07-02',date '2007-08-06',date '2007-08-13',date '2007-08-20',date '2007-07-19',date '2007-07-26',date '2007-08-02',date '2007-07-13',date '2007-07-20',date '2007-07-12',date '2007-07-18', date '2007-07-17',date '2007-07-27',date '2007-07-25',date '2007-07-24',date '2007-08-03',date '2007-07-31',date '2007-08-16',date '2007-08-10',date '2007-08-01',date '2007-08-09',date '2007-06-28',date '2007-08-07',date '2007-08-08',date '2007-08-17', date '2007-07-11',date '2007-05-04',date '2007-05-03',date '2007-05-07',date '2007-06-27',date '2007-07-10',date '2007-08-15',date '2007-07-03',date '2007-05-02',date '2007-08-21',date '2007-04-30',date '2007-05-08',date '2007-07-04',date '2007-05-01', date '2007-05-09',date '2007-05-10',date '2007-04-27',date '2007-07-05',date '2007-08-23',date '2007-07-28',date '2007-07-21',date '2007-08-04',date '2007-07-14',date '2007-06-30',date '2007-08-11',date '2007-08-18',date '2007-08-05',date '2007-07-06', date '2007-08-12',date '2007-07-29',date '2007-05-05'), CON_MAX_DEL_TM TIME(0), CND_NOTE_SRC_CD CHAR ( 02 ) CHARACTER SET LATIN NOT NULL COMPRESS ('QS','IS','QL','EB','PS','EX','ED','BL','Z','MT','GH','DI','CR','SC','AC'), CON_OPSA_TGRS_WT DECIMAL(9,3) NOT NULL COMPRESS (.100,.500,1.000,.200,.300,.050,2.000,.120,.250,.150,.400,.080,3.000,.140,5.000), CON_OPSC_TGRS_WT DECIMAL(9,3) NOT NULL COMPRESS (.100,.500,1.000), CON_OA_TOT_VL DECIMAL(7,3) NOT NULL COMPRESS (.000), CON_OC_TOT_VL DECIMAL(7,3) NOT NULL COMPRESS (.000,.001,.002,.003,.005,.004,.006,.010,.008,.012,.007,.009,.011,.016, .018,.024,.014,.013,.030,.015,.027,.017,.036,.019,.021,.040,.022,.033, .025,.028,.048,.032,.026,.034,.060,.038,.072,.029,.031,.035,.045,.096, .042,.037,.120,.054,.080,.043,.044,.064,.039,.041,.051,.075,.047,.056, .055,.240,.144,.058,.046,.100,.192,.049,.059,.090,.288,.070,.053,.067, .084,.384,.108,.063,.074,.125,.480,.068,.061,.066,.057,.085,.081,.069, .091,.216,.073,.065,.576,.076,.077,.960,.071,.086,.768,.088,.082,.078, .168,.336,.150,.079,.180,.087,.175,.360,.083,.160,.112,.128,.110,.200, .101,.089,.162,.130,.140,.105,.095), CON_OA_TITEM_QT DECIMAL(5) NOT NULL COMPRESS (1.), CON_OC_TITEM_QT DECIMAL(5) NOT NULL COMPRESS (1.), CON_VAL_OF_GDS_AM DECIMAL(13,2) NOT NULL COMPRESS (.00), CON_CHK_WT_IN CHAR ( 01 ) CHARACTER SET LATIN NOT NULL COMPRESS ('Y'), CON_CHK_VL_IN CHAR ( 01 ) CHARACTER SET LATIN NOT NULL COMPRESS ('N'), CON_DESP_DT DATE COMPRESS (date '2007-06-27',date '2007-07-10',date '2007-06-26',date '2007-06-28',date '2007-07-11',date '2007-07-18',date '2007-07-12',date '2007-07-31',date '2007-07-25',date '2007-07-17',date '2007-06-29',date '2007-07-24',date '2007-07-19',date '2007-07-13', date '2007-07-26',date '2007-07-20',date '2007-08-01',date '2007-05-03',date '2007-08-02',date '2007-08-07',date '2007-08-08',date '2007-07-23',date '2007-08-14',date '2007-08-03',date '2007-07-09',date '2007-07-16',date '2007-08-09',date '2007-08-10',date '2007-08-16'), ...... plus dozens of other columns ...... PRIMARY INDEX example_tab_UPI ( CON_SRKY_ID ) ;

Given that we're moving to UNICODE, I'm personally hoping this provides significant business benefit.

ttfn

Dave Clough
Database Designer
Express ICS

Attachments

Library

Quick Reference

Archives of the TeradataForum

Message Posted: Thu, 25 Oct 2007 @ 18:13:44 GMT