Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2008
    Posts
    45

    Unanswered: Strange Histogram

    I'm running Oracle 11g at Linux Redhat 4

    For anyone who has any knowledge about histogram, please free me from this curiousity...

    When I saw the histogram's limitation (which only store the first 32 bytes of any string), I tried to test it for myself, and I found that even though the buckets are far more than NDV (number of distinct values), the histogram created is a Height-Balanced one (when it's supposed to be a Frequency histogram)

    And also, I don't know why, but when I trace the statement with 10053 event, the Cardinality formula also most likely changed, since I got 2.79 when I supposed to get 3 (the number of rows with same values in the same column). Does anyone has any clue ???

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Please provide DDL & DML to reproduce your results.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2008
    Posts
    45
    Thanks for the reply, my DDL and DML are like the followings

    DDL
    CREATE TABLE tbl (string VARCHAR(100));
    CREATE INDEX idx ON tbl(string); -> since histogram only meaningful on
    indexed column

    DML
    INSERT INTO tbl VALUES('XYZ');
    INSERT INTO tbl VALUES('ABC');
    INSERT INTO tbl VALUES('ABC');
    INSERT INTO tbl VALUES('ABC');
    INSERT INTO tbl VALUES('ABC');
    INSERT INTO tbl VALUES('abcdefghijklmnopqrstuvwxyzabcdefghij');
    ~~~~the 2nd f is the 32nd char~~~~
    INSERT INTO tbl VALUES('abcdefghijklmnopqrstuvwxyzabcdefZZZ');
    INSERT INTO tbl VALUES('abcdefghijklmnopqrstuvwxyzabcdefgggg');
    INSERT INTO tbl VALUES('DEF');
    INSERT INTO tbl VALUES('GHI');
    INSERT INTO tbl VALUES('JKL');
    INSERT INTO tbl VALUES('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

    If you have any clue, please m(_ _)m

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •