[DB]02-09.Oracle DB 정규화(Normalization), 역정규화(Denormalization), 색인(Index)

1.정규화, 비정규화

정규화는 DB 설계 자체에서 데이터의 중복을 최소화하여 이상현상을 최소화하는 것이 목표이다.

이상현상으로는 아래와 같이 3가지가 있다.

삭제 이상 - 원치 않는 데이터도 같이 삭제

삽입 이상 - 원치 않는 데이터가 같이 삽입

갱신 이상 - 데이터의 일부만 갱신

정규화를 위한 기본 폼(NF, Nomal Form), 규칙같은게 있는데 아래와 같다.

1NF - 모든 항목에 값이 있어야 하며(NOT NULL), 중복열이 없어야 한다.

2NF - 개체의 속성이 한 식별자에 종속 되어야 한다.(부분 함수적 종속 제거)

3NF - X로 인해 Y가 결정 되고, Y로 인해 Z가 결정되는 경우(이행 함수 종속성)

~

4NF...

5NF...

.....

4NF부턴 이론적으로만 존재

이 NF 규칙을 봤을땐 바로 이해가지 않을 것이다.

솔직히 말을 되게 어렵게 써서 어떻게 간단하게 쓸지도 모르겠다.

고로 예제를 통해서 이해해보자.

데이터 표의 밑줄은 항목을 키로 지정했음을 표시하는것

1NF

우측 그림은 비식별관계여야 함, 잘못 표시

2NF

복합키는 NAME 항목을 보면 중복값이 많기 때문에 어쩔 수 없이 묶어 복합키를 사용했다.

하지만 쪼갤 수 있으므로 분리해서 사용할 수 있을 땐 분리하자

쉽게 말하면 복합키를 구지 사용하지 말자.

3NF

만약 두 데이터가 아래와 같이 합쳐져 있었다면

연도  우승국  감독

1998    X        X

2002    X        X

2006    X        X

2010    X        X

2014    X        X

만약 우승국이 바뀐다면 감독도 바뀌어야 한다.

고로 위와같이 분리하였으니 우승국만 바꾸면 된다.

위와같은 데이터는 항목, 컬럼이 한두개 밖에 없으니 바꾸는 작업이 별로 없을것 같지만

실제로는 컬럼이 훨씬 많이 지정되므로 바꾸지 않으면 매우 번거로운 작업이 된다.

NF에서 요구하는건 대부분 테이블 규모를 키우지 말고 되도록 쪼개라는 말이 된다.

하지만 테이블을 지나치게 쪼개면, 정규화를 하면 쿼리가 복잡해지고, 복잡해지면 성능이 저하된다.

예를들어 테이블 참조를 여러개, 만약 6개를 한다면??, 속도가 매우 느려질것.

그래도 이해가 안간다면 정규화의 예시를 구글에서 찾아보자

역정규화는 말 그대로 정규화의 반대.

쪼갠 테이블을 합치는 것이다.

2. INDEX (색인)

색인은 검색을 빠르게 하기위한 수단

기본키(Primary Key), 유니크 키(Unique Key)가 지정되면 자동으로 Index가 생성된다.

색인의 예로는 대표적으로 사전이나 책이 있다.(가나다라, ABCD~ 기준으로 나열된다.)

장점은 검색 속도가 빨라지고 시스템의 부하가 줄어든다.

단점으로는 색인 생성에 시간, 공간이 요구되고, 데이터가 삽입/갱신/삭제가 빈번히 일어나면 시스템에 부하가 걸린다.

색인은 키값을 지정할 때 자동으로 생성 되게 끔 되있는데

임의로 지정할 수도 있다. 아래의 예시를 확인해보자.

색인을 생성하는 것을 Indexing이라고 한다.

-- 1) 비트맵 인덱스

-- bit, 각 컬럼에 대해 적은 갯수의 값이 있을 경우 사용()

-- (예 : 남|여, 1|0, True|False)

-- 데이터가 어디 있는지 BIT로 표시, 속도가 빠르지만 컬럼이 추가 될 때 마다 인덱스를 다시 만든다.

-- 수정/변경/추가가 적은 데이터에 적합

-- Standard Edition 버전(유료버전)에서만 사용 가능

-- CREATE BITMAP INDEX [index name] ON table(column)

CREATE BITMAP INDEX emp_idx_deptno ON emp(deptno);

-- 위 구문은 유료 버전이 아니라면 오류가 발생한다.

-- 2) UNIQUE INDEX

-- 기본키와 유니크키 설정 시 생성되는 인덱스

-- 중복되지 않은 데이터에게만 걸 수 있는 인덱스

-- CREATE UNIQUE INDEX [index name] ON table(column)

CREATE UNIQUE INDEX emp_idx_ename ON emp(ename);

-- 위와 같이 중복된 데이터가 있을 경우 에러 발생

-- ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

-- 01452. 00000 -  "cannot CREATE UNIQUE INDEX; duplicate keys found"

-- 기본키와 비슷해서 사용할 일이 별로 없음

-- 3) NON-UNIQUE INDEX

-- 컬럼에 중복이 있어도 인덱스 생성이 가능

-- 가장 일반적으로 사용하는 INDEX 구문

-- CREATE INDEX [index name] ON table(column)

CREATE INDEX emp_idx_ename ON emp(ename);

-- 4)결합 인덱스

-- 16개 까지 컬럼을 조합하여 인덱스 생성

-- INDEX앞에 UNIQUE를 붙일 수 있다. (중복되는 컬럼 비허용)

-- CREATE INDEX [index name] ON table(column, column, ...)

CREATE INDEX emp_combi_idx ON emp(ename, job);

-- 5)인덱스 검색

-- 어떤 인덱스가 있나 검색

SELECT * FROM USER_INDEXES;

SELECT index_name, table_name, uniqueness FROM USER_INDEXES;

-- 6)인덱스 삭제

DROP INDEX EMP_IDX_ENAME;