You are here: Reference Information > SQL Reference Information > SQL Statements > ANALYZE

ANALYZE

ANALYZE — generate table and index statistics

Syntax

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

Description

Index statistics are used by the optimizer to improve query performance allowing the optimizer to choose the most efficient index access method.

In NuoDB version 3.0, index statistics for permanent tables are automatically updated periodically without any user intervention. As a result, users generally will not need to run the ANALYZE command to update the index statistics for permanent tables. However, indexes statistics on temporary tables are not automatically updated.

The ANALYZE command can be used to manually update index statistics on permanent tables or temporary tables.

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.

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.

In NuoDB version 3.0, the ANALYZE table and ANALYZE index commands behave in the same way and will update statistics for all version 2.x indexes associated with the table.

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 unless using Version 2  stats */
        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             

Note: Using Version 2 stats, both indexes in the above example will have updated statistics because in Version 2 stats, the analyze index behaves like an analyze table.