Identifying tables requiring compression

Database
N/A

Identifying tables requiring compression

Hi All,

I have come across a requirement where we need to identify all the tables, preferably the large ones, requiring compression. We are aiming to improve performance by compressing some tables, large tables.

Is there a way to know which tables need compression by quering system tables? If I supply the database name, it should output potential candidates to which we can apply compression.

Regards,

Shardul

3 REPLIES
N/A

Re: Identifying tables requiring compression

Need help !

Any sugesstions are welcome.

Teradata Employee

Re: Identifying tables requiring compression

I would create a new table with the identity column and get the data from old table like this:

/*
I suppose your starting point is EXAMPLE
*/
CREATE TABLE EXAMPLE
(
 COL1 CHAR(4)
)PRIMARY INDEX( COL1 );

INSERT INTO EXAMPLE (COL1) VALUES( 'AAAA' );
INSERT INTO EXAMPLE (COL1) VALUES( 'AAAB' );
INSERT INTO EXAMPLE (COL1) VALUES( 'AABB' );
INSERT INTO EXAMPLE (COL1) VALUES( 'BBBB' );

/*
Create a new EXAMPLE table
*/
CREATE MULTISET TABLE EXAMPLE_ID
(
 ID_PETICION INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
           (START WITH 1
            INCREMENT BY 1
            MINVALUE 1
            MAXVALUE 2147483647
            NO CYCLE),
 COL1 CHAR(4)
)PRIMARY INDEX( COL1 );

INSERT INTO EXAMPLE_ID (COL1)  SELECT COL1 FROM EXAMPLE;

/*Swap the names*/
DROP TABLE EXAMPLE;
RENAME TABLE EXAMPLE_ID TO EXAMPLE;

SELECT * FROM EXAMPLE;
Teradata Employee

Re: Identifying tables requiring compression

Sorry , wrong site!!