How to analyze statistics on all tables in a schema?

Moderator: NorbertKrupa

Post Reply
Brett
Beginner
Beginner
Posts: 49
Joined: Fri Oct 11, 2013 1:19 am

How to analyze statistics on all tables in a schema?

Post by Brett » Thu Dec 01, 2016 8:01 pm

I am trying to analyze the stats on all tables in a schema. Is this possible using the analyze statistics function? I tried the following:

Code: Select all

dbadmin=> SELECT analyze_statistics('hippies');
ERROR 2065:  analyze_statistics: Invalid table/projection/column hippies

dbadmin=> SELECT analyze_statistics('hippies.*');
ERROR 2065:  analyze_statistics: Invalid table/projection/column hippies.*

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1817
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How to analyze statistics on all tables in a schema?

Post by JimKnicely » Thu Dec 01, 2016 8:16 pm

Yeah, you can't do that (yet).

There are work-arounds, though. Here's one... Say I have the following tables in the schema "test":

Code: Select all

dbadmin=> \dt test.*
              List of tables
 Schema | Name | Kind  |  Owner  | Comment
--------+------+-------+---------+---------
 test   | t1   | table | dbadmin |
 test   | t2   | table | dbadmin |
 test   | t3   | table | dbadmin |
(3 rows)
I can analyze them all like this:

Code: Select all

dbadmin=> \! vsql -atc "select 'select analyze_statistics(''' || table_schema || '.' || table_name || ''');' from tables where table_schema = 'test';" | vsql
               ?column?
---------------------------------------
 select analyze_statistics('test.t1');
 select analyze_statistics('test.t2');
 select analyze_statistics('test.t3');
(3 rows)

 analyze_statistics
--------------------
                  0
(1 row)

 analyze_statistics
--------------------
                  0
(1 row)

 analyze_statistics
--------------------
                  0
(1 row)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica SQL Functions”