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.
