I am new to TERADATA technology. Can anyone let me know how to know the % change in table size in teradata. Is there any specific query to identify the % change in table size ???
Or else is there any table (system/DBC) from which we will get the required result.
We need to find the % change in table size for every week/15days/month.
Kindly help me to get the required data.
Thanks in adavance.
As such if you have the PDCR or SYS_MGMT enabled to hold the historical query log related data, you will see a table tablespace_hst which gives the associated information at a day level for all the tables in all the databases.
I am unsure about the tablespace_hst option mentioned above, but the following query should give you some direction towards the entire approach:
If you don't have a tool, then you can resort to using scripts(linux, perl, python etc) which will do computation.Then you can schedule your scripts as per your requirement using schedulers.
Sample query to see space:
select sum(currentperm) actualspace, max(currentperm)*(hashamp()+1) effective_space from dbc.tablesize
where databasename = 'yourdb' and tablename = 'yourtblname';
Thanks a lot for your valuable suggestions :-)
According to my knowledge if the change in table size is 10 % (It may be increase or decrease) then stats would be stale.
So I wanted to know the % change in tables, If the % change is =< 10 % then stats will stale and I wanted to work on that stats stale.
Please help me to come up from this situation.
If you only want to achieve this knowledge of stale-stats from this .... why dont you just create COLLECT STATS script and schedule it with your batch?
If in some tables the data volume is huge i.e. collecting stats may take alot of time, you can schedule that seperately over the weekend or non-business critical hours.