Search My Oracle Blog

Custom Search

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 ?

.
.
.

2 comments:

How to Find the Area of a Triangle said...

A graphical representation which shows a visual impression of the distribution of data termed as Histogram . Histogram consists of tabular frequencies which are shown as adjacent rectangles, with an area equal to the frequency of the observations in the interval.

Term papers said...

Good Article About "Create Histogram without having to gather Table Stats"

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com