개요
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"
관련 링크