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.