Incremental statistics does work with locked statistics

when testing this scheme, I found that after locking the statistics on several of the older partitions and applying DML activity, dbms_stats.gather_table_stats ignores the fact that the incremental preference for the table is set to TRUE, and gathers global stats via full table scan.

rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t
  2  partition by range(dt)
  3  (  partition p1 values less than ( to_date('01-FEB-2012','dd-mon-yyyy') ) ,
  4     partition p2 values less than ( to_date('01-MAR-2012','dd-mon-yyyy') ) ,
  5     partition p3 values less than ( to_date('01-APR-2012','dd-mon-yyyy') ) ,
  6     partition p4 values less than ( to_date('01-MAY-2012','dd-mon-yyyy') ) ,
  7     partition p5 values less than ( to_date('01-JUN-2012','dd-mon-yyyy') ) ,
  8     partition p6 values less than ( to_date('01-JUL-2012','dd-mon-yyyy') ) ,
  9     partition p7 values less than ( to_date('01-AUG-2012','dd-mon-yyyy') ) ,
 10     partition p8 values less than ( to_date('01-SEP-2012','dd-mon-yyyy') ) ,
 11     partition p9 values less than ( to_date('01-OCT-2012','dd-mon-yyyy') ) ,
 12     partition p10 values less than ( to_date('01-NOV-2012','dd-mon-yyyy') ) ,
 13     partition p11 values less than ( to_date('01-DEC-2012','dd-mon-yyyy') ) ,
 14     partition p12 values less than ( to_date('01-JAN-2013','dd-mon-yyyy') ) ,
 15     partition pmax values less than ( maxvalue )
 16  ) as
 17  select a.*, add_months( to_date('05-JAN-2012','dd-mon-yyyy'), mod(rownum,12) ) dt
 18  from all_objects a;

Table created.

Elapsed: 00:00:02.32
rajesh@ORA11GR2> begin
  2     dbms_stats.set_table_prefs
  3     (ownname=>user,
  4      tabname=>'T',
  5      pname=>'INCREMENTAL',
  6      pvalue=>'TRUE');
  7
  8     dbms_stats.set_table_prefs
  9     (ownname=>user,
 10      tabname=>'T',
 11      pname=>'GRANULARITY',
 12      pvalue=>'AUTO');
 13
 14     dbms_stats.set_table_prefs
 15     (ownname=>user,
 16      tabname=>'T',
 17      pname=>'PUBLISH',
 18      pvalue=>'TRUE');
 19  end;
 20  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.75
rajesh@ORA11GR2>
rajesh@ORA11GR2> column incremental format a20;
rajesh@ORA11GR2> column granularity format a20;
rajesh@ORA11GR2> column publish format a20;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select  dbms_stats.get_prefs('INCREMENTAL',user,'T') as incremental,
  2             dbms_stats.get_prefs('GRANULARITY',user,'T') as granularity,
  3             dbms_stats.get_prefs('PUBLISH',user,'T') as publish
  4  from dual;

INCREMENTAL          GRANULARITY          PUBLISH
-------------------- -------------------- --------------------
TRUE                 AUTO                 TRUE

Elapsed: 00:00:00.82
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.37
rajesh@ORA11GR2>
rajesh@ORA11GR2> select partition_name, stattype_locked, last_analyzed, num_rows
  2  from user_tab_statistics
  3  where table_name ='T'
  4  order by last_analyzed desc;

PARTITION_NAME                 STATT LAST_ANALYZED          NUM_ROWS
------------------------------ ----- -------------------- ----------
                                     09-apr-2012 12:55:31      82804
P3                                   09-apr-2012 12:55:30       6901
P9                                   09-apr-2012 12:55:30       6900
P6                                   09-apr-2012 12:55:30       6900
P12                                  09-apr-2012 12:55:29       6900
P2                                   09-apr-2012 12:55:29       6901
P5                                   09-apr-2012 12:55:29       6901
P11                                  09-apr-2012 12:55:29       6900
P8                                   09-apr-2012 12:55:29       6900
P4                                   09-apr-2012 12:55:29       6901
P1                                   09-apr-2012 12:55:29       6900
P7                                   09-apr-2012 12:55:28       6900
P10                                  09-apr-2012 12:55:28       6900
PMAX                                 09-apr-2012 12:55:28          0

14 rows selected.

Elapsed: 00:00:00.82
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2    dbms_stats.lock_partition_stats
  3    ( ownname=>user,
  4      tabname=>'T',
  5      partname=>'P3');
  6
  7    dbms_stats.lock_partition_stats
  8    ( ownname=>user,
  9      tabname=>'T',
 10      partname=>'P4');
 11  end;
 12  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.56
rajesh@ORA11GR2>
rajesh@ORA11GR2> delete from t partition (p3);

6901 rows deleted.

Elapsed: 00:00:00.67
rajesh@ORA11GR2>
rajesh@ORA11GR2> delete from t partition (p7);

6900 rows deleted.

Elapsed: 00:00:00.67
rajesh@ORA11GR2>
rajesh@ORA11GR2> commit;

Commit complete.

Elapsed: 00:00:00.54
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.48
rajesh@ORA11GR2> select partition_name, stattype_locked, last_analyzed, num_rows
  2  from user_tab_statistics
  3  where table_name ='T'
  4  order by last_analyzed desc
  5  /
PARTITION_NAME                 STATT LAST_ANALYZED          NUM_ROWS
------------------------------ ----- -------------------- ----------
                                     09-apr-2012 12:56:19      69003
P9                                   09-apr-2012 12:56:19       6900
PMAX                                 09-apr-2012 12:56:19          0
P8                                   09-apr-2012 12:56:19       6900
P7                                   09-apr-2012 12:56:18          0
P6                                   09-apr-2012 12:56:18       6900
P2                                   09-apr-2012 12:56:17       6901
P5                                   09-apr-2012 12:56:17       6901
P11                                  09-apr-2012 12:56:16       6900
P12                                  09-apr-2012 12:56:16       6900
P1                                   09-apr-2012 12:56:15       6900
P10                                  09-apr-2012 12:56:15       6900
P3                             ALL   09-apr-2012 12:55:30       6901
P4                             ALL   09-apr-2012 12:55:29       6901

14 rows selected.

Elapsed: 00:00:01.79
rajesh@ORA11GR2>

As you can see that oracle has scanned all the partitions by ignoring the fact that the Incremental preference for this table is set to True.