by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

14S_숫자 타입




숫자타입

오라클 10g이상에서 숫자를 저장히가 위한 세 가지 데이터타입을 지원한다.

NUMBER 오라클 NUMBER 타입은 38자리 정밀도까지 숫자를 저장 할 수 있다.
기본적 데이터 형식은 묶음 10진수
데이터타입은 길이가 0~22바이트인 가변길이 형식
사용되는 가장 일반적인 형식
BINARY_FLOAT IEEE에서 정의한 단정도 부동소수점 숫자다.
디스크에서 5바이트 저장공간을 차지
BINARY_DOUBLE IEEE에서 정의한 배정도 부동소수점 숫자다.
디스크에서 9바이트 저장공간을 차지

-- 숫자 타입
SQL> DROP TABLE T;

테이블이 삭제되었습니다.

SQL>
SQL> CREATE TABLE T
  2  (
  3     NUM_COL NUMBER,
  4     FLOAT_COL BINARY_FLOAT,
  5     DBL_COL BINARY_DOUBLE
  6  );

테이블이 생성되었습니다.


SQL> INSERT INTO T (NUM_COL, FLOAT_COL, DBL_COL)
  2  VALUES(1234567890.0987654321
  3        ,1234567890.0987654321
  4        ,1234567890.0987654321);

1 개의 행이 만들어졌습니다.

SQL> SET NUMFORMAT 9999999999.9999999999
SQL>
SQL> SELECT * FROM T;

               NUM_COL              FLOAT_COL                DBL_COL
---------------------- ---------------------- ----------------------
 1234567890.0987654321  1234567940.0000000000  1234567890.0987654000


-- NUM : 정확한 숫자보존
-- BINARU_FLOAT : 7자리까지 정확하게 표현
-- BINARY_DOUBLE : 17자리까지 정확하게 표현

SQL> DELETE FROM T;

0 행이 삭제되었습니다.

SQL> INSERT INTO T (NUM_COL, FLOAT_COL, DBL_COL)
  2  VALUES(9999999999.9999999999
  3        ,9999999999.9999999999
  4        ,9999999999.9999999999);

1 개의 행이 만들어졌습니다.

SQL>
SQL>
SQL> SELECT * FROM T;

   NUM_COL  FLOAT_COL    DBL_COL
---------- ---------- ----------
1.0000E+10   1.0E+010   1.0E+010

SQL> SET NUMFORMAT 9999999999.9999999999
SQL> SELECT * FROM T;

               NUM_COL              FLOAT_COL                DBL_COL
---------------------- ---------------------- ----------------------
 9999999999.9999999999 ###################### ######################

-- NUM_COL: 정확한 표현
 -- FLOAT_COL : 정확하지 않은 표현
 -- DBL_COL : 정확하지 않은 표현


SQL> DELETE FROM T;

1 행이 삭제되었습니다.

SQL> INSERT INTO T (NUM_COL) VALUES (123*1e20+123*1e-20);

1 개의 행이 만들어졌습니다.


SQL> SET NUMFORMAT 999999999999999999999999.999999999999999999999999
SQL> SELECT NUM_COL, 123*1e20, 123*1e-20 FROM T;

                                           NUM_COL
--------------------------------------------------
                                          123*1E20
--------------------------------------------------
                                         123*1E-20
--------------------------------------------------
  12300000000000000000000.000000000000000000000000
  12300000000000000000000.000000000000000000000000
                         .000000000000000001230000


-- 매우 큰숫자(123*1E20), 매우 작은 숫자(123*1E-20) 시 38자리 정밀도를 초과하므로 정확한 표현하지못함

SQL> SELECT NUM_COL FROM T
  2  WHERE NUM_COL = 123*1e20;

                                           NUM_COL
--------------------------------------------------
  12300000000000000000000.000000000000000000000000

-- NUM_COL의 값은 123*1e20이지, 입력값(123*1e20+123*1e-20)은 아니다.

