기록지

bitmap index란

bitmap index 는 컴퓨터에서 사용하는 최소단위인 비트를 이용하여 컬럼값을 저장하고, rowid를 자동으로 생성하는 인덱스의 한 방법이다. 비트를 직접 관리하므로 저장공간이 크게 감소하고 비트연산을 수행할 수 있다는 이점이 있다.

bitmap index 는 index key value + start rowid + end rowid + bitmap 엔트리로 구성되어있다.

start rowid와 end rowid 의 range 사이에 있는 모든 수 만큼 bitmap이 표현되어야 하지만, 오라클에서는 내부적인 압축 알고리즘을 사용하여 bitmap을 생성하기 때문에 모두 표현되지 않는 경우도 있다. 비트맵도 b-tree처럼 조직되어 있지만, leaf 노드는 rowid 값들 대신 각 key값에 대한 bitmap을 저장한다. 

bitmap index의 장단점

장점

- 테이블이 많은 행을 가지며 키 열을 적은 분포도를 가질 때, 즉 열이 갖는 서로 다른 값이 몇 개 안될 때 유리하다 ( 성별이나 결혼 여부) 

- 질의문이 or 연산자를 포함하는 여러 개의 where 조건을 자주 사용할 때 유리하다.

단점

- insert, update, delete와 같은 query 문에서는 무의미 하다.

- 분포도가 좋은 값에 대해서는 일반적은 b-tree인덱스가 유리하다. 즉  bitmapped index 적용 column의 후보는 대게 5가지 정도 이내의 값(cardinality)을 갖는다.

bitmap index와 b-tree index 비교

bitmap index 생성 문법

CREATE BITMAP INDEX [schema.]index
ON [schema.]table ( column [, column]) 
[ tablespace tablespace ]
[ pctfree integer ]
[ initrans integer ]
[ maxtrans integer ]
[ storage-clause ]
[ loggin | nologging ]
[ nosort]

참고

http://www.gurubee.net/lecture/1109

Transaction isolation level 에 따라 일어 날 수 있는 현상 

Drity read 

커밋되지 않거나 아직 dirty buffer에 있는 데이터를 읽을 수 있는것을 말한다. 예를 들어 변경 후 아직 커밋되지 않은 값을 읽는데 변경을 가한 트랜잭션이 최종적으로 롤백된다면 그 값을 읽은 트랜잭션은 일관성이 없는 상태가 된다. 

Non repeateble read

한 트랜잭션 내에서 같은 쿼리를 두번 수행했는데 그 사이에 다른 세션에서 수정 또는 삭제가 일어나 두 쿼리의 결과값이 다르게 나타나는것을 말한다. 

Phantom Read 

한 트랜잭션 내에서 같은 쿼리를 두 번 수행할때, 다른 트랜잭션에서 삽입이 일어나면 두 쿼리의 결과값이 다르게 나타나는 현상을 말한다. Nonrepeatable read와의 차이점은 데이터가 변경되지는 않았지만 이전보다 쿼리에 만족하는 데이터값이 늘어난다는 점이 차이가 있다. ( 행값의 변화가 있느냐, 행의 총 수의 변화가 있느냐의 차이) 

Serializtion Anomaly

일련의 트랜잭션 단위를 수행한 결과가 순서대로 수행했을 시 확인되는 트랜잭션들의 결과와 상이한 현상.

Transaction 격리 level 

READ UNCOMMITTED

 select for update, delete 구문 실행 후, commit, rollback에 관계 없이 수행전의 데이터를 읽어 들인다. 이로 인해 데이터 간의 일관성을 보장하기 위한 lock이 발생하지 않는다.

트랜잭션에서 처리 중인 아직 커밋되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용한다. 해당 수준에서는 Dirty Read, Non-Repeatable Read, Phantom Read가 일어날 수 있다. 이 설정은 정합성에 문제가 있기 때문에 권장하는 설정은 아니다.

기존 RDBMS 에서는 위의 방식으로 인해 일관성이 보장되지 않는 데이터를 읽어 들이는 Dirty Read 가 발생할 수 있으나 PostgreSQL는 이를 허용하지 않는다.

DB 내에서 SET 으로 옵션을 설정 할 수 있으나 PostgreSQL 에서 작동 메커니즘은 READ COMMITTED와 거의 동일하다. 그래서 공식 DOC 에서도 실제 Isolation 유형은 READ UNCOMMITED/READ COMMITTED를 묶어서 아래의 2개와 함께 총 3개로 구분한다.

READ COMMITED

ORACLE, PostgreSQL의 default Isolation Level 이다.

Read uncommitted 와는 다르게 select for update, delete 구문 실행 후, commit 된 데이터를 읽어 들인다.

트랜잭션이 커밋되어 확정된 데이터만 다른 트랜잭션이 읽도록 허용한다. 따라서 Dirty Read의 발생가능성을 막는다. 커밋 되지 않은 데이터에 대해서는 실제 DB 데이터가 아닌 Undo 로그에 있는 이전 데이터를 가져오는 것. 하지만 Non-Repeatable Read와 Phanton Read에 대해서는 발생 가능성이 있다.

PostgreSQL에서는 데이터 변경이 발생 시, commit되지 않은 데이터 변경 내역 마다 새로운 페이지를 만들어서(ORACLE에서 데이터블록) 변경이력을 관리하는 MVCC 개념을 사용하기 때문에 commit 되지 않는 내용들에 대해서는 기존에 있는 갱신 이전 버전의 페이지를 확인한다.

위의 방식으로 인해 비슷한 시간대에 동일 쿼리를 수행했는데 처음 수행했을떄 보여지는 데이터 row와 다른 트랜잭션에서 commit 이후에 보여지는 row 정합성 차이 혹은 조회 일관성 이상등이 발생 할 수 있다.

이로 인하여 이상이 발생하는 row를 phantom row라고 하며 (read 이상은 phantom read라고 한다), 이를 방지하기 위해서 gap lock 이라는 개념이 존재하는데 해당 격리 단계 에서는 사용하지 않는다.

REPEATABLE READ

Mysql/MariaDB의 default Isolation Level 이다.

 동일 세션내의 트랜잭션에서도 데이터 조회 변동 시점간의 gap을 관리하기 위하여 snapshot 이라는 개념을 사용하며, 이를 통하여 다른 세션으로 인하여 데이터가 변동되도 각 시점간의 snapshot을 이용하여 일관된 데이터를 조회 및 변동할 수 있다.

위의 방식으로 인하여 gap lock을 사용하므로 이 lock이 발생 할 수 있으며 select for update, delete 구문 실행 후, commit, rollback 및 다양한 상황에서 조건에 따라 record lock 도 발생 가능하다.

조건에 따라 index range scan lock 도 발생하기 때문에 다른 세션으로 부터 들어오는 create select, insert select 방식의 조건 데이터 조회 후 기입 방식도 lock이 걸릴 수 있다.

트랜잭션내에서 삭제, 변경에 대해서 Undo 로그에 넣어두고 앞서 발생한 트랜잭션에 대해서는 실제 데이터가 아닌 Undo 로그에 있는 백업데이터를 읽게 한다. 이렇게 함으로써 트랜잭션 중 값의 변경에 대해서 일정한 값으로 처리할 수 있습니다. 이렇게하면 삭제와 수정에 대해서 트랜잭션내에서 불일치를 가져오던 Non-Reapeatable Read를 해소할 수 있다.

SERIALIZABLE(직렬화) 

격리성이 제일 높은 단계다.

 트랜잭션 내에서 쿼리를 두 번 이상 수행할 때, 첫 번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌지 않음은 물론 새로운 레코드가 나타나지도 않도록 하는 설정이다.

작동구조는 REPEATABLE READ와 유사하지만 아래에 설명될 일련의 트랜잭션 기입 및 조회가 동시에 이뤄질 시 발생할 수 있는 Serialization Anomaly를 방지하기 위하여 동시적으로 데이터 변동으로 인한 상이성이 보이면 아래의 에러를 발생시킨다.

ERROR: could not serialize access due to concurrent update

