SQL> select owner, count(distinct object_id)from ohs group by owner order by owner;
OWNER COUNT(DISTINCTOBJECT_ID) -------------------- ------------------------ APEX_040200 3421 APPQOSSYS 5 AUDSYS 12 CTXSYS 409 DBSNMP 55 DVF 19 DVSYS 292 FLOWS_FILES 13 GSMADMIN_INTERNAL 108 LBACSYS 237 MDSYS 1873 OJVMSYS 24 OLAPSYS 25 ORACLE_OCM 6 ORDDATA 292 ORDPLUGINS 10 ORDSYS 3157 OUTLN 10 PUBLIC 37028 SI_INFORMTN_SCHEMA 8 SYS 41981 SYSTEM 639 WMSYS 389 XDB 986 24 rows selected. Elapsed: 00:00:02.74
SQL> select owner,approx_count_distinct(object_id) from ohs group by owner order by owner;
OWNER APPROX_COUNT_DISTINCT(OBJECT_ID) -------------------- -------------------------------- APEX_040200 3511 APPQOSSYS 5 AUDSYS 12 CTXSYS 409 DBSNMP 55 DVF 19 DVSYS 294 FLOWS_FILES 13 GSMADMIN_INTERNAL 106 LBACSYS 237 MDSYS 1904 OJVMSYS 24 OLAPSYS 25 ORACLE_OCM 6 ORDDATA 291 ORDPLUGINS 10 ORDSYS 3178 OUTLN 10 PUBLIC 36881 SI_INFORMTN_SCHEMA 8 SYS 41933 SYSTEM 628 WMSYS 395 XDB 989 24 rows selected. Elapsed: 00:00:02.40 SQL>
SQL> select owner,count(distinct object_id) from ohs group by owner order by owner;
Elapsed: 00:00:00.00
Execution Plan ---------------------------------------------------------- Plan hash value: 3150715592 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5281K| 397M| 23250 (1)| 00:00:01 | | 1 | SORT GROUP BY | | 5281K| 397M| 23250 (1)| 00:00:01 | | 2 | VIEW | VM_NWVW_1 | 5281K| 397M| 23250 (1)| 00:00:01 | | 3 | HASH GROUP BY | | 5281K| 397M| 23250 (1)| 00:00:01 | | 4 | TABLE ACCESS FULL| OHS | 5281K| 397M| 23115 (1)| 00:00:01 | ---------------------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2)
SQL> select owner,approx_count_distinct(object_id) from ohs group by owner order by owner;
Elapsed: 00:00:00.00
Execution Plan ---------------------------------------------------------- Plan hash value: 424073367 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5281K| 397M| 23250 (1)| 00:00:01 | | 1 | SORT GROUP BY APPROX| | 5281K| 397M| 23250 (1)| 00:00:01 | | 2 | TABLE ACCESS FULL | OHS | 5281K| 397M| 23115 (1)| 00:00:01 | ----------------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2) SQL>
Reference
http://docs.oracle.com/database/121/NEWFT/chapter12102.htm#NEWFT003
http://algo.inria.fr/flajolet/Publications/FlFuGaMe07.pdf
http://www.ic.unicamp.br/~celio/peer2peer/math/bitmap-algorithms/durand03loglog.pdf
