Analyze Statement

analyze-statement:
  ANALYZE schema_name [ . table_name [ ( column_name [, column_name ] [, ... ] ) ] ]

Analyze and update statistics on column(s) of table(s) in a specific schema. When only the schema name is specified all tables in that schema are analyzed. When a schema and table name are specified all columns of that table are analyzed. When a schema, table and a column name(s) is specified only those columns of that table are analyzed.

The statistics information is used by the SQL query optimizer. (Re)Run the ANALYZE command after many table manipulations (insert/update/delete). For large tables or big schemas this command may take some time. The statistics information for user tables/columns can be queried from system view: sys.statistics. The statistics information for all tables/columns (including system tables) can be queried from system functions: sys.statistics() or sys.statistics('myschema') or sys.statistics('myschema','mytable') or sys.statistics('myschema','mytable','mycolumn').

Examples

CREATE TABLE sys.store (id INT, product VARCHAR(32), price DECIMAL(8,2));
INSERT INTO sys.store VALUES (1, 'apple', 1.2), (2, 'orange', 2.0), (3, 'banana', 1.5);

-- Analyze every column from table 'sys.store' and update the column statistics
ANALYZE sys.store;

-- Retrieve statistics for all user tables in the database.
SELECT * FROM sys.statistics;

-- Retrieve statistics for table 'store' in schema 'sys'
SELECT * FROM sys.statistics
 WHERE "schema" = 'sys'
   AND "table" = 'store';

-- Or more efficiently, the table function version can be used:
SELECT * FROM sys.statistics('sys', 'store');
/*
+-----------+--------+-------+---------+---------+-------+-------+--------+-------+--------+--------+--------+-----------+
| column_id | schema | table | column  | type    | width | count | unique | nils  | minval | maxval | sorted | revsorted |
+===========+========+=======+=========+=========+=======+=======+========+=======+========+========+========+===========+
|      8412 | sys    | store | id      | int     |     4 |     3 | true   | false | 1      | 3      | true   | false     |
|      8413 | sys    | store | product | varchar |     1 |     3 | true   | false | apple  | orange | false  | false     |
|      8414 | sys    | store | price   | decimal |     4 |     3 | true   | false | 120    | 200    | false  | false     |
+-----------+--------+-------+---------+---------+-------+-------+--------+-------+--------+--------+--------+-----------+
*/

-- Meanwhile more inserts are performed...
INSERT INTO sys.store VALUES (4, 'pear', 1.2);

-- Analyze columns 'product' and 'price' from 'sys.store'
ANALYZE sys.store ("product", "price");

-- Retrieve statistics for column 'price'
SELECT * FROM sys.statistics('sys', 'store', 'price');
/*
+-----------+--------+-------+--------+---------+-------+-------+--------+-------+--------+--------+--------+-----------+
| column_id | schema | table | column | type    | width | count | unique | nils  | minval | maxval | sorted | revsorted |
+===========+========+=======+========+=========+=======+=======+========+=======+========+========+========+===========+
|      8414 | sys    | store | price  | decimal |     4 |     4 | false  | false | 120    | 200    | false  | false     |
+-----------+--------+-------+--------+---------+-------+-------+--------+-------+--------+--------+--------+-----------+
*/