Import Table Statistics to Another Table

It?s been a long time since I posted on my blog. This post is in response to the question that I received through email:

<<

Just like to know if I can use dbms_stats.import_table_stats to import table statistics to another table?

I am wondering if I can import table statistics from T1 to T2 using dbms_stats package?.

>>

The simple answer to the above question is ?NO?. You cannot copy statistics to a different table even if they are identical. However, there are two ways of achieving this:

1) Temporarily rename the destination table (T_IDENTICAL) as original table (T) and import statistics or

2) Break into the statistics table.

Option -1 is pretty straight forward so we will not discuss that. Let?s see how to break into statistics table.

Let us create the two tables: source table ?T? and the destination table ?T_IDENTICAL?. I am creating both these tables in the same schema, so as to avoid exporting and importing of statistics table.

  SQL>  SQL> drop table t purge;    Table dropped.    SQL> drop table t_identical purge;    Table dropped.    SQL>  SQL> create table t as select rownum id, object_name name from all_objects where rownum < 1001;    Table created.    SQL>  SQL> create table t_identical as select * from t where 1=2;    Table created.    SQL>  SQL>  

Collect statistics for the source table (T)

  SQL>  SQL> exec dbms_stats.gather_table_stats(user, 't');  PL/SQL procedure successfully completed.  SQL>  SQL> select table_name, num_rows from user_tables where table_name in ('T', 'T_IDENTICAL');  TABLE_NAME                       NUM_ROWS  ------------------------------ ----------  T                                    1000  T_IDENTICAL  SQL>  

Create a statistics table (?MY_STATS_TAB?) and export statistics of the source table ?T?.

  SQL>  SQL> exec dbms_stats.create_stat_table('TEST','MY_STATS_TAB');  PL/SQL procedure successfully completed.  SQL> exec dbms_stats.export_table_stats('TEST','T',NULL,'MY_STATS_TAB');  PL/SQL procedure successfully completed.  SQL>  

Now let us try to import statistics to the destination table (T_IDENTICAL):

  SQL> exec dbms_stats.import_table_stats('TEST','T_IDENTICAL', null, 'MY_STATS_TAB');  PL/SQL procedure successfully completed.  SQL>  SQL>  SQL> select table_name, num_rows from user_tables where table_name in ('T', 'T_IDENTICAL');  TABLE_NAME                       NUM_ROWS  ------------------------------ ----------  T                                    1000  T_IDENTICAL  SQL>  

No affect. ?

Oracle simply ignored my request without reporting any errors.

As we have source table statistics in a safe place, let us delete statistics from the source table and import them back from the container table. (This step ensures stats import is working as expected)

  SQL> exec dbms_stats.delete_table_stats(user, 'T');    PL/SQL procedure successfully completed.    SQL> select table_name, num_rows from user_tables where table_name in ('T', 'T_IDENTICAL');    TABLE_NAME                       NUM_ROWS  ------------------------------ ----------  T  T_IDENTICAL    SQL>    SQL>  SQL> exec dbms_stats.import_table_stats('TRADE','T', null, 'MY_STATS_TAB');    PL/SQL procedure successfully completed.    SQL> select table_name, num_rows from user_tables where table_name in ('T', 'T_IDENTICAL');    TABLE_NAME                       NUM_ROWS  ------------------------------ ----------  T                                    1000  T_IDENTICAL    SQL>  SQL>  

As you can see, import works only when the source and destination tables are same.

Let us see what?s there in the MY_STATS_TABLE:

  SQL>  SQL> select count(*) from my_stats_tab;      COUNT(*)  ----------           3    SQL>  SQL>  SQL> select c1, c5 from my_stats_tab;    C1                             C5  ------------------------------ ------------------  T                              TRADE  T                              TRADE  T                              TRADE    SQL>  SQL>  

Columns ?C5? and ?C1? of the statistics table represent schema owner and table name respectively. Let us update the table name from ?T? to ?T_IDENTICAL?.

  SQL> update my_stats_tab set c1 = 'T_IDENTICAL';    3 rows updated.    SQL> commit;    Commit complete.    SQL>  

Now import the table statistics for ?T_IDENTICAL? table

  SQL> select table_name, num_rows from user_tables where table_name in ('T', 'T_IDENTICAL');    TABLE_NAME                       NUM_ROWS  ------------------------------ ----------  T                                    1000  T_IDENTICAL    SQL>  SQL> exec dbms_stats.import_table_stats('TRADE','T_IDENTICAL', null, 'MY_STATS_TAB');    PL/SQL procedure successfully completed.    SQL> select table_name, num_rows from user_tables where table_name in ('T', 'T_IDENTICAL');    TABLE_NAME                       NUM_ROWS  ------------------------------ ----------  T                                    1000  T_IDENTICAL                          1000    SQL>  SQL>  

There you go. We were able to successfully move statistics to another table by hacking into the statistics table.

Hope you enjoyed this post.

reff : http://momendba.blogspot.com/2011/01/import-table-statistics-to-another.html

Video yang berkaitan dengan Import Table Statistics to Another Table