Is there any "Row Processed Count" variable for the last executed query in Vertica database, either in system variables or system tables?

Question

I want to capture total row processed count of the last ran query in Vertica VSQL shell.

I know this can be done programmatically, but that is not the question here.

For the answer, any system table pointers will also help, or Vertica maintained variable.

For e.g. in Teradata database system I know of variable activity_count which keeps count of records processed in last executed query.

Do you mean "records returned" or "records processed"? For example if a SELECT... GROUP BY... returns 1 row after reading 10 rows from table A and 20 rows from table B... what do you want to get? — mauro, Oct 06 '17 at 07:59
I want to get 1. Let is process N number of rows internally but that is not required. I need final count which was returned by Select, Insert, or Update. — minatverma, Oct 06 '17 at 08:02

score 1 · Accepted Answer · answered Oct 06 '17 at 08:15

1

Check PROCESSED_ROW_COUNT from V_MONITOR.QUERY_PROFILES. For example (rows returned by the last 5 queries):

SELECT 
    LEFT(query, 30), 
    processed_row_count 
FROM 
    v_monitor.query_profiles 
ORDER BY 
    query_start DESC 
LIMIT 5;

Also check the Fine Manual

answered Oct 06 '17 at 08:15

mauro

This is helpful. But this is not what I exactly want as the table is capturing all users in query_profiles . For each statement which has run now I will have to find Statement_ID and Transaction_ID for the running queries. I just wanted to get the activity count of the last ran sql, which I will use in Logging – minatverma Oct 06 '17 at 19:48
@minatverma. Not true. You can use SESSION_ID and LIMIT x sorting on "query_start" to find the last x queries from your session. And - to prevent your next request - check the Fine Manual for CURRECT_SESSION(). Your question shows no research effort... – mauro Oct 07 '17 at 00:49
I accept the answer although I was looking for an implicitly maintained variable(I think Vertica does not have this), without the need for querying system table after every DML. – minatverma Oct 07 '17 at 06:57

1 Answers1