{"id":73263,"date":"2013-01-21T10:42:17","date_gmt":"2013-01-21T10:42:17","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/having-cardinality\/"},"modified":"2021-07-14T13:07:46","modified_gmt":"2021-07-14T13:07:46","slug":"having-cardinality","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/having-cardinality\/","title":{"rendered":"HAVING Cardinality"},"content":{"rendered":"<p>When performing\u00a0<strong>aggregate GROUP BY<\/strong>\u00a0operations an additional filter on the aggregates can be applied using the\u00a0<strong>HAVING<\/strong>\u00a0clause.<\/p>\n<p>Usually aggregates are one of the\u00a0<strong>last steps<\/strong>\u00a0executed before the final result set is returned to the client.<\/p>\n<p>However there are various reasons, why a GROUP BY operation might be somewhere in the middle of the execution plan operation, for example it might be part of a view that cannot be\u00a0<strong>merged<\/strong>\u00a0(or was hinted not to be merged using the NO_MERGE hint), or in the more recent releases (11g+) the optimizer decided to use the<strong>GROUP BY PLACEMENT<\/strong>\u00a0transformation that deliberately can move the GROUP BY operation to a different execution step of the plan.<\/p>\n<p>In such cases, when the GROUP BY operation will be input to some other operation, it becomes essential for the overall efficiency of the execution plan preferred by the optimizer that\u00a0<strong>the cardinality estimates<\/strong>\u00a0are in the right ballpark, as it will influence the choice of other related execution steps like join orders and methods or simply the decision between an index-based access or a full table scan.<\/p>\n<p>While the optimizer based on the statistics can come up with a\u00a0<strong>reasonable estimate<\/strong>\u00a0regarding the cardinality of the GROUP BY expression (the emphasis here is on *can*, it might also be wrong), it is important to understand that an additional filter on the aggregates using the\u00a0<strong>HAVING clause<\/strong>\u00a0is in principle treated like an<strong>&#8220;unknown&#8221; expression<\/strong>\u00a0and therefore the estimates are based on\u00a0<strong>built-in defaults<\/strong>\u00a0that might not have much to do with actual filter selectivities of that HAVING expression.<\/p>\n<p>I have published a simple example on my <a title=\"Oracle related stuff - HAVING Cardinality\" href=\"http:\/\/oracle-randolf.blogspot.co.uk\/2013\/01\/having-cardinality.html\" target=\"_blank\">blog<\/a> to demonstrate the point. Read the full article at:\u00a0<a title=\"Oracle related stuff - HAVING Cardinality\" href=\"http:\/\/oracle-randolf.blogspot.co.uk\/2013\/01\/having-cardinality.html\" target=\"_blank\">oracle-randolf.blogspot.co.uk\/2013\/01\/having-cardinality<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>When performing\u00a0aggregate GROUP BY\u00a0operations an additional filter on the aggregates can be applied using the\u00a0HAVING\u00a0clause. Usually aggregates are one of the\u00a0last steps\u00a0executed before the final result set is returned to the client. However there are various reasons, why a GROUP BY operation might be somewhere in the middle of the execution plan operation, for example it might be part of&hellip;<\/p>\n","protected":false},"author":316197,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[],"coauthors":[],"class_list":["post-73263","post","type-post","status-publish","format-standard","hentry","category-oracle-databases"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73263","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316197"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73263"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73263\/revisions"}],"predecessor-version":[{"id":91734,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73263\/revisions\/91734"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73263"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73263"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73263"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73263"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}