거래처와 우리 회사의 직원 DB화를 한다고 하자
그리고 기본적인 거래처의 정보를 아래와 같다고 하자
cpname (거래처)
cpaddr (거래처 주소)
cptel (거래처 전화)
ename (거래처 직원)
jik (직원 직급)
mphone (직원 연락처)
email (직원 e-mail)
그리고 이 정보를 직관적인 테이블로 만들어 보자
# 잠깐! 전화번호등을 입력할 때, 문자형을 써야 하는가 아니면 숫자형을 사용해야 하는가?
오라클의 숫자형은 NUMBER형을 쓴다. 별도의 int나 double등이 존재하지 않는다.
선언 방식 - NUMBER(5) : -99999~99999, NUMBER(5,1) : -9999.9 ~ 9999.9
우선 같은 테이터를 각기 다른 형의 컬럼으로 가지는 테이블을 만들어 보자
CREATE TABLE test1(
hakbun1 NUMBER(7),
hakbun2 VARCHAR2(7)
);
그리고 같은 데이터를 넣었을때, 결과값을 확인해 보자.
INSERT into test1 values (9812345,'9812345');
INSERT into test1 values (0812345,'0812345');
SELECT * FROM test1;
그러므로 가장 앞자리가 0이 올 수 있을 경우, 이를 방지하기 위하여 문자형을 사용한다.
--거래처 직원의 테이블
CREATE TABLE companyemp (
cpname VARCHAR2(20),
cpaddr VARCHAR2(100),
cptel VARCHAR2(15),
ename VARCHAR2(10),
jik VARCHAR2(10),
mphone VARCHAR(15),
email VARCHAR(30)
); tablespace users;
-- tablespace 테이블의 존재하는 영역을 지정.추후 설명 MS-SQL의 on GroupName 과 같다
INSERT into companyemp
values('LG','서울 영등포구 여의도동','02-345-6789','임경민','과장','010-555-8888','lkm@naver.com');
INSERT into companyemp
values('SK','서울 강남구 대치동','02-777-1234','이주홍','과장','011-333-7777','ljh@naver.com');
INSERT into companyemp
values('LG','서울 영등포구 여의도동','02-345-6789','박사은','대리','019-555-8888','pse@naver.com');
INSERT into companyemp
values('LG','부산 동래구 사직동','051-222-3333','강진구','대리','010-999-1234','kjk@naver.com');
INSERT into companyemp
values('LG','서울 영등포구 여의도동','02-345-6789','김현우','부장','010-222-8989','khw@naver.com');
COMMIT;
--Oracle은 수동 Commit임! 잊지 말것
SELECT * FROM companyemp;
이는 잘못 설계된 테이블이다. 왜냐하면, 회사의 정보가 바뀔 시 (이사, 전화번호 변경 등), 그에 따른 정보의 수정이 너무 많이 일어나 자원이 부족해질 수 있다. 만약, 여의도의 LG에 근무하는 직원의 수가 100만이라고 하자. 이 때, 여의도의 LG가 경기도 성남시 분당구로 이사한다면 100만명에 대해 cpaddr, cptel 에 대한 변경이 요구된다.
UPDATE companyemp SET cpaddr='경기 성남시 분당구', cptel = '031-345-0987'
WHERE cpname ='LG' AND cpaddr = '서울 영등포구 여의도동';
이와 같은 경우 Memory에 Load되는 1행의 크기는 20+100+15+10+10+15+30 = 200byte이며 100만명의 정보를 올릴시 200*1,000,000 = 200MB의 크기를 RAM에 올려야 한다. 그러므로 Memory의 손실이 크며, 이는 서버의 성능이 현저히 떨어지게 한다.
이를 정규화를 통하여 자원의 필요없는 소비를 막을 수 있다. column의 중복성을 기준으로 일정 법칙에 따라 분리하면, 중복된 데이터를 제거함으로써 Memorydp Load되는 크기를 줄이므로 Oracle Server의 성능을 향상시켜준다. 이는 크게 제 1 정규화, 제 2 정규화, 제 3 정규화, 제 4 정규화 그리고 역졍규화로 나눠진다.
#제 1 정규화
어떤 Entity(개체, TABLE)에서 속성(Column)값들이 반복되어 나온다면, 반복되어지는 속성값들의 속성을 분리해서 새로운 Entity를 생성한다. Parent Table의 Identity Column(Primary Key)은 반드시 Child Table의 Foreign Key Column으로 전이가 되어지며, Parent : Child 관계는 1:N 관계가 되어진다.
-- Parent Table
CREATE TABLE tbl_company(
cpno NUMBER(5) NOT NULL ,
cpname VARCHAR2(20),
cpaddr VARCHAR2(100),
cptel VARCHAR2(15),
constraint tbl_company_cpno_pk primary key(cpno)
) tablespace users;
Primary Key는 참조를 받기 위한 column으로 반드시 고유 값(Primary key나 Unique Key)을 가져야 한다. Primary Key를 설정 할 시 not null이 자동으로 설정 되나, 위의 테이블에서 Primary Key를 설정함에도 불구하고 NOT NULL을 명시하는 이유는 복사를 하게 될 때에 Colum의 속성이 틀려지기 때문이다. - 다음 예제 참조
Unique Key와 Primary Key의 차이점은 NULL의 허용여부가 가능함과 불가능함이 있으며 Unique Key의 Index 는 Non-Cluster Index이며, Primary Key는 Cluster Index이다. 그러므로 이에 대한 이용은 상황에 따라 사용이 틀려진다. index와 cluster 관계를 따져서 상황에 따라 사용해야 한다.
※ 주의 : 오라클에선 Default 가 제약조건이 아니고, NOT NULL이 제약조건이며, 반면 MS-SQL에선 Default가 제약조건이고, NOT NULL이 제약조건이 아니다.
-- NOT NULL 의 복사 예제
CREATE TABLE tbl_company2(
cpno NUMBER(5) ,
cpname VARCHAR2(20),
cpaddr VARCHAR2(100),
cptel VARCHAR2(15),
constraint tbl_company2_cpno_pk primary key(cpno)
) tablespace users;
DESCRIBE tbl_company;
DESC tbl_company2;
처음 생성한 tbl_company Table과 같이 Primary Key와 NOT NULL을 설정한 Table과 Primary Key만을 설정하여 생성한 방금 생성한 tbl_company2 Table을 비교하면 위와 같다. 하지만 이를 복사하여 보면 Column의 속성이 달라짐을 볼 수 있다.
CREATE TABLE tbl_company_copy
AS
SELECT * FROM tbl_company;
CREATE TABLE tbl_company2_copy
AS
SELECT * FROM tbl_company2;
DESC tbl_company_copy;
DESC tbl_company2_copy;
처음 생성했던 테이블에 값을 넣어보자.
INSERT into tbl_company values (1,'LG','서울 영등포구 여의도동','02-345-6789');
INSERT into tbl_company values (2,'SK','서울 강남구 대치동','02-777-1234');
INSERT into tbl_company values (3,'LG','부산 동래구 사직동','051-222-3333');
COMMIT;
SELECT * FROM tbl_company;
-- Child Table
CREATE TABLE tbl_emp(
empno NUMBER(5) NOT NULL,
ename VARCHAR2(10) NOT NULL,
jik VARCHAR2(10) NOT NULL ,
mphone VARCHAR2(15),
email VARCHAR2(30),
cpno NUMBER(5) NOT NULL,
constraint tbl_emp_empno_pk primary key(empno),
constraint tbl_emp_cpno_fk foreign key(cpno) references tbl_company(cpno)
)tablespace users;
위의 테이블은 직원들의 정보를 나타내며 cpno은 회사에 대한 정보를 가진다.
※ 주의 : Oracle에서 MS-SQL과 달리 ON UPDATE cascade는 존재하지 않는다. 그러므로 이는 tigger로 직접 생성해서 사용해야 하며, 또한 ON DELETE Cascade는 1:1관계에서만 사용하도록 주의해야 한다.
INSERT into tbl_emp values (1001,'임경민','과장','010-555-8888','lkm@naver.com',1);
INSERT into tbl_emp values (1002,'이주홍','과장','011-333-7777','ljh@naver.com',2);
INSERT into tbl_emp values (1003,'박사은','대리','019-555-8888','pse@naver.com',1);
INSERT into tbl_emp values (1004,'강진구','대리','010-999-1234','kjk@naver.com',3);
INSERT into tbl_emp values (1005,'김현우','부장','010-222-8989','khw@naver.com',1);
COMMIT;
SELECT * FROM tbl_emp;
이를 원래의 Data를 가져오기 위해서는 JOIN을 사용하면 된다.
SELECT C.cpname, C.cpaddr,C.cptel,E.ename,E.jik,E.MPHONE,E.EMAIL
FROM tbl_emp E JOIN tbl_company C
ON E.CPNO = C.CPNO;
이렇게 만들어진 Table에서 Company에 대한 정보가 수정되었다면 tbl_company Table만을 수정함으로써 Memory의 점유를 줄일 수 있다.
UPDATE tbl_company
SET cpaddr ='경기도 성남시 분당구', cptel = '031-345-0987'
WHERE cpno =1;
SELECT C.cpname, C.cpaddr,C.cptel,E.ename,E.jik,E.MPHONE,E.EMAIL
FROM tbl_emp E JOIN tbl_company C
ON E.CPNO = C.CPNO;
COMMIT;
Table 당 생성할 수 있는 Primary Key의 최대 갯수는 1개이나, 그 Primary Key를 이루는 Column의 갯수는 복수일 수도 있다. 이는 각각 Single Primary Key, Composite Primary Key라고 부른다.
"주문" 테이블을 생성할 때 예제
--------------------------------
제품 코드, 사용자 ID, 주문일자, 주문량
--------------------------------
=========================
P.K
--------------------------------
주문번호, 제품 코드, 사용자 ID, 주문일자, 주문량
--------------------------------
========
P.K
만일, 제품 코드를 Primary Key로 한다면 같은 제품을 주문할 수 없는 경우가 생기고, 사용자 ID를 Primary Key로 잡으면 한번 주문한 사람은 다시 주문할 수 없다. 이를 피하기 위해서는 같은 값을 가지지 않도록 위의 경우 중 첫번째 처럼 여러 Key를 조합하여 사용하는 Composite Primary Key를 사용할 수 있다. 또는 주문마다 별도의 주문번호를 할당하여, 그에 종속적으로 다른 값을 가지도록 Single Primary Key를 설정할 수도 있다.
※ Oracle의 시간 함수
- MS-SQL에서 현재 시간을 가지기 위해서는 GETDATE() 함수를 사용했으나 Oracle에서는 SYSDATE와 LOCALTIMESTAMP라는 두가지 값을 가져 올 수 있다.
SELECT SYSDATE, LOCALTIMESTAMP FROM dual;
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),
LOCALTIMESTAMP
FROM DUAL;
SQL Gate 프로그램 상 결과로만 볼때에는 다른 점을 느끼지 못하나, 실상은 그렇치 않다. 이를 CMD창에서 SqlPlus를 이용하여 비교하여 보면 확실하게 알 수 있다.
이는 MS-SQL에서의 SMALLDATETIME형과 DATETIME형의 차이와 동일하다.
#제 2 정규화
테이블의 모든 속성이 식별자에 직접적으로 의존적이도록 분리한다. 즉, 복합키를 가진 식별자에 대한 경우, 복합키 중 일부에만 종속적인 속성이 있을 시 이를 분리한다.
위의 주문 테이블의 제품 코드, 사용자 ID, 주문일자, 주문량는 주문량을 제외한 3가지 값을 식별자로 동시에 가진다. 이때 제품코드는 그에 따른 제품의 정보(이름, 제원, 식별번호, 무게 등)이 있을 것이고, 사용자 ID는 그에 따른 사용자의 정보(사용자이름, 암호, 성별, 생년월일, 연락처 등)이 있을 것이다. 이처럼 전체적인 속성에 의존하지 않는 경우에는 별도로 분리할 수 있다.
#제 3 정규화
제 2 정규화에서 식별자에 종속된 속성을 분리하였다면, 제 3 정규화에서는 식별자가 아닌 다른 속성에 종속된 경우, 이를 분리하여 낸다.
#제 4 정규화
의존적인 N:N 관계를 제거한다. 상호 의존적인 테이블을 합하여 복합식별자로하는 테이블을 별도로 생성하여 1:N관계로 변경하도록 한다.
회원(id,name…)과 상품(pid,pname,price…)의 테이블이 있고, 회원 테이블의 값이 (m1,주홍), (m2,사은), (m3,경민)이며 상품 테이블의 값이 (p1,ㅇㅇ),(p2,ㅋㅋ),(p3,ㅎㅎ) 라 할 때,
M1이 p1과 p2등 여러 개에 대해 주문할 수 있고, P1이 M1과 M2등 여러 사람에 의해 주문 될 수 있다. 이러한 관계를 N:N관계라 하며, 실제로는 존재하지 않도록 하고 논리적으로만 존재하도록 하며 이를 1:N관계로 나누어야 한다.
주문 테이블을 만들고 그에 필요한 DATA가 ID, PID, DATE, QTY라 할 때, ID,PID,DATE를 PRIMARY KEY로 사용할 수 있다. 이 경우 INDEX를 3개의 값으로 사용하므로 전체적 용량이 커진다. 이를 보완하기 위해, Ordnum이란 추가적인 COLUMN을 넣고 (ordnum, id, pid, date, qty)의 형태로 할 경우에는 INDEX가 단일이므로 메모리가 적게 소요 된다. 이 두가지 경우에는 속도와 데이터의 크기를 장단점을 서로 가지고 있는데, 이는 참조하는 비율과 용도에 따라 구분해서 처리해야 한다.
#역 정규화
부서와 사원의 관계에서 각각의 테이블이
부서 (부서번호, 부서명, 위치)
10 연구 신림
20 총무 당산
사원 (사번, 이름, 직급, 부서번호)
1001 현우 과장 10
1002 영훈 팀장 20
의 형식을 가진다고 하자.
부서의 총 개수는 3개고 사원이 100만개라고 할 때 – 두 테이블의 사이즈가 큰 차이를 가질때 -와 부서의 총 개수가 80만이고 사원이 100만일 때 – 두테이블의 사이즈가 비슷할 때 - 역 정규화의 여부가 성능의 차이를 가져온다.
부서명은 부서테이블에 포함되어 있으므로 사원과 Join하여야 한다. Join을 하게 되면 사용하지 않는 위치 정보등도 가저 온다. 이를 사원에 부서명이라는 컬럼을 추가해 주면, 3정규화를 위반 - 부서번호에 대한 부서명이 종속되므로 식별자가 아닌 것에 대한 종속 - 하게 되나, 사용하게 되는 메모리 양은 줄어들게 된다. 이를 역정규화 라고 한다. 이는 환경에 따라 다르다 전자의 경우라면 join하는 것이 유리하나 후자의 경우에는 양측의 값이 많아 요구되는 정보의 양이 커지므로 역정규화가 유리하다. 각 컬럼의 사이즈가 10이라 할 때 전자의 경우 90바이트와 100MB의 Join이다 이때 역정규화 된 크기는 110MB이다. 후자의 경우 부서의 크기는 24MB이고 사원이 100MB일 때 이때 크기는 110이 더 작다 그러므로 수가 많을 경우에는 추가하는 것이 더 유리하다.
# 효율성 향상 방법
1) 자주 참조되는 id와 password의 경우, 별도의 테이블로 구성하여 메모리 용량을 아낄 수 있다.
2) MS-SQL에서는 PK에 따른 Cluster Index가 생성되어 자동 Sort되나 이 경우 Index 정렬에 따른 Insert 속도가 느려진다. 반면 Oracle은 별도의 Index가 존재하지 않는다. 대신 Not null을 할당 시 Unique 제약이 되고, Index가 생성되며, Data page로 가지 않고 Index 만으로 조회하여 메모리 사용이 적고 속도가 빠르다
3) 추측이 가능한 범위의 Value의 경우는 Code화 하여, 그 안에서 선택하도록 처리하는 것이 Data 입력과 검색에 편리하다. 예) 가족 관계 : 1 – 아버지 2- 어머니 3 – 형제 4 – 누이 ……..
댓글 없음:
댓글 쓰기