Tuesday, August 14, 2018

In the Ballpark


When 'close enough' is 'good enough', try the 12c statement APPROX_COUNT_DISTINCT. (It was actually available in 11g, but not documented.)

Neither Count(Distinct) or Approx_Count_Distinct count nulls, nor do they work on long/lob data types.

Approx_Count_Distinct beat Count(Distinct) consistently in my tests. The first query shown below ran for 3 seconds -- the second for less than one.

SELECT test_state
     , COUNT(DISTINCT test_address)
  FROM master_test
 GROUP BY test_state  ;


SELECT test_state
     , APPROX_COUNT_DISTINCT(test_address)
  FROM master_test
 GROUP BY test_state ;

No comments:

Post a Comment