ANALYZE

ANALYZE — generate table and index statistics

Syntax

ANALYZE { INDEX | TABLE } name;
ANALYZE ALL TABLES IN SCHEMA schema [ LIKE pattern ];

Description

Run ANALYZE to update statistics that are used by the optimizer. This can help to improve the performance of a slow query. Statistics are recomputed automatically, in the background, after table DML operations. However, updating statistics may lag depending on system resource loads.
ANALYZE INDEX generates statistics for the individual index named.
ANALYZE TABLE generates statistics for all indexes on the table.
ANALYZE ALL TABLES IN SCHEMA generates statistics for all tables in a given schema.
The optimizer will choose the most efficient index access method, once statistics have been generated.
NuoDB executes ANALYZE as a DDL operation that blocks the transaction engine from executing any other DDL operation until execution of ANALYZE is complete. The transaction engine can execute DML operations concurrently with a DDL operation, such as an ANALYZE operation.

Parameters

Example

This example uses a table that is a copy of the HOCKEY table, which is created by running the SQL QuickStart. (See Running the SQL QuickStart
Index statistics are not updated until after ANALYZE is executed. Statistics can be updated for each index or for all indexes defined on the table.

CREATE TABLE hockey_dup
 (
    Id       BIGINT NOT NULL PRIMARY KEY,
    Number   INTEGER,
    Name     STRING,
    Position STRING,
    Team     STRING
 );
CREATE UNIQUE INDEX player_dup_idx ON hockey_dup (Number, Name, Team);
INSERT INTO hockey_dup SELECT * FROM hockey;
 
SELECT i.indexname, i.keycount, d.distinctcount 
   FROM system.indexstatistics i 
    LEFT OUTER JOIN system.indexprefixstatistics d 
       ON i.objectid = d.objectid 
       AND i.catalogid = d.catalogid 
       AND d.prefixlength = (
            SELECT MAX(n.prefixlength) FROM system.indexprefixstatistics n
                WHERE n.objectid = i.objectid and n.catalogid = i.catalogid )
  WHERE i.tablename = 'hockey_dup';
 
/* Index statistics have not yet been updated by analyze */
        INDEXNAME        KEYCOUNT  DISTINCTCOUNT 
 ----------------------- --------- --------------
 HOCKEY_DUP..PRIMARY_KEY  <null>       <null>    
 PLAYER_DUP_IDX           <null>       <null>         
 
/* run analyze for just one index */ 
ANALYZE INDEX "HOCKEY_DUP..PRIMARY_KEY";
SELECT i.indexname, i.keycount, d.distinctcount
   FROM system.indexstatistics i
    LEFT OUTER JOIN system.indexprefixstatistics d
       ON i.objectid = d.objectid
       AND i.catalogid = d.catalogid
       AND d.prefixlength = (
            SELECT MAX(n.prefixlength) FROM system.indexprefixstatistics n
                WHERE n.objectid = i.objectid and n.catalogid = i.catalogid )
  WHERE i.tablename = 'hockey_dup';
 
        INDEXNAME        KEYCOUNT  DISTINCTCOUNT 
 ----------------------- --------- --------------
 HOCKEY_DUP..PRIMARY_KEY      24           24    
 PLAYER_DUP_IDX           <null>       <null>     
 
/* run analyze for all indexes defined on the table */
ANALYZE TABLE hockey_dup;
SELECT i.indexname, i.keycount, d.distinctcount
   FROM system.indexstatistics i
    LEFT OUTER JOIN system.indexprefixstatistics d
       ON i.objectid = d.objectid
       AND i.catalogid = d.catalogid
       AND d.prefixlength = (
            SELECT MAX(n.prefixlength) FROM system.indexprefixstatistics n
                WHERE n.objectid = i.objectid and n.catalogid = i.catalogid )
  WHERE i.tablename = 'hockey_dup';
 
        INDEXNAME        KEYCOUNT  DISTINCTCOUNT 
 ----------------------- --------- --------------
 HOCKEY_DUP..PRIMARY_KEY    24           24      
 PLAYER_DUP_IDX             24           24