Oracle APPROX_COUNT_DISTINCT_DETAIL, APPROX_COUNT_DISTINCT_AGG, TO_APPROX_COUNT_DISTINCT 설명 : 오라클 함수

* 주의 : 본 함수는 Oracle Database 12C R2 이상 사용가능함

 

1. 함수의 목적
   

    Oracle APPROX_COUNT_DISTINCT_DETAIL는 Group by의 모든 디멘젼(all dimension)의 개략적인 집계를 BLOB로 반환한다.

    APPROX_COUNT_DISTINCT_AGG는 APPROX_COUNT_DISTINCT_DETAIL을 통해 생성된 상세 집계를 이용하여 source 테이블을 다시 한번 읽지 않고도 상위레벨 집계를 생성한다.

   두 함수의 리턴값은 BLOB로 반환하기 때문에 사람이 인식가능한 형태로 변환한기 위해서는 TO_APPROX_COUNT_DISTINCT 함수로 변환과정을 거쳐야 한다. (개략적이라고는 하나 거의 100% 맞다)

 

   * approximate query를 위한 함수로, 읽는 범위가 넓은 대용량 데이터베이스를 정확도보다는 속도를 중요시하는 상황에 쓰이도록 개발되었다(속도 매우 빠름, 오차허용 범위내의 정확도). 예를 들어 BI/DW등 대용량 데이터베이스에서 집계 테이블을 별도로 만들지 않고, 바로 소스 테이블을 approximate query를 사용할 수 있다. 오라클 내장 함수 중 접두어로 [APPROX_]가 붙은 함수는 approximate query를 위한 함수이다. 

  
2. 샘플을 통한 개념 퀵뷰

 

1) times 테이블과 sales를 조인하여 calendar_year, calendar_month_number, day_number_in_month로 구성된 디멘젼(dimension)의 detail 집계를 생성한다.

 

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;

 

1) daily_prod_count_mv를 사용하여 원 소스테이블인 times와 sales 테이블을 읽지 않고 year, month의 디멘전으로 구성된 상위 집계를 생성한다.

 

CREATE MATERIALIZED VIEW monthly_prod_count_mv

AS

SELECT year, month, APPROX_COUNT_DISTINCT_AGG(daily_detail) monthly_detail

FROM daily_prod_count_mv

GROUP BY year, month;

 

2) 생성된 상위집계인 monthly_prod_count_mv을 사람이 인식가능한 형태로 조회하기 위해 TO_APPROX_COUNT_DISTINCT로 monthly_detail 컬럼을 한번 더 감싼다. 

3. 사용방법 

4. 함수 PARAMETER 설명
 
[expr]

expr의 경우 BFILE, BLOB, CLOB, LONG, LONG RAW 또는 NCLOB 이외의 스칼라 데이터 유형의 컬럼을 지정할 수 있다.

 

[detail]

APPROX_COUNT_DISTINCT_DETAIL로 생성된 BLOB 컬럼을 지정한다.

 

5. 다양한 샘플표현