오라클은 Read committed와 Serializable을 지원하고 SQL server 와 DB2, PostgreSQL는 4가지 레벨을 모두 지원한다. (오라클은 select ... for update 구문을 통해 Repeatable Read를 구현할 수는 있지만 공식적으로 트랜잭션 고립성 레벨에서 지원하는 것은 두가지 뿐)

1. Window Function 개요

윈도우 함수랑 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수를 말한다. 분석 함수(ANALYSTIC FUNCTION)이나 순위 함수(RANK FUNCTION)등이 해당된다. 서브쿼리는 사용이 가능하고, 중첩(NEST)해서는 사용이 불가하다. 

WINDOW FUNCTION 종류

SYNTAX

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER 
( [PARTITION BY 컬럼][ORDER BY 절][WINDOWING 절])
FROM 테이블 명; 

-- WINDOW_FUNCTION : 윈도우함수 
-- ARGUMENTS : 함수에 따라 0~N개의 인수가 저장
-- OVER : Window 함수 사용에 있어 OVER 문구는 필수 키워드이다.
-- PARTITION BY 절 : 전체 집합을 기준에 대해 순위를 지정할 지 ORDER BY 절을 기술
-- WINDOWING절 : WINDOWS 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.
                 --ROWS는 물리적인 결과 행의 수, RANGE는 논리적인 값에 의한 범위를, 둘 중 하나 선택 사용
                 --WINDOWING절은 SQL Server에서는 지원하지 않는다.

WINDOWING 절 SYNTAX 상세

ROWS | RANGE BETWEEN UNBOUNDED PRECEDING[CURRENT ROW] AND UNBOUNDED FOLLOWING[CURRENT ROW]

ROWS : 물리적인 행을 의미
RANGE : 논리적인 값으로 범위를 지정 / 같은값을 하나로 본다 

WINDOWINDG 기본 설정값이 존재 : UNBOUNDED PRECEDING 

UNBOUNDED PRECEDING : 현재행을 기준으로 선행(이전)하는 모든 행들

SELECT empno, ename, sal , 
            SUM(sal) OVER (ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) c_sum
FROM emp
ORDER BY sal;

UNBOUNDED FOLLOWING : 현재행을 기준으로 이후 모든 행들 

n PRECEDING : 현재행을 기준으로 n칸 앞 
n FOLLWING  : 현재행을 기준으로 n칸 뒤

그룹 내 비율 함수

* 칼럼 값에 대한 백분율 -> RATIO_TO_REPORT

* 행의 순서에 대한 (0부터 1사이 값) 백분율 -> PERCENT_RANK

* 1 / (파티션) 전체 건수로 표현하는 백분율 -> CUME_DIST 

RATIO_TO_REPORT 함수 

파티션 내 전체 SUM(칼럼) 값에 대한 행별 칼럼 값의 백분율을 소수점으로 구함

* 0 < 백분율 < 1, 개별 RATIO의 합 = 1 

SQL Sever 에서는 지원하지 않는 함수 

SELECT FIRST_NAME, SALARY, 
            ROUND(RATIO_TO_REPORT(SALARY) OVER (),2) AS R_R,
            SUM(SALARY) Over ( Order by JOB_ID  ) AS "Total"
FROM EMPLOYEES e  
WHERE JOB_ID='SA_REP' ;

FIRST_NAME |SALARY|R_R |Total |
-----------+------+----+------+
Peter      | 10000|0.04|250500|
David      |  9500|0.04|250500|
Peter      |  9000|0.04|250500|
Christopher|  8000|0.03|250500|
Nanette    |  7500|0.03|250500|
Oliver     |  7000|0.03|250500|
Janette    | 10000|0.04|250500|
Patrick    |  9500|0.04|250500|
Allan      |  9000|0.04|250500|
Lindsey    |  8000|0.03|250500|
Louise     |  7500|0.03|250500|
Sarath     |  7000|0.03|250500|
Clara      | 10500|0.04|250500|
Danielle   |  9500|0.04|250500|
Mattea     |  7200|0.03|250500|
David      |  6800|0.03|250500|
...

 PERCENT_RANK 

파티션 별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여 값이 아닌 행의 순서별 백분율을 구함

* 0 < 백분율 < 1 

같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번째 위치쯤에 있는지 0과 1사이의 값으로 출력 

SELECT DEPARTMENT_ID, FIRST_NAME, SALARY,
       PERCENT_RANK() OVER ( PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC ) AS P_R
FROM EMPLOYEES e   ;
    
DEPARTMENT_ID|FIRST_NAME |SALARY|P_R                                       |
-------------+-----------+------+------------------------------------------+
           10|Jennifer   |  4400|                                         0|
           20|Michael    | 13000|                                         0|
           20|Pat        |  6000|                                         1|
           30|Den        | 11000|                                         0|
           30|Alexander  |  3100|                                       0.2|
           30|Shelli     |  2900|                                       0.4|
           30|Sigal      |  2800|                                       0.6|
           30|Guy        |  2600|                                       0.8|
           30|Karen      |  2500|                                         1|
           40|Susan      |  6500|                                         0|
           50|Adam       |  8200|                                         0|
...

CUME_DIST

파티션 별 윈도우에서 전체 건수에 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구함 

* 0 < 백분율 < 1 

같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번째 위치쯤에 있는지 0과 1사이의 값으로 출력 

SELECT DEPARTMENT_ID, FIRST_NAME, SALARY,
       CUME_DIST() OVER ( PARTITION BY DEPARTMENT_ID ORDER BY SALARY ) AS C_D
FROM EMPLOYEES e   ;   

DEPARTMENT_ID|FIRST_NAME |SALARY|C_D                                       |
-------------+-----------+------+------------------------------------------+
           10|Jennifer   |  4400|                                         1|
           20|Pat        |  6000|                                       0.5|
           20|Michael    | 13000|                                         1|
           30|Karen      |  2500|0.1666666666666666666666666666666666666667|
           30|Guy        |  2600|0.3333333333333333333333333333333333333333|
           30|Sigal      |  2800|                                       0.5|
           30|Shelli     |  2900|0.6666666666666666666666666666666666666667|
           30|Alexander  |  3100|0.8333333333333333333333333333333333333333|
           30|Den        | 11000|                                         1|
           40|Susan      |  6500|                                         1|
...

NFILE

파티션 별 전체 건수를 ARGUMENT 값으로 N등분 한 결과 구함

사원들을 연봉이 높은 순으로 정렬 해서 4단계로 나눠 보여주는 예제 

SELECT FIRST_NAME, SALARY, NTILE(4) OVER (ORDER BY SALARY DESC ) AS QUAR_TILE
FROM EMPLOYEES e   ; 

