{"id":18,"date":"2006-06-19T17:12:52","date_gmt":"2006-06-19T17:12:52","guid":{"rendered":"http:\/\/etlguru.com\/blog\/2006\/06\/19\/aggregate-aware-queries\/"},"modified":"2006-06-19T17:12:52","modified_gmt":"2006-06-19T17:12:52","slug":"aggregate-aware-queries","status":"publish","type":"post","link":"https:\/\/etlguru.com\/?p=18","title":{"rendered":"Aggregate aware queries"},"content":{"rendered":"<p>Imagine a scenario table having 20 million transactions for 10 years and a user writing a query to get data aggregated at monthly level. The best guess for the time required to return the result set, even after extensive database tuning can be in multiples of 10 minutes or even an hour.<\/p>\n<p><strong>What is the solution?<br \/>\n<\/strong>There are three solutions based on the same concept of pre-aggregation, but the utilization is quite different.<br \/>\n1. Write different queries in advance to hit different tables<br \/>\n2. Have a reporting tool, which is aggregate aware (Client side). The reporting tool itself will decide ehich tbales to hit and rewrite it\u00e2\u20ac\u2122s own queries.<br \/>\n3. Have a database that supports transparent query re-write. The database it self takes a simple query and re-writes against aggregrate tables.<\/p>\n<p>Here\u00e2\u20ac\u2122s an <a title=\"Oracle Query  Rewrite\" href=\"http:\/\/web.archive.org\/web\/20070523031241\/http:\/\/www.oracle.com\/technology\/products\/bi\/db\/10g\/pdf\/twp_bi_dw_improve_perf_using_query_rewrite_10gr1_1203.pdf\" target=\"_blank\">Oracle version<\/a> of query re-write based on point 3 in this <strong>article.<\/strong><\/p>\n<p><strong>\u00c2\u00a0More details to come\u00e2\u20ac\u00a6\u00e2\u20ac\u00a6\u00e2\u20ac\u00a6\u00e2\u20ac\u00a6\u00c2\u00a0<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Imagine a scenario table having 20 million transactions for 10 years and a user writing a query to get data aggregated at monthly level. The best guess for the time required to return the result set, even after extensive database tuning can be in multiples of 10 minutes or even an hour. What is the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[25,17,4,3,6,1],"tags":[],"class_list":["post-18","post","type-post","status-publish","format-standard","hentry","category-data-aggregration","category-data-modeling","category-database-tuning","category-etl-performance-tuning","category-oracle","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/etlguru.com\/index.php?rest_route=\/wp\/v2\/posts\/18","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/etlguru.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/etlguru.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/etlguru.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/etlguru.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=18"}],"version-history":[{"count":0,"href":"https:\/\/etlguru.com\/index.php?rest_route=\/wp\/v2\/posts\/18\/revisions"}],"wp:attachment":[{"href":"https:\/\/etlguru.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=18"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/etlguru.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=18"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/etlguru.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=18"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}