Table space occupied is different eventhough all are same

Database
Enthusiast

Table space occupied is different eventhough all are same

Greetings Experts,

We needed to check the table space after dropping one of the column from the table.

SELECT TABLENAME, SUM(CURRENTPERM) FROM DBC.TABLESIZE WHERE TABLENAME='TABLE_ABC' AND DATABASENAME='DATABASE_T'
GROUP BY TABLENAME;

To estimate the size after drop, created another table DATABASE_C.TABLE_DEF in the same server (note that the length of the table_name is also same)  with the same structure through SHOW TABLE DATABASE_T.TABLE_ABC  in another database as we don't have access to create table in database_t.  Then inserted all rows from DATABASE_T.TABLE_ABC to DATABASE_C.TABLE_DEF

Now when I check the space occupied by this new table DATABASE_C.TABLE_DEF (even before the required column drop) the size is a bit lesser.  (DATABASE_T.TABLE_ABC had 15266.13 MB where as DATABASE_C.TABLE_DEF had 15133.52 MB)  I have verified that all indexes that are present in TABLE_ABC is also present in TABLE_DEF through dbc.indices.  Indeed there is only partition primary index.  As the new table is created through the show table of older one, if there is any compression being applied, then the new table should also have the same compression and the overall tablesize should not change as per my assumption.  I am not able to figure it out.  Please note that the table DDLs for both are same, NO Fallback, No other indexes, default mergeblockratio,  Then why was there a difference in the space.  I am assuming there might be a higher value assigned for a data block in DATABASE_C so that it may have held some rows in a block than in the other one, there by decreasing the space.  What may have caused for the decrease in space and how can I check those settings..  Also, when I created the new table with a table_name of length 34, it did not show up in the dbc.tablesize.  When I created the table with length < 30, I couldn't any rows in dbc.tablesize.  When we can create a table_name of length 34, then why doesn't it show in dbc.tablesize.  Am I doing something wrong..Thank you for your time.

 

1 REPLY
Teradata Employee

Re: Table space occupied is different eventhough all are same

Most likely the original table had some data blocks with embedded freespace remaining, e.g. from deleted rows or from block splits due to inserting a new row between existing rows in an already full block.

 

DBC.TableSize is the backward-compatible legacy view and truncates the object names at 30 characters, so it won't ever match a 34 character long name. Since TD12.0, you should be using DBC.TableSizeV. That's also true of DBC.TablesV, DBC.DatabasesV, DBC.ColumnsV, and any other views with versions which have identical names except for the V suffix.