9 Replies Latest reply on Oct 27, 2014 4:41 PM by jgarry

    Incorrect estimations of cardinality for the table even though it is having the frequency Histogram defined on it

    2780902

      Hello Everyone,

       

      I have a table named t and i have created the frequency histogram on it.Here is the Information

       

      CREATE TABLE t

      AS

      SELECT rownum AS id,

             50+round(dbms_random.normal*4) AS val1,

             100+round(ln(rownum/3.25+2)) AS val2,

             100+round(ln(rownum/3.25+2)) AS val3,

             dbms_random.string('p',250) AS pad

      FROM dual

      CONNECT BY level <= 1000

      ORDER BY dbms_random.value;

       

       

      UPDATE t SET val1 = NULL WHERE val1 < 0;

       

       

      ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);

       

       

      CREATE INDEX t_val1_i ON t (val1);

       

       

      CREATE INDEX t_val2_i ON t (val2);

       

       

      BEGIN

        dbms_stats.gather_table_stats(ownname          => user,

                                      tabname          => 'T',

                                      estimate_percent => dbms_stats.auto_sample_size,

                                      method_opt       => 'for columns size skewonly id, val1 size 15, val2, val3 size 5, pad',

                                      cascade          => TRUE);

      END;

      /

       

      SQL> COLUMN endpoint_value FORMAT 9999

      SQL> COLUMN endpoint_number FORMAT 999999

      SQL> COLUMN frequency FORMAT 999999

      SQL>

      SQL> SELECT endpoint_value, endpoint_number,

        2         endpoint_number - lag(endpoint_number,1,0)

                               OVER (ORDER BY endpoint_number) AS frequency

        3    4  FROM dba_tab_histograms

      WHERE table_name = 'T'

        5    6  AND column_name = 'VAL2'

        7  ORDER BY endpoint_number;

       

       

      ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY

      -------------- --------------- ---------

                 101               8         8

                 102              33        25

                 103             101        68

                 104             286       185

                 105             788       502

                 106            1000       212

       

      so i tried to do the explain plan for some of the statements to check the cardinality.

       

      EXPLAIN PLAN SET STATEMENT_ID '101' FOR SELECT * FROM t WHERE val2 = 101;

      EXPLAIN PLAN SET STATEMENT_ID '102' FOR SELECT * FROM t WHERE val2 = 102;

      EXPLAIN PLAN SET STATEMENT_ID '103' FOR SELECT * FROM t WHERE val2 = 103;

      EXPLAIN PLAN SET STATEMENT_ID '104' FOR SELECT * FROM t WHERE val2 = 104;

      EXPLAIN PLAN SET STATEMENT_ID '105' FOR SELECT * FROM t WHERE val2 = 105;

      EXPLAIN PLAN SET STATEMENT_ID '106' FOR SELECT * FROM t WHERE val2 = 106;

       

      when i saw the results below,

       

      SQL> COLUMN statement_id FORMAT A12

       

       

      SELECT statement_id, cardinality

      FROM plan_table

      WHERE id = 0

      ORDER BY statement_id;SQL> SQL>   2    3    4

       

       

      STATEMENT_ID CARDINALITY

      ------------ -----------

      101                    8

      102                   25

      103                   68

      104                  100

      105                  100

      106                  100

       

      when you check the above values 104,105 and 106 the values are wrong.By the way it is 11 2 0 4 database.your response is much appreciated.

       

      Thanks,

      Bhavani