Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Does CREATE INDEX Gather Global Statistics?

17 December 2009 4 Comments Written by Christian Antognini

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.

10gR1, 10gR2, 11gR1, 11gR2, 9iR2, Indexes, Object Statistics, Partitioning
Instance Caging
Chinese Edition of TOP Available!

4 Comments

1 Ping/Trackback

  1. Albertian Albertian
    18 December 2009    

    Thanks for this information!

    Reply
  2. Blogroll Report 11/12/2009-18/12/2009 « Coskan’s Approach to Oracle Blogroll Report 11/12/2009-18/12/2009 « Coskan’s Approach to Oracle
    2 January 2010    

    […] 16-Auto stats gathering of index creation operation is not working for global statistics on partitioned indexes Christian Antognini-Does create index gather global statistics? […]

  3. Mohamed Houri Mohamed Houri
    13 March 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

    Reply
  4. Christian Antognini Christian Antognini
    21 March 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

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

Leave a Reply Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.