|
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 AX.AX_SLE_LINES 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=>'AX', stattab=>'AX_SLE_LINES_STATS', tblspace=>'USERS');
END;
Second you have to export partition PART001 statistics to the table AX_SLE_LINES_STATS.
BEGIN
DBMS_STATS.EXPORT_TABLE_STATS( 'AX', 'AX_SLE_LINES','PART001','AX_SLE_LINES_STATS','MY_AX_SLE_LINES_PART001',FALSE,'AX');
END;
Third you have to import the statistics to the partition PART002
BEGIN
DBMS_STATS.IMPORT_TABLE_STATS( 'AX', 'AX_SLE_LINES','PART002','AX_SLE_LINES_STATS','MY_AX_SLE_LINES_PART001',FALSE, 'AX');
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 AX.AX_SLE_LINES_STATS SET C2='PART002';
COMMIT;
Import the statistics again!
BEGIN
DBMS_STATS.IMPORT_TABLE_STATS( 'AX', 'AX_SLE_LINES','PART002','AX_SLE_LINES_STATS','MY_AX_SLE_LINES_PART001',FALSE, 'AX');
END;
|
