表分析语句参考如下:
单表分析:
分区表:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'lbi_edm',
tabname => 'T_E_CUSTOMER_D',
estimate_percent => 100,
no_invalidate => FALSE,--立马生效
degree => 8,
granularity => 'ALL', --分区
cascade => TRUE);
END;
/
非分区表:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'lbi_ods',
tabname => 'T_O_CUSTOMER',
estimate_percent => 100,
no_invalidate => FALSE,--立马生效
degree => 8,
cascade => TRUE);
END;
/
按用户分析(大数据量不建议使用,耗时长):
exec dbms_stats.gather_schema_stats('LBI_ODS_VDF',estimate_percent=>100,cascade=> TRUE);
exec dbms_stats.gather_schema_stats('LBI_EDM_VDF',estimate_percent=>100,cascade=> TRUE);
exec dbms_stats.gather_schema_stats('LBI_LS_VDF',estimate_percent=>100,cascade=> TRUE);
exec dbms_stats.gather_schema_stats('LBI_DM_VDF',estimate_percent=>100,cascade=> TRUE);
删除分析信息:
dbms_stats.delete_schema_stats(owner => 'lbi_ods' );
dbms_stats.delete_table_stats(ownname => 'lbi_ods',
tabname => 'T_O_CUSTOMER'