컬럼에 히스토그램을 삭제하고 자동 통계가 생성되지 않도록 하려면?

컬럼에 히스토그램을 삭제하고 자동 통계가 생성되지 않도록 하려면?

 

데이터 분포가 왜곡된 ID라는 VARCHAR2 열이 있는 테이블을 가지고 있다고 할 때 이 테이블에 대해 자동 통계 작업이 시작되면 거의 모든 SQL 문에서 사용되며 데이터 왜곡이 있기 때문에 이 열에 히스토그램이 자동으로 생성됩니다. 그러나 ID 열의 값은 매우 길고 각 ID의 처음 32자는 동일할 데이터라고 가정합니다.(데이터베이스는 Oracle Database 11g임).

Oracle이 VARCHAR2 열에 히스토그램을 생성할 때 Oracle Database 12c에서 열 값의 처음 64자 또는 Oracle Database 11g에서 열 값의 처음 32자만 고려합니다. 즉, 열 값의 처음 32/64바이트가 동일하면 실제로 값이 다르고 다른 히스토그램 버킷에 있는 것으로 가정하더라도 모든 값이 히스토그램의 단일 버킷에 포함됩니다. Optimizer는 이 히스토그램을 사용할 때 잠재적으로 카디널리티를 잘못 추정할 수 있으며, 이는 차선의 계획을 초래할 수 있습니다. 고유 값 및 기타 통계의 수는 정확하지만 히스토그램에만 이 문제가 있습니다. 이러한 이유로 현재 또는 미래에는 이 특정 열에 히스토그램을 사용하지 않는 것이 좋습니다. 그렇다면 기존 히스토그램을 삭제하고 향후 수집되지 않도록 하려면 어떻게 해야 할까요?

1. 먼저 ID 열에 히스토그램을 삭제합니다.

DBMS_STATS.DELETE_COLUMN_STATS 프로시저를 사용하여 col_stat_type 매개변수를 'HISTOGRAM'으로 설정하면 됩니다.

BEGIN
   dbms_stats.delete_column_stats(
         ownname=>'SH', tabname=>'SALES', colname=>'PROD_ID', col_stat_type=>'HISTOGRAM');
END;

 

2. DBMS_STATS.SET_TABLE_PREF 프로시저를 사용하여 PROD_ID 컬럼을 제외한 SALES 테이블의 모든 컬럼에 대해 히스토그램을 수집하도록 합니다. 아래 프로시저를 수행하면 PROD_ID 컬렁에 대한 히스토그램을 생성하지 않습니다. 하나의 버킷만을 생성하므로 통계 정보로써의 의미가 없도록 하는 것입니다.

BEGIN
   dbms_stats.set_table_prefs('SH', 'SALES',
   'METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 1 PROD_ID');
END;
/

 

컬럼에 히스토그램을  자동 통계가 생성하도록 하려면?

 

 

자동 통계 수집 작업이 PORD_ID 컬럼에 히스토그램을 다시 생성도록 하려면 아래와 같이 DBMS_STATS.SET_TABLE_PREF 프로시저를 사용합니다. Oracle 이 자동으로 통계 정보에 대한 버킷을 생성하도록 합니다.

BEGIN
   dbms_stats.set_table_prefs('SH', 'SALES',
   'METHOD_OPT', 
   'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE AUTO PROD_ID');
END;
/