전문가를 위한 오라클 데이터베이스 아키텍처 (2014년)
숫자타입
- 오라클 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타입도 부동소수점의 성능을 낼 수 있다.
"데이터베이스 스터디모임" 에서 2014년에 "전문가를 위한 오라클 데이터베이스 아키텍처
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4052
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.