tag:blogger.com,1999:blog-5124641802818980374.post5948992165600724038..comments2023-09-06T11:58:30.752+02:00Comments on Oracle related stuff: Column Groups - Edge CasesUnknownnoreply@blogger.comBlogger2125tag:blogger.com,1999:blog-5124641802818980374.post-26953883654279168992012-04-11T22:01:01.982+02:002012-04-11T22:01:01.982+02:00Hi Martin,
thanks for posting your experiments. I...Hi Martin,<br /><br />thanks for posting your experiments. I didn't have time yet to look into this, but certainly something to investigate further.<br /><br />Thanks,<br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-63496952953127584062012-04-11T11:05:54.307+02:002012-04-11T11:05:54.307+02:00Randolf,
that's a very interesting observatio...Randolf,<br /><br />that's a very interesting observation. Playing a little bit with your example I see that NULL values also seem to have a strange effect on the cardinality estimates:<br /><br />-- after using your create script<br /><br />-- case 1: a single NULL value for attr1<br />update t set attr1 = null where id = 1;<br />-- gather statistics again<br />exec dbms_stats.gather_table_stats(null, 't', method_opt => 'for all columns size 1')<br /><br />explain plan for<br />select * from t where attr1 = 1 and attr2 = 1;<br /><br />------------------------------------------<br />| Id | Operation | Name | Rows |<br />------------------------------------------<br />| 0 | SELECT STATEMENT | | 500K|<br />|* 1 | TABLE ACCESS FULL| T | 500K|<br />------------------------------------------<br /><br /><br />-- case 2: 100.000 NULL values for attr1<br />update t set attr1 = null where id <= 100000;<br /><br />explain plan for<br />select * from t where attr1 = 1 and attr2 = 1;<br /><br />------------------------------------------<br />| Id | Operation | Name | Rows |<br />------------------------------------------<br />| 0 | SELECT STATEMENT | | 450K|<br />|* 1 | TABLE ACCESS FULL| T | 450K|<br />------------------------------------------<br /><br />-- case 3: 100.000 NULL values for attr1 and attr2<br />update t set attr1 = null, attr2 = null where id <= 100000;<br /><br />explain plan for<br />select * from t where attr1 = 1 and attr2 = 1;<br /><br />------------------------------------------<br />| Id | Operation | Name | Rows |<br />------------------------------------------<br />| 0 | SELECT STATEMENT | | 900K|<br />|* 1 | TABLE ACCESS FULL| T | 900K|<br />------------------------------------------<br /><br />So there is clearly a pattern - but I don't see the purpose ...<br /><br />MartinMartin Preisshttps://www.blogger.com/profile/06388592214305009761noreply@blogger.com