All Forums Database
sk8s3i 35 posts Joined 06/13
07 Apr 2016
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

-Thanks Shardul

sk8s3i 35 posts Joined 06/13
07 Apr 2016

Need help !
Any sugesstions are welcome.

-Thanks Shardul

AtardecerR0j0 71 posts Joined 09/12
07 Apr 2016

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;

 

Be More!!

AtardecerR0j0 71 posts Joined 09/12
07 Apr 2016

Sorry , wrong site!!

Be More!!

You must sign in to leave a comment.