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