大資料量分割槽表統計資訊的管理
對於大資料量分割槽表，當資料載入後，如果不能及時更新統計資訊，將導致sql產生不正確的執行計劃，引起查詢效能的下降，而對於一個資料載入比較頻繁的分割槽表，往往無法在每次載入資料
後及時收集統計資訊，而且對於資料量比較大的表來說，收集統計資訊本身就是比較耗費資源的操作。這種情況下，可以採用DBMS_STATS.COPY_TABLE_STATS這個儲存過程來將一個分割槽的
統計資訊複製到另外一個分割槽，從而避免由於無法及時更新統計資訊而導致的sql執行計劃出現問題。
SQL> create or replace function display_raw (rawval raw, type varchar2)
2 return varchar2
3 is
4 cn number;
5 cv varchar2(32);
6 cd date;
7 cnv nvarchar2(32);
8 cr rowid;
9 cc char(32);
10 begin
11 if (type = 'NUMBER') then
12 dbms_stats.convert_raw_value(rawval, cn);
13 return to_char(cn);
14 elsif (type = 'VARCHAR2') then
15 dbms_stats.convert_raw_value(rawval, cv);
16 return to_char(cv);
17 elsif (type = 'DATE') then
18 dbms_stats.convert_raw_value(rawval, cd);
19 return to_char(cd);
20 elsif (type = 'NVARCHAR2') then
21 dbms_stats.convert_raw_value(rawval, cnv);
22 return to_char(cnv);
23 elsif (type = 'ROWID') then
24 dbms_stats.convert_raw_value(rawval, cr);
25 return to_char(cnv);
26 elsif (type = 'CHAR') then
27 dbms_stats.convert_raw_value(rawval, cc);
28 return to_char(cc);
29 else
30 return 'UNKNOWN DATATYPE';
31 end if;
32 end;
33 /
Function created.
SQL> set lines 132 pages 100
SQL> col COLUMN_NAME for a20
SQL> col PARTITION_NAME for a10
SQL> col low_val for a30
SQL> col high_val for a30
SQL> col data_type for a20
SQL> alter session set nls_date_format='yyyymmdd hh24:mi:ss';
Session altered.
SQL> SELECT TABLE_NAME,PARTITION_NAME,NUM_ROWS,TO_CHAR(LAST_ANALYZED,'YYYYMMDD HH24:MI:SS') FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SALES';
TABLE_NAME PARTITION_NAME NUM_ROWS TO_CHAR(LAST_ANAL
------------------------------ ------------------------------ ---------- -----------------
SALES P201301 99920 20140207 20:50:59
SALES P201302 96640 20140207 20:50:59
SALES P201303 100680 20140207 20:50:59
SALES P201304 100500 20140207 20:50:59
SALES P201305 99780 20140207 20:50:59
SALES P201306 0 20140207 20:50:59
SALES P201307 0 20140207 20:50:59
SALES P201308 0 20140207 20:50:59
SALES P201309 0 20140207 20:50:59
SALES P201310 0 20140207 20:50:59
SALES P201311 0 20140207 20:50:59
SALES P201312 0 20140207 20:50:59
12 rows selected.
SQL> select
2 a.column_name, a.partition_name,
3 a.num_distinct,
4 display_raw(a.low_value,b.data_type) as low_val,
5 display_raw(a.high_value,b.data_type) as high_val,
6 b.data_type
7 from
8 dba_part_col_statistics a, dba_tab_cols b
9 where
10 a.owner='SYS' and
11 b.owner='SYS' and
12 a.table_name='SALES' and
13 a.table_name=b.table_name and
14 a.column_name=b.column_name and
15 a.column_name = 'TIME_ID' and
16 a.low_value is not null
17 order by 1, 2
18 /
COLUMN_NAME PARTITION_ NUM_DISTINCT LOW_VAL HIGH_VAL DATA_TYPE
-------------------- ---------- ------------ ------------------------------ ------------------------------ --------------------
TIME_ID P201301 1 20130110 00:00:00 20130110 00:00:00 DATE
TIME_ID P201302 1 20130210 00:00:00 20130210 00:00:00 DATE
TIME_ID P201303 1 20130310 00:00:00 20130310 00:00:00 DATE
TIME_ID P201304 1 20130410 00:00:00 20130410 00:00:00 DATE
TIME_ID P201305 1 20130510 00:00:00 20130510 00:00:00 DATE
12 rows selected.
SQL> EXEC DBMS_STATS.COPY_TABLE_STATS (user, 'SALES', 'p201305', 'p201306', FORCE=>TRUE);
PL/SQL procedure successfully completed.
SQL> select
2 a.column_name, a.partition_name,
3 a.num_distinct,
4 display_raw(a.low_value,b.data_type) as low_val,
5 display_raw(a.high_value,b.data_type) as high_val,
6 b.data_type
7 from
8 dba_part_col_statistics a, dba_tab_cols b
9 where
10 a.owner='SYS' and
11 b.owner='SYS' and
12 a.table_name='SALES' and
13 a.table_name=b.table_name and
14 a.column_name=b.column_name and
15 a.column_name = 'TIME_ID' and
16 a.low_value is not null
17 order by 1, 2
18 /
COLUMN_NAME PARTITION_ NUM_DISTINCT LOW_VAL HIGH_VAL DATA_TYPE
-------------------- ---------- ------------ ------------------------------ ------------------------------ --------------------
TIME_ID P201301 1 20130110 00:00:00 20130110 00:00:00 DATE
TIME_ID P201302 1 20130210 00:00:00 20130210 00:00:00 DATE
TIME_ID P201303 1 20130310 00:00:00 20130310 00:00:00 DATE
TIME_ID P201304 1 20130410 00:00:00 20130410 00:00:00 DATE
TIME_ID P201305 1 20130510 00:00:00 20130510 00:00:00 DATE
TIME_ID P201306 1 20130601 00:00:00 20130701 00:00:00 DATE
6 rows selected.
SQL> SELECT TABLE_NAME,PARTITION_NAME,NUM_ROWS,TO_CHAR(LAST_ANALYZED,'YYYYMMDD HH24:MI:SS') FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SALES';
TABLE_NAME PARTITION_ NUM_ROWS TO_CHAR(LAST_ANAL
------------------------------ ---------- ---------- -----------------
SALES P201301 100020 20140207 08:42:09
SALES P201302 98820 20140207 08:42:09
SALES P201303 98960 20140207 08:42:09
SALES P201304 100520 20140207 08:42:09
SALES P201305 100280 20140207 08:42:09
SALES P201306 100280 20140207 08:42:09
SALES P201307 0 20140207 08:42:09
SALES P201308 0 20140207 08:42:09
SALES P201309 0 20140207 08:42:09
SALES P201310 0 20140207 08:42:09
SALES P201311 0 20140207 08:42:09
SALES P201312 0 20140207 08:42:09
12 rows selected.
SQL> EXEC DBMS_STATS.COPY_TABLE_STATS (user, 'SALES', 'p201305', 'p201307', FORCE=>TRUE);
PL/SQL procedure successfully completed.
SQL> select
2 a.column_name, a.partition_name,
3 a.num_distinct,
4 display_raw(a.low_value,b.data_type) as low_val,
5 display_raw(a.high_value,b.data_type) as high_val,
6 b.data_type
7 from
8 dba_part_col_statistics a, dba_tab_cols b
9 where
10 a.owner='SYS' and
11 b.owner='SYS' and
12 a.table_name='SALES' and
13 a.table_name=b.table_name and
14 a.column_name=b.column_name and
15 a.column_name = 'TIME_ID' and
16 a.low_value is not null
17 order by 1, 2
18 /
COLUMN_NAME PARTITION_ NUM_DISTINCT LOW_VAL HIGH_VAL DATA_TYPE
-------------------- ---------- ------------ ------------------------------ ------------------------------ --------------------
TIME_ID P201301 1 20130110 00:00:00 20130110 00:00:00 DATE
TIME_ID P201302 1 20130210 00:00:00 20130210 00:00:00 DATE
TIME_ID P201303 1 20130310 00:00:00 20130310 00:00:00 DATE
TIME_ID P201304 1 20130410 00:00:00 20130410 00:00:00 DATE
TIME_ID P201305 1 20130510 00:00:00 20130510 00:00:00 DATE
TIME_ID P201306 1 20130601 00:00:00 20130701 00:00:00 DATE
TIME_ID P201307 1 20130701 00:00:00 20130801 00:00:00 DATE
7 rows selected.
SQL> SELECT TABLE_NAME,PARTITION_NAME,NUM_ROWS,TO_CHAR(LAST_ANALYZED,'YYYYMMDD HH24:MI:SS') FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SALES';
TABLE_NAME PARTITION_ NUM_ROWS TO_CHAR(LAST_ANAL
------------------------------ ---------- ---------- -----------------
SALES P201301 100020 20140207 08:42:09
SALES P201302 98820 20140207 08:42:09
SALES P201303 98960 20140207 08:42:09
SALES P201304 100520 20140207 08:42:09
SALES P201305 100280 20140207 08:42:09
SALES P201306 100280 20140207 08:42:09
SALES P201307 100280 20140207 08:42:09
SALES P201308 0 20140207 08:42:09
SALES P201309 0 20140207 08:42:09
SALES P201310 0 20140207 08:42:09
SALES P201311 0 20140207 08:42:09
SALES P201312 0 20140207 08:42:09
12 rows selected.
可以看出，對於分割槽鍵所在的列，oracle自動將列的最大值和最小值置為分割槽邊界值，對於其它列的統計資訊則是直接複製，雖然這不能完全反映資料分佈的真實情況，但能最大程度的接近真實的統計資訊。
後及時收集統計資訊，而且對於資料量比較大的表來說，收集統計資訊本身就是比較耗費資源的操作。這種情況下，可以採用DBMS_STATS.COPY_TABLE_STATS這個儲存過程來將一個分割槽的
統計資訊複製到另外一個分割槽，從而避免由於無法及時更新統計資訊而導致的sql執行計劃出現問題。
SQL> create or replace function display_raw (rawval raw, type varchar2)
2 return varchar2
3 is
4 cn number;
5 cv varchar2(32);
6 cd date;
7 cnv nvarchar2(32);
8 cr rowid;
9 cc char(32);
10 begin
11 if (type = 'NUMBER') then
12 dbms_stats.convert_raw_value(rawval, cn);
13 return to_char(cn);
14 elsif (type = 'VARCHAR2') then
15 dbms_stats.convert_raw_value(rawval, cv);
16 return to_char(cv);
17 elsif (type = 'DATE') then
18 dbms_stats.convert_raw_value(rawval, cd);
19 return to_char(cd);
20 elsif (type = 'NVARCHAR2') then
21 dbms_stats.convert_raw_value(rawval, cnv);
22 return to_char(cnv);
23 elsif (type = 'ROWID') then
24 dbms_stats.convert_raw_value(rawval, cr);
25 return to_char(cnv);
26 elsif (type = 'CHAR') then
27 dbms_stats.convert_raw_value(rawval, cc);
28 return to_char(cc);
29 else
30 return 'UNKNOWN DATATYPE';
31 end if;
32 end;
33 /
Function created.
SQL> set lines 132 pages 100
SQL> col COLUMN_NAME for a20
SQL> col PARTITION_NAME for a10
SQL> col low_val for a30
SQL> col high_val for a30
SQL> col data_type for a20
SQL> alter session set nls_date_format='yyyymmdd hh24:mi:ss';
Session altered.
SQL> SELECT TABLE_NAME,PARTITION_NAME,NUM_ROWS,TO_CHAR(LAST_ANALYZED,'YYYYMMDD HH24:MI:SS') FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SALES';
TABLE_NAME PARTITION_NAME NUM_ROWS TO_CHAR(LAST_ANAL
------------------------------ ------------------------------ ---------- -----------------
SALES P201301 99920 20140207 20:50:59
SALES P201302 96640 20140207 20:50:59
SALES P201303 100680 20140207 20:50:59
SALES P201304 100500 20140207 20:50:59
SALES P201305 99780 20140207 20:50:59
SALES P201306 0 20140207 20:50:59
SALES P201307 0 20140207 20:50:59
SALES P201308 0 20140207 20:50:59
SALES P201309 0 20140207 20:50:59
SALES P201310 0 20140207 20:50:59
SALES P201311 0 20140207 20:50:59
SALES P201312 0 20140207 20:50:59
12 rows selected.
SQL> select
2 a.column_name, a.partition_name,
3 a.num_distinct,
4 display_raw(a.low_value,b.data_type) as low_val,
5 display_raw(a.high_value,b.data_type) as high_val,
6 b.data_type
7 from
8 dba_part_col_statistics a, dba_tab_cols b
9 where
10 a.owner='SYS' and
11 b.owner='SYS' and
12 a.table_name='SALES' and
13 a.table_name=b.table_name and
14 a.column_name=b.column_name and
15 a.column_name = 'TIME_ID' and
16 a.low_value is not null
17 order by 1, 2
18 /
COLUMN_NAME PARTITION_ NUM_DISTINCT LOW_VAL HIGH_VAL DATA_TYPE
-------------------- ---------- ------------ ------------------------------ ------------------------------ --------------------
TIME_ID P201301 1 20130110 00:00:00 20130110 00:00:00 DATE
TIME_ID P201302 1 20130210 00:00:00 20130210 00:00:00 DATE
TIME_ID P201303 1 20130310 00:00:00 20130310 00:00:00 DATE
TIME_ID P201304 1 20130410 00:00:00 20130410 00:00:00 DATE
TIME_ID P201305 1 20130510 00:00:00 20130510 00:00:00 DATE
12 rows selected.
SQL> EXEC DBMS_STATS.COPY_TABLE_STATS (user, 'SALES', 'p201305', 'p201306', FORCE=>TRUE);
PL/SQL procedure successfully completed.
SQL> select
2 a.column_name, a.partition_name,
3 a.num_distinct,
4 display_raw(a.low_value,b.data_type) as low_val,
5 display_raw(a.high_value,b.data_type) as high_val,
6 b.data_type
7 from
8 dba_part_col_statistics a, dba_tab_cols b
9 where
10 a.owner='SYS' and
11 b.owner='SYS' and
12 a.table_name='SALES' and
13 a.table_name=b.table_name and
14 a.column_name=b.column_name and
15 a.column_name = 'TIME_ID' and
16 a.low_value is not null
17 order by 1, 2
18 /
COLUMN_NAME PARTITION_ NUM_DISTINCT LOW_VAL HIGH_VAL DATA_TYPE
-------------------- ---------- ------------ ------------------------------ ------------------------------ --------------------
TIME_ID P201301 1 20130110 00:00:00 20130110 00:00:00 DATE
TIME_ID P201302 1 20130210 00:00:00 20130210 00:00:00 DATE
TIME_ID P201303 1 20130310 00:00:00 20130310 00:00:00 DATE
TIME_ID P201304 1 20130410 00:00:00 20130410 00:00:00 DATE
TIME_ID P201305 1 20130510 00:00:00 20130510 00:00:00 DATE
TIME_ID P201306 1 20130601 00:00:00 20130701 00:00:00 DATE
6 rows selected.
SQL> SELECT TABLE_NAME,PARTITION_NAME,NUM_ROWS,TO_CHAR(LAST_ANALYZED,'YYYYMMDD HH24:MI:SS') FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SALES';
TABLE_NAME PARTITION_ NUM_ROWS TO_CHAR(LAST_ANAL
------------------------------ ---------- ---------- -----------------
SALES P201301 100020 20140207 08:42:09
SALES P201302 98820 20140207 08:42:09
SALES P201303 98960 20140207 08:42:09
SALES P201304 100520 20140207 08:42:09
SALES P201305 100280 20140207 08:42:09
SALES P201306 100280 20140207 08:42:09
SALES P201307 0 20140207 08:42:09
SALES P201308 0 20140207 08:42:09
SALES P201309 0 20140207 08:42:09
SALES P201310 0 20140207 08:42:09
SALES P201311 0 20140207 08:42:09
SALES P201312 0 20140207 08:42:09
12 rows selected.
SQL> EXEC DBMS_STATS.COPY_TABLE_STATS (user, 'SALES', 'p201305', 'p201307', FORCE=>TRUE);
PL/SQL procedure successfully completed.
SQL> select
2 a.column_name, a.partition_name,
3 a.num_distinct,
4 display_raw(a.low_value,b.data_type) as low_val,
5 display_raw(a.high_value,b.data_type) as high_val,
6 b.data_type
7 from
8 dba_part_col_statistics a, dba_tab_cols b
9 where
10 a.owner='SYS' and
11 b.owner='SYS' and
12 a.table_name='SALES' and
13 a.table_name=b.table_name and
14 a.column_name=b.column_name and
15 a.column_name = 'TIME_ID' and
16 a.low_value is not null
17 order by 1, 2
18 /
COLUMN_NAME PARTITION_ NUM_DISTINCT LOW_VAL HIGH_VAL DATA_TYPE
-------------------- ---------- ------------ ------------------------------ ------------------------------ --------------------
TIME_ID P201301 1 20130110 00:00:00 20130110 00:00:00 DATE
TIME_ID P201302 1 20130210 00:00:00 20130210 00:00:00 DATE
TIME_ID P201303 1 20130310 00:00:00 20130310 00:00:00 DATE
TIME_ID P201304 1 20130410 00:00:00 20130410 00:00:00 DATE
TIME_ID P201305 1 20130510 00:00:00 20130510 00:00:00 DATE
TIME_ID P201306 1 20130601 00:00:00 20130701 00:00:00 DATE
TIME_ID P201307 1 20130701 00:00:00 20130801 00:00:00 DATE
7 rows selected.
SQL> SELECT TABLE_NAME,PARTITION_NAME,NUM_ROWS,TO_CHAR(LAST_ANALYZED,'YYYYMMDD HH24:MI:SS') FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SALES';
TABLE_NAME PARTITION_ NUM_ROWS TO_CHAR(LAST_ANAL
------------------------------ ---------- ---------- -----------------
SALES P201301 100020 20140207 08:42:09
SALES P201302 98820 20140207 08:42:09
SALES P201303 98960 20140207 08:42:09
SALES P201304 100520 20140207 08:42:09
SALES P201305 100280 20140207 08:42:09
SALES P201306 100280 20140207 08:42:09
SALES P201307 100280 20140207 08:42:09
SALES P201308 0 20140207 08:42:09
SALES P201309 0 20140207 08:42:09
SALES P201310 0 20140207 08:42:09
SALES P201311 0 20140207 08:42:09
SALES P201312 0 20140207 08:42:09
12 rows selected.
可以看出，對於分割槽鍵所在的列，oracle自動將列的最大值和最小值置為分割槽邊界值，對於其它列的統計資訊則是直接複製，雖然這不能完全反映資料分佈的真實情況，但能最大程度的接近真實的統計資訊。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10972173/viewspace-1078328/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用dbms_redefinition包線上把大資料量非分割槽錶轉換為分割槽表大資料
- 資料泵匯入分割槽表統計資訊報錯（七）
- 資料泵匯入分割槽表統計資訊報錯（二）
- 資料泵匯入分割槽表統計資訊報錯（四）
- 資料泵匯入分割槽表統計資訊報錯（三）
- 資料泵匯入分割槽表統計資訊報錯（六）
- 資料泵匯入分割槽表統計資訊報錯（五）
- Oracle分割槽表的管理Oracle
- 【學習筆記】分割槽表和分割槽索引——分割槽表的其他管理（三）筆記索引
- MySQL表分割槽管理MySql
- ORACLE分割槽表管理Oracle
- 分割槽表大物件物件
- 深入分割槽表的增量統計資訊收集技術(incremetal statistics collection)REM
- 大資料量資料遷移後統計資訊問題大資料
- 分割槽表入無分割槽的資料庫資料庫
- ORACLE分割槽表管理[轉]Oracle
- 【學習筆記】分割槽表和分割槽索引——管理索引分割槽（四）筆記索引
- ORACLE分割槽表的使用和管理Oracle
- 《RHEL6硬碟的分割槽和swap分割槽管理》——硬碟分割槽的大總結硬碟
- 全面學習分割槽表及分割槽索引(17)--其它索引分割槽管理操作索引
- PostgreSQL：傳統分割槽表SQL
- [zt] 收集基於成本的優化統計資料 - 分割槽表優化
- 如何查詢分割槽表的分割槽及子分割槽
- oracle分割槽表和分割槽表exchangeOracle
- rebuild分割槽表分割槽索引的方法Rebuild索引
- Oracle10g 優化統計資訊(自動)不包含統計分割槽表中的local index ?Oracle優化Index
- (轉)ORACLE 分割槽表的設計Oracle
- 全面學習分割槽表及分割槽索引(7)--怎樣管理索引
- PLSQL根據分割槽表的分割槽名批次truncate分割槽SQL
- 把一個已存在資料的大表改成分割槽表
- 全面學習分割槽表及分割槽索引(13)--分隔表分割槽索引
- oracle分割槽表和非分割槽表exchangeOracle
- 範圍分割槽表和INTERVAL分割槽表對於SPLIT分割槽的區別
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- AppBoxFuture: 大資料表分割槽的3種策略APP大資料
- 資料庫系統設計：分割槽資料庫
- 全面學習分割槽表及分割槽索引(7)--怎樣管理(續)索引
- oracle 分割槽表move和包含分割槽表的lob moveOracle