1 Reply Latest reply: Feb 7, 2013 7:54 PM by Hemant K Chitale RSS

    Histogram

    becks
      Not a DBA, but have done a google on this.

      I can use the following to create a histogram on a indexed cloumn.

      EXECUTE dbms_stats.gather_table_stats.

      But do anyone knows how do i verify that histogram is created?
      I tried:
      Select count(*) buckets from user_tab_histograms (Is this correct?, but seem to be getting 0 rows.)

      I'm doing this as i hope it can improve the query time of this table which consists of around 4 millions rows.
        • 1. Re: Histogram
          Hemant K Chitale
          You would query USER_TAB_HISTOGRAMS to look for the histograms on a specific column.
          select endpoint_number, endpoint_value
          from user_tab_histograms
          where table_name='MY_TABLE'
          and column_name='MY_COLUMN'
          To identify if any Histogram exists, query USER_TAB_COL_STATISTICS. For example
          select table_name, column_name, histogram, num_buckets
          from user_tab_col_statistics
          where histogram != 'NONE'
          order by table_name, column_name
          Hemant K Chitale