Does CREATE INDEX Gather Global Statistics?

You can add the COMPUTE STATISTICS clause to the CREATE INDEX statement. It instructs the SQL statement to gather and store index statistics in the data dictionary, while creating the index. This is useful because the overhead associated with the gathering of statistics while executing this SQL statement is negligible. In Oracle9i, the gathering of statistics is performed only when this clause is specified. As of Oracle Database 10g, whenever statistics are not locked, their gathering is done by default, which means the COMPUTE STATISTICS clause is deprecated and available for backward compatibility only.

Unfortunately, CREATE INDEX does not gather global statistics. As a result, whenever you are creating partitioned indexes, the global statistics might be inaccurate. Let me show you an example:

  • Create partitioned table, insert data (notice that the number of distinct values is equal to the number of rows) and create a local index
SQL> CREATE TABLE t (n1 number, n2 number)
  2  PARTITION BY RANGE (n1) (
  3    PARTITION p1 VALUES LESS THAN (11),
  4    PARTITION p2 VALUES LESS THAN (21)
  5  );

Table created.

SQL> INSERT INTO t
  2  SELECT rownum, rownum
  3  FROM dual
  4  CONNECT BY level <= 20;

20 rows created.

SQL> CREATE INDEX i ON t (n2) LOCAL;

Index created.
  • The CREATE INDEX statement gathered the statistics for the index; let’s check them…
SQL> SELECT partition_name, global_stats, distinct_keys
  2  FROM user_ind_statistics
  3  WHERE index_name = 'I';

PARTITION_NAME GLOBAL_STATS DISTINCT_KEYS
-------------- ------------ -------------
               NO                      10
P1             NO                      10
P2             NO                      10

As you can see 1) the number of distinct keys at the global level is wrong; it should be 20! 2) the GLOBAL_STATS column at the index level is set to NO. As a result, when you create a partitioned index, you should manually gather the global index statistics straight after. In other words, you should do the following:

  • Manually gather global level index statistics
SQL> execute dbms_stats.gather_index_stats(ownname=>user, indname=>'i', granularity=>'global')

PL/SQL procedure successfully completed.
  • Check whether the index statistics are accurate
SQL> SELECT partition_name, global_stats, distinct_keys
  2  FROM user_ind_statistics
  3  WHERE index_name = 'I';

PARTITION_NAME GLOBAL_STATS DISTINCT_KEYS
-------------- ------------ -------------
               YES                     20
P1             NO                      10
P2             NO                      10

There are situations, however, where it is not necessary to manually gather the global index statistics. For example, when the index is prefixed. But, as a general rule, I would not rely on the automatically gathered statistics for partitioned indexes.

3 Comments

  1. Albertian's Gravatar Albertian
    December 18, 2009    

    Thanks for this information!

  2. March 13, 2012    

    Hi Christian

    I know this is an old post but I was wondering if we really need to manually gather global level index statistics. Would collecting table statistics not enough?

    sql> CREATE TABLE t5 (n1 number, n2 number)
    2 PARTITION BY RANGE (n1) (
    3 PARTITION p1 VALUES LESS THAN (11),
    4 PARTITION p2 VALUES LESS THAN (21)
    5 );

    Table created.

    sql>> INSERT INTO t5
    2 SELECT rownum, rownum
    3 FROM dual
    4 connect by level commit;

    Commit complete.

    sql> CREATE INDEX i ON t5 (n2) LOCAL;

    Index created.

    sql> SELECT partition_name, global_stats, distinct_keys
    2 FROM user_ind_statistics
    3 WHERE index_name = ‘I’;

    PARTITION_NAME GLO DISTINCT_KEYS
    —————————— — ————-
    NO 10
    P1 NO 10
    P2 NO 10

    sql> exec dbms_stats.gather_table_stats(user, ‘T5′);

    PL/SQL procedure successfully completed.

    sql>> SELECT partition_name, global_stats, distinct_keys
    2 FROM user_ind_statistics
    3 WHERE index_name = ‘I’;

    PARTITION_NAME GLO DISTINCT_KEYS
    —————————— — ————-
    YES 20
    P1 YES 10
    P2 YES 10

    Best regards

    Mohamed Houri

  3. March 21, 2012    

    Hi Mohamed

    > Would collecting table statistics not enough?

    If the default value of the CASCADE parameter is TRUE, executing GATHER_TABLE_STATS gathers the statistics for both the table and the index. Hence, it might be enough. Be careful, however, that also partition-level statistics are re-gathered. So, it depends what do you want to achieve and how much time you have to do it…

    HTH
    Chris

  1. Blogroll Report 11/12/2009-18/12/2009 « Coskan’s Approach to Oracle on January 2, 2010 at 18:50

Leave a Reply

Your email address will not be published. Required fields are marked *