Neo's Programming

PIVOT의 경우 로우 단위의 내용을 컬럼 기준으로 하여 각기 통계를 내 놓았다면

UNPIVOT의 경우 컬럼 단위의 내용을 로우 기준으로 하여 나타 낼 수 있다.

샘플은 아래와 같다.

SELECT VALUE FROM
(
    SELECT 'A' AS A1, 'B' AS B2 FROM DUAL
)
UNPIVOT
(
VALUE FOR VALUE_TYPE IN (A1, B2)
)

인덱스에는 클러스터 인덱스와 넌 클러스터 인덱스 두가지로 나뉘어진다.

클러스터 인덱스의 경우 PK를 지정하면 자동적으로 생성이 되며, 인덱스의 열을 자동으로 정렬을 한다.

즉, 기본키를 지정함과 동시에 클러스터 인덱스가 생성이 되며 그 열을 기준으로 정렬이 된다.

특성상 범위 검색에는 좋으나 삽입, 삭제, 수정에 대해서는 속도가 느리다.

넌 클러스터 인덱스는 FK를 예를 들 수가 있는데 정렬 기능이 존재하지 않는다.

특성상 범위 검색에 약하며 삽입, 삭제, 수정에 대해서는 속도가 빠르다.

서로 반대되는 특징을 지니고 있다고 보면 된다.

생성 할 시 CREATE 옆에 CLUSTERED/NONCLUSTERED 를 붙여 생성 할 수 있다.

유일한 값을 생성한다. 증가 내용이나 이런건 물론 사용자가 지정 할 수가 있다.

PRIMARY KEY와 관련되는 경우 종종 사용하는 편인데.. 난 쓸 곳이 없어서 잘 안쓰는 듯..

다음은 예제이다.

CREATE SEQUENCE seq_no

START WITH 1

INCREMENT BY 1

MAXVALUE 1000

위와 같이 하면 기본적으로 생성이 된다. 시작과 증가 정도, 맥스 값을 지정한다.

NEXTVAL을 이용하면 하나씩 증가 하여 자동으로 입력이 가능하고 CURRVAL을 이용하면 현재 시퀀스의 값을 알 수가 있다.

또한 NOMAXVALUE나 NOMINVALUE를 지정하면 제한이 풀리며 CYCLE을 이용할 경우 최대 값을 넘기면 처음부터 순환하게 되어있다. NOCYCLE은 당연히 그 반대이다.

수정할 경우 ALTER를 이용하면 된다. 삭제시에는 DROP을 이용한다.

뷰는 하나의 가상 테이블이다. 실제 데이터가 저장되는 것이 아니라 이를 통하여 데이터를 관리하면서 간단한 쿼리 작성만으로 원하는 결과를 얻을 수가 있다.

다음은 뷰의 제약 조건이다.

1. 테이블 상 NOT NULL로 만든 컬럼들이 뷰에 포함되어야한다.

2. ROWID, ROWNUM, NEXTVAL, CURRVAL등 같은 가상에 대한 참조를 한다면 INSERT 작업이 불가능하다.

3. WITH READ ONLY 옵션을 설정한 뷰에도 데이터 갱신이란 없다.

4. WITH CHECK OPTION을 설정한 뷰의 경우 조건에 해당하는 경우만 INSERT, DELETE, UPDATE가 가능하다.

다음은 뷰 생성 예제이다.

CREATE OR REPLACE VIEW v_view AS

SELECT A.ID, B.NAME

FROM t_table A, t_table_sec B

WHERE A.ID = B.ID

WITH READ ONLY를 살펴보면 SELECT만 가능한 뷰를 생성한다.

CREATE OR REPLACE VIEW v_view AS

SELECT ID, NAME

FROM t_table

WHERE VALUE = 10

WITH READ ONLY

하지만 여기에서 WITH CHECK ONLY를 적용을 한다면 VALUE = 10에 의한 조건에 의해 INSERT, DELETE, UPDATE가 가능하다.

VIEW에 대한 정보는 USER_VIEW를 조회하면 확인이 가능하다.

인덱스는 테이블이나 클러스트 상 쓰여지는 것으로 원하는 레코드를 빠르게 찾기 위한 데이터 주고이다.

이는 조건절이나 조인절에서 주로 자주  쓰이는 것이 대상이 적합하며 테이블 규모가 작거나 자주 바뀌는 경우에는 적합하지 않다.

인덱스의 종류는 다음과 같다.

1. BITMAP INDEX - 적은 개수의 특정한 값이 있는 경우 적합하다. 이는 인덱스 구조가 B-TREE 구조임을 감안 할 때 테이블의 크기가 크거나 변경 사항이 잘 없는 경우와 이를 사용하지 않을 경우 적합하다.

