{"id":1755,"date":"2016-09-21T01:47:05","date_gmt":"2016-09-21T06:47:05","guid":{"rendered":"https:\/\/bijoos.com\/oraclenotes\/?p=1755"},"modified":"2026-02-07T15:34:42","modified_gmt":"2026-02-07T21:34:42","slug":"approximate-query-processing-in-12c-release-2","status":"publish","type":"post","link":"https:\/\/bijoos.com\/oraclenotes\/2016\/1755\/","title":{"rendered":"Approximate Query Processing in 12c Release 2"},"content":{"rendered":"<p><a href=\"https:\/\/i0.wp.com\/bijoos.com\/oraclenotes\/wp-content\/uploads\/2016\/09\/dbcloud.png?ssl=1\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\" wp-image-1756 alignleft\" src=\"https:\/\/i0.wp.com\/bijoos.com\/oraclenotes\/wp-content\/uploads\/2016\/09\/dbcloud.png?resize=79%2C79&#038;ssl=1\" alt=\"dbcloud\" width=\"79\" height=\"79\" \/><\/a>Approximate Query Processing appears to be a major feature of Oracle Database 12c Release 2 SQL. In 12cR1 patchset 2 (12.1.0.2), we saw one approximate query function appear &#8211; APPROX_COUNT_DISTINCT to use instead of COUNT(DISTINCT). In 12.2 there are several APPROX functions introduced:<\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li><code>APPROX_COUNT_DISTINCT_DETAIL<\/code><\/li>\n<li><code>APPROX_COUNT_DISTINCT_AGG<\/code><\/li>\n<li><code>TO_APPROX_COUNT_DISTINCT<\/code><\/li>\n<li><code>APPROX_MEDIAN<\/code><\/li>\n<li><code>APPROX_PERCENTILE<\/code><\/li>\n<li><code>APPROX_PERCENTILE_DETAIL<\/code><\/li>\n<li><code>APPROX_PERCENTILE_AGG<\/code><\/li>\n<li><code>TO_APPROX_PERCENTILE<\/code><\/li>\n<\/ul>\n<p>According to Oracle documentation, &#8220;Approximate query processing is primarily used in data discovery applications to return quick answers to explorative queries. Users typically want to locate interesting data points within large amounts of data and then drill down to uncover further levels of detail. For explorative queries, quick responses are more important than exact values.&#8221;<\/p>\n<p>The interesting part is that you can utilize\u00a0the Approximate functions without changing code. There are three\u00a0initialization parameters introduced to control which functions should be treated as an approximate function during run time&#8230; cool!<\/p>\n<p>The initialization parameters are:<\/p>\n<ul>\n<li><code>approx_for_aggregation<\/code><\/li>\n<li><code>approx_for_count_distinct<\/code><\/li>\n<li><code>approx_for_percentile<\/code><\/li>\n<\/ul>\n<p>To replace only the <code>COUNT(DISTINCT)<\/code> function in queries with the <code>APPROX_COUNT_DISTINCT<\/code> function, set the <code>approx_for_count_distinct<\/code> initialization parameter to <code>TRUE<\/code> for the current session or for the entire database.<\/p>\n<p>To replace percentile functions with the corresponding functions that return approximate results, set <code>approx_for_percentile<\/code> to <code>PERCENTILE_CONT<\/code>, <code>PERCENTILE_DISC<\/code>, or <code>ALL<\/code> (replaces all percentile functions) for the current session or for the entire database.<\/p>\n<p>To run all queries using the corresponding SQL functions that return approximate results instead of the specified SQL functions, set the <code>approx_for_aggregation<\/code> initialization parameter to <code>TRUE<\/code> for the current session or for the entire database.<\/p>\n<p>Read more&#8230; <a href=\"https:\/\/docs.oracle.com\/cloud\/latest\/exadataexpress-cloud\/CSDBF\/approximate-query-processing.htm#CSDBF-GUID-6A387B85-B347-4065-8A48-8F3E4AA91F87\" target=\"_blank\">Oracle Documentation<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Approximate Query Processing appears to be a major feature of Oracle Database 12c Release 2 SQL. In 12cR1 patchset 2 (12.1.0.2), we saw one approximate query function appear &#8211; APPROX_COUNT_DISTINCT to use instead of COUNT(DISTINCT). In 12.2 there are several APPROX functions introduced: &nbsp; APPROX_COUNT_DISTINCT_DETAIL APPROX_COUNT_DISTINCT_AGG TO_APPROX_COUNT_DISTINCT APPROX_MEDIAN APPROX_PERCENTILE APPROX_PERCENTILE_DETAIL APPROX_PERCENTILE_AGG TO_APPROX_PERCENTILE According to Oracle &#8230; <a title=\"Approximate Query Processing in 12c Release 2\" class=\"read-more\" href=\"https:\/\/bijoos.com\/oraclenotes\/2016\/1755\/\" aria-label=\"Read more about Approximate Query Processing in 12c Release 2\">Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[133],"tags":[],"class_list":["post-1755","post","type-post","status-publish","format-standard","hentry","category-oracledb"],"acf":[],"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/1755","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/comments?post=1755"}],"version-history":[{"count":1,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/1755\/revisions"}],"predecessor-version":[{"id":3011,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/1755\/revisions\/3011"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=1755"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=1755"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=1755"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}