개요

Oracle 19c에 추가된 BITMAP_BUCKET_NUMBER, BITMAP_BIT_POSITION, BITMAP_CONSTRUCT_AGG, BITMAP_OR_AGG, BITMAP_COUNT 함수를 사용하면 숫자 값에 대해 비트맵 기반의 COUNT (DISTINCT) 집계를 수행할 수 있습니다.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 NUMBER, c3 NUMBER);
 
INSERT INTO t1 VALUES (1, 3, 1);
INSERT INTO t1 VALUES (1, 3, 2);
INSERT INTO t1 VALUES (1, 4, 2);
INSERT INTO t1 VALUES (1, 4, 3);
INSERT INTO t1 VALUES (2, 3, 3);
INSERT INTO t1 VALUES (2, 3, 3);
INSERT INTO t1 VALUES (2, 4, 4);
INSERT INTO t1 VALUES (2, 4, 4);
COMMIT;

 

기본 동작

아래 쿼리에 사용한 BITMAP_BUCKET_NUMBER 함수는 버킷 번호, BITMAP_BIT_POSITION 함수는 비트 위치를 반환합니다.

-- 2
SELECT c1, c2, c3
     , BITMAP_BUCKET_NUMBER (c3) AS bbn
     , BITMAP_BIT_POSITION (c3) AS bbp
  FROM t1;
 
C1 C2 C3 BBN BBP
-- -- -- --- ---
 1  3  1   1   0
 1  3  2   1   1
 1  4  2   1   1
 1  4  3   1   2
 2  3  3   1   2
 2  3  3   1   2
 2  4  4   1   3
 2  4  4   1   3
 
8 행이 선택되었습니다.

 

테스트를 위해 아래와 같이 t2 테이블을 생성하겠습니다. 아래 CTAS 문에 사용한 BITMAP_CONSTRUCT_AGG 함수는 비트 위치를 집계한 비트맵을 16진수 BLOB 값으로 반환합니다. 참고로 문서화되지 않았지만 BITMAP_CONSTRUCT_AGG 함수의 두 번째 인수에 RAW를 지정하면 RAW 값이 반환됩니다.

-- 3
DROP TABLE t2 PURGE;
 
CREATE TABLE t2
AS
SELECT   c1, c2
       , BITMAP_BUCKET_NUMBER (c3) AS bbn
       , BITMAP_CONSTRUCT_AGG (BITMAP_BIT_POSITION (c3)) AS bca
    -- , BITMAP_CONSTRUCT_AGG (BITMAP_BIT_POSITION (c3), 'RAW') AS bca
    FROM t1
GROUP BY c1, c2
       , BITMAP_BUCKET_NUMBER (c3);

 

아래는 t2 테이블을 조회한 결과입니다. bca 칼럼에 비트 위치를 나타내는 16진수 비트맵 값이 표시됩니다.

-- 4
SELECT * FROM t2;
 
C1 C2 BBN BCA
-- -- --- ----
 1  3   1 0003 -- 0000 0011 (1, 2)
 1  4   1 0006 -- 0000 0110 (2, 3)
 2  3   1 0004 -- 0000 0100 (3, 3)
 2  4   1 0008 -- 0000 1000 (4, 4)
 
4 행이 선택되었습니다.

 

아래 쿼리에 사용한 BITMAP_OR_AGG 함수는 비트맵 값을 OR 집계 연산한 비트맵, BITMAP_COUNT 함수는 비트맵의 1비트 개수를 반환합니다. 비트맵 OR 집계 연산을 사용하면 c1 칼럼과 c2 칼럼에 대해 각각의 COUNT(DISTINCT) 집계를 수행할 수 있습니다.

-- 5-1
SELECT   c1, c2
       , BITMAP_OR_AGG (bca) AS boa
       , BITMAP_COUNT (BITMAP_OR_AGG (bca)) AS bc
    FROM t2
GROUP BY c1, c2;
 
C1 C2 BOA  BC
-- -- ---- --
 1  3 0003  2 -- 0000 0011 (1, 2) -> 2
 1  4 0006  2 -- 0000 0110 (2, 3) -> 2
 2  3 0004  1 -- 0000 0100 (3, 3) -> 1
 2  4 0008  1 -- 0000 1000 (4, 4) -> 1
 
4 행이 선택되었습니다.
 
-- 5-2
SELECT   c1
       , BITMAP_OR_AGG (bca) AS boa
       , BITMAP_COUNT (BITMAP_OR_AGG (bca)) AS bc
    FROM t2
GROUP BY c1;
 
C1 BOA  BC
-- ---- --
 1 0007  3 -- 0000 0111 (1, 2, 3)
 2 000C  2 -- 0000 1100 (3, 4)
 
2 행이 선택되었습니다.
 
-- 5-3
SELECT   c2
       , BITMAP_OR_AGG (bca) AS boa
       , BITMAP_COUNT (BITMAP_OR_AGG (bca)) AS bc
    FROM t2
GROUP BY c2;
 
C2 BOA  BC
-- ---- --
 3 0007  3 -- 0000 0111 (1, 2, 3)
 4 000E  3 -- 0000 1110 (2, 3, 4)
 
2 행이 선택되었습니다.

 

Materialized View

Materialized View의 Query Rewrite 기능을 사용하면 원본 테이블로 COUNT (DISTINCT) 집계를 수행할 수 있습니다.

 

테스트를 위해 아래와 같이 Materialized View를 생성하겠습니다.

-- 6
DROP MATERIALIZED VIEW mv1;
 
CREATE MATERIALIZED VIEW mv1
ENABLE QUERY REWRITE
AS
SELECT   c1, c2
       , BITMAP_BUCKET_NUMBER (c3) AS bbn
       , BITMAP_CONSTRUCT_AGG (BITMAP_BIT_POSITION (c3)) AS bca
    FROM t1
GROUP BY c1, c2
       , BITMAP_BUCKET_NUMBER (c3);

 

아래 쿼리는 원본 테이블로 COUNT (DISTINCT) 집계를 수행합니다. 실행 계획에서 Materialized View가 사용된 것을 확인할 수 있습니다.

-- 7-1
SELECT   c1, c2, COUNT (DISTINCT c3) AS cn
    FROM t1
GROUP BY c1, c2;
 
C1 C2 CN
-- -- --
 1  3  2
 1  4  2
 2  3  1
 2  4  1
 
4 행이 선택되었습니다.
 
----------------------------------------
| Id  | Operation               | Name |
----------------------------------------
|   0 | SELECT STATEMENT        |      |
|   1 |  HASH GROUP BY          |      |
|   2 |   VIEW                  |      |
|   3 |    HASH GROUP BY        |      |
|   4 |     MAT_VIEW ACCESS FULL| MV1  |
----------------------------------------
 
Outline Data
-------------
      REWRITE(@"SEL$F8BD1AD3" "MV1")
      EXPAND_GSET_TO_UNION(@"SEL$1")
 
-- 7-2
SELECT   c1, COUNT (DISTINCT c3) AS cn
    FROM t1
GROUP BY c1;
 
C1 CN
-- --
 1  3
 2  2
 
2 행이 선택되었습니다.
 
-- 7-3
SELECT   c2, COUNT (DISTINCT c3) AS cn
    FROM t1
GROUP BY c2;
 
C2 CN
-- --
 3  3
 4  3
 
2 행이 선택되었습니다.

 

아래는 7-1번 쿼리에 대한 10053 트레이스의 Final query입니다. 참고로 가독성을 위해 쿼리를 포맷팅했습니다.

-- 8
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT   "gs_to_ua_view"."C1" "C1"
       , "gs_to_ua_view"."C2" "C2"
       , SUM ("gs_to_ua_view"."VW_COL_1") "CN"
    FROM (SELECT   BITMAP_COUNT (BITMAP_OR_AGG ("MV1"."BCA" /*+ LOB_BY_VALUE */)) "VW_COL_1"
                 , "MV1"."C2" "C2"
                 , "MV1"."C1" "C1"
                 , "MV1"."BBN"
              FROM "TUNA"."MV1" "MV1"
          GROUP BY "MV1"."C2"
                 , "MV1"."C1"
                 , "MV1"."BBN") "gs_to_ua_view"
GROUP BY "gs_to_ua_view"."C2"
       , "gs_to_ua_view"."C1"

 

관련 링크

저작자표시 비영리 변경금지

티스토리툴바