CREATE BITMAP INDEX idx_no ON t_table(NO);

2. UNIQUE INDEX - 컬럼의 중복을 포함하지 않고 PK와 UNIQUE 조건시 생성된다.

CREATE UNIQUE INDEX idx_no ON t_table(NO);

3. NON-UNIQUE INDEX - 컬럼의 중복을 허락하는 데이터를 가질 수 있다.

CREATE INDEX idx_no ON t_table(NO);

4. 결합 인덱스 - 여러개의 컬럼에 생성이 가능하며 총 16개까지 가능하다.

CREATE UNIQUE INDEX idx_no ON t_table(NO,NAME,ID);

인덱스의 삭제는 DROP을 통하여 하며 데이터사전 정보를 확인하려면 USER_INDEXES를 조회하면 확인이 가능하다.

둘다 누적에 대한 통계에 이용하면 편리한 내용이다.

ROLLUP의 경우 각 해당하는 분야에 대하여 데이터가 있다면 누적이 되서 그 기준에 대하여 통계를 나타낸다.

다음은 그 예제이다.

SELECT A.NAME, A.TITLE, SUM(B.QTY)

FROM t_table A, t_table_sec B

WHERE A.ID = B.ID

GROUP BY ROLLUP (A.NAME, A.TITLE)

위 쿼리에 대한 결과는 대략 아래와 같다.

A.NAME        A.TITLE      QTY

-------         -------      -----

JIM              A                     3

JIM              B                     2

JIM                                     5

BOBBY        C                     1

BOBBY        A                     2

BOBBY                               3

                                         8

하지만 각기 TITLE별의 총 합을 보려면 TITLE에 대한 것만 또다시 SELECT 하여 ROLLUP 한 것을 UNION 키셔 줘야한다. 즉 두번의 ROLLUP이 들어간다는 것이다.

하지만 큐브를 사용하면 아래와 같은 결과를 가질 수가 있다.

SELECT A.NAME, A.TITLE, SUM(B.QTY)

FROM t_table A, t_table_sec B

WHERE A.ID = B.ID

GROUP BY CUBE (A.NAME, A.TITLE)

A.NAME        A.TITLE      QTY

-------         -------      -----

JIM              A                     3

JIM              B                     2

JIM                                     5

BOBBY        C                     1

BOBBY        A                     2

BOBBY                               3

                  A                     5

                  B                     2

                  C                     1 

조인은 둘 이상의 테이블간 연계하여 데이터를 검색한다.

보통 PK와 FK를 이용하는데 조인 조건을 정의 하지않거나 잘못된 경우, 첫 테이블이 다음 테이블의 모든 행과 조인 될 경우 카테션 곱(Cartesian Product)이 일어나 상호 테이블간의 모든 데이터가 반환 되는 현상이 일어난다.

조인의 종류는 다음과 같다.

1. Equi Join - 조건절에 = 에 의해 일어난다.

SELECT A.DATA, B.DATA

FROM t_table A, t_table_sec B

WHERE A.ID = B.ID;

2. Non-Equi Join - = 외의 조인 조건을 가진는 경우

SELECT A.DATA, B.DATA

FROM t_table A, t_table_sec B

WHERE A.VALUE BETWEEN B.VALUE AND B.NEXT_VALUE;

3. Self-Join - 자기 스스로 조인 일어나는 것으로 두개의 테이블을 조인 하는 것 처럼 보이는 경우

SELECT A.DATA, B.DATA

FROM t_table A, t_table B

WHERE A.ID = B.ID;

4. Outer Join - LEFT, RIGHT, FULL OUTER JOIN이 있으며 두개의 테이블의 컬럼들에서 공통되는 값이 없을 경우 종종 이용한다. (+) 를 이용하여 사용하는데 값이 없는 측에 붙여 사용한다.

SELECT A.DATA, B.DATA

FROM t_table A, t_table_sec B

WHERE A.ID(+) = B.ID;

위와 같이 작성을 했으나 한쪽에 데이터가 맞지 않은 경우가 있다 가령 NULL 값이 들어간 경우이다.

결과가

A.DATA     B.DATA

-------     -------

1              1

               2

나와야 하는 경우 1에 대한 행만 나왔다면 다른 조건을 걸어 (+) 시켜 줘야한다.

4.1 LEFT OUTER JOIN - 오른쪽에 조인시킬 컬럼 값이 없을 경우

SELECT A.DATA, B.DATA

FROM t_table A LEFT OUTER JOIN t_table_sec B

WHERE A.ID = B.ID;

4.2 RIGHT OUTER JOIN - 쪽에 조인시킬 컬럼 값이 없을 경우

SELECT A.DATA, B.DATA

FROM t_table A RIGHT OUTER JOIN t_table_sec B

