In a recent posting on OTN, someone was having trouble copying stats from one index to another using the import_index_stats and export_index_stats procedures from package dbms_stats modifying the contents of their “stat table” between the export and import.
Part of the problem with this approach is that you’re not really supposed to do what they were trying to do (or so I was informed by a client who had been told by Oracle Support that they should not modify the stat table). But if you want to do something similar it’s quite easy (and “legal”) to write a little bit of code that (run by a user with the appropriate privileges to the data dictionary) does a simple pair of calls to get_index_stats/set_index_stats:
declare m_numrows number; m_numlblks number; m_numdist number; m_avglblk number; m_avgdblk number; m_clstfct number; m_indlevel number; begin dbms_stats.get_index_stats( ownname => 'SOURCE_SCHEMA', indname => 'SOURCE_IND', numrows => m_numrows, numlblks => m_numlblks, numdist => m_numdist, avglblk => m_avglblk, avgdblk => m_avgdblk, clstfct => m_clstfct, indlevel => m_indlevel ); -- -- Something here to change a few variables -- e.g. m_indlevel := 1; -- dbms_stats.set_index_stats( ownname => 'DEST_SCHEMA', indname => 'DEST_INDEX', numrows => m_numrows, numlblks => m_numlblks, numdist => m_numdist, avglblk => m_avglblk, avgdblk => m_avgdblk, clstfct => m_clstfct, indlevel => m_indlevel ); end; /
The same sort of code, with a few minor changes, can obviously be used for (composite) partitioned indexes and secondary indexes on IOTs (index organized tables); and it’s not difficult to wrap a pl/sql loop around this type of code, possibly driven by a look-up table, to copy stats on a much larger scale.