무한소수의 집계 순서에 따른 결과 값

무한소수는 집계 순서에 따라 결과 값이 달라질 수 있습니다. 이로 인해 데이터 마이그레이션 이후 데이터 조회 순서가 달라져 쿼리의 결과 값이 달라질 수 있습니다.

 

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

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM * 100 AS c1 FROM XMLTABLE ('1 to 12');

 

아래 쿼리의 c2 열은 c1 칼럼을 12로 나눠 일부 값이 무한소수로 반환됩니다.

-- 2
SELECT c1, c1 / 12 AS c2 FROM t1;

  C1         C2
---- ----------
 100 8.33333333 -- !
 200 16.6666667 -- !
 300         25
 400 33.3333333 -- !
 500 41.6666667 -- !
 600         50
 700 58.3333333 -- !
 800 66.6666667 -- !
 900         75
1000 83.3333333 -- !
1100 91.6666667 -- !
1200        100

12 행이 선택되었습니다.

 

아래 예제는 t1 테이블을 임의로 정렬하여 집계를 10회 반복 수행합니다. 무한소수 값이 TRUNCATE되어 수행할 때마다 c1 값이 달라지는 것을 확인할 수 있습니다.

-- 3
SET SERVEROUTPUT ON

DECLARE
    v1 NUMBER;
    v2 NUMBER;
BEGIN
    FOR i IN 1 .. 10 LOOP
        SELECT TRUNC (SUM (c1 / 12)) AS c1
             , SUM (c1 / 12) AS c2
          INTO v1
             , v2
          FROM (SELECT   *
                    FROM t1
                   WHERE ROWNUM >= 1
                ORDER BY DBMS_RANDOM.VALUE);

        DBMS_OUTPUT.PUT_LINE (v1 || ', ' || v2);
    END LOOP;
END;
/

649, 649.999999999999999999999999999999999999  -- !
650, 650
650, 650.000000000000000000000000000000000001
650, 650
649, 649.999999999999999999999999999999999999  -- !
650, 650
650, 650.000000000000000000000000000000000001
650, 650
649, 649.999999999999999999999999999999999999  -- !
650, 650

PL/SQL 처리가 정상적으로 완료되었습니다.

 

나눗셈에 의해 무한소수가 발생할 수 있는 표현식에 ROUND 함수를 사용해야 결과 값이 달라지는 현상을 방지할 수 있습니다. 아래 예제는 c1 / 12 표현식 대신 ROUND (c1 / 12, 20) 표현식을 사용합니다. 코드를 반복 수행해도 결과가 변경되지 않는 것을 확인할 수 있습니다.

-- 4
DECLARE
    v1 NUMBER;
    v2 NUMBER;
BEGIN
    FOR i IN 1 .. 10 LOOP
        SELECT TRUNC (SUM (ROUND (c1 / 12, 20))) AS c1
             , SUM (ROUND (c1 / 12, 20)) AS c2
          INTO v1
             , v2
          FROM (SELECT   *
                    FROM t1
                   WHERE ROWNUM >= 1
                ORDER BY DBMS_RANDOM.VALUE);

        DBMS_OUTPUT.PUT_LINE (v1 || ',' || v2);
    END LOOP;
END;
/
650, 650
650, 650
650, 650
650, 650
650, 650
650, 650
650, 650
650, 650
650, 650
650, 650

PL/SQL 처리가 정상적으로 완료되었습니다.