DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Saturday, July 14, 2018

Transfer partition statistics to another partition




Summary

It is very often to have a partitioned table and want to calculate or estimate the statistics for the partitions. Partitions usually holds millions of records, so calculating or estimating statistics its a bit time consuming effort. But if the partitions approximately have the same number of records then there is no need to calculate statistics for every partition.

One good idea is to calculate statistics for one partition and then to "copy" them to the other partitions.

How to export statistics from one partition and import them to another
In the example will use the table TST.TST_TABLE which is partitioned (PART001, PART002, PART003, ...) and want the partition PART002 to have the same statistics as PART001

First you have to setup a table to hold the exported partition statistics
BEGIN
DBMS_STATS.CREATE_STAT_TABLE(ownname=>'TST', stattab=>'TST_TABLE_STATS', tblspace=>'USERS');
END;
Second you have to export partition PART001 statistics to the table TST_TABLE_STATS.
BEGIN
DBMS_STATS.EXPORT_TABLE_STATS( 'TST', 'TST_TABLE','PART001','TST_TABLE_STATS','MY_TST_TABLE_PART001',FALSE,'TST');
END;
Third you have to import the statistics to the partition PART002
BEGIN
DBMS_STATS.IMPORT_TABLE_STATS( 'TST', 'TST_TABLE','PART002','TST_TABLE_STATS','MY_TST_TABLE_PART001',FALSE, 'TST');
END;
But if you query the dictionary you will notice that the statistics of the partition PART002 still is empty

Tip: You have to update the table that holds the statistics with the following value
UPDATE TST.TST_TABLE_STATS SET C2='PART002';
COMMIT;
Import the statistics again!
BEGIN
DBMS_STATS.IMPORT_TABLE_STATS( 'TST', 'TST_TABLE','PART002','TST_TABLE_STATS','MY_TST_TABLE_PART001',FALSE, 'TST');
END;

No comments: