Happy3w

如何高水位线下无数据却需要全表扫描

2017.02.10
/**统计高水位线下无数据,且需要全表扫描的**/ select table_name, (Blocks * 8192/1024/1024) - (num_rows*avg_row_len/1024/1024) as "data lower than hwm in MB" from user_tables where table_name=upper('sm_log_operatelog_t01'); /***统计 用户 BGY65_JY1223 表 sm_log_operatelog_t01**/ exec dbms_stats.gather_table_stats('BGY65_JY1223','SM_LOG_OPERATELOG_T01',CASCADE=>TRUE); /***获取empty_blocks信息.**/ SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name =upper('sm_log_operatelog_t01'); /**由于前面 无法获取 empty_blocks. 需要先分析**/ analyze table t1 compute statistics; /***分析后重新查询一下.**/ SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name =upper('sm_log_operatelog_t01'); /**对表进行碎片整理.**/ alter table sm_log_operatelog_t01 enable row movement; alter table sm_log_operatelog_t01 shrink space cascade; /**查询数据块.**/ select sum(bytes)/1024/1024 from dba_segments where segment_name='sm_log_operatelog_t01'; select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID' ; /**再看一下**/ /**统计高水位线下无数据,且需要全表扫描的**/ select table_name,(Blocks * 8192/1024/1024) - (num_rows*avg_row_len/1024/1024) as "data lower than hwm in MB" from user_tables where table_name=upper('sm_log_operatelog_t01'); /***统计 用户 BGY65_JY1223 表 sm_log_operatelog_t01**/ exec dbms_stats.gather_table_stats('BGY65_JY1223','SM_LOG_OPERATELOG_T01',CASCADE=>TRUE);