Is there a way or query to find total uncompressed data in the system?

Database
Highlighted
Teradata Employee

Is there a way or query to find total uncompressed data in the system?

Hi Forum,

 

Is there a way or query to find total uncompressed data in the system?

 

 

Best Regards,

Sandeep.

 

GANGA SANDEEP KUMAR


Accepted Solutions
Senior Apprentice

Re: Is there a way or query to find total uncompressed data in the system?

Hi,

 

Not easy, and it also depends on exactly what you mean by "uncompressed data".

 

Is this the number of tables that have no compression applied to them?

Or is it the amount of disk space occupied by uncompressed data?

 

A couple of bits of SQL to get you started:

SELECT databasename
   ,TABLENAME
   ,(CASE 
     WHEN BLOCKCOMPRESSION = 'never' THEN 'never'
	 ELSE 'perhaps'
	 end) AS BLOCKCOMPRESSION
FROM dbc.tablesv
WHERE tablekind IN ('t','o');

This first will give you a list of tables and indicate whether they do not have BLC applied (blockcompression = 'never') or whether they may have BLC applied (blockcompression = 'perhaps'). You'd have to use the new filesystem macros to investigate each 'perhaps' table.

 

Actually, I've just thought that even that might not be truly accurate - because the blockcompression attributre retrieved from DBC.TablesV will be the current one. Even if that says 'never' there may be some data blocks in the table which are BLC'd from a 'previous' setting. You might simply have to use the file system macros on every table.

 

SELECT col.databasename
  ,col.TABLENAME
FROM (
SELECT col.databasename
   ,col.TABLENAME
   ,SUM(CASE 
               WHEN col.compressvaluelist IS NULL THEN 1 
			   WHEN col.compressible = 'N' THEN 1
			   ELSE 0 end) AS uncomp_col_count
   ,COUNT(*) AS col_count
FROM dbc.columnsv AS col
HAVING uncomp_col_count = col_count
GROUP BY 1,2) AS col
INNER JOIN dbc.tablesv AS tbls
  ON col.databasename = tbls.databasename
    AND col.databasename NOT IN ('dbc','tdwm')
    AND col.TABLENAME = tbls.TABLENAME
	AND tbls.tablekind IN ('t','o');

This will give you a list of all tables that do not have any MVC or ALC applied.

 

Once you've found the list of tables then you can start doing space calculations if required.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
2 REPLIES
Senior Apprentice

Re: Is there a way or query to find total uncompressed data in the system?

Hi,

 

Not easy, and it also depends on exactly what you mean by "uncompressed data".

 

Is this the number of tables that have no compression applied to them?

Or is it the amount of disk space occupied by uncompressed data?

 

A couple of bits of SQL to get you started:

SELECT databasename
   ,TABLENAME
   ,(CASE 
     WHEN BLOCKCOMPRESSION = 'never' THEN 'never'
	 ELSE 'perhaps'
	 end) AS BLOCKCOMPRESSION
FROM dbc.tablesv
WHERE tablekind IN ('t','o');

This first will give you a list of tables and indicate whether they do not have BLC applied (blockcompression = 'never') or whether they may have BLC applied (blockcompression = 'perhaps'). You'd have to use the new filesystem macros to investigate each 'perhaps' table.

 

Actually, I've just thought that even that might not be truly accurate - because the blockcompression attributre retrieved from DBC.TablesV will be the current one. Even if that says 'never' there may be some data blocks in the table which are BLC'd from a 'previous' setting. You might simply have to use the file system macros on every table.

 

SELECT col.databasename
  ,col.TABLENAME
FROM (
SELECT col.databasename
   ,col.TABLENAME
   ,SUM(CASE 
               WHEN col.compressvaluelist IS NULL THEN 1 
			   WHEN col.compressible = 'N' THEN 1
			   ELSE 0 end) AS uncomp_col_count
   ,COUNT(*) AS col_count
FROM dbc.columnsv AS col
HAVING uncomp_col_count = col_count
GROUP BY 1,2) AS col
INNER JOIN dbc.tablesv AS tbls
  ON col.databasename = tbls.databasename
    AND col.databasename NOT IN ('dbc','tdwm')
    AND col.TABLENAME = tbls.TABLENAME
	AND tbls.tablekind IN ('t','o');

This will give you a list of all tables that do not have any MVC or ALC applied.

 

Once you've found the list of tables then you can start doing space calculations if required.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Is there a way or query to find total uncompressed data in the system?

That was very helpful and thats which i am looking for, thank you very much DaveWellman.

 

Best Regards,

Sandeep.

 

GANGA SANDEEP KUMAR.