FIRST_NAME |SALARY|QUAR_TILE|
-----------+------+---------+
Steven     | 24000|        1|
Neena      | 17000|        1|
Lex        | 17000|        1|
John       | 14000|        1|
Karen      | 13500|        1|
Michael    | 13000|        1|
Nancy      | 12008|        1|
Shelley    | 12008|        1|
Alberto    | 12000|        1|
Lisa       | 11500|        1|
Den        | 11000|        1|
Gerald     | 11000|        1|
Ellen      | 11000|        1|
Eleni      | 10500|        1|
Clara      | 10500|        1|
Janette    | 10000|        1|
Peter      | 10000|        1|
Hermann    | 10000|        1|
Harrison   | 10000|        1|
Tayler     |  9600|        1|
Danielle   |  9500|        1|
David      |  9500|        1|
Patrick    |  9500|        1|
Peter      |  9000|        1|
Alexander  |  9000|        1|
Allan      |  9000|        1|
Daniel     |  9000|        1|
Alyssa     |  8800|        2|
Jonathon   |  8600|        2|
Jack       |  8400|        2|
William    |  8300|        2|
Adam       |  8200|        2|
John       |  8200|        2|
Matthew    |  8000|        2|
Lindsey    |  8000|        2|
Christopher|  8000|        2|
Payam      |  7900|        2|
Jose Manuel|  7800|        2|
Ismael     |  7700|        2|
Louise     |  7500|        2|
Nanette    |  7500|        2|
William    |  7400|        2|
Elizabeth  |  7300|        2|
Mattea     |  7200|        2|
Oliver     |  7000|        2|
Kimberely  |  7000|        2|
Sarath     |  7000|        2|
Luis       |  6900|        2|
David      |  6800|        2|
Susan      |  6500|        2|
Shanta     |  6500|        2|
Sundar     |  6400|        2|
Charles    |  6200|        2|
Amit       |  6200|        2|
Sundita    |  6100|        3|
Pat        |  6000|        3|
Bruce      |  6000|        3|
Kevin      |  5800|        3|
Valli      |  4800|        3|
David      |  4800|        3|
Jennifer   |  4400|        3|
Nandita    |  4200|        3|
Diana      |  4200|        3|
Alexis     |  4100|        3|
Sarah      |  4000|        3|
Britney    |  3900|        3|
Kelly      |  3800|        3|
Jennifer   |  3600|        3|
Renske     |  3600|        3|
Trenna     |  3500|        3|
Julia      |  3400|        3|
Jason      |  3300|        3|
Laura      |  3300|        3|
Julia      |  3200|        3|
Samuel     |  3200|        3|
Winston    |  3200|        3|
Stephen    |  3200|        3|
Alexander  |  3100|        3|
Alana      |  3100|        3|
Jean       |  3100|        3|
Curtis     |  3100|        3|
Kevin      |  3000|        4|
Anthony    |  3000|        4|
Shelli     |  2900|        4|
Timothy    |  2900|        4|
Michael    |  2900|        4|
Sigal      |  2800|        4|
Vance      |  2800|        4|
Girard     |  2800|        4|
Mozhe      |  2800|        4|
John       |  2700|        4|
Irene      |  2700|        4|
Guy        |  2600|        4|
Douglas    |  2600|        4|
Donald     |  2600|        4|
Randall    |  2600|        4|
Karen      |  2500|        4|
James      |  2500|        4|
Randall    |  2500|        4|
Peter      |  2500|        4|
Martha     |  2500|        4|
Joshua     |  2500|        4|
Ki         |  2400|        4|
James      |  2400|        4|
Hazel      |  2200|        4|
Steven     |  2200|        4|
TJ         |  2100|        4|

1. Window Function 개요

윈도우 함수랑 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수를 말한다. 분석 함수(ANALYSTIC FUNCTION)이나 순위 함수(RANK FUNCTION)등이 해당된다. 서브쿼리는 사용이 가능하고, 중첩(NEST)해서는 사용이 불가하다. 

WINDOW FUNCTION 종류

SYNTAX

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER 
( [PARTITION BY 컬럼][ORDER BY 절][WINDOWING 절])
FROM 테이블 명; 

-- WINDOW_FUNCTION : 윈도우함수 
-- ARGUMENTS : 함수에 따라 0~N개의 인수가 저장
-- OVER : Window 함수 사용에 있어 OVER 문구는 필수 키워드이다.
-- PARTITION BY 절 : 전체 집합을 기준에 대해 순위를 지정할 지 ORDER BY 절을 기술
-- WINDOWING절 : WINDOWS 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.
                 --ROWS는 물리적인 결과 행의 수, RANGE는 논리적인 값에 의한 범위를, 둘 중 하나 선택 사용
                 --WINDOWING절은 SQL Server에서는 지원하지 않는다.

WINDOWING 절 SYNTAX 상세

ROWS | RANGE BETWEEN UNBOUNDED PRECEDING[CURRENT ROW] AND UNBOUNDED FOLLOWING[CURRENT ROW]

ROWS : 물리적인 행을 의미
RANGE : 논리적인 값으로 범위를 지정 / 같은값을 하나로 본다 

WINDOWINDG 기본 설정값이 존재 : UNBOUNDED PRECEDING 

UNBOUNDED PRECEDING : 현재행을 기준으로 선행(이전)하는 모든 행들

SELECT empno, ename, sal , 
            SUM(sal) OVER (ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) c_sum
FROM emp
ORDER BY sal;

UNBOUNDED FOLLOWING : 현재행을 기준으로 이후 모든 행들 

n PRECEDING : 현재행을 기준으로 n칸 앞 
n FOLLWING  : 현재행을 기준으로 n칸 뒤

FIRST_VALUE

파티션 별 윈도우에서 가장 먼저 나온 값을 구한다. 

FIRST_VALUE는 공동 등수를 인정하지 않고 처음 나온 행만을 구한다. 

부서별로 나누어진 윈도우에서 가장 먼저 나온 사람의 이름을 구하는 예제 

SELECT EMPLOYEE_ID,  DEPARTMENT_ID, FIRST_NAME, SALARY,
       FIRST_VALUE(FIRST_NAME) OVER ( PARTITION BY DEPARTMENT_ID ) AS Fisrst_name
FROM EMPLOYEES e  ;

EMPLOYEE_ID|DEPARTMENT_ID|FIRST_NAME |SALARY|FISRST_NAME|
-----------+-------------+-----------+------+-----------+
        200|           10|Jennifer   |  4400|Jennifer   |
        201|           20|Michael    | 13000|Michael    |
        202|           20|Pat        |  6000|Michael    |
        114|           30|Den        | 11000|Den        |
        115|           30|Alexander  |  3100|Den        |
        116|           30|Shelli     |  2900|Den        |
        117|           30|Sigal      |  2800|Den        |
        118|           30|Guy        |  2600|Den        |
        119|           30|Karen      |  2500|Den        |
        203|           40|Susan      |  6500|Susan      |
        120|           50|Matthew    |  8000|Matthew    |
        121|           50|Adam       |  8200|Matthew    |
        122|           50|Payam      |  7900|Matthew    |
        123|           50|Shanta     |  6500|Matthew    |
        ...

order by를 사용하여 급여로 정렬 후 같은 부서내에서 연봉이 가장 작은 사람중 한명의 이름을 구하는 예제

SELECT EMPLOYEE_ID,  DEPARTMENT_ID, FIRST_NAME, SALARY,
       FIRST_VALUE(FIRST_NAME) OVER ( PARTITION BY DEPARTMENT_ID ORDER BY SALARY ) AS Fisrst_name
FROM EMPLOYEES e  ;

EMPLOYEE_ID|DEPARTMENT_ID|FIRST_NAME |SALARY|FISRST_NAME|
-----------+-------------+-----------+------+-----------+
        200|           10|Jennifer   |  4400|Jennifer   |
        202|           20|Pat        |  6000|Pat        |
        201|           20|Michael    | 13000|Pat        |
        119|           30|Karen      |  2500|Karen      |
        118|           30|Guy        |  2600|Karen      |
        117|           30|Sigal      |  2800|Karen      |
        116|           30|Shelli     |  2900|Karen      |
        115|           30|Alexander  |  3100|Karen      |
        114|           30|Den        | 11000|Karen      |
        203|           40|Susan      |  6500|Susan      |
        132|           50|TJ         |  2100|TJ         |
        136|           50|Hazel      |  2200|TJ         |
        128|           50|Steven     |  2200|TJ         |
        ...

LAST_VALUE

파티션 별 윈도우에서 가장 나중에 나온 값을 구한다. 

부서별로 나누어진 사원들 중 가장 나중의 사원 이름을 구하는 예

SELECT EMPLOYEE_ID,  DEPARTMENT_ID, FIRST_NAME, SALARY,
       LAST_VALUE(FIRST_NAME) OVER ( PARTITION BY DEPARTMENT_ID ) AS Fisrst_name
FROM EMPLOYEES e  ;

EMPLOYEE_ID|DEPARTMENT_ID|FIRST_NAME |SALARY|FISRST_NAME|
-----------+-------------+-----------+------+-----------+
        200|           10|Jennifer   |  4400|Jennifer   |
        201|           20|Michael    | 13000|Pat        |
        202|           20|Pat        |  6000|Pat        |
        114|           30|Den        | 11000|Karen      |
        115|           30|Alexander  |  3100|Karen      |
        116|           30|Shelli     |  2900|Karen      |
        117|           30|Sigal      |  2800|Karen      |
        118|           30|Guy        |  2600|Karen      |
        119|           30|Karen      |  2500|Karen      |
        203|           40|Susan      |  6500|Susan      |
...

부서별 직원들의 연봉이 높은 순서 부터 정렬하고, 파티션 내에서 가장 마지막에 나온 사원의 이름을 출력