NUMBER 타입 문법과 사용법

NUMBER(P,S)

  • PRECISION(전체자릿수) : 1~38 범위의 값을 표현한다.
  • SCALE(소수점 자리수) : 기본값은 0, 유효값은 -84~127이다
SQL> DROP TABLE T;

테이블이 삭제되었습니다.

SQL> CREATE TABLE T(NUM_COL NUMBER(5,0));

테이블이 생성되었습니다.

SQL>
SQL> INSERT INTO T(NUM_COL) VALUES(12345);

1 개의 행이 만들어졌습니다.

SQL> INSERT INTO T(NUM_COL) VALUES(123456);
INSERT INTO T(NUM_COL) VALUES(123456)
                              *
1행에 오류:
ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다.

-- 데이터 무결성 강화하기 위해 PERCISION을 사용가능. 
-- 5자리보다 큰 숫자는 허용되지 않음.

SQL> DROP TABLE T;

테이블이 삭제되었습니다.

SQL> CREATE TABLE T(MSG VARCHAR2(10), NUM_COL NUMBER(5,2));

테이블이 생성되었습니다.

SQL>
SQL> INSERT INTO T(MSG,NUM_COL) VALUES('123.45',123.45);

1 개의 행이 만들어졌습니다.

SQL> INSERT INTO T(MSG,NUM_COL) VALUES('123.456',123.456);

1 개의 행이 만들어졌습니다.


SQL> SELECT * FROM T;

MSG                                                             NUM_COL
-------------------- --------------------------------------------------
123.45                                     123.450000000000000000000000
123.456                                    123.460000000000000000000000

SQL> SET NUMFORMAT 0
SQL> SELECT * FROM T;

MSG                  NUM_COL
-------------------- -------
123.45                    ##
123.456                   ##

SQL> SET NUMFORMAT 9999999999.99
SQL> SELECT * FROM T;

MSG                         NUM_COL
-------------------- --------------
123.45                       123.45
123.456                      123.46


-- 숫자 123.456은 5자리보다 크지만 입력된다.
-- 근데 왜 45가 아니라 46이지???

MSG                         NUM_COL
-------------------- --------------
123.410                      123.41
123.429                      123.43
123.438                      123.44
123.447                      123.45
123.456                      123.46
123.465                      123.47
123.474                      123.47
123.483                      123.48
123.492                      123.49
123.401                      123.40

10 개의 행이 선택되었습니다.

-- 형태의 데이타를 확인결과 .000 자리의 숫자를 반올림 처리하는걸 확인 할 수 있다.


SQL> INSERT INTO T(MSG, NUM_COL) VALUES('1234',1234);
INSERT INTO T(MSG, NUM_COL) VALUES('1234',1234)
                                          *
1행에 오류:
ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다.

-- 숫자 1234.00은 전체 5자리 보다 크다.
-- SCALE를 2로 지정하였으므로 십진수자리는 3자리이다.


SQL> DROP TABLE T;

테이블이 삭제되었습니다.

SQL> CREATE TABLE T(MSG VARCHAR2(10), NUM_COL NUMBER(5,-2));

테이블이 생성되었습니다.

SQL> INSERT INTO T(MSG, NUM_COL) VALUES('123.45', 123.45);

1 개의 행이 만들어졌습니다.

SQL> INSERT INTO T(MSG, NUM_COL) VALUES('123.456', 123.456);

1 개의 행이 만들어졌습니다.

SQL> SELECT * FROM T;

MSG                         NUM_COL
-------------------- --------------
123.45                       100.00
123.456                      100.00

-- 두 숫자 모두 100으로 반올림되었다.
-- 5자리 PRECISION이지만 10진수 7자리 (10의 자리까지0)까지


SQL> INSERT INTO T(MSG,NUM_COL) VALUES('1234567',1234567);

1 개의 행이 만들어졌습니다.

SQL> SELECT * FROM T;

