Re: Rebuilding Histograms - Single columns vs. all columns

From: Brian Wisniewski <brian_wisniewski_at_yahoo.com>
Date: Tue, 15 Aug 2000 14:12:18 -0700 (PDT)
Message-Id: <10590.114655@fatcity.com>

The 'for table' is just going to analyze the table. If you left this clause off it would analyze the table and associated indexes. I estimate tables via 'for table' and then I compute statistics for associated indexes. If I didn't use 'for table' the indexes would be estimated along with the table and then it would be computed when I make my second pass through.

Histograms are stored at the table level, not the index. It helps the optimizer determine whether to use an index or not. This isn't the best example but it may help.

eg. Table has 10000 rows, column A has 4000 distinct values and the other 6000 rows belong to 1 particular value - 'abc'. If I wanted to select all of the rows that equaled 'abc' then it would be better for me to do a full table scan since 6000 of 10000 rows equal this value. However, if I looked for value 'bbb' it only has 1 value and it would be better if it was selected via and index. The optimizer will know this based upon the histograms and buckets you create for it.

Take a look at document 72539.1 in MetaLink for more info.

Let me know if this helps or just muddies the water more.

• Brian
  • Cherie_Machler_at_gelco.com wrote:
    > Brian,
    >
    > What do you mean in the statement below? Does the 'for table' clause
    > produce
    > histograms
    > at the table level? If yes, why would you want to do that? If no,
    > what is the
    > difference between
    > using the 'for table' clause and just analyzing the table with a
    > regular analyze
    > command and
    > not creating histograms?
    >
    >
    > 'the 'for table' clause just analyzes the table, not the associated
    > indexes, the 'for [all] column[s]' clause will also do histograms for
    > every column or for the columns you specify. '
    >
    > Thanks for taking time to reply. I'm having difficulty getting all
    > my questions answered by reading the various documentation I have.
    >
    > Cherie
    >
    >
    > --
    > Author:
    > INET: Cherie_Machler_at_gelco.com
    >
    > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    > San Diego, California -- Public Internet access / Mailing
    > Lists
    > --------------------------------------------------------------------
    > To REMOVE yourself from this mailing list, send an E-Mail message
    > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    > the message BODY, include a line containing: UNSUB ORACLE-L
    > (or the name of mailing list you want to be removed from). You may
    > also send the HELP command for other information (like subscribing).
    

    ---

Do You Yahoo!?
Yahoo! Mail – Free email you can access from anywhere! Received on Tue Aug 15 2000 - 16:12:18 CDT