WHERE A.ID = B.ID;

4.3 FULL OUTER JOIN - 양측에 OUTER JOIN을 거는 경우

SELECT A.DATA, B.DATA

FROM t_table A LEFT FULL JOIN t_table_sec B

WHERE A.ID = B.ID;

5. INNER JOIN - 일반 조인에서 콤마 대신 INNER JOIN을 넣고 WHERE 대신 ON 넣는 방법

SELECT A.DATA, B.DATA

FROM t_table A INNER JOIN t_table_sec B

ON A.ID = B.ID;

6. NATURAL JOIN - Equi Join과 동일한 형태로 동일한 이름을 지니는 컬럼은 모두 조인이 된다. 또한 Alias는 사용하지 못하며 동일한 컬럼이 두개 이상일 경우 JOIN~USING을 이용하여 제어가 가능하다.

SELECT DATA, NAME FROM t_table NATURAL JOIN t_table_sec

7. JOIN~USING - NATURAL JOIN에 나타나는 문제를 제어하기 위한 것으로 해당하는 컬럼을 선택해서 조인이 가능하다. 또한 USING절 안에 포함되는 컬럼에 Alias를 지정하면 오류가 난다.

SELECT A.DATA, B.NAME, ID FROM t_table A JOIN t_table_sec B USING (ID)

8. ON - 조인 조건을 지정하며 논리 연산과 서브쿼리 지정이 가능하다.

SELECT A.DATA, B.NAME

FROM t_table A JOIN t_table_sec B ON (A.DATA = B.DATA)

                       JOIN t_table_third C ON (C.ID = A.ID)

테이블은 데이터베이스 상에 가장 기본적인 데이터 저장 단위이다.

이들은 컬럼과 레코드로 구성이 되며 두개 이상의 엔티티간의 관계 또한 표기가 가능하다.

테이블 생성하는 방법은 다음과 같다.

CREATE TABLE t_table

(

ID NUMBER CONSTRAINT table_id PRIMARY KEY,

NAME VARCHAR(100)

);

위에 보면 CONSTRAINT를 이용하여 PRIMARY KEY로 제약 조건을 걸어 놓은 것을 볼 수 있다.

또한 로그인한 계정으로 만든 테이블 내역들을 보려면 USER_TABLES 를 조회하면 확인 할 수가 있다.

위에서 제약 조건이란 무결성 원칙을 위한 규칙 정용이라 생각을 하면 되며 이들은 데이터 사전에 모두 저장이된다.

다음은 그 예들이다.

1. NOT NULL

CREATE TABLE t_table

(

ID NUMBER CONSTRAINT table_id NOT NULL,

NAME VARCHAR(100)

);

2. CHECK - 해당 컬럼 값 범위 제한

ALTER TABLE t_table

ADD CONTRAINT table_ck CHECK (NO>=1 AND NO<=100); --추가

DROP CONTRAINT table_ck; --삭제

3. DEFAULT 지정

CREATE TABLE t_table

(

ID NUMBER CONSTRAINT table_id NOT NULL,

DATE DEFAULT SYSDATE

);

4. UNIQUE - 유일한 데이터, 자동 인덱스 생성

ALTER TABLE t_table

ADD CONSTRAINT uk_ID UNIQUE(ID); -- 추가

DROP CONTRAINT uk_ID; -- 삭제

5. PRIMARY KEY - 유일한 기본키, UNIQUE와 NOT NULL의 특성을 함께 가짐. 참조 무결성을 위해 이용함. 자동 인덱스 생성

CREATE TABLE t_table

(

ID NUMBER CONSTRAINT table_id NOT NULL,

NAME VARCHAR(100)

);

ARTER TABLE t_table ADD CONTRAINT pk_name PRIMARY KEY(NAME);

6. FOREIGN KEY - 기본키들을 참조하는 컬럼이나 컬럼들의 집합을 말하며 반드시 참조하는 PK와 데이터형이 일치해야한다.

ARTER TABLE t_table ADD CONTRAINT fk_ID

FOREIGN KEY(ID) REFERENCES t_table_sec(ID);

정보처리기사나 각종 서적에 보면 DDL, DML, DCL이라는 것을 볼 수가 있다.

여기에선 그 정의를 알아본다. 총 3가지로 나뉠 수가 있다.

DDL(Data Definition Language) - 데이터베이스 객체인 테이블이나 뷰, 인덱스, 시퀀스 등의 구조를 정의 하며 CREATE, DROP, ALTER를 이용한다.

DML(Data Manipulation Language) - 데이터의 삽입, 삭제, 업데이트 등을 처리

DCL(Data Control Language) - 데이터베이스 사용자 권한 제어한다. GRANT로 권한을 부여하고, REVOKE로 취소한다.