Tag Archives: statistics

Delete stats of table, index, partition and column in Oracle

Delete stats of table, index, partition and column in Oracle

Check and drop the Stats of the table

-- Check the stats
col table_name for a15
SELECT table_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') "LASTANALYZED" FROM DBA_TABLES WHERE owner='SYS' AND table_name='TEST';

-- Drop the stats
EXEC DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'SCOTT', TABNAME=>'EMP');

Check and drop the stats of partition in table

-- Check partition stats
Col table_name for a10
col partition_name for a10
col lastanalyzed for a18
SELECT table_name, partition_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') "LASTANALYZED" FROM DBA_TAB_PARTITIONS
WHERE table_name='EMP' AND partition_name='EMP201701';

-- Drop the partition stats
EXEC DBMS_STATS.DELETE_TABLE_STATS (OWNNAME => 'HR', TABNAME => 'EMP',PARTNAME => 'EMP201701');

Check and drop the stats of Column level in table

-- Check stats of column in table
Col table_name for a10
col column_name for a10
col lastanalyzed for a18
SELECT table_name,column_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') "LASTANALYZED" from DBA_TAB_COL_STATISTICS;

-- Drop the column stats
EXEC DBMS_STATS.DELETE_COLUMN_STATS(OWNNAME=>'', TABNAME=>'', COLNAME=>'', CASCADE_PARTS=>TRUE, COL_STAT_TYPE=>'HISTOGRAM');

Check and drop the stats of Index

--Check index stats
SELECT table_name,index_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') "LASTANALYZED" FROM DBA_INDEXES;

-- Drop the index stats
EXEC DBMS_STATS.DELETE_INDEX_STATS('SCOTT', 'EMP_PK');

Advertisements