MSG                         NUM_COL
-------------------- --------------
123.45                       100.00
123.456                      100.00
1234567                  1234600.00

SQL> SET NUMFORMAT 999999999999
SQL> SELECT * FROM T;

MSG                        NUM_COL
-------------------- -------------
123.45                         100
123.456                        100
1234567                    1234600


SQL> INSERT INTO T(MSG, NUM_COL) VALUES('12345678',12345678);
INSERT INTO T(MSG, NUM_COL) VALUES('12345678',12345678)
                                              *
1행에 오류:
ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다.

-- PRECISION은 반올림 후 얼마나 많은 자릿수를 허용하지는지 나타내고
-- SCALE은 어느 자리까지 반올림 할것인지 결정한다.
-- PRECISION은 무결성을 검토하고, SCALE은 값의 표현과 관련 있다.


SQL> DROP TABLE T;

테이블이 삭제되었습니다.

SQL> CREATE TABLE T(X NUMBER, Y NUMBER);

테이블이 생성되었습니다.

SQL>
SQL> INSERT INTO T(X)
  2  SELECT TO_NUMBER(RPAD('9',ROWNUM*2,'9'))
  3  FROM ALL_OBJECTS
  4  WHERE ROWNUM <= 14;

14 개의 행이 만들어졌습니다.

SQL> UPDATE T SET Y = X+1;

14 행이 갱신되었습니다.

SQL> SET NUMFORMAT 999999999999999999999999999999
SQL> COLUMN V1 FORMAT 99
SQL> COLUMN V2 FORMAT 99
SQL> SELECT X,Y,VSIZE(X) V1, VSIZE(Y) V2
  2  FROM T
  3  ORDER BY X;

                              X                               Y  V1  V2
------------------------------- ------------------------------- --- ---
                             99                             100   2   2
                           9999                           10000   3   2
                         999999                         1000000   4   2
                       99999999                       100000000   5   2
                     9999999999                     10000000000   6   2
                   999999999999                   1000000000000   7   2
                 99999999999999                 100000000000000   8   2
               9999999999999999               10000000000000000   9   2
             999999999999999999             1000000000000000000  10   2
           99999999999999999999           100000000000000000000  11   2
         9999999999999999999999         10000000000000000000000  12   2

                              X                               Y  V1  V2
------------------------------- ------------------------------- --- ---
       999999999999999999999999       1000000000000000000000000  13   2
     99999999999999999999999999     100000000000000000000000000  14   2
   9999999999999999999999999999   10000000000000000000000000000  15   2

14 개의 행이 선택되었습니다.

-- NUMBER 타입은 가변길이 데이터 0~22바이트 저장공간은 차지
-- 오라클 NUMBER 타입은 가변 길이 문자열과 유사하다.
-- 두 개의 유효 숫자는 1바이트의 저장공간을 차지한다.
-- 그러나 X보다 1이 더 큰 Y 컬럼은 값에 상관없이 모두 2바이트를 차지한다.

BINARY_FLOAT/BINARY_DOUBLE 타입 문법과 사용법

  • 오라클10g이후에 새로운 숫자 타입 2가지를 지원한다(IEEE 표준 부동소수점)
SQL> SELECT TO_CHAR(0.3f+0.1f, '0.99999999999999') FROM DUAL;