SELECT EMPLOYEE_ID,  DEPARTMENT_ID, FIRST_NAME, SALARY,
       LAST_VALUE(FIRST_NAME) OVER ( PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC
       ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS Fisrst_name
FROM EMPLOYEES e  ;

EMPLOYEE_ID|DEPARTMENT_ID|FIRST_NAME |SALARY|FISRST_NAME|
-----------+-------------+-----------+------+-----------+
        200|           10|Jennifer   |  4400|Jennifer   |
        201|           20|Michael    | 13000|Pat        |
        202|           20|Pat        |  6000|Pat        |
        114|           30|Den        | 11000|Karen      |
        115|           30|Alexander  |  3100|Karen      |
        116|           30|Shelli     |  2900|Karen      |
        117|           30|Sigal      |  2800|Karen      |
        118|           30|Guy        |  2600|Karen      |
        119|           30|Karen      |  2500|Karen      |
        203|           40|Susan      |  6500|Susan      |
        ...

윈도우잉 절을 기술하지 않으면 다음과 같이 자기 자신의 로우까지 가장 마지막값으로 여긴다.

SELECT EMPLOYEE_ID,  DEPARTMENT_ID, FIRST_NAME, SALARY,
       LAST_VALUE(FIRST_NAME) OVER ( PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC ) AS Fisrst_name
FROM EMPLOYEES e  ;

EMPLOYEE_ID|DEPARTMENT_ID|FIRST_NAME |SALARY|FISRST_NAME|
-----------+-------------+-----------+------+-----------+
        200|           10|Jennifer   |  4400|Jennifer   |
        201|           20|Michael    | 13000|Michael    |
        202|           20|Pat        |  6000|Pat        |
        114|           30|Den        | 11000|Den        |
        115|           30|Alexander  |  3100|Alexander  |
        116|           30|Shelli     |  2900|Shelli     |
        117|           30|Sigal      |  2800|Sigal      |
        118|           30|Guy        |  2600|Guy        |
        119|           30|Karen      |  2500|Karen      |
        203|           40|Susan      |  6500|Susan      |
        ...

NTH_VALUE

파티션 별 윈도우에서 n 번째를 지정하여 값을 가져올 수 있다. 

SELECT EMPLOYEE_ID,  DEPARTMENT_ID, FIRST_NAME, SALARY,
       NTH_VALUE(FIRST_NAME, 2) OVER ( PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC ) AS Fisrst_name
FROM EMPLOYEES e  ;

LAG 

파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.

*LAG ( 인수1, 인수2, 인수3) 

- 인수1  : 입력 컬럼

- 인수2 : 몇 번째 앞의 행을 가져올 지 결정 ( DEFAULT 1) 

- 인수3 : 파티션의 첫 번째 행의 경우 가져올 데이터가 없어 NULL값일 경우, 변경할 값 입력 

사원들을 입사일자가 빠른 기준으로 정렬하고, 본인 보다 입사일자가 한 명 앞선 사원의 급여를 본인 급여와 함께 출력하는 예제 

SELECT FIRST_NAME, HIRE_DATE, JOB_ID, SALARY, LAG(SALARY) OVER ( ORDER BY HIRE_DATE ) AS PREV_SAL
     FROM EMPLOYEES e  
     WHERE JOB_ID ='SA_REP';
     
FIRST_NAME |HIRE_DATE              |JOB_ID|SALARY|PREV_SAL|
-----------+-----------------------+------+------+--------+
Janette    |2004-01-30 00:00:00.000|SA_REP| 10000|        |
Patrick    |2004-03-04 00:00:00.000|SA_REP|  9500|   10000|
Ellen      |2004-05-11 00:00:00.000|SA_REP| 11000|    9500|
Allan      |2004-08-01 00:00:00.000|SA_REP|  9000|   11000|
Peter      |2005-01-30 00:00:00.000|SA_REP| 10000|    9000|
Lindsey    |2005-03-10 00:00:00.000|SA_REP|  8000|   10000|
Lisa       |2005-03-11 00:00:00.000|SA_REP| 11500|    8000|
Alyssa     |2005-03-19 00:00:00.000|SA_REP|  8800|   11500|
David      |2005-03-24 00:00:00.000|SA_REP|  9500|    8800|
Peter      |2005-08-20 00:00:00.000|SA_REP|  9000|    9500|
Clara      |2005-11-11 00:00:00.000|SA_REP| 10500|    9000|
...

사원들을 입사일자가 빠른 기준으로 정렬하고, 본인 보다 입사일자가 두 단계 앞선 사원의 급여를 본인 급여와 함께 출력하는 예제 

SELECT FIRST_NAME, HIRE_DATE, JOB_ID, SALARY, LAG(SALARY, 2) OVER ( ORDER BY HIRE_DATE ) AS PREV_SAL
     FROM EMPLOYEES e  
     WHERE JOB_ID ='SA_REP';
     
 FIRST_NAME |HIRE_DATE              |JOB_ID|SALARY|PREV_SAL|
-----------+-----------------------+------+------+--------+
Janette    |2004-01-30 00:00:00.000|SA_REP| 10000|        |
Patrick    |2004-03-04 00:00:00.000|SA_REP|  9500|        |
Ellen      |2004-05-11 00:00:00.000|SA_REP| 11000|   10000|
Allan      |2004-08-01 00:00:00.000|SA_REP|  9000|    9500|
Peter      |2005-01-30 00:00:00.000|SA_REP| 10000|   11000|
Lindsey    |2005-03-10 00:00:00.000|SA_REP|  8000|    9000|
Lisa       |2005-03-11 00:00:00.000|SA_REP| 11500|   10000|
Alyssa     |2005-03-19 00:00:00.000|SA_REP|  8800|    8000|
David      |2005-03-24 00:00:00.000|SA_REP|  9500|   11500|
Peter      |2005-08-20 00:00:00.000|SA_REP|  9000|    8800|
Clara      |2005-11-11 00:00:00.000|SA_REP| 10500|    9500|
Louise     |2005-12-15 00:00:00.000|SA_REP|  7500|    9000|
...

* 맨 위 없는 값들에 대해 NULL 대신 0 출력
SELECT FIRST_NAME, HIRE_DATE, JOB_ID, SALARY, LAG(SALARY, 2, 0) OVER ( ORDER BY HIRE_DATE ) AS PREV_SAL
     FROM EMPLOYEES e  
     WHERE JOB_ID ='SA_REP';
     
FIRST_NAME |HIRE_DATE              |JOB_ID|SALARY|PREV_SAL|
-----------+-----------------------+------+------+--------+
Janette    |2004-01-30 00:00:00.000|SA_REP| 10000|       0|
Patrick    |2004-03-04 00:00:00.000|SA_REP|  9500|       0|
Ellen      |2004-05-11 00:00:00.000|SA_REP| 11000|   10000|
Allan      |2004-08-01 00:00:00.000|SA_REP|  9000|    9500|
Peter      |2005-01-30 00:00:00.000|SA_REP| 10000|   11000|
Lindsey    |2005-03-10 00:00:00.000|SA_REP|  8000|    9000|
Lisa       |2005-03-11 00:00:00.000|SA_REP| 11500|   10000|
Alyssa     |2005-03-19 00:00:00.000|SA_REP|  8800|    8000|
David      |2005-03-24 00:00:00.000|SA_REP|  9500|   11500|
Peter      |2005-08-20 00:00:00.000|SA_REP|  9000|    8800|
Clara      |2005-11-11 00:00:00.000|SA_REP| 10500|    9500|
...

LEAD 

파티션 별 윈도우에서 이후 몇 번째 행의 값을 가져 올 수 있다. 

*LEAD ( 인수1, 인수2, 인수3)

- 인수1  : 입력 컬럼

- 인수2 : 몇 번째 앞의 행을 가져올 지 결정 ( DEFAULT 1) 

- 인수3 : 파티션의 첫 번째 행의 경우 가져올 데이터가 없어 NULL값일 경우, 변경할 값 입력 

입사일자로 정렬 후 현재 로우 보다 입사일자가 한 단계 뒤에있는 사원의 급여를 구하는 예제 

SELECT FIRST_NAME, HIRE_DATE, JOB_ID, SALARY, LEAD(SALARY) OVER ( ORDER BY HIRE_DATE ) AS PREV_SAL
FROM EMPLOYEES e  
WHERE JOB_ID ='SA_REP';

FIRST_NAME |HIRE_DATE              |JOB_ID|SALARY|PREV_SAL|
-----------+-----------------------+------+------+--------+
Janette    |2004-01-30 00:00:00.000|SA_REP| 10000|    9500|
Patrick    |2004-03-04 00:00:00.000|SA_REP|  9500|   11000|
Ellen      |2004-05-11 00:00:00.000|SA_REP| 11000|    9000|
Allan      |2004-08-01 00:00:00.000|SA_REP|  9000|   10000|
Peter      |2005-01-30 00:00:00.000|SA_REP| 10000|    8000|
Lindsey    |2005-03-10 00:00:00.000|SA_REP|  8000|   11500|
Lisa       |2005-03-11 00:00:00.000|SA_REP| 11500|    8800|
Alyssa     |2005-03-19 00:00:00.000|SA_REP|  8800|    9500|
David      |2005-03-24 00:00:00.000|SA_REP|  9500|    9000|
...

개발자에게 NULL은 쉬우면서도 어려운 존재다. 경험이 많은 개발자라도 NULL의 예상치 못한 결과에 당황하는 경우가 간혹 있다. 오라클에서 NULL은 길이가 0인 문자열과 동일한 의미를 가지거나, 알 수 없는 미지의 값을 의미하기도 한다. 이러한 NULL은 프로젝트에서 의도하든 않든 간에 많은 문제를 야기하기도 한다. 

1. 사칙연산에서의 NULL

테스트 데이터 

create table test ( id number, name varchar(10), location varchar(10), age number );
create table test2 ( id number, name varchar(10), location varchar(10), age number );

insert into test values (1,	'혜리','광주',   	30) ;
insert into test values (2,	'미연','인천',   	NULL) ;
insert into test values (3,	'리정','서울',   	26) ;
insert into test values (4,	'예원','서울',   	25) ;
insert into test values (5,	'채원','서울',   	24) ;
insert into test values (6,	'파트리샤','광주',  22) ;

insert into test2 values(1,	'혜리',	'광주',      	30);
insert into test2 values(2,	'미연',	'인천',      	27);
insert into test2 values(3,	'리정',	'서울',      	26);
insert into test2 values(4,	'예원',	'서울',      	NULL);
insert into test2 values(5,	'채원',	'서울',      	24);
insert into test2 values(6,	'파트리샤',	'광주',      	22);





## test
ID	NAME	LOCATION	AGE
1	혜리	광주      	30
2	미연	인천      	[NULL]
3	리정	서울      	26
4	예원	서울      	25
5	채원	서울      	24
6	파트리샤	광주      	22

## test2
ID	NAME	LOCATION	AGE
1	혜리	광주      	30
2	미연	인천      	27
3	리정	서울      	26
4	예원	서울      	[NULL]
5	채원	서울      	24
6	파트리샤	광주      	22

사칙연산에서 NULL 계산 결과는 항상 NULL이다. 알 수 없는 값 NULL에 어떤 값을 더하거나 빼거나 곱하거나 나누거나 사칙연산 계산 결과는 무조건 NULL이 됨을 이해해야한다.

SELECT a.ID, 
       a.AGE, 
       b.ID, 
       b.AGE,
       (a.age + b.age), 
       (a.age - b.age), 
       (a.age * b.age), 
       (a.age / b.age)  
FROM test a, test2 b 
WHERE a.id = b.id ;

-- # ORACLE
ID	AGE	    ID	AGE	   (A.AGE+B.AGE)  (A.AGE-B.AGE)	(A.AGE*B.AGE)  (A.AGE/B.AGE)
1	30	    1	30	    60	          0	            900	            1
2	[NULL]	2	27	    [NULL]	      [NULL]	    [NULL]	        [NULL]
3	26	    3	26	    52	          0	            676	            1
4	25	    4	[NULL]	[NULL]	      [NULL]	    [NULL]	        [NULL]
5	24	    5	24	    48	          0	            576	            1
6	22	    6	22	    44	          0	            484	            1


-- # PostgreSQL
ID	AGE	    ID	AGE	   (A.AGE+B.AGE)  (A.AGE-B.AGE)	(A.AGE*B.AGE)  (A.AGE/B.AGE)
1	30	    1	30	    60	          0	            900	            1
2	[NULL]	2	27	    [NULL]	      [NULL]	    [NULL]	        [NULL]
3	26	    3	26	    52	          0	            676	            1
4	25	    4	[NULL]	[NULL]	      [NULL]	    [NULL]	        [NULL]
5	24	    5	24	    48	          0	            576	            1
6	22	    6	22	    44	          0	            484	            1

2. 비교연산에서의 NULL ( IN 조건 포함)

알 수 없는 값인 NULL은 어떤 비교를 하든 그 결과값은 NULL(알 수 없는 값)이다. 

SELECT의 결과가 반환될 때는 WHERE절의 결과가 TRUE가 될 때 뿐이다. FALSE와 NULL의 경우는 반환되지 않는다. 

-- =
SELECT a.ID FROM test a 
WHERE a.age = NULL ; 

--아무것도 조회되지 않는다.

-- >
select b.* from test a, test2 b
where a.id = 2 and a.age > b.age ;

--아무것도 조회되지 않는다.

-- <> 
SELECT a.* FROM test a 
WHERE a.age <> '50' ; 

id	name	location	age
1	혜리	광주	30
3	리정	서울	26
4	예원	서울	25
5	채원	서울	24
6	파트리샤	광주	22

-- age가 널값인 미연의 데이터는 나오지 않는다. 

-- in 조건 
SELECT a.* FROM test a, test b
WHERE a.age IN (b.age) ;

id	name	location	age
1	혜리	광주	30
3	리정	서울	26
4	예원	서울	25
5	채원	서울	24
6	파트리샤	광주	22

-- age가 널값인 미연의 데이터가 나오지 않는다.

SELECT a.* FROM test a 
WHERE a.age IN (30 , NULL);

id	name	location	age
1	혜리	광주	30

-- age가 널값인 미연의 데이터가 나오지 않는다.

3. 집계함수에서의 NULL 

집계함수 : sum, avg, max, min 과 같은 함수들에서는 NULL 값이 있는 레코드는 제외하고 계산한다. 

select sum(age), avg(age), max(age), min(age) from test;

sum	avg	max	min
127	25.4	30	22


select count(age) from test;
5
-- age가 널값인 미연은 제외하고 count 되었다.

4. 논리연산에서의 NULL

논리연산자는 AND, OR, NOT이 있다. 

5. INDEX에서의 NULL

오라클에서는 인덱스가 있는 컬럼이라도 조건절에서 IS NULL 혹은 IS NOT NULL 구문 사용 시 인덱스를 사용하지 못하고 해당 테이블을 FULL SCAN 하게 된다. 그 이유는 인덱스는 기본적으로 NULL 정보를 보관하지 않기 때문이다. (PostgreSQL에선 NULL이 존재하더라도 인덱스를 사용할 수 있다.) 

그러나, 인덱스가 복합 인덱스로 구성되어있고, NOT NULL인 컬럼이 있다면 인덱스 정보를 저장할 수 있기 때문에 인덱스 스캔이 가능하다.

--## ORACLE
CREATE INDEX test_idx_1 ON test ( age ); 
SELECT /*+ index(a test_idx_1) */ * FROM test a WHERE age = 30 ;

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST       |      1 |      1 |    23 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN                  | TEST_IDX_1 |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------------------------------------------------

SELECT /*+ index(a test_idx_1) */ * FROM test a WHERE age IS null;

---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST |      1 |    23 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


-- ## PostgreSQL 
-- PostgreSQL 같은 경우엔 is null 조건이여도 index scan이 가능하다.

postgres=# /*+ IndexScan(a)  */
explain select * from test a where age = 30;
                                QUERY PLAN
--------------------------------------------------------------------------
 Index Scan using test_idx_1 on test a  (cost=0.13..8.15 rows=1 width=84)
   Index Cond: (age = 30)
(2 rows)

postgres=# /*+ IndexScan(a)  */
explain select * from test a where age is null;
                                QUERY PLAN
--------------------------------------------------------------------------
 Index Scan using test_idx_1 on test a  (cost=0.13..8.15 rows=1 width=84)
   Index Cond: (age IS NULL)
(2 rows)

6. 함수에서의 NULL

오라클에서 사용하는 대표적인 NULL 관련 함수는 NVL과 NVL2 이다. 

SELECT sum(age) FROM test;           -- 127
SELECT sum(NVL(age, 0)) FROM test;   -- 127
SELECT NVL(sum(age), 0) FROM test;   -- 127

위의 결과는 다 같지만 우리는 세번째 방법을 사용하여 쿼리를 작성해야 한다. 첫번째 방식에선 집계함수 같은 경우 NULL 제외하고 계산이 되기 때문에 올바른 값이 나오긴 하지만 모든 값이 NULL인 경우 NULL 값을 반환하게 된다. 

두번째 방법은 집계함수에서는 NULL 값이 있는 레코드는 제외하고 계산하기 때문에 NVL 사용의 의미가 없다. 오히려 레코드 수만큼 함수를 호출함에 따라 부하가 발생한다. 세번째 방법은 집계할 코드가 없더라도 0값을 리턴 하고 NVL함수는 최종 집계 결과에 대해서 한번만 호출 되었다. 당연히 세번째 방법을 사용해야 한다. 

7. GROUP BY에서의 NULL

예제

• deptno가 NULL인 행들도 포함되어 있다면,
• deptno IS NULL인 행들이 별도의 그룹으로 묶여 집계됨

 예시 출력

8. CASE 구문

1. 조건식에 NULL이 사용될 때 (CASE WHEN 조건 THEN ...)

• 조건이 NULL이면, 이는 FALSE가 아니라 "UNKNOWN", 즉 조건 불충족으로 간주됩니다.
• 그래서 해당 WHEN 절은 건너뛰고, 다음 조건을 평가하거나 ELSE로 넘어갑니다.

 예제

• WHEN NULL → 조건식이 UNKNOWN → 건너뜀 → ELSE 'B' 반환

2. CASE expression 형태일 때 (CASE expr WHEN value THEN ...)

이 경우엔 = 비교를 수행합니다.

예제

• NULL = 'A', NULL = 'B' → 둘 다 FALSE (정확히는 UNKNOWN) → ELSE 실행

3. WHEN 컬럼 IS NULL 처럼 명시적으로 비교하면?

정상 작동합니다.

→ 이 방식은 NULL을 올바르게 인식합니다.

요약

엔터티, 인스턴스, 속성, 속성값의 관계 

엔터티(Entity) 

 
1. 엔터티의 개념 
 
엔터티는 쉽게 말해 실체, 객체라고 할 수 있다.
일반적으로 엔터티를 정의하는 개념들을 정리하여 나타내면 다음과 같이 볼 수 있다. 
 

• 엔터티는 사람, 장소 물건, 사건, 개념 등과 같은 명사에 해당된다. 
• 엔터티는 업무상 관리가 필요한 것에 해당된다. 
• 엔터티는 저장 되기 위한 어떤 것에 해당 된다. 
• 사전적 의미는 '독립체'
• 식별이 가능한 객체 
• 업무에서 쓰이는 데이터를 용도별로 분류한 그룹 ( 테이블 : table ) 

 
2. 엔터티의 특징 
 

• 업무에서 쓰이는 정보여야 함 
• 유니크함을 보장할 수 있는 식별자가 있어야함 : 인스턴스(row)를 식별 가능하도록 설계하는것이 바람짐하다. 
• 2개 이상의 인스턴스를 가지고 있어야함 : 1개의 인스턴스만 존재하는 엔터티는 존재가 없는 엔터티와 같다. 
• 반드시 속성( Attribute)을 가지고 있어야 함
• 다른 엔터티와 1개 이상의 관계를 가지고 있어야 함 : 각각의 엔터티는 다른 엔터티와 연관성을 가지고 있어야 한다. 

 
3. 엔터티의 분류 
 

• 유형 vs 무형 
  • 유형 엔터티 : 물리적인 형태 존재 ( 상품, 회원, 사원, 가맹점 등), 지속적으로 활용
  • 개념 엔터티 : 물리적인 형태 없음 ( 부서, 학과 등), 개념적인 정보
  • 사건 엔터티 : 행위를 함으로써 발생 됨. 통계로 자주 이용 (주문, 거래, 이벤트 응모 출석) 

• 발생시점
  • 기본 엔터티 : 독립적으로 존재 및 생성. 다른 엔터티의 부모역할을 한다. 즉 자식 엔터티 소유 가능 (상품, 회원 등)
  • 중심 엔터티 : 기본 엔터티로 부터 파생, 업무에 있어서 중심적인 역할을 함. 일반적으로 데이터 양이 많고, 행위 엔터티 생성 (주문, 거래 등)
  • 행위 엔터티 : 2개 이상의 부모 엔터티로 부터 파생 자주 엔터티의 내용이 바뀌거나 데이터양이 증감한다. 분석 초기 단계보다는 상세 설계단계나 프로세스와 상관 모델링을 진행하면서 도출될 수 있다.( 주문내역, 이벤트 응모 이력 HISTORY 등)  

 
4. 엔터티의 명명 

• 가능하면 현업업무에서 사용하는 용어를 사용한다.
• 가능하면 약어를 사용하지 않는다.
• 단수 명사를 사용한다.
• 모든 엔터티를 통틀어서 유일한 이름을 가져야 한다.
• 엔터티의 생성 의미대로 이름을 부여한다. 

속성(Attribute)

 
1. 속성의 개념 
속성 또한 엔터티와 같이 다음과 같은 성질을 가지고 있으며, 이러한 성질을 지니지 않는다면 적절하지 못한 속성일 확률이 높다.
 

• 사물이나 개념의 특징을 설명해 줄 수 있는 항목들을 속성이라고 부름 ( 속성은 엔터티의 특징을 나타내는 최소의 데이터 단위) 
• 속성은 의미상 더 이상 쪼개지지 않는 레벨 
• 엔터티와 마찬가지로 반드시 해당 업무에서 필요하고 관리하고자 하는 정보이어야 한다.
• 정규화 이론에 근간하여 정해진 주 식별자(기본키)에 함수적 종속성을 가져야 한다. 
• 다양하게 존재하는 인스턴스들에 대해 유일하게 구별할 수 있는 주식별자를 통해서 식별될 수 있어야 한다. 
• 하나의 속성에는 단 한개의 값만을 가진다. 

 
2. 속성값

• 속성값은 엔터티에 속한 하나의 인스턴스를 구체적으로 나타내주는 데이터라고 함
• 하나의 속성에는 단 한개의 값만을 가진다.

 
3. 속성 분류

• 특성에 따른 분류
  • 기본 속성 : 업무 프로세스 분석을 통해 바로 정의가 가능한 속성. 엔터티에서 가장 일번적이고 많은 속성을 차지 하지만 코드성 데이터, 엔터티를 식별하기 위해 부여된 일련번호, 또는 다른 속성을 계산되어 만들어진 속성들은 기본 속성이 아니다.
  • 설계 속성 : 설계를 하다보니 필요하다고 판단되어 도출된 속성(유니크함을 보장하기 위해 학번 등 설계속성을 만들어 학생 개개인에게 고유번호를 할당 할 수 있다. )  일반적으로 코드성 속성은 기존의 속성을 업무상 필요에 의해 변형하여 만든 설계 속성이다. 또한 일련번호와 같은 속성 또한 단일한 식별자를 부여하기 위해 모델 상에서 새롭게 정의하는 설계속성이다.
  • 파생 속성 : 다른 속성의 속성값을 계산하거나 특정한 규칙으로 변형하여 생성한 속성 ( 상품 구매 수량, 이벤트응모건수 등)  파생 속성은 가급적 적게 정의하는것이 좋다.
• 구성방식에 따른 분류
  • PK속성 : 엔터티의 인스턴스들을 식별할 수 있는 속성(유니크함을 부여하는 속성)
  • FK속성 : 다른 엔터티의 속성에서 가져온 속성 ( 다른 엔터티와 관계 매개체 역할을 수행하는 속성) 
  • 일반속성 : PK, FK를 제외한 나머지 속성 ( PK, FK 속성을 제외한 나머지 속성) 
• 세부 의미 유무에 따른 분류 
  • 단순 속성 : 나이, 성별과 같은 데이터는 더 이상 다른 속성들로 구성될 수 없는 단순한 속성이므로 단순 속성이라고 한다. 
  • 복합 속성 : 주소 속성에 대해서는 시, 구, 동, 번지와 같이 여러 세부 속성들로 구성될 수 있는데 이때 주소 속성과 같은 것들을 복합 속성이라고 한다. 

도메인(Domain) 

 
1. 도메인의 개념 
각 속성은 무한정적인 값을 갖는 것이 아니라 그 범위가 지정된다. 이 때 속성의 값이 가질 수 있는 범위를 그 속성의 도메인이라고 한다. 따라서 속성 값이 가질 수 있는 데이터 타입과 크기 그리고 추가적인 제약사항이라고 생각할 수 있다.
 
 
 
 
참고
https://176farm.tistory.com/21

https://doorbw.tistory.com/227

Memory 관련 parameter

• shared_buffer

 
PostgreSQL은 자체 버퍼를 사용하며 커널 버퍼 IO도 사용합니다. 이는 데이터가 메모리에 두 번 저장된다는 것을 의미하며, 먼저 Postgres에 저장됩니다. SQL 버퍼 다음으로 커널 버퍼입니다. 다른 데이터베이스와 달리 PostgreSQL은 직접 IO를 제공하지 않습니다. 이를 이중 버퍼링이라고 합니다. PostgreSQL은 버퍼를 shared_buffer라고 하며 대부분의 운영 체제에서 가장 효과적인 조정 가능 매개 변수입니다. 이 매개 변수는 PostgreSQL은에서 사용할 전용 메모리의 양을 설정합니다 캐시에 대한 파라미터로 디스크의 엑세스를 줄여 성능을 확보할 수 있는 파라미터 입니다.
 
shared_buffer의 기본값은 매우 낮게 설정되어 있으므로 큰 이점을 얻을 수 없습니다. 특정 시스템과 운영 체제가 더 높은 값을 지원하지 않기 때문에 이 값이 낮습니다. 그러나 대부분의 최신 시스템에서는 최적의 성능을 위해 이 값을 늘려야 합니다.
 
권장 값은 총 시스템 RAM의 25%입니다. 경우에 따라 25% 이상의 설정으로 양호한 성능을 얻을 수 있으므로 더 낮은 값과 더 높은 값을 시도해야 합니다. 구성은 실제로 시스템과 작업 데이터 세트에 따라 달라집니다. 작업 중인 데이터 세트가 RAM에 쉽게 들어갈 수 있는 경우, 전체 데이터베이스를 포함하도록 shared_buffer 값을 늘려 전체 작업 데이터 세트가 캐시에 상주할 수 있도록 할 수 있습니다. 그렇긴 해도, 당신은 Postgre를 위해 모든 RAM을 사용하고 싶진 않을 겁니다. 그리고 PostgreSQL은 캐싱(이중 버퍼링)을 위해 운영 체제에 크게 의존합니다. 따라서 shared_buffers에 RAM의 40% 이상을 할당할 경우, 더 작은 값을 할당하는 경우에 비해 더 좋은 성능을 발휘할 가능성이 낮습니다.
 
운영 환경에서는 shared_buffer 값이 크면 성능이 매우 우수하지만 항상 벤치마크를 통해 적절한 균형을 찾아야 합니다.
 

• Default : 128MB
• 산정 방법 : 서버 메모리의 1/4~1/2
• 설정 주의 : 장시간에 걸쳐 대량의 데이터 또는 변경된 데이터 쓰기 프로세스를 실행하기 위해 shared_buffers를 더 크게 설정하면 checkpoint_segments(10미만 버전),

                       max_wal_size(10이상) 에서도 그에 맞게 설정을 증가시켜야 합니다.
               * PostgreSQL 10부터 checkpoint_segments가 max_wal_size로 대체되었습니다.
 
 
shared_buffer 확인
 

testdb=# SHOW shared_buffers;

shared_buffers

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

128MB0000000000000

(1 row)

• wal_buffers

 
PostgreSQL은 WAL(Write Ahead Log) 레코드를 버퍼에 기록한 다음 이러한 버퍼를 디스크에 플러시합니다. wal_buffer는 wal 로그를 메모리에 저장할 수 있는 크기를 지정한다.
Default : -1 ( 4MB) shared_buffers에 따라 자동적으로 설정된다. , shared_buffers가 128MB 면 4MB로 설정된다. ( shared_buffers / 32 )
* PostgreSQL 10부터 shared_buffers 크기에 따라 자동적으로 설정이 되기 때문에 설정해 줄 필요가 없어졌습니다.
 

• work_mem

 
이 파라미터는 sort, merge, join 에 사용는 메모리 사용량을 의미합니다. 만약 당신이 복잡한 정렬을 해야 한다면 좋은 결과를 얻기 위해 work_mem의 값을 늘려야 한다. 메모리 내 정렬은 디스크로 유출되는 정렬보다 훨씬 빠릅니다. 이 매개 변수는 사용자 정렬 작업당이므로 매우 높은 값을 설정하면 배포 환경에 메모리 병목 현상이 발생할 수 있습니다. 따라서 정렬 작업을 실행하려는 사용자가 많은 경우 시스템은 모든 사용자에게 (work_mem * 총 정렬 작업 개수)  만큼의 메모리 크기를 할당합니다. 이 매개 변수를 전체적으로 설정하면 메모리 사용량이 매우 높을 수 있습니다. 따라서 세션 수준에서 이를 수정하는 것이 좋습니다.
 

• Default : 4MB
• 산정 방법
  • 방법 1 : ( 시스템 전체 메모리) / ( max_connections * 16 )
  • 방법 2 : ( OS cache memory / max_connections ) * 0.5

testdb=# SET work_mem TO "2MB";
testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
Gather Merge  (cost=509181.84..1706542.14 rows=10000116 width=24)
   Workers Planned: 4
   ->  Sort  (cost=508181.79..514431.86 rows=2500029 width=24)
         Sort Key: b
         ->  Parallel Seq Scan on bar  (cost=0.00..88695.29 rows=2500029 width=24)
(5 rows)

초기 쿼리의 정렬 노드의 추정 비용은 514431.86이다. 비용은 임의의 계산 단위이다. 위 쿼리의 경우 work_mem은 2MB밖에 없습니다. 테스트를 위해 256MB로 늘리고 비용에 영향이 있는지 확인해 보겠습니다.
 

testdb=# SET work_mem TO "256MB";
testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
Gather Merge  (cost=355367.34..1552727.64 rows=10000116 width=24)
   Workers Planned: 4
   ->  Sort  (cost=354367.29..360617.36 rows=2500029 width=24)
         Sort Key: b
         ->  Parallel Seq Scan on bar  (cost=0.00..88695.29 rows=2500029 width=24)

쿼리 비용이 514431.86에서 360617.36으로 30% 감소했습니다.
 

• maintenance_work_mem

maintenance_work_mem은 Vacuum, Create Index, Alter Table ADD Foreign key와 같은 유지 관리 작업에 사용되는 메모리 설정입니다. 기본값은 64MB입니다. 큰 값을 설정하면 VACUM, RESTORE, CREATE INDEX, ADD Foreign KEY, ALTER TABLE 등의 작업에 도움이 됩니다.
 

• Default : 64MB
• 산정 방법
  • 서버 메모리의 1/16 설정
  • 대형 데이터베이스의 경우 256MB에서 1GB 정도 값을 설정

postgres=# CHECKPOINT;
postgres=# SET maintenance_work_mem to '10MB';

postgres=# CREATE INDEX foo_idx ON foo (c);
CREATE INDEX
Time: 170091.371 ms (02:50.091)

postgres=# CHECKPOINT;
postgres=# set maintenance_work_mem to '256MB';

postgres=# CREATE INDEX foo_idx ON foo (c);
CREATE INDEX
Time: 111274.903 ms (01:51.275)

maintenance_work_mem을 10MB로 설정한 경우 인덱스 생성 시간은 170091.371ms이지만 maintenance_work_mem 설정을 256MB로 늘리면 111274.903ms로 단축됩니다.

Disk 기록 관련 파라미터

• synchronous_commit

이는 클라이언트에 성공 상태를 반환하기 전에 디스크에 WAL이 기록될 때까지 대기하는 커밋을 시행하는 데 사용됩니다. 이는 성능과 신뢰성 사이의 균형입니다. 애플리케이션이 안정성보다 성능이 더 중요하도록 설계된 경우 synchronous_commit을 해제하십시오. 즉, 성공 상태와 보장된 디스크 쓰기 사이에 시간 차이가 발생합니다. 서버 충돌의 경우 클라이언트가 커밋 시 성공 메시지를 수신했음에도 데이터가 손실될 수 있습니다. 이 경우 트랜잭션은 WAL 파일이 플러시될 때까지 기다리지 않기 때문에 매우 빠르게 커밋되지만 신뢰성은 저하됩니다.
 

• Default : on

• checkpoint_timeout, checkpoint_completion_target

PostgreSQL은 WAL에 변경 사항을 기록합니다. 체크포인트 프로세스는 데이터를 데이터 파일로 플러시합니다. 이 활동은 CHECKPOINT가 발생할 때 수행됩니다. 이는 비용이 많이 드는 작업이며 엄청난 양의 IO를 유발할 수 있습니다. 이 전체 프로세스에는 값비싼 디스크 읽기/쓰기 작업이 수반됩니다. 사용자는 필요할 때마다 CHECKPOINT를 발행하거나 checkpoint_timeout 및 checkpoint_completion_target 매개 변수를 통해 자동화할 수 있습니다.
 
checkpoint_timeout 매개 변수는 WAL 체크포인트 사이의 시간을 설정하는 데 사용됩니다. 이 값을 너무 낮게 설정하면 Disk에 더 많은 데이터가 기록되므로 충돌 복구 시간이 단축되지만 모든 체크포인트가 귀중한 시스템 리소스를 사용하게 되므로 성능도 저하됩니다.
 
  checkpoint_completion_target은 체크포인트 완료를 위한 체크포인트 사이의 시간 비율입니다. 대량의 페이지 쓰기로 I/O 시스템이 플러딩되는 것을 방지하기 위해 체크포인트 동안 더티 버퍼 쓰기는 일정 기간 동안 분산됩니다. 지정된 checkpoint_timeout 초의 일부가 경과하거나 max_wal_size가 초과되기 전 중 더 빠른 시점에 체크포인트가 완료되도록 I/O 속도가 조정됩니다. 체크포인트의 빈도가 높으면 성능에 영향을 줄 수 있습니다. 원활한 체크포인트를 위해 checkpoint_timeout은 낮은 값이어야 합니다. 그렇지 않으면 OS는 비율이 충족될 때까지 모든 더티 페이지를 축적한 다음 큰 플러시를 수행합니다.
 

• Default
• checkpoint_timeout : 5min
• checkpoint_completion_target : 0.5

• fsync

이 매개 변수가 설정되어 있으면 PostgreSQL 서버는 fsync() 시스템 호출 또는 이와 동등한 다양한 메소드를 실행하여 업데이트가 실제로 디스크에 기록되도록 합니다. 이를 통해 운영 체제 또는 하드웨어 충돌 후 데이터베이스 클러스터가 일관된 상태로 복구 될 수 있습니다.
 
fsync= on - PostgreSQL서버는 fsync()시스템 콜을 통해서 변경분을 디스크에 물리적으로 바로 씁니다. 이는 데이터베이스클러스터가 OS나 하드웨어 장애시 consistent한 상태로 복구가 가능함을 보장합니다.
fsync= off - OS가 알아서 메모리에 있는 것을 디스크로 내려쓰게 됩니다. 성능상 이득을 볼수는 있겠지만, 전원장애나 system crash로가 발생했을때 복구가 불가능할 수도 있습니다. 외부 데이터로 전체 데이터베이스를 손쉽게 재생성할 수 있는 경우에만 fsync를 해제하는 것이 바람직합니다.
 

• Default : on
• 설정 주의만약 off하기로 했다면, full_page_writes도 off하는 것을 고려하도록 합니다.

• full_page_writes
• full_page_writes = on

• PostgreSQL 서버는 checkpoint 이후의 각 디스크 페이지를 처음 수정하는 도중에 해당 페이지의 전체 내용을 WAL에 기록합니다.
• 운영 체제 충돌 시 진행 중인 페이지 쓰기가 부분적으로만 완료되어 디스크 상의 페이지에 옛날 데이터와 새 데이터가 공존할 수 있기 때문에 필요합니다.
• 전체 페이지 이미지를 저장하면 페이지의 올바른 복구가 보장되지만 WAL에 기록해야 하는 데이터량의 증가를 감수해야 합니다.

• full_page_writes = off

• 정상적인 운영 속도가 빨라지지만 시스템 장애 발생 시 손상된 데이터가 복구 불가능하게 되거나 데이터 손상이 드러나지 않을 수 있습니다.

• Default : on

Database 접속 세션 수 관련

• max_connections

데이터베이스 서버에 대한 최대 동시 연결 수를 결정합니다.

• Default : 100

Optimizer 관련 파라미터

• random_page_cost

PostgreSQL 옵티마이저가 디스크에서 임의 페이지를 읽는 비용을 추정하고 인덱스 또는 순차 스캔의 사용을 결정할 수 있습니다. 값이 높을수록 순차 스캔이 더 많이 사용됩니다. 값이 낮을 수록 인덱스를 사용할 가능성이 높아집니다.
 

• Default : 4
• 설정 주의

• effective_cache_size

effective_cache_size는 단일 쿼리에 대해 쿼리당 데이터 캐싱을 위한 총 메모리 양을 결정합니다. 다시 말해 디스크 캐시의 효율적인 크기 (캐시할 수 있는 메모리 양) 이것은 정확하게 할당된 메모리나 캐시 크기가 아닌 지침일 뿐이다. 실제 메모리를 할당하지는 않지만 커널에서 사용 가능한 캐시의 양을 옵티마이저에 알려준다. 이 값이 너무 낮게 설정된 경우 쿼리 플래너는 일부 인덱스가 유용하더라도 사용하지 않기로 결정할 수 있습니다. 따라서 큰 값을 설정하는 것이 항상 유익합니다.
 

• Default : 4GB
• 산정 방법
• 설정 주의

• cpu_tuple_cost

쿼리 중 각 행을 처리하는 플래너의 추정 비용을 설정합니다. 기본값은 0.01입니다.

• Default : 0.01

• cpu_index_tuple_cost

인덱스 스캔 중 각 인덱스 항목을 처리하는 비용의 플래너의 추정치를 설정합니다.

• Default : 0.005

• cpu_operator_cost

플래너가 예상한, 쿼리 도중 실행된 각 연산자 또는 함수의 처리 비용을 설정한다.

• Default : 0.0025

실행계획  관련 파라미터

• default_statistics_target

Postgresql은 개별 테이블 통계가 필요합니다. ALTER TABLE SET STATISTICS를 통해 설정 하면 테이블 컬럼에 대한 기본 통계 타겟을 설정한다. 큰 값을 설정 하면 ANALYZE를 수행하는 데 필요한 시간이 늘어나지만 플래너 평가 수준을 높일 수 있다.

• Default : 100
• 설정 주의

• track_count

데이터베이스 작업에 대한 통계 수집을 활성화 합니다.

• Default : on
• 설정 주의

 
결론
더 나은 성능을 얻기 위해 조정할 수 있는 더 많은 매개 변수가 있지만 여기서 강조한 매개 변수보다 영향이 적습니다. 마지막으로, 모든 매개 변수가 모든 애플리케이션 유형과 관련이 있는 것은 아니라는 점을 항상 염두에 두어야 합니다. 또한 데이터베이스 매개 변수는 응용 프로그램 및 응용 프로그램이 실행되는 OS의 특정 요구에 대해 수행되어야 합니다.