嚴重 screwed column，強制指定建立 254 階直方圖，讓oracle 選擇正確excution plan

REPORT_PROCESS_MAIL 嚴重 screwed 

BEGIN

  DBMS_STATS.GATHER_TABLE_STATS(

    ownname          => 'REVIEWREPORT',

    tabname          => 'REPORT_PROCESS_MAIL',

    method_opt       => 'FOR COLUMNS SIZE 254 SEND_MAIL_FLAG',

    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
-- estimate_percent => 100,

    cascade          => TRUE

  );

END;

/


收集後驗證 Histogram 是否成功建立

SELECT column_name, num_distinct, num_buckets, histogram 

FROM all_tab_col_statistics 

WHERE owner = 'REVIEWREPORT' 

  AND table_name = 'REPORT_PROCESS_MAIL' 

  AND column_name = 'SEND_MAIL_FLAG';

如果 HISTOGRAM 顯示 FREQUENCY 或 HYBRID（且 NUM_BUCKETS $> 1$），才代表直方圖建立成功