列長度多於32個字元 NUM_DISTINCT值錯誤

psufnxk2000發表於2013-10-31
字元長度多於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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章