ITPub博客

首页 > Linux操作系统 > Linux操作系统 > analyze table不能统计分区表统计信息的问题

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

原创 Linux操作系统 作者:tian1982tian 时间:2012-05-23 11:19:14 0 删除 编辑

如果在分区表上用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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-12-29

  • 博文量
    70
  • 访问量
    163923