개요
Oracle 21c에 비트 값을 집계 연산할 수 있는 BIT_AND_AGG, BIT_OR_AGG, BIT_XOR_AGG 함수가 추가되었습니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 NUMBER, c3 NUMBER);
기본 함수
비트 값과 관련하여 기본적으로 BIN_TO_NUM, BITAND 함수를 사용할 수 있습니다. BIN_TO_NUM 함수는 비트 벡터에 해당하는 숫자 값을 반환합니다. BITAND 함수는 두 숫자 값의 비트 AND 결과를 숫자 값으로 반환합니다.
아래와 같이 BIN_TO_NUM 함수를 사용하여 데이터를 삽입하겠습니다.
-- 2
INSERT INTO t1 VALUES (1, 1, BIN_TO_NUM (0, 0, 0, 0, 1));
INSERT INTO t1 VALUES (2, 1, BIN_TO_NUM (0, 0, 0, 1, 0));
INSERT INTO t1 VALUES (3, 2, BIN_TO_NUM (0, 0, 1, 0, 0));
INSERT INTO t1 VALUES (4, 2, BIN_TO_NUM (0, 1, 0, 0, 0));
INSERT INTO t1 VALUES (5, 3, BIN_TO_NUM (1, 0, 0, 0, 0));
INSERT INTO t1 VALUES (6, 3, BIN_TO_NUM (1, 0, 1, 0, 1));
COMMIT;
아래 쿼리는 BITAND 함수로 c3 값의 비트 벡터(b1 ~ b5)를 조회합니다.
-- 3
SELECT c1, c2, c3
, LEAST (BITAND (c3, 1), 1) AS ba1 -- 1
, LEAST (BITAND (c3, 2), 1) AS ba2 -- 2
, LEAST (BITAND (c3, 4), 1) AS ba3 -- 4
, LEAST (BITAND (c3, 8), 1) AS ba4 -- 8
, LEAST (BITAND (c3, 16), 1) AS ba5 -- 16
FROM t1;
C1 C2 C3 BA1 BA2 BA3 BA4 BA5
-- -- -- --- --- --- --- ---
1 1 1 1 0 0 0 0
2 1 2 0 1 0 0 0
3 2 4 0 0 1 0 0
4 2 8 0 0 0 1 0
5 3 16 0 0 0 0 1
6 3 21 1 0 1 0 1
6 행이 선택되었습니다.
아래 쿼리는 BITAND 함수로 비트 벡터에 해당하는 로우를 조회합니다.
-- 4-1
SELECT *
FROM t1
WHERE BITAND (c3, 4) > 0;
C1 C2 C3
-- -- --
3 2 4
6 3 21
2 행이 선택되었습니다.
-- 4-2
SELECT *
FROM t1
WHERE BITAND (c3, 1) > 0
AND BITAND (c3, 4) > 0;
C1 C2 C3
-- -- --
6 3 21
1개의 행이 선택되었습니다.
비트 값을 사용하는 이유는 다수의 여부 속성을 단일 속성으로 저장할 수 있어 저장 공간을 절약할 수 있기 때문입니다. 참고로 비트 값은 17 바이트로 100개의 여부 속성을 저장할 수 있습니다. 다만 데이터 가독성이 떨어지므로 사용자에게 노출되지 않는 데이터에 한해 사용하는 편이 바람직합니다.
-- 5
SELECT MIN (c1) AS min_val
, MAX (c1) AS max_val
, VSIZE (c2) AS bytes
, VSIZE (c2) - MAX (c1) AS saved_bytes
FROM (SELECT ROWNUM AS c1
, POWER (2, ROWNUM) AS c2
FROM XMLTABLE ('1 to 100'))
GROUP BY VSIZE (c2);
MIN_VAL MAX_VAL BYTES SAVED_BYTES
------- ------- ----- -----------
1 6 2 -4
7 13 3 -10
14 19 4 -15
20 26 5 -21
27 33 6 -27
34 39 7 -32
40 46 8 -38
47 53 9 -44
54 59 10 -49
60 66 11 -55
67 73 12 -61
74 79 13 -66
80 86 14 -72
87 93 15 -78
94 99 16 -83
100 100 17 -83
16 행이 선택되었습니다.
참고로 V$STATNAME 뷰의 class 칼럼은 비트 값을 저장하고 있습니다.
-- 6
SELECT class
, RTRIM (CASE WHEN BITAND (class, 1) > 0 THEN 'User + ' END
|| CASE WHEN BITAND (class, 2) > 0 THEN 'Redo + ' END
|| CASE WHEN BITAND (class, 4) > 0 THEN 'Enqueue + ' END
|| CASE WHEN BITAND (class, 8) > 0 THEN 'Cache + ' END
|| CASE WHEN BITAND (class, 16) > 0 THEN 'OS + ' END
|| CASE WHEN BITAND (class, 32) > 0 THEN 'RAC + ' END
|| CASE WHEN BITAND (class, 64) > 0 THEN 'SQL + ' END
|| CASE WHEN BITAND (class, 128) > 0 THEN 'Debug + ' END
|| CASE WHEN BITAND (class, 256) > 0 THEN 'Instance + ' END
, ' + ') AS class_name
, COUNT (*) AS cn
FROM v$statname
GROUP BY ROLLUP (class)
ORDER BY class;
CLASS CLASS_NAME CN
----- ---------------------- ----
1 User 181
2 Redo 69
4 Enqueue 9
8 Cache 156
32 RAC 40
33 User + RAC 3
34 Redo + RAC 1
40 Cache + RAC 77
64 SQL 210
72 Cache + SQL 15
128 Debug 782
192 SQL + Debug 2
256 Instance 27
257 User + Instance 38
258 Redo + Instance 7
264 Cache + Instance 63
288 RAC + Instance 2
296 Cache + RAC + Instance 10
320 SQL + Instance 7
322 Redo + SQL + Instance 2
384 Debug + Instance 515
2216
22 행이 선택되었습니다.
집계 함수
BIT_AND_AGG 함수는 비트 AND 집계 연산, BIT_OR_AGG 함수는 비트 OR 집계 연산, BIT_XOR_AGG 함수는 비트 XOR 집계 연산을 수행합니다.
아래 쿼리는 BIT_AND_AGG, BIT_OR_AGG, BIT_XOR_AGG 함수로 c3 값을 비트 집계 연산합니다.
-- 7
SELECT c2
, BIT_AND_AGG (c3) AS baa
, BIT_OR_AGG (c3) AS aoa
, BIT_XOR_AGG (c3) AS bxa
FROM t1
GROUP BY c2;
C2 BAA AOA BXA
-- --- --- ---
1 0 3 3
2 0 12 12
3 16 21 5
3 행이 선택되었습니다.
아래 쿼리는 BITAND 함수와 BIT_OR_AGG 함수로 비트 벡터에 해당하는 집계 결과를 조회합니다.
-- 8
SELECT c2
, BIT_OR_AGG (c3) AS baa
FROM t1
GROUP BY c2
HAVING BITAND (BIT_OR_AGG (c3), 4) > 0;
C2 BAA
-- ---
2 12
3 21
2 행이 선택되었습니다.
BIT_AND_AGG, BIT_OR_AGG, BIT_XOR_AGG 함수를 분석 함수로 사용할 수 있습니다.
-- 9
SELECT c1, c2, c3
, BIT_AND_AGG (c3) OVER (ORDER BY c1) AS baa
, BIT_OR_AGG (c3) OVER (ORDER BY c1) AS aoa
, BIT_XOR_AGG (c3) OVER (ORDER BY c1) AS bxa
FROM t1;
C1 C2 C3 BAA AOA BXA
-- -- -- --- --- ---
1 1 1 1 1 1
2 1 2 0 3 3
3 2 4 0 7 7
4 2 8 0 15 15
5 3 16 0 31 31
6 3 21 0 31 10
6 행이 선택되었습니다.
사용자 정의 함수
오라클은 비트 벡터 문자 값과 숫자 값을 상호 변환할 수 있는 빌트인 함수를 제공하지 않습니다.
아래 fnc_num_bin 함수는 비트 벡터 문자 값에 해당하는 숫자 값을 반환합니다.
-- 10
CREATE OR REPLACE FUNCTION fnc_bin_to_num (i_val IN VARCHAR2)
RETURN NUMBER
IS
PRAGMA UDF;
v_val PLS_INTEGER := 0;
begin
IF LTRIM (i_val, '01') IS NOT NULL THEN
RAISE VALUE_ERROR;
END IF;
FOR i IN 1 .. LENGTH (i_val) LOOP
v_val := (v_val * 2) + TO_NUMBER (SUBSTR (i_val, i, 1));
END LOOP;
RETURN v_val;
end;
/
아래는 fnc_bin_to_num 함수를 사용한 결과입니다.
-- 11
SELECT fnc_bin_to_num ('10101') AS c1 FROM DUAL;
C1
--
21
1개의 행이 선택되었습니다.
아래 fnc_num_bin 함수는 숫자 값에 해당하는 비트 벡터 문자 값을 반환합니다.
-- 12
CREATE OR REPLACE FUNCTION fnc_num_to_bin (i_val in number)
RETURN VARCHAR2
IS
PRAGMA UDF;
v_tmp PLS_INTEGER := i_val;
v_val VARCHAR2(4000);
BEGIN
WHILE (v_tmp > 0) LOOP
v_val := MOD (v_tmp, 2) || v_val;
v_tmp := TRUNC (v_tmp / 2);
END LOOP;
RETURN v_val;
END;
/
아래는 fnc_num_bin 함수를 사용한 결과입니다.
-- 13
SELECT fnc_num_to_bin (21) AS c1 FROM DUAL;
C1
-----
10101
1개의 행이 선택되었습니다.