Oracle设置统计信息

最新推荐文章于 2024-09-14 19:55:29 发布

weixin_33782386

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量148

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/kanlianhui/blog/148489

版权

2019独角兽企业重金招聘Python工程师标准>>>

重置统计信息

SET SERVEROUTPUT ON
BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'EGISNBUDATA',
TABNAME => 'NBU_QUOTATE_CC_ATTR_VALUE',
METHOD_OPT => 'FOR ALL COLUMNS SIZE REPEAT',
DEGREE => 1,
CASCADE => TRUE,
NO_INVALIDATE => FALSE);
END;
/
declare
v_last_analyzed date;
BEGIN
SELECT LAST_ANALYZED INTO V_LAST_ANALYZED 
 FROM DBA_TABLES WHERE OWNER = 'EGISNBUDATA'
 AND TABLE_NAME = 'NBU_QUOTATE_CC_ATTR_VALUE';
 IF V_LAST_ANALYZED >= TRUNC(SYSDATE) THEN 
DBMS_OUTPUT.PUT_LINE('NBU_QUOTATE_CC_ATTR_VALUE表统计信息收集成功！');
ELSE
DBMS_OUTPUT.PUT_LINE('NBU_QUOTATE_CC_ATTR_VALUE表统计信息收集失败，请排查原因！');
END IF;
END;
/
------------delete_statistics_if_num_rows_is_zero ---------------
DECLARE 
V_COUNT NUMBER;
BEGIN 
SELECT num_rows
INTO V_COUNT FROM DBA_TABLES
WHERE owner = 'EGISNBUDATA' AND table_name = 'NBU_QUOTATE_CC_ATTR_VALUE';
if v_count=0 then 
BEGIN DBMS_STATS.DELETE_TABLE_STATS(OWNNAME => 'EGISNBUDATA',
TABNAME => 'NBU_QUOTATE_CC_ATTR_VALUE',
CASCADE_COLUMNS => TRUE ,
CASCADE_INDEXES => TRUE ,
CASCADE_PARTS => TRUE );
END;
END IF;
END;
/
------------delete_statistics_if_num_rows_is_zero ---------------

转载于:https://my.oschina.net/kanlianhui/blog/148489