TO_CHAR(0.3F+0.1F,'0.9999999999999'
----------------------------------
 0.40000000600000

-- 0.3 + 0.1을 더하면 0.4이자만 부동소수점에서는 0.4보다 조금 큰 값이다.
-- 부동소수점 IEEE 숫자 방식에서 나타나는 현상

Non-native 숫자 타입

  • NUMERIC(p,s) : NUMBER(p,s) 지정, p가 생각되면 기본 38
  • DECIMAL(p,s), DEC(p,s) : NUMBER(p,s)지정, p가 생각되면 기본 38
  • INTEGER 또는 INT : NUMBER(38) 지정
  • SMALLINT : NUMBER(38) 지정
  • FLOAT(p) : NUMBER 타입 지정
  • DOUBLE PRECISION : NUMBER 타입 지정
  • REAL : NUMBER 타입 지정

성능 고려사항


SQL> DROP TABLE T;

테이블이 삭제되었습니다.

SQL> CREATE TABLE T
  2  (
  3     NUM_TYPE NUMBER,
  4     FLOAT_TYPE BINARY_FLOAT,
  5     DOUBLE_TYPE BINARY_DOUBLE
  6  )
  7  ;

테이블이 생성되었습니다.


SQL> INSERT /*+ APPEND */ INTO T
  2  SELECT ROWNUM , ROWNUM, ROWNUM
  3  FROM ALL_OBJECTS;

71578 개의 행이 만들어졌습니다.


SELECT SUM(LN(NUM_TYPE)) FROM T;

call         count         cpu    elapsed        disk       query    current        rows
------- ----------  ---------- ---------- ----------- ----------- ----------  ----------
Parse            1        0.00       0.00           0           0          0           0
Execute          1        0.00       0.00           0           0          0           0
Fetch            2        1.25       1.26           0         242          0           1
------- ----------  ---------- ---------- ----------- ----------- ----------  ----------
Total            4        1.25       1.26           0         242          0           1


SELECT SUM(LN(FLOAT_TYPE)) FROM T;

call         count         cpu    elapsed        disk       query    current        rows
------- ----------  ---------- ---------- ----------- ----------- ----------  ----------
Parse            1        0.00       0.00           0           0          0           0
Execute          1        0.00       0.00           0           0          0           0
Fetch            2        0.00       0.02           0         241          0           1
------- ----------  ---------- ---------- ----------- ----------- ----------  ----------
Total            4        0.00       0.02           0         241          0           1


SELECT SUM(LN(DOUBLE_TYPE)) FROM T;

call         count         cpu    elapsed        disk       query    current        rows
------- ----------  ---------- ---------- ----------- ----------- ----------  ----------
Parse            1        0.00       0.00           0           0          0           0
Execute          1        0.00       0.00           0           0          0           0
Fetch            2        0.02       0.02           0         241          0           1
------- ----------  ---------- ---------- ----------- ----------- ----------  ----------
Total            4        0.02       0.02           0         241          0           1

-- NUMBER타입은 부동소수점 타입보다 CPU시간을 더 많이 사용 한것을 볼 수 있다.

SQL> SET NUMFORMAT 999999.9999999999999999
SQL>
SQL> SELECT SUM(LN(NUM_TYPE)) FROM T;

       SUM(LN(NUM_TYPE))
------------------------
 728566.2621548182059859

SQL>
SQL> SELECT SUM(LN(DOUBLE_TYPE)) FROM T;

    SUM(LN(DOUBLE_TYPE))
------------------------
 728566.2621548145500000

 -- 부동소수점 숫자는 6에서 13자리 정밀도를 가진, 숫자의 근삿값이다.
 -- NUMBER타입의 결과는 부동소수점보다 더 정확하다. 
 -- 데이터 마이닝이나 과학 데이터의 복잡한 수학적인 분석을 수행시 정확도를 일부 포기하고 빠른 수행을 위해 사용


 SELECT SUM(CAST(NUM_TYPE AS BINARY_DOUBLE)) FROM T;

 call         count         cpu    elapsed        disk       query    current        rows
------- ----------  ---------- ---------- ----------- ----------- ----------  ----------
Parse            1        0.00       0.00           0           0          0           0
Execute          1        0.00       0.00           0           0          0           0
Fetch            2        0.06       0.05           0         241          0           1
------- ----------  ---------- ---------- ----------- ----------- ----------  ----------
Total            4        0.06       0.05           0         241          0           1

-- CAST 함수를 사용하여 부동소수점으로 변환하여 NUMBER타입도 부동소수점의 성능을 낼 수 있다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.