Analyze Statistics - HAS_STATISTICS returning false

Sam37

We are running analyze statistics on all objects every weekend using select analyze_statistics(''); but then when they look up at the Projections table, HAS_STATISTICS column, it is returning false.

 

When I look up the documentation for HAS_STATISTICS column on Projections table, it says

 

"This column returns true only when all non-epoch columns for a table have full statistics. Otherwise column returns false"

 

Can you help me understand what does non-epoch column mean and would there be any reason by statistics may not be collected for all the tables?

 

Note: None of these tables are empty

eli_revach

Hi ,
Looks like something was failed with the execution of your statistics collections .
I advice you to take a look into your error_messages table see eg: query
select * from error_messages orderby event_timestamp desc;
Or vertica.log file .

Regarding this statement "This column returns true only when all non-epoch columns for a table have full statistics. Otherwise column returns false"

When you create a table (projection ) in vertica , vertica automatically add internal column named by "epoch" to each projection  , the epoch column values are being set internally by vertica at a commit time , it basically include the system commit number ( in Oracle thay call it SCN ) and it mainly being used by vertica for managing the on going activities of the database .

 

I hope you find it useful

 

Thanks 

Sam37

Thanks for the reply. I did not see anything in the error_messages which is relevent to this. What I observed is that for the tables, that has_statistics is showing up as false, when I look up under projections_columns table, stats that seems to be applied are ROW_COUNT. But FULL stats always has the precedence over ROW_COUNT.

 

However, they are not truncating the table as it got history data. I was more interested to see if there is any way to find out Analyze_statistics('') function ran successfully and if yes, the list of projections for which stats got updated. All I could see is the statistics updated timestamp in the projection_columns table.

 

 

 

 

Sashidhar_Arcot

Select get_projections('<schema_name.table_name>'); to see the list of projections for that table and if the stats show "yes/no".

 

Also, when you run Select Analyze_Statistics('<schema_name.table_name>');  It will return 0 after successful completion. 

 

If your projection_columns table still shows 'ROWCOUNT' for some columns, you may benefit from running 

Select Analyze_Histogram('<schema_name.table_name.column_name>',100); just for those columns. It will show FULL after that.

 

Live aggregate projections will not have statistics and they will continue to show 'NONE'. It could be misleading but analyze_statistics is not applicable for those projections. 

 

Thanks,

Sashi

 

 

PDS

I happen to be testing something with statistics and found this thread while searchign for an answer.  I ran many variaions - but to summarize the relevant parts:  What i have found is that if you have 0 rows and take stats they do not show up in projection_columns. (NULL timestamp)  If you have rows, stat, then remove rows and stat again, they convert from "FULL" to 'ROWCOUNT'.  

For my test cases, i checked the associatd projections and they now show with has_statistics = f.     So, while not sure if this would fit your issue it does seem to match:  if you had rows in a table and stat'ed them, then removed rows and re-stated them, your stats will change to ROWCOUNT and your projection has_statistics will change to 'f'.    hth

-paul