Re: CBO histograms & density

Hi,

I did some further investigations. It seems, that the density information changes its meaning in the presence of histograms on a given table.

CREATE TABLE dens2 (a NUMBER)
CREATE INDEX dens2_idx ON dens2(a)

then insert 1000 rows ( values 1...1000), do an analyze with and without histograms. density will always be 1/1000.

then, after "update dens2 set a=1 where rownum<200)" there'll be major differences in the density value. Without histograms, the value is still 1/1000, with "size 2" I get a value of 0.04 (same with size 4 and 8, back to 1/1000 with size 16).

Back to my original question:
The column ean with a "good" density value in any case has max. 2 rows per key, while object_id has up to 3000 per key. This seems to make the difference. I created a new table via "create table ... as select from ... where rownum < num_rows).
I detected all values of object_id occured only 2 times, with the exception of 2 object_ids (id1, id2) that occured several hundred times. Then I ran the analyze with a bucket count of 2. Additionally I determined the number of rows with "object_id in (id1, id2)". The result is significant:
# rows in table #distinct count(id1+id1) density (count(id2+id2) / #distinct/10

500                    326           139                     0,04
0,04
1000                  454            503                    0,13        0,11
2000                  986            897                    0,10        0,09
4000                  1512          2308                  0,17        0,15
10000                4356          4965                  0,13        0,11

Maybe you're interested to evaluate this in more detail.

Stephan Received on Fri Nov 15 2002 - 08:27:05 CST