-- Oracle 11g Optimizer and Incremental Statistics --

As I was doing some research about the 11g optimizer I found out that in 11g you can now gather Incremental statistics for partitioned tables. The idea behind this feature is to be able to capture global statistics on large partitioned tables at a lower resource cost by minimizing the time required to collect statistics.

This makes complete sense, think about a large range partitioned table, for example, SALES_DATA. Let's assume that your SALES_DATA table is partitioned by month, it is very unlikely that old sales data will change, especially data that correspond to previous years. As you know Oracle keeps statistics at the partition level and overall statistics for the table,  Oracle monitors DML operations at the table and subpartition levels, normally statistics are gathered only for those partitions that changed (> than 10%), however, global statistics are gathered by scanning the entire table and that makes it a very expensive operation.

In order to do that, you need to first set the table preferences to incremental.

SQL> exec dbms_stats.set_table_prefs('TPM','SALES_DATA','INCREMENTAL','TRUE');

Next we gather the global statistics

SQL> exec dbms_stats.gather_table_stats('TPM','SALES_DATA', GRANULARITY=> 'GLOBAL');

If you query the last analyzed column from users_table you will see that the timestamp is updated, however when you query the same column on user_tab_partitions you will see that not all the partitions got updated, furthermore you should see a different on the time it took to gather the statistics.