This discussion is archived
5 Replies Latest reply: Apr 26, 2012 1:02 AM by Hemant K Chitale RSS

Histograms

Chewy Newbie
Currently Being Moderated
Hi Guys,

Is there a way to ONLY generate histograms? not touching the rest of the statistics that are not histograms

Kindly advise.

thanks
  • 1. Re: Histograms
    Aman.... Oracle ACE
    Currently Being Moderated
    Chewy wrote:
    Hi Guys,

    Is there a way to ONLY generate histograms? not touching the rest of the statistics that are not histograms
    But why you don't want any other statistics to be generated?

    Aman....
  • 2. Re: Histograms
    Chinar Guru
    Currently Being Moderated
    Chewy wrote:
    Hi Guys,

    Is there a way to ONLY generate histograms? not touching the rest of the statistics that are not histograms

    Kindly advise.

    thanks
    What is oracle version?
    can you please explain more? to creating histogram you have to use dbms_stats.gather_table_stats procedure with METHOD_OPT option via according column and bucket settings.So histogram is representation of column distribution. When creating histogram here need refreshing/gathering column/table statistics according column stats and type of histogram(identifying popular and non-popular values). But why you want without refresh stats?
  • 3. Re: Histograms
    Chewy Newbie
    Currently Being Moderated
    thanks.

    Version 10.2.0.5

    Actually just curious if there's way to gather only histograms alone.

    The following command will also gather the other table statistics right as well? eg, avg row length, blocks, row num, etc.

    exec dbms_stats.gather_table_stats('acer','tb_form_parameters',method_opt=>'for columns size 254 param_value');

    thanks
  • 4. Re: Histograms
    Chinar Guru
    Currently Being Moderated
    The following command will also gather the other table statistics right as well?
    Yes of course.
  • 5. Re: Histograms
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    if there's way to gather only histograms alone.
    I think that this is a sensible requirement in some circumstances. Unfortunately, testing shows that it is not possible.
    You could log an SR with Oracle Support and/or file an Enhancement Request.


    Hemant K Chitale

    Test :
    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> 
    Edited by: Hemant K Chitale on Apr 26, 2012 4:02 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points