analyze table不能统计分区表统计信息的问题

如果在分区表上用dbms_stats统计后，再使用”analyze table”来统计，就会出现表信息不被更新的问题

举例如下：

SQL> drop table test;

Table dropped.

SQL> create table test(x int) partition by hash(x) partitions 2
  2  ;

Table created.

SQL> create index test_idx on test(x) local;

Index created.
SQL> begin                               
  2   for i in 1..10000 loop
  3   insert into test values(i);
  4   end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname=>’XFAN’,tabname=>’TEST’,cascade=>true);

PL/SQL procedure successfully completed.

SQL>
SQL> select index_name,num_rows,last_analyzed from user_indexes where index_name=’TEST_IDX’
  2  union
  3  select table_name,num_rows,last_analyzed from user_tables where table_name=’TEST’;

INDEX_NAME                       NUM_ROWS LAST_ANALYZED
—————————— ———- —————–
TEST                                10000 20070302 17:31:39
TEST_IDX                            10000 20070302 17:31:39
SQL> begin
  2  for i in 10001..20000 loop
  3   insert into test values(i);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> analyze table test compute statistics;

Table analyzed.
统计信息没有被更新
SQL> select index_name,num_rows,last_analyzed from user_indexes where index_name=’TEST_IDX’
  2  union
  3  select table_name,num_rows,last_analyzed from user_tables where table_name=’TEST’;

INDEX_NAME                       NUM_ROWS LAST_ANALYZED
—————————— ———- —————–
TEST                                10000 20070302 17:31:39
TEST_IDX                            10000 20070302 17:31:39

删除后再分析就更新了，或者直接用dbms_stats分析

SQL> exec dbms_stats.delete_table_stats(ownname=>’XFAN’,tabname=>’TEST’);

PL/SQL procedure successfully completed.

SQL> analyze table test compute statistics;

Table analyzed.

SQL>  select index_name,num_rows,last_analyzed from user_indexes where index_name=’TEST_IDX’
  2   union
  3   select table_name,num_rows,last_analyzed from user_tables where table_name=’TEST’;

INDEX_NAME                       NUM_ROWS LAST_ANALYZED
—————————— ———- —————–
TEST                                20000 20070302 17:33:00
TEST_IDX                            20000 20070302 17:33:00

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16400082/viewspace-730664/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16400082/viewspace-730664/