Archives of the TeradataForum

Message Posted: Fri, 07 Sep 2001 @ 08:58:36 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Ruth,

There is no simple sql that will do this. The numbers in the dbc.databasespace table (which the dbc.allspace, dbc.diskspace and dbc.tablesize views are based on) include index and data rows - you can't split them out.

The "create table as" option is a nice ideas for small tables.

For large tables you may want to try using the calculations in the Database Design manual (pages 12-26 for USI and 12-27 for NUSI). The sql for USI's does something like;

- build derived table#1 containing a single row which is the row-count for the target table

- build derived table#2 by joining dbc.indices to dbc.columns to give you the total data length of all columns in the USI (again this is a single row)

- multiply the value in DT#1 by (the value in DT#2 + 25)

- add on @512bytes per amp (table header - I assume a usi sub-table has one) -- you've got a good approximation to the size of the USI index

Calculations for NUSI's are more complex, but follow much the same lines. Write this as a bteq script or a macro and you can then tailor the processing for specific tables, indexes etc etc.

Also, Tom Stanek is correct in that you can use Ferret to do this. You can run Ferret from the Remote Console function within Teradata Manager - you don't need a Unix logon.

Cheers,

Dave

	<Prev	Next>		<<First	<Prev	Next>	Last>>