26 April, 2012

Create Histogram without having to gather Table Stats

There was a question on forums : "Is there a way to ONLY generate histograms? not touching the rest of the statistics that are not histograms".

If you use DBMS_STATS.GATHER_TABLE_STATS( ... method_opt='FOR COLUMNS x SIZE n'); Table Stats are also gathered and updated.  Can this be avoided ?

Here's a test case :

00:57:48 SQL> create table ST_TEST as select object_id as id, owner as col_1, object_name as col_2
00:57:56   2  from dba_objects;

Table created.

00:58:05 SQL> exec dbms_stats.gather_table_stats('','ST_TEST');

PL/SQL procedure successfully completed.

00:58:13 SQL> select num_rows, last_analyzed from user_tables where table_name = 'ST_TEST';

  NUM_ROWS LAST_ANALYZED
---------- ------------------
     76713 26-APR-12 00:58:13

00:58:24 SQL> delete ST_TEST where col_2 like 'DBA_%';     

1650 rows deleted.

00:58:48 SQL> commit;

Commit complete.

00:58:52 SQL> !sleep 30

00:59:30 SQL> exec dbms_stats.gather_table_stats('','ST_TEST',method_opt=>'FOR COLUMNS COL_1 SIZE 250');

PL/SQL procedure successfully completed.

01:00:12 SQL> select num_rows, last_analyzed from user_tables where table_name = 'ST_TEST';

  NUM_ROWS LAST_ANALYZED
---------- ------------------
     75063 26-APR-12 01:00:12

01:00:20 SQL>
The DBMS_STATS call with METHOD_OPT also updated Table level statistics.  Can you avoid having to gather Table level statistics ?  What about column statistics and histograms on other columns in the table ?  Are they recomputed and updated as well ?  Can you avoid the CPU overheads for these actions ?

.
.
.

No comments: