18c approx_count function

View All Scripts Login to Run Script

Script Name 18c approx_count function
Description Data analysis applications heavily use aggregate functions. Approximate query processing (available since Oracle Database 12c Release 1) aims to deliver faster results for these queries. The approximate results are not identical to the exact results but they are very close. New approximate SQL functions for rank, sum and count are now available for Top-N style queries. By making use of approximate query processing, you can instantly improve the performance of existing analytic workloads and enable faster ad-hoc data exploration
Area SQL General / Analytics
Contributor Connor Mcdonald (Oracle)
Created Saturday February 17, 2018

Statement 1
```
drop table t purge
```
```
ORA-00942: table or view does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

create table t as  
select * from all_objects 
where owner in ('SYS','SYSTEM','PUBLIC','SCOTT','HR','SALES')

Table created.

Statement 3

select owner, count(*)  
from t 
group by owner 
order by 1


OWNER COUNT(*)
HR 27
PUBLIC 12249
SCOTT 4
SYS 38583
SYSTEM 6

5 rows selected.

Statement 4
For the new approx_count functions, you must have a matching approx_rank function as part of the GROUP BY definition
```
select owner, approx_count(*)  
from t 
group by owner 
order by 1
```
```
ORA-62237: Missing HAVING clause when approximate top functions are used.
```
More Details: https://docs.oracle.com/error-help/db/ora-62237

OWNER	COUNT(*)
HR	27
PUBLIC	12249
SCOTT	4
SYS	38583
SYSTEM	6

Statement 5

select owner, approx_count(*)  
from t 
group by owner 
having approx_rank(partition by owner order by approx_count(*) desc) <= 1 
order by 1


OWNER APPROX_COUNT(*)
HR 27
PUBLIC 12249
SCOTT 4
SYS 38583
SYSTEM 6

5 rows selected.

OWNER	APPROX_COUNT(*)
HR	27
PUBLIC	12249
SCOTT	4
SYS	38583
SYSTEM	6

Statement 6

select owner, approx_count(*) , approx_rank(partition by owner order by approx_count(*) desc) 
from t 
group by owner 
having approx_rank(partition by owner order by approx_count(*) desc) <= 1 
order by 1


OWNER APPROX_COUNT(*) APPROX_RANK(PARTITIONBYOWNERORDERBYAPPROX_COUNT(*)DESC)
HR 27 1
PUBLIC 12249 1
SCOTT 4 1
SYS 38583 1
SYSTEM 6 1

5 rows selected.

OWNER	APPROX_COUNT(*)	APPROX_RANK(PARTITIONBYOWNERORDERBYAPPROX_COUNT(*)DESC)
HR	27	1
PUBLIC	12249	1
SCOTT	4	1
SYS	38583	1
SYSTEM	6	1

Statement 7

You can see the benefit of the restriction when you see that the partition by clause can be a subset of the group by aggregation columns. Hence in this example, we get the top 8 ranked object types *per owner*

select owner, object_type, approx_count(*) , approx_rank(partition by owner order by approx_count(*) desc) 
from t 
group by owner, object_type 
having approx_rank(partition by owner order by approx_count(*) desc) <= 8 
order by 1


OWNER OBJECT_TYPE APPROX_COUNT(*) APPROX_RANK(PARTITIONBYOWNERORDERBYAPPROX_COUNT(*)DESC)
HR VIEW 1 3
HR TABLE 7 2
HR INDEX 19 1
PUBLIC SYNONYM 12249 1
SCOTT INDEX 2 2
SCOTT TABLE 2 1
SYS PROCEDURE 17 8
SYS TABLE 37 7
SYS FUNCTION 90 6
SYS PACKAGE 225 5
SYS TYPE 1031 4
SYS VIEW 1155 3
SYS JAVA RESOURCE 1634 2
SYS JAVA CLASS 34344 1
SYSTEM TABLE 3 2
SYSTEM INDEX 3 1

16 rows selected.

OWNER	OBJECT_TYPE	APPROX_COUNT(*)	APPROX_RANK(PARTITIONBYOWNERORDERBYAPPROX_COUNT(*)DESC)
HR	VIEW	1	3
HR	TABLE	7	2
HR	INDEX	19	1
PUBLIC	SYNONYM	12249	1
SCOTT	INDEX	2	2
SCOTT	TABLE	2	1
SYS	PROCEDURE	17	8
SYS	TABLE	37	7
SYS	FUNCTION	90	6
SYS	PACKAGE	225	5
SYS	TYPE	1031	4
SYS	VIEW	1155	3
SYS	JAVA RESOURCE	1634	2
SYS	JAVA CLASS	34344	1
SYSTEM	TABLE	3	2
SYSTEM	INDEX	3	1

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database