Statistics while Updating a large table

Database
Enthusiast

Statistics while Updating a large table

Hi 
I wanted to know is there any system table or view or any other way to find out the progress of the update statement in teradata?
for example if i am updating a table with 500 million rows, while it is getting updated can i find out how many rows have been updated through a query? Even if it the data is not accurate it would work for me? Is there any table where the size of the logs gets changed (so that i can get the % change ) or any such thing when the query is in progress? 


Accepted Solutions
Junior Apprentice

Re: Statistics while Updating a large table

Hi,

 

What you're trying to do is not really provided for by the DBMS. It doesn't give that sort of 'in flight' information.

 

"is there any way that i can get the number of rows that have been inserted when the query is still running." No. As per my above comment, this information is not made available by the DBMS.

 

"DBC.TableSizeV do give me the change in size when the insert query is running but calculating the % in this case is seeming difficult as i dont know what will be total size of the table in the end." Agreed. You'd need to make some rough estimates from testing (i.e. 100000 rows = 10MB - or something) and then convert the change in size to a number of rows. It will only be a rough estimate.

 

I think that is probably the best you can do.

 

Cheers,

Dave

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

Re: Statistics while Updating a large table

Hi,

 

There is no system table which will track this for you.

 

A couple of things that may help you:

1) If your table has a column which gets updated on each run (e.g. a 'DataUpdateTimeStamp') then you could run a query against the table to check for the required value in that column.

2) The closest you may come is to look at the size of DBC.TransientJournal. Note however that this is part of the WAL log and I don't know if the reported size is just for DBC.TransientJournal or for the entire WAL log. Also note thta this table holds change rows for all currently executing transactions, not just your update.

 

What you want is going to be very hard to do using system data.

 

Cheers,

Dave

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

Re: Statistics while Updating a large table

Hi. 

Thanks for the quick response. I am afraid that solution 1 wont be possible as there is not such field and for solution 2 it does not allow me to access DBC.TransientJournal via teradata sql. 
I was wondering if the size of the table or the log size gets changed when the table is updated which can be fetched while the update query is still in progress.

Junior Apprentice

Re: Statistics while Updating a large table

Hi,

 

If using DBC.TransientJournal then you're only looking at the size - not the content - and you should be able to do this using SQL which uses view DBC.TableSizeV.

 

If you're adding rows to your target table then the table size will change and this can be tracked  (roughly) using DBC.TableSizeV.

 

If you're updating rows and the row size isn't changing then the table size won't change.

 

A question I probably should have asked earlier: Why do you want to find out this information?

 

Cheers,

Dave

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

Re: Statistics while Updating a large table

Thanks! 
The concern is that I am updating tables with large data on backend and On UI i want to show the progress somehow otherwise the user has to wait for hours and hours till the whole table is updated. Earlier we used to update the table in chunks but it was not good performance wise and used to take days on 1 billion rows. 

the size was the alst option in case i dont get the no. of rows affected. also if i choose the insert and delete option instead of updating, is there any way that i can get the number of rows that have been inserted when the query is still running. 
DBC.TableSizeV do give me the change in size when the insert query is running but calculating the % in this case is seeming difficult as i dont know what will be total size of the table in the end.

Junior Apprentice

Re: Statistics while Updating a large table

Hi,

 

What you're trying to do is not really provided for by the DBMS. It doesn't give that sort of 'in flight' information.

 

"is there any way that i can get the number of rows that have been inserted when the query is still running." No. As per my above comment, this information is not made available by the DBMS.

 

"DBC.TableSizeV do give me the change in size when the insert query is running but calculating the % in this case is seeming difficult as i dont know what will be total size of the table in the end." Agreed. You'd need to make some rough estimates from testing (i.e. 100000 rows = 10MB - or something) and then convert the change in size to a number of rows. It will only be a rough estimate.

 

I think that is probably the best you can do.

 

Cheers,

Dave

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

Re: Statistics while Updating a large table

Yes! Estimate is the best i can get in this scenario. 

Thanks a lot Dave. It was really helpful.