개요

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개의 행이 선택되었습니다.
저작자표시 비영리 변경금지

티스토리툴바