列长度多于32个字符 NUM_DISTINCT值错误

字符长度多于32位，造成NUM_DISTINCT 值错误。

这里给出的建议是 把 直接图删掉， 这样，可以暂时的解决 num_distinct 个数的问题，使个数是正确的。

但是如果这个列，再次被当做where条件使用，并且数据变化达到重新收集的标准时，

就会重新收集统计信息而

SQL> select dbms_stats.get_param('METHOD_OPT') from dual;

DBMS_STATS.GET_PARAM('METHOD_OPT')

--------------------------------------------------------------------------------

FOR ALL COLUMNS SIZE AUTO

这个默认的是值 是 auto，

会- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

根据数据的分布和 列是否使用过 重新收集，如果 重新收集了，那么原来删除的直方图又被重新收集回来了。

还是没有根本的解决问题。  通过锁定表的统计信息可以。

具体过程：

SQL> show user;       

USER is "SONG"

SQL> create table mytest (col1 number, col2 varchar2(255));

Table created.

SQL> begin

  2  for i in 1..1000 

  3  loop

  4  insert into mytest values (i, 'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC' || to_char(i) );

  5  end loop;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> create unique index mytest_uix on mytest(col1, col2);

Index created.

SQL> exec dbms_stats.gather_table_stats('SONG','MYTEST', method_opt=> 'for all indexed columns size 10');

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct from user_tab_col_statistics where table_name = 'MYTEST';

COLUMN_NAME                                                  NUM_DISTINCT

------------------------------------------------------------ ------------

COL1                                                                 1000

COL2                                                                    1   --只有一个不同值,意思就是值全是一样的

SQL> set line 200

SQL> col column_name format a30

SQL> select column_name, endpoint_number, endpoint_value from user_histograms where table_name='MYTEST';

COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE

------------------------------ --------------- --------------

COL1                                         0              1

COL1                                         1            101

COL1                                         2            201

COL1                                         3            301

COL1                                         4            401

COL1                                         5            501

COL1                                         6            601

COL1                                         7            701

COL1                                         8            801

COL1                                         9            901

COL1                                        10           1000

COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE

------------------------------ --------------- --------------

COL2                                      1000     3.4925E+35   

12 rows selected.

SQL> exec dbms_stats.gather_table_stats('SONG','MYTEST', method_opt=> 'FOR ALL COLUMNS SIZE 1');      --删除直方图

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct from user_tab_col_statistics where table_name = 'MYTEST';

COLUMN_NAME                    NUM_DISTINCT

------------------------------ ------------

COL1                                   1000

COL2                                   1000    --值是正确的，MOS给出的是 删掉直方图

SQL> select column_name, endpoint_number, endpoint_value from user_histograms where table_name='MYTEST';

COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE

------------------------------ --------------- --------------

COL1                                         0              1

COL2                                         0     3.4925E+35

COL1                                         1           1000

COL2                                         1     3.4925E+35

SQL> select * from mytest where col2='CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC1';  --做为谓语条件 查询一次

      COL1

----------

COL2

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

         1

CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC1

SQL> begin

  2  for i in 1..1000 

  3  loop

  4  insert into mytest values (i, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' || to_char(i) );

  5  end loop;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> commit;    --改变量增加，使自动收集统计信息时，会自动收集

Commit complete.

SQL> exec DBMS_STATS.GATHER_DATABASE_STATS ();

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct from user_tab_col_statistics where table_name = 'MYTEST';

COLUMN_NAME                    NUM_DISTINCT

------------------------------ ------------

COL1                                   1000

COL2                                      2  --只有两个不同值，这是错误的

SQL> select column_name, endpoint_number, endpoint_value from user_histograms where table_name='MYTEST';

COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE

------------------------------ --------------- --------------

COL2                                      2000     3.4925E+35

COL2                                      1000     3.3882E+35

COL1                                         0              1

COL1                                         1           1000

SQL> EXEC DBMS_STATS.SET_COLUMN_STATS ('SONG','MYTEST','COL2',distcnt=>2000,force =>TRUE);    -- 设一个值还是不行

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct from user_tab_col_statistics where table_name = 'MYTEST';

COLUMN_NAME                    NUM_DISTINCT

------------------------------ ------------

COL1                                   1000

COL2                                   2000

SQL> select * from mytest where col2='CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC1';

      COL1

----------

COL2

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

         1

CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC1

SQL> begin

  2  for i in 1..1000 

  3  loop

  4  insert into mytest values (i, 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB' || to_char(i) );

  5  end loop;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> exec DBMS_STATS.GATHER_DATABASE_STATS ();    --再次插入之后，又收集

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct from user_tab_col_statistics where table_name = 'MYTEST';

COLUMN_NAME                    NUM_DISTINCT

------------------------------ ------------

COL1                                   1000

COL2                                      3   --统计信息不是真正的

SQL> 

SQL> EXEC DBMS_STATS.SET_COLUMN_STATS ('SONG','MYTEST','COL2',distcnt=>2000,force =>TRUE);

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.LOCK_TABLE_STATS ('SONG','MYTEST');       --锁定表的统计信息

PL/SQL procedure successfully completed.

SQL> delete  from mytest where col2 like 'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC%';

1000 rows deleted.

SQL> commit;

Commit complete.

SQL> exec DBMS_STATS.GATHER_DATABASE_STATS ();select *   from mytest where col2 like 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB1' 

SQL> select *   from mytest where col2 like 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB1';

      COL1

----------

COL2

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

         1

BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB1

SQL> exec DBMS_STATS.GATHER_DATABASE_STATS ();

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct from user_tab_col_statistics where table_name = 'MYTEST';

COLUMN_NAME                    NUM_DISTINCT

------------------------------ ------------

COL1                                   1000

COL2                                   2000   --锁定表的成功

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

转载于:http://blog.itpub.net/25099483/viewspace-775456/