Approximate query returns less accurate results for better response time. Approximate query is preferred in following use case
Why use the approximate query?
Many queries are scale-able, which mean the performance can improve when add more HW to the database. For example, select sum(revenue) from sales, the query can be distributed to many machines which has access to subsets of data, calculate the subtotal of the subsets, then add the subtotal to get the final answer. There are also some of the queries can't scale easily without sacrifice accuracy. For example, select count(distinct customers) from sales.
Graph Source: https://antognini.ch/2014/10/the-approx_count_distinct-function-a-test-case/
Oracle approximate functions
APPROX_COUNT_DISTINCT
APPROX_COUNT_DISTINCT_AGG
APPROX_COUNT_DISTINCT_DETAIL
TO_APPROX_COUNT_DISTINCT
APPROX_MEDIAN
APPROX_PERCENTILE
APPROX_PERCENTILE_AGG
APPROX_PERCENTILE_DETAIL
TO_APPORX_PERCENTILE
Oracle approximate functions related parameters
approx_for_aggregation = TRUE|FALSE;
approx_for_count_distinct = TRUE|FALSE;
approx_for_percentile = 'PERCENTILE_CONT DETERMINISTIC|PERCENTILE_DISC|NONE';
Other databases provide similar functions
Presto/Athena -approx_distinct()
Big Query – automatic switch between approximate function and exact function
Impala - ndv()
Redshift – approximate keyword
How to use Oracle approximate query features
1. Explicit function call For example:
select approx_count_distinct (badge_id) from access_transactions
2. Change system or session level configuration to implicitly use approximate query feature
alter session set approx_for_count_distinct =true;
select count(distinct badge_id) from access_transactions;
The count(distinct) is implicitly converted to approx_count_distinct call
3. Approximate query supports materialized view with query rewrite feature
CREATE MATERIALIZED VIEW data_ndv_mv ENABLE QUERY REWRITE AS
SELECT created_date, APPROX_COUNT_DISTINCT(id) AS data_count
FROM t1
GROUP BY created_date;
ALTER SESSION SET query_rewrite_enabled=true;
ALTER SESSION SET approx_for_aggregation = TRUE;
SELECT created_date, COUNT(DISTINCT id) AS data_count
FROM t1
GROUP BY created_date;
The above query will use materialized view with approximate result instead of using base tables
4. Use approximate functions to support multiple levels of approximate queries
CREATE MATERIALIZED VIEW daily_prod_count_mv AS
SELECT t.calendar_year year,
t.calendar_month_number month,
t.day_number_in_month day,
APPROX_COUNT_DISTINCT_DETAIL(s.prod_id) daily_detail
FROM times t, sales s
WHERE t.time_id = s.time_id
GROUP BY t.calendar_year, t.calendar_month_number, t.day_number_in_month;
CREATE MATERIALIZED VIEW annual_prod_count_mv AS
SELECT year,
APPROX_COUNT_DISTINCT_AGG(daily_detail) annual_detail
FROM daily_prod_count_mv
GROUP BY year;
SELECT year,
TO_APPROX_COUNT_DISTINCT(annual_detail) "NUM PRODUCTS"
FROM annual_prod_count_mv
ORDER BY year;
How can Strategy customers utilize Oracle approximate query in existing products?
1. Oracle DBA to set system level parameter to force use approximate queries.
When Oracle DBA set the approx._for_aggregation parameter in system level, all count(distinct) type of call automatically be converted to approximate queries. In this case, Strategy users do not need to do anything to utilize approximate query feature.
2. Use pre-statement to implicitly utilize approximate query feature
Strategy users can set pre-statement “alter session set approx._for_aggregation = true” and keep connection open VLDB setting in Strategy, therefore all the subsequent count(distinct) type of function calls to Oracle database implicitly use approximate query. The pre-statement can be set in different levels. Setting it in DB instance level affects all connections to the database. Setting in project level or report level affects the behavior of the project or the report only.
The pre-statement method can be used in all Strategy product versions, and it does not require users to know the specific Oracle approximate query syntax.
3. Specify approximate query in metrics level
Users can also use Strategy Pass Through Function (for example: applyagg functions) to explicitly use Oracle approximate functions. These method allows mix use of approximate functions and exact functions in the same reports.
Please reference article KB5814 for how to use applyagg metrics.
4. Free form SQL
Similar to Pass Through Functions, customers can write freeform SQL for reports or data import workflow to explicitly utilize the approximate functions
