검색결과 리스트
데이터베이스에 해당되는 글 5건
- 2015.08.29 [SQLD] 2과목 SQL 기본및 활용 - 1장 7절 GROUP BY, HAVING 절
- 2015.08.26 [SQLD] 2과목 SQL 기본및 활용 - 1장 1절 관계형 데이터베이스 개요
- 2015.08.24 [SQLD] 1과목 데이터 모델링의 이해 - 2장 4절:대량 데이터에 따른 성능
- 2015.08.23 [SQLD] 1과목 데이터 모델링의 이해 - 2장 3절:반정규화와 성능
- 2015.08.23 [SQLD] 1과목 데이터 모델링의 이해 - 2장 1절:성능 데이터 모델링의 개요
글
집계함수 | 사용목적 |
COUNT(*) | NULL 값을 포함한 행의 수를 출력 |
COUNT(표현식) | 표현식의 값이 NULL 값인 것을 제외한 행의 수를 출력 |
SUM([DISTINCT | ALL] 표현식) | 표현식의 NULL 값을 제외한 합계를 출력한다. |
AVG([DISTINCT | ALL] 표현식) | 표현식의 NULL값을 제외한 평균을 출력한다. |
MIN([DISTINCT | ALL] 표현식) | 표현식의 최소값을 출력 (문자,날짜,데이터타입 사용가능) |
STDDEV([DISTINCT | ALL] 표현식) | 표현식의 표준편차 출력 |
VARIAN([DISTINCT | ALL] 표현식 ) | 표현시의 분산 출력 |
기타. | 벤더별로 다양한 통계식 제공함. |
- 일반적으로 집계함수는 GROUP BY 절과 같이 사용된다.
(테이블 전체가 하나의 그룹이 되는 경우에는 GROUP BY 절 없이 단독으로 사용 가능하다.)
SELECT COUNT(*) " 전체행수", COUNT(HEIGHT) "키 건수", MAX(HEIGHT) 최대키, MIN(HEIGHT) 최소키, ROUND(AVG(HEIGHT),2) 평균키
FROM PLAYER;
-> COUNT(HEIGHT)는 NULL을 제외한 결과만 출력. COUNT(*)은 NULL을 포함한 결과값 출력.
2. GROUP BY 절.
- WHERE 절을 통해 조건에 맞는 데이터를 조회했지만, 테이블에 1차적으로 존재하는 데이터 이외의 정보 등 2차 가공이 필요할 때.,
(각 팀별로 선수가 몇명인지, 선수들의 평균 신장과 몸무게가 얼마나 되는지, 또는 각 팀에서 가장 큰 키의 선수가 누구인지 등의 2차 가공 정보도 필요하다.)
- GROUP BY 절은 SQL 문에서 FROM 절과 WHERE 절 뒤에 오며 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별로 통계 정보를 얻을 때 추가로 사용된다.
* GROUP BY 절과 HAVING절의 특징
- GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다.
- 집계함수의 통계 정보는 NULL값을 가진 행을 제외하고 수행한다.
- GROUP BY 절에는 SELET 절과는 달리 ALIAS 명을 상용할 수 없다.
- 집계 함수는 WHERE 절에는 올 수 없다.
(집계 함수를 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행된다.)
- HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있다.
- GRUOP BY 절에 의한 소그룹 별로 만들어진 집계 데이터 중 HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다.
- HAVING 절은 일반적으로 GROUP BY절 뒤에 위치한다.
* GROUP BY 절에서 그룹 단위를 표시해 주어야 SELECT 절에서 그룹단위의 칼럼과 집계함수를 사용할 수 있다.
EX. 각 부서별 평균급여 집계
1) GROUP BY 사용없이 집계함수 사용.
SELECT DEPTNO, AVG(SAL)
FROM EMP;
결과 :
SELECT SAL, AVG(SAL)
*
ERROR at line 1:
ORA-00937: not a single-group group function
=> 에러가 발생한다.( GROUP BY 절에 그룹 단위를 표시해 주어야 그릅단위 컬럼과 집계함수를 사용할 수 있다.)
2) GROUP BY 사용.
SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO;
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2175
10 2916.66667
=> 원하는 결과 출력됨.
SELECT DEPTNO DEPART_NUMBER, AVG(SAL) AVG_SAL
FROM EMP
GROUP BY DEPART_NUMBER;
ERROR at line 3:
ORA-00904: "DEPART_NUMBER": invalid identifier
=> 칼럼에 대한 ALIAS는 SELECT 절에 정의하고 ORDER BY 절에서는 재활용 할 수 없지만 GROUP BY 절에서는 사용할 수 없다.
- SELECT에 GROUP BY에 포함되지 않은 칼럼을 사용할 경우 에러가 발생한다.
SELECT ENAME, AVG(SAL) AVG_SAL
FROM EMP
GROUP BY DEPTNO;
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SELECT DEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO; DEPTNO AVG_SAL ---------- ---------- 30 1566.66667 20 2175 10 2916.66667 |
3. HAVING 절
예제 1. K - 리그 선수들의 포지션별 평균 키를 구하는데 평균키가 180센티미터 이상인 정보만 표시하라.
( WHERE절과 GROUP BY 절을 사용해 작성하라.
A. 오답
SELECT POSITION, ROUND(AVG(HEIGHT),2)
FROM PLAYER
WHERE AVG(HEIGHT)>=180
GROUP BY(POSITION)
=> 에러발생한다. WHERE절에는 AVG()라는 집계함수를 사용할 수 없다.
- WHERE 절은 FROM절에 정의된 집합의 개별행에 WHERE절의 조건이 먼저 적용되고 WHERE절의 조건에 맞는 행이 GROUP BY절의 대상이된다. 그런다음 HAVING 조건절이 적용된다.
- HAVING절은 WHERE절과 비슷하지만 그룹을 나타내는 결과 집합의 행에 조건이 적용된다는 점에 차이가 있다.
- GROUP BY 절과 HAVING 절의 순서를 바꿔도 ORACLE 에서는 문법에러가 없지만, SQL server 에서는 문법오류가 발생한다.
A. 정답 SELECT POSITION, ROUND(AVG(HEIGHT),2) AVG_HEIGHT FROM PLAYER GROUP BY POSITION HAVING AVG(HEIGHT) >=180 ; POSITION AVG_HEIGHT -------------- ------------- GK 186,26 DF 180.21
SELECT DEPTNO, ROUND(AVG(SAL),2) FROM EMP GROUP BY DEPTNO HAVING ROUND(AVG(SAL),2)>=2000;
DEPTNO ROUND(AVG(SAL),2) ---------- ----------------- 20 2175 10 2916.67 |
예제2.
K - 리그 선수들 중 삼성블루윙즈(K02)와 FC서울(K09)의 인원수는 얼마인가에 대한 쿼리를 써라.
A1.
SELECT TEAM_ID, COUNT(*)
FROM PLAYER
WHERE TEAM_ID IN ('K02','K09')
GROUP BY (TEAM_ID)
A2.
SELECT TEAM_ID, COUNT(*)
FROM PLAYER
GROUP BY TEAM_ID
HAVING TEAM_ID IN ('K02','K09)
결과는 둘다 다음과 같다.
TEAM_ID COUNT
------------- -------------
K02 49
K09 49
* GROUP BY 소그룹의 데이터 중 일부만 필요한 경우 GROUP BY 연산 전 WHERE 절에서 조건을 적용하여 필요한 데이터만 추출하여 GROUP BY 연산을 하는 방법과, HAVING 절에서 필요한 데이터만 필터하는 두 가지 방법이 있다.
가능하면 WHERE 절에서 조건을 적용하여 GROUP BY대상을 줄이는 것이 효율적인 자원 사용 측면에서 바람직하다.
실습 가능한 EMP 테이블로 조회 Q. 각 DEPTNO 10번과 20번의 사원들 수 를 추출하라. A1. SELECT DEPTNO, COUNT(*) FROM EMP WHERE DEPTNO IN(10,20) GROUP BY DEPTNO; A.2 SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO HAVING DEPTNO IN (10,20) DEPTNO COUNT(*) ---------- ---------- 20 5 10 3 |
예제3. 포지션별 평균키만 출력하는데, 최대키가 190 이상인 선수를 가지고 있는 포지션의 정보만 추출한다.
* SELECT 절에 사용하지않은 MAX라는 집계함수를 HAVING 절에 조건으로 사용한 예시
A.
SELECT POSITION, AVG(HEIGHT)
FROM PLAYER
GROUP BY POSITION
HAVING MAX(HEIGHT) > =190 ;
즉, HAVING 절은 SELECT 절에 사용되지 않은 컬럼이나 집계함수가 아니더라도 GROUPBY 절의 기준항목이나 소그룹의 집계함수를 이용한 조건을 표시할 수 있다.
* 주의 : WHERE절의 조건 변경은 대상 데이터의 개수가 변경되므로 결과 데이터의 값이 변경될 수 있지만, HAVING 절의 조건 변경은 결과 데이터 변경은 없고 출력되는 레코드의 개수만 변경될 수 있다.
EMP 테이블에 적용 Q. 부서별 평균급여를 출력하는데, 최대 급여가가 2000 이상인 부서의 정보만 추출 - 부서의 맥스 SAL 결과 FROM EMP GROUP BY DEPTNO DEPTNO MAX(SAL) ---------- ---------- 30 2850 20 3000 10 5000
============================================================================= A. SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING MAX(SAL) > = 3000; DEPTNO AVG(SAL) ---------- ---------- 20 2175
10 2916.66667 * 30번 부서는 MAX가 3000 이하이므로 결과에서 필터된것을 확인할 수 있다. |
4. CASE 표현을 활용한 월별 데이터 집계
- 집계함수 CASE () ~ GROUP BY 기능은 모델링의 제1 정규화로 인해 반복되는 칼럼의 경우 구분 칼럼을 두고 여러 개의 레코드로 만들어진 집합을, 정해진 칼럼 수만큼 확장해서 집계 보고서를 만드는 유용한 기법이다.
예제 : 부서별로 월별 입사자의 평균 급여를 알고싶다.
STEP 1) 개별 입사정보에서 월별 데이터를 추출하는 작업 진행
A1. 오라클 SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) HIRE_MON, SAL FROM EMP; A2. SQL server SELECT ENAME, DEPTNO, DATEPART(MONTH, HIREDATE) HIRE_MON, SAL FROM EMP;
STEP 2. 월별 데이터 구분 A. ORACLE SELECT ENAME, DEPTNO, CASE MONTH WHEN 1 THEN SAL END M01, CASE MONTH WHEN 2 THEN SAL END M02, CASE MONTH WHEN 3 THEN SAL END M03, CASE MONTH WHEN 4 THEN SAL END M04, CASE MONTH WHEN 5 THEN SAL END M05, CASE MONTH WHEN 6 THEN SAL END M06, CASE MONTH WHEN 7 THEN SAL END M07, CASE MONTH WHEN 8 THEN SAL END M08, CASE MONTH WHEN 9 THEN SAL END M09, CASE MONTH WHEN 10 THEN SAL END M10, CASE MONTH WHEN 11 THEN SAL END M11, CASE MONTH WHEN 12 THEN SAL END M12 FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL FROM EMP) ; * FROM 절에 사용된 인라인뷰는 2장4절에서 배울예정. STEP 3. 부서별 데이터 집계 ( 부서별로 월별 입사자의 평균급여를 알기위해 AVG함수와 GROUP BY 절 사용.) SELECT DEPTNO, AVG(CASE MONTH WHEN 1 THEN SAL END) M01, AVG(CASE MONTH WHEN 2 THEN SAL END) M02, AVG(CASE MONTH WHEN 3 THEN SAL END) M03, AVG(CASE MONTH WHEN 4 THEN SAL END) M04, AVG(CASE MONTH WHEN 5 THEN SAL END) M05, AVG(CASE MONTH WHEN 6 THEN SAL END) M06, AVG(CASE MONTH WHEN 7 THEN SAL END) M07, AVG(CASE MONTH WHEN 8 THEN SAL END) M08, AVG(CASE MONTH WHEN 9 THEN SAL END) M09, AVG(CASE MONTH WHEN 10 THEN SAL END) M10, AVG(CASE MONTH WHEN 11 THEN SAL END) M11, AVG(CASE MONTH WHEN 12 THEN SAL END) M12 FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL FROM EMP) GROUP BY DEPTNO; |
=> 같은 예제 ORACLE의 DECODE 사용 쿼리.
SELECT DEPTNO,
AVG(DECODE(MONTH, 1,SAL)) M01,
AVG(DECODE(MONTH, 2,SAL)) M02,
AVG(DECODE(MONTH, 3,SAL)) M03,
AVG(DECODE(MONTH, 4,SAL)) M04,
AVG(DECODE(MONTH, 5,SAL)) M05,
AVG(DECODE(MONTH, 6,SAL)) M06,
AVG(DECODE(MONTH, 7,SAL)) M07,
AVG(DECODE(MONTH, 8,SAL)) M08,
AVG(DECODE(MONTH, 9,SAL)) M09,
AVG(DECODE(MONTH, 10,SAL)) M10,
AVG(DECODE(MONTH, 11,SAL)) M11,
AVG(DECODE(MONTH, 12,SAL)) M12
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL FROM EMP)
GROUP BY DEPTNO;
5. 집계 함수와 NULL 처리.
- 리포트의 빈칸을 NULL이 아닌 숫자 0 으로 표현하기 위해 NVL / ISNULL 함수를 사용하는 경우가 많은데, 다중행 함수를 이용하는 경우 오히려 불필요한 부하가 발생하므로 굳이 NVL 함수를 다중행 함수안에 사용할 필요가 없다.
- 다중행 함수는 입력값의 전체가 NULL인 경우에만 NULL이 리턴되고 전체 건수 중 일부만 NULL인 경우 NULL을 함수의 대상에서 제외한다.
- CASE 사용시 ELSE 절을 생략하게 되면 Default 값이 null이다. null 이 연산의 대상이 아닌 반면 SUM(CASE MONTH WHEN 1 THEN SAL ELSE 0 END) 처럼 ELSE절에서 0을 지정하게되면 SUM 연산에 사용되므로 자원의 사용이 많아진다. 같은 결과를 얻을 수 있다면 가능한 ELSE절의 상수값을 지정하지 않거나 ELSE 절을 작성하지 않는것이 좋다.
- 개별 데이터의 급여가 NULL인 경우 NULL의 특성으로 자동적으로 연산에서 제외되게 되는데 불필요하게 ISNULL 또는 NVL함수를 사용해 데이터 건수만큼 연산이 일어가네 하는 것은 자원낭비다.
ex) SUM(NVL(SAL,0)) / SUM(ISNULL(SAL,0))
-리포트 출력 때 NULL이 아는 0을 표시하고 싶은 경우에는 NVL(SUM(SAL),0) 혹은 ISNULL(SUM(SAL),0) 을 사용하면된다.
예제: 팀별 포지션별 FW, MF, DF, GK 포지션의 인원수와 팀별 전체 인원수를 구하는 SQL 문장 작성
A. SIMPLE CASE EXPRESSION
SELECT TEAM_ID,
NVL(SUM(CASE POSITION WHEN 'FW' THEN 1 ELSE 0 END),0) FW,
NVL(SUM(CASE POSITION WHEN 'MF' THEN 1 ELSE 0 END),0) MF,
NVL(SUM(CASE POSITION WHEN 'DF' THEN 1 ELSE 0 END),0) DF,
NVL(SUM(CASE POSITION WHEN 'GK' THEN 1 ELSE 0 END),0) GK,
COUNT(*)
GROUP BY TEAM_ID
A2. ELSE 생략가능하므로 생략
SELECT TEAM_ID,
NVL(SUM(CASE POSITION WHEN 'FW' THEN 1 END),0) FW,
NVL(SUM(CASE POSITION WHEN 'MF' THEN 1 END),0) MF,
NVL(SUM(CASE POSITION WHEN 'DF' THEN 1 END),0) DF,
NVL(SUM(CASE POSITION WHEN 'GK' THEN 1 END),0) GK,
COUNT(*)
GROUP BY TEAM_ID
ISNULL(SUM(CASE WHEN POSITION='MF' THEN 1 END),0) MF,
ISNULL(SUM(CASE WHEN POSITION='DF' THEN 1 END),0) DF,
ISNULL(SUM(CASE WHEN POSITION='GK' THEN 1 END),0) GK,
COUNT(*)
FROM PLAYER
GROUP BY TEAM_ID
예제 2. GROUP BY절 없이 PLAYER의 포지션별 평균 키 및 전체 평균키 출력
SELECT
ROUND(AVG(CASE WHEN POSITION='FW' THEN HEIGHT END),2)
ROUND(AVG(CASE WHEN POSITION='MF' THEN HEIGHT END),2)
ROUND(AVG(CASE WHEN POSITION='DF' THEN HEIGHT END),2)
ROUND(AVG(CASE WHEN POSITION='GK' THEN HEIGHT END),2)
'자격증을 위한 > SQLD' 카테고리의 다른 글
| [SQLD] 2과목 SQL 기본및 활용 - 1장 9절 JOIN (0) | 2016.03.09 |
|---|---|
| [SQLD] 2과목 SQL 기본및 활용 - 1장 8절 ORDER BY 절 (0) | 2016.03.09 |
| [SQLD] 2과목 SQL 기본및 활용 - 1장 6절 함수 (0) | 2015.08.29 |
| [SQLD] 2과목 SQL 기본및 활용 - 1장 5절 WHERE (0) | 2015.08.29 |
| [SQLD] 2과목 SQL 기본및 활용 - 1장 4절 TCL (0) | 2015.08.29 |
설정
트랙백
댓글
글
[SQLD] 2과목 SQL 기본및 활용 - 1장 1절 관계형 데이터베이스 개요
1. 데이터베이스
- 특정 기업이나 조직 또는 개인이 필요에 의해 데이터를 일정한 형태로 저장해 놓은 것.
* 데이터베이스의 발전
- 1960년대: 플로우 차트 중심의 개발 방법을 사용하였으며 파일 구조를 통해 데이터를 저장하고 관리하였다.
- 1970년대: 데이터베이스 관리기법이 처음 태동되던 시기였으며 계층형 데이터베이스, 망형 데이터베이스 같은 제품들이 상용화 되었다.
- 1980년대: 현재 대부분의 기업에서 사용되고있는 관계형 데이터베이스가 상용화 되었으며 Oracle, Sybase, DB2와 같은 제품이 있다.
- 1990년대: Oracle, Sybase, Informix, DB2, Teradata, SQL Server외 많은 제품들이 보다 향상된 기능으로 정보시스템의 확실한 핵심 솔루션으로 자리잡게 되었으며 인터넷 환경의 급속한 발전과 객체지향 정보를 지원하기 위해 객체 관계형 데이터베이스로 발전하게되었다.
* 관계형 데이터베이스
- 관계형 데이터베이스는 정규화를 통한 합리적인 테이블 모델링을 통해 이상 현상을 제거하고 데이터중복을 피할 수 있으며 동시성관리, 병행제어를 통해 많은 사용자들이 동시에 데이터를 공유 및 조작할 수 있는 기능을 제공한다.
- 관계형 데이터베이스는 메타 데이터를 총괄 관리할 수 있기 때문에 데이터의 성격, 속성 또는 표현 방법 등을 체계화할 수 있고, 데이터 표준화를 통한 데이터 품질을 확보할 수 있는 장점을 가지고 있다.
- 테이블 생성시에 사용할 수 있는 다양한 제약조건을 이용하여 사용자가 실수로 조건에 위배되는 데이터를 입력한다든지 관계를 연결하는 중요한 데이터를 삭제하는 것을 방지하여 무결성을 보장할 수 있다.
2. SQL
- 관계형 데이터베이스에서 데이터 정의 데이터 조작, 데이터 제어를 하기위해 사용하는 언어.
명령어의 종류 | 명령어 | 설명 |
데이터 조작어( DML) | SELECT | 데이터베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어를 말하는 것으로 RETRIEVE라고도 한다. |
INSERT UPDATE DELETE | 데이터베이스의 테이블에 들어있는 데이터에 변형을 가하는 종류의 명령어들을 말한다. 예를 들어 데이터를 테이블에 새로운 행을 집어넣거나, 원하지 않는 데이터를 삭제하거나 수정하는 것들의 명령어 | |
데이터 정의어 (DDL) | CREATE ALTER DROP RENAME | 테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어들로 그러한 구조를 생성하거나 변경하거나 삭제하거나 이름을 바꾸는 데이터 구조와 관련된 명령어들을 DDL이라고 한다. |
데이터 제어어(DCL) | GRANT REVOKE | 데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어를 DCL 이라고 한다. |
트랜잭션 제어어(TCL) | COMMIT ROLLBACK | 논리적인 작업단위를 묶어서 DML에 의해 조작된 결과를 작업단위로 제어하는 명령어를 말한다. |
3. TABLE
- 데이터는 관계형 데이터베이스에서 테이블 형태로 저장된다.
- 모든 자료는 테이블에 등록되고 우리는 테이블로부터 원하는 자료를 꺼내올 수 있다.
- 테이블은 어느 특정한 주제와 목적으로 만들어지는 일종의 집합이다.
- 테이블은 데이터를 저장하는 객체로서 관계형 데이터베이스의 기본단위이다.
- 세로방향은 컬럼, 가로방향은 로우 라고하며 칼럼과 행이 겹치는 부분을 필드라고한다.
용어 | 설명 |
테이블 | 행과 컬럼의 2차원 구조를 가진 데이터 저장 장소, 데이터베이스의 가장 기본적인 개념 |
칼럼/열 | 2차원 구조를 가진 테이블에서 세로 방향으로 이루어진 하나하나의 특정 속성 (더이상 나눌 수 없는 특성) |
로우/행 | 2차원 구조를 가진 테이블에서 가로 방향으로 이루어진 연결된 데이터 |
- 선수와 관련된 데이터는 선수테이블과 구단 테이블 이라는 복수의 테이블로 분할하여 저장한다.
분할된 테이블은 그 칼럼의 값에 의해 결정된다. 이렇게 테이블을 분할하여 데이터의 불필요하는 중복을 줄이는 것을 정규화라고 한다.
- 각 행을 한가지 의미로 특정할 수 있는 한 개 이상의 칼럼을 기본키(Primary Key)라고 하며 다른 테이블의 기본 키로 사용되면서 테이블과의 관계를 연결하는 역할을 하는 칼럼을 외부키라고 한다.
용어 | 설명 |
정규화 | 테이블을 분할하여 데이터의 정합성을 확보하고 불필요한 중복을 줄이는 프로세스. |
기본키 | 테이블에 존재하는 각 행을 한 가지 의미로 특정할 수 있는 한 개 이상의 칼럼 |
| 외부키 | 다른 테이블의 기본키로 사용되고있는 관계를 연결하는 칼럼 |
4. ERD
- ERD의 구성요소는 엔터티, 관계, 속성 3가지 이며 이 3가지 구성요소로 모두 표현이 가능하다.
'자격증을 위한 > SQLD' 카테고리의 다른 글
| [SQLD] 2과목 SQL 기본및 활용 - 1장 3절 DML (0) | 2015.08.26 |
|---|---|
| [SQLD] 2과목 SQL 기본및 활용 - 1장 2절 DDL (0) | 2015.08.26 |
| [SQLD] 1과목 데이터 모델링의 이해 - 2장 6절:분산 데이터베이스와 성능 (0) | 2015.08.24 |
| [SQLD] 1과목 데이터 모델링의 이해 - 2장 5절:데이터베이스 구조와 성능 (0) | 2015.08.24 |
| [SQLD] 1과목 데이터 모델링의 이해 - 2장 4절:대량 데이터에 따른 성능 (0) | 2015.08.24 |
설정
트랙백
댓글
글
[SQLD] 1과목 데이터 모델링의 이해 - 2장 4절:대량 데이터에 따른 성능
1. 대량 데이터발생에 따른 테이블 분할 개요.
- 일처의 처리량이 한군데에 몰리는 현상은 어떤 업무에 있어서 중요한 업무에 해당되는 데이터가 특정 테이블에 있는 경우에 발생이 되는데 이런 경우 트랜잭션이 분산 처리될 수 있도록 테이블단위에서 분할의 방법을 적용할 필요가 있다.
- 대량의 데이터가 존재하는 테이블에 많은 트랜잭션이 발생하여 성능이 저하되는 테이블 구조에 대해 수평/수직 분할 설계를 통해 성능저하를 예방할 수 있음.
- 수평분할 : 칼럼단위로 분할하여 I/O 경감.
- 수직분할 : 로우단위로 분할하여 I/O 경감.
하나의 테이블에 대량의 데이터가 존재하는 경우에는 인덱스의 Tree구조가 너무 커져 효율성이 떨어져 데이터를 처리(입력,수정,삭제,조회) 할 때 디스크 I/O를 많이 유발하게 된다.
하나의 테이블에 많은 수의 컬럼이 존재하면 데이터가 디스크의 여러 블록에 존재하므로 디스크에서 데이터를 읽는 IO량이 많아지게되어 성능이 저하되게 된다.
- 조회조건에 따른 인덱스를 적절하게 이용하면 해당 테이블에 데이터가 아무리 많아도 원하는 데이터만 접근하기 때문에 I/O의 양이 그다지 증가하지 않을것으로 생각할 수 있으나 대량의 데이터가 하나의 테이블에 존재하게 되면 인덱스를 생성할 때 인덱스의 크기가 커지게 되고 그렇게되면 인덱스를 찾아가는 단계가 깊어지게 되어 조회성능에 영향을 미치게된다.
- 칼럼이 많아지게되면 물리적인 디스크에 여러 블록에 데이터가 저장되게 되어 데이터를 처리할 때 여러블록에서 I/O를 해야하는 SQL문장의 성능이 저하될 수 있다.
- 칼럼이 많을경우 로우체이닝과 로우마이그레이션이 많아지게되어 성능이 저하된다.
* 로우체이닝: 로우 길이가 너무 길어서 데이터블록 하나에 모두 저장되지 않고 두 개 이상의 블록에 걸쳐 하나의 로우형태가 저장되어있는 형태.
* 로우 마이그레이션: 특정 데이터 블록에서 수정이 발생하게되면 수정된 데이터를 해당 데이터 블록에서 저장하지 못하고 다른 블록의 빈공간을 찾아 저장하는 방식.
=> 로우체이닝과 로우마이그레이션이 발생하여 많은 블록에 데이터가 저장되면 데이터베이스 메모리에서 디스크와 I/O가 발생할 때 불필요하게 많은 I/O가 발생하여 성능이 저하된다.
2. 한 테이블에 많은 수의 컬럼을 가지고 있을 경우.
- 많은 칼럼을 가지고 있는 테이블에 대해서 트랜잭션이 발생될 때 어떤 칼럼에 대해 집중적으로 발생하는지 분석하여 테이블을 쪼개어 주면 디스크 I/O가 감소하게 되어 성능이 개선된다.
- 분리된 테이블은 디스크에 적어진 칼럼이 저장되므로 로우 마이그레이션과 로우체이닝이 많이 줄어들 수 있다.
3. 대량 데이터 저장 및 처리로 인한 성능.
- 테이블에 많은 양의 데이터가 예상될 경우 파티셔닝을 적용하거나 PK에 의해 테이블을 분할하는 방법을 적용할 수있다.
- 오라클은 LIST PARTITION(특정값 지정), RANGE PARTITION(범위), HASH PARTITION(해쉬적용), COMPOSITE PARTITION(범위와 해쉬가 복합) 등이 가능하다.
- 데이터양이 몇 천만 건을 넘어서면 논리적으로는 하나의 테이블로 보이지만 물리적으로는 여러 개의 테이블스페이스에 쪼개어 저장될 수 있는 구조의 파티셔닝을 적용하도록 한다.
가. RANGE PARTITOIN 적용
- 요금 테이블에 PK가 요금일자+유금번호로 구성되어있고 데이터 건수가 1억2천만 건인 대용량 테이블의 경우 하나의 테이블로는 너무 많은 데이터가 존재하므로 성능이 느려진다. 이 때 요금의 특성상 월 단위로 데이터를 처리하는 경우가 많으므로 PK인 요금일자의 년+월을 이용하여 12개의 파티션 테이블을 만들게되면 SQL 문장을 처리할 때는 마치 하나의 테이블처럼 보이는 요금테이블을 이용하여 처리되지만 DBMS내부적으로는 SQL WHERE 절에 비교된 요금일자에 의해 각 파티션에 있는 정보를 찾아가므로 평균 10000만 건의 데이터가 있는 곳을 찾아도 되어 성능이 향상될 수 있다.
- 가장 많이 사용되는 파티셔닝의 기준으로 대상 테이블이 날자 또는 숫자값으로 분리가 가능하고 각 영역별로 트랜잭션이 분리된다면 RANGE PARTITOIN을 적용한다. 또한 데이터 보관주기에 따라 테이블에 데이터를 쉽게 지우는 것이 가능하므로 테이블 관리가 용이하다.
나. LIST PARTITION 적용
- 지점, 사업소, 사업장, 핵심적인 코드값 등으로 PK가 구성되어 있고 대량의 데이터가 있는 테이블이라면 값 각각에 의해 파티셔닝 되는 LIST PARTITION을 적용할 수 있다.
- 리스트 파티션은 대용량 데이터를 특정 값에 따라 분리 저장할 수는 있으나 RANGE PARTITION과 같이 데이터 보관주기에 따라 쉽게 삭제하는 기능은 제공될 수 없다.
다. HASH PARTITION 적용
- 기타 HASH PARTITION은 지정된 HASH 조건에 따라 해싱 알고리즘이 적용되어 테이블이 분리되며 설계자는 테이블에 데이터가 정확하게 어떻게 들어갔는지 알 수 없다.
- 성능 향상을 위해 사용하며 데이터 보관주기에 따라 쉽게 삭제하는 기능은 제공할 수 없다.
4. 테이블에 대한 수평분할/수직분할 절차
- 테이블에 대한 수평분할/수직분할에 대한 결정은 다음의 4가지 원칙을 적용한다.
1)데이터 모델링을 완성한다.
2)데이터베이스 용량을 산정한다.
3) 대량 데이터가 처리되는 테이블에 대해서 트랜잭션 처리 패턴을 분석한다.
4) 컬럼단위로 집중화된 처리가 발생하는지 로우단위로 집중화된 처리가 발생하는지 분석하여 집중화된 단위로 테이블 분리하는것을 검토한다.
* 칼럼의 수가 많은경우 트랜잭션의 특성에 따라 1:1 형태로 분리할 수 있는지 검증하면된다.
* 칼럼의 수가 적지만 데이터 용량이 많아 성능저하가 예상되는 경우 테이블에 대해 파티셔닝 전략을 고려하도록 한다.
'자격증을 위한 > SQLD' 카테고리의 다른 글
| [SQLD] 1과목 데이터 모델링의 이해 - 2장 6절:분산 데이터베이스와 성능 (0) | 2015.08.24 |
|---|---|
| [SQLD] 1과목 데이터 모델링의 이해 - 2장 5절:데이터베이스 구조와 성능 (0) | 2015.08.24 |
| [SQLD] 1과목 데이터 모델링의 이해 - 2장 3절:반정규화와 성능 (0) | 2015.08.23 |
| [SQLD] 1과목 데이터 모델링의 이해 - 2장 2절:정규화와 성능 (0) | 2015.08.23 |
| [SQLD] 1과목 데이터 모델링의 이해 - 2장 1절:성능 데이터 모델링의 개요 (0) | 2015.08.23 |
설정
트랙백
댓글
글
[SQLD] 1과목 데이터 모델링의 이해 - 2장 3절:반정규화와 성능
1.반정규화를 통한 성능향상 전략.
가. 반정규화의 정의
- 정규화된 엔터티, 속성, 관계에 대해 시스템의 성능향상과 개발과 운영의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터모델링의 기법을 말한다.
- 협의의 반정규화는 데이터를 중복하여 성능을 향상시키기 위한 기법이라고 정의할 수 있고 좀 더 넓은 의미의 반정규화는 성능을 향상시키기 위해 정규화된 데이터 모델에서 중복, 통합, 분리등을 수행하는 모든 과정을 말한다.
- 데이터를 중복하여 반정규화를 적용하는 이유는 데이터를 조회할 때 디스크 I/O량이 많아서 성능이 저하되거나 경로가 너무 멀어 조인으로 인한 성능저하가 예상되거나 칼럼을 계산하여 읽을 때 성닝이 저하될 것이 예상되는 경우 반정규화를 수행한다.
- 프로젝트에서는 설계 단계에서 반정규화를 적용하게 된다.
* 반정규화를 기술적으로 수행하지 않는 경우
1) 성능이 저하된 데이터베이스가 생성될 수 있다.
2) 구축단계나 시험단계에서 반정규화를 적용할 때 수정에 따른 노력비용이 더 많이 들게된다.
나. 반정규화의 적용방법.
- 개발을 하다가 SQL문장 작성이 복잡해지고 그에 따라 SQL 단위 성능 저하가 예상되어 다른 테이블에서 조인하여 가져와야 할 칼럼을 기준이 되는 테이블에 중복하여 SQL문장을 단순하게 처리하도록 하기위해 요청하는 경우가 많다.
(무분별하게 칼럼의 반정규화를 많이 하게되는것은 데이터에 대한 무결성을 깨뜨리는 결정적인 역할을 하는 경우가 많다.)
- 반정규화에 대한 필요성이 결정되면 칼럼의 반정규화 뿐만아니라, 테이블의 반정규화와 관계의 반정규화를 종합적으로 고려하여 적용해야 한다. 또한 막연하게 중복을 유도하는 것만을 수행하기 보다는 성능을 향상시킬 수 있는 다른 방법들을 고려하고 그 이후에 반정규화를 적용하도록 해야 한다.
- 반정규화를 적용할 때는 기본적으로 데이터 무결성이 깨질 가능성이 많기 때문에 반드시 데이터 무결성을 보장할 수 있는 방법을 고려한 후 반정규화를 적용하도록 해야한다.
* 반정규화 절차
1) 반정규화의 대상을 조사한다.
- 데이터의 양을 조사하고 그 데이터가 해당 프로세스를 처리할 때 성능저하가 나타날 수 있는지 검증해야한다.
* 자주 사용되는 테이블에 접근하는 프로세스의 수가 많고 항상 일정한 범위만을 조회하는 경우 반정규화를 검토한다.
* 테이블에 대량의 데이터가 있고 대량의 데이터 범위를 자주 처리하는 경우에 처리범위를 일정하게 줄이지 않으면 성능을 보장할 수 없을 경우에 반정규화를 검토한다.
* 통계성 프로세스에 의해 통계정보를 필요로 할 때 별도의 통계테이블을 생성한다.
* 테이블에 지나치게 많은 조인이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우 반정규화를 검토한다.
2) 반정규화의 대상에 대해 다른 방법으로 처리할 수 있는지 검토한다
- 가급적이면 데이터를 중복하여 데이터 무결성을 깨뜨릴 위험을 제어하기 위하여 반정규화를 결정하기 전에 성능을 향상시킬 수 있는 다른 방법을 모색하도록 한다.
* 지나치게 많은 조인이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우 뷰를 사용하면 해결할 수도 있다.
(성능을 고려한 뷰를 생성하여 개발자가 뷰를 통해 접근함으로 써 성능저하의 위험을 예방할 수 있다)
* 대량의 데이터 처리나 부분처리에 의해 성능이 저하되는 경우 클러스터링을 적용하거나 인덱스를 조정함으로써 성능을 향상시킬 수 있다.
( 클러스터링을 적용하는 방법은 대량의 데이터를 특정 클러스터링 팩트에 의해 저장방식을 다르게 하는 방법이다.
이 방법의 경우 데이터를 입력,수정,삭제 하는 경우 성능이 많이 저하되므로 조회중심의 테이블이 아니라면 생성하면 안된다. )
* 대량의 데이터는 PK의 성격에 따라 부분적인 테이블로 분리할 수 있다. 즉 파티셔닝 기법이 적용되어 성능저하를 방지할 수 있다.
* 응용애플리케이션에서 로직을 구사하는 방법을 변경함으로써 성능을 향상시킬 수 있다.
(응용메모리에 데이터를 처리하기 위한 값을 캐쉬한다든지 중간 클래스 영역에 데이터를 캐쉬하여 공유하게 하여 성능을 향상 시키는 것도 성능을 향상시키는 방법이 될 수 있다.)
3) 반정규화를 적용한다.
- 반정규화를 적용하기 이전에 충분히 성능에 대한 고려가 이루어져 판단이 들었다면 세가지 규칙을 고려하여 반정규화를 적용한다
- 반정규화를하는 대상으로는 테이블, 속성, 관계에 대해 적용할 수 있으며 꼭 테이블과 속성, 관계에 대해 중복으로 가져가는 방법만이 반정규화가 아니고 테이블, 속성, 관계를 추가할 수도 있고 분할할 수도 있으며 제거할 수도 있다.
2. 반정규화의 기법.
가. 테이블 반정규화
기법분류 | 기법 | 내용 |
테이블 병합 | 1:1 관계 테이블 병합 | 1:1 관계를 통합하여 성능 향상 |
1:M 관계 테이블 병합 | 1:M 관계를 통합하여 성능향상 | |
슈퍼/서브타입 테이블 병합 | 슈퍼/서브 관계를 통합하여 성능향상 | |
테이블 분할 | 수직분할 | 칼럽단위의 테이블을 디스크 I/O를 분산처리하기 위해 테이블을 1:1로 분리하여 성능향상(트랜잭션의 처리되는 유형파악이 선행되야함) |
수평분할 | 로우 단위로 집중 발생되는 트랜잭션을 분석하여 디스크 I/O 및 데이터 접근 효율성을 높여 성능을 향상하기 위해 로우단이로 테이블을 쪼갬(관계가 없음) | |
테이블 추가 | 중복테이블 추가 | 다른 업무이거나서버가 다른 경우 동일한 테이블구조를 중복하여 원격조인을 제거하여 성능 향상 |
통계 테이블 추가 | SUM, AVG 등을 미리 수행하여 계산해 둠으로써 조회 시 성능을 향상 | |
이력 테이블 추가 | 이력테이블 중에서 마스터 테이블에 존재하는 레코드를 중복하여 이력테이블에 존재하는 방법은 반정규화의 유형 | |
부분 테이블 추가 | 하나의 테이블의 전체 칼럼 중 자주 이용하는데 자주 이용하는 집중화된 칼럼들이 있을 때 디스크 I/O를 줄이기 위해 해당 칼럼들을 모아놓은 별도의 반정규화된 테이블을 생성 |
나. 칼럼 반정규화
반정규화 기법 | 내용 |
중복칼럼 추가 | 조인에 의해 처리할 때 성능저하를 예방하기위해(조인을 감소하기위해) 중복된 칼럼을 위치시킴 |
파생칼럼 추가 | 트랜잭션이 처리되는 시점에 계산에 의해 발생되는 성능저하를 예방하기 위해 미리 값을 계산하여 칼럼에 보관함 (Derived Column이라고함.) |
이력테이블 칼럼 추가 | 대량의 이력데이터를 처리할 때 불특정 날 조회나 최근 값을 조회할 때 나타날 수 있는 성능 저하를 예방하기 위해 이력테이블에 기능성 칼럼(최근값 여부, 시작과 종료일자 등)을 추가함. |
PK에 의한 칼럼 추가 | 복합의미를 갖는 PK를 단일 속성으로 구성하였을 경우 발생됨. 단일 PK 안에서 특정 값을 별도로 조회하는 경우 성능저하가 발생될 수 있음. 이 때 이미 PK안에 데이터가 존재하지만 성능향상을 위해 일반속성으로 포함하는 방법이 PK의한 칼럼추가 반정규화임. |
응용시스템의 오작동을 위한 칼럼추가 | 업무적으로는 의미가 없지만 사용자가 데이터를 처리하다가 잘못 처리하여 원래 값으로 복구하기를 원하는 경우 이전 데이터를 임시적으로 중복하여 보관하는 기법. 칼럼으로 이것을 보관하는 방법은 오작동 처리를 위한 임시적인 기법이지만 이것을 이력데이터 모델로 풀어내면 정상적인 데이터모델의 기법이 될 수 있음. |
다. 관계 반정규화.
반정규화 기법 | 내용 |
중복관계 추가 | 데이터를 처리하기 위한 여러 이동경로를 거쳐 조인이 가능하지만 이 때 발생할 수 있는 성능저하를 예방하기 위해 추가적인 관계를 맺는방법이 관계 반정규화이다. |
=> 테이블과 칼럼의 반정규화는 데이터 무결성에 영향을 미치게 되나 관계의 반정규화는 데이터 무결성을 깨뜨릴 위험을 갖지 않고서도 데이터 처리의 능력을 향상시킬 수 있는 반정규화의 기법이 된다.
3. 정규화가 잘 정의된 데이터 모델에서 성능이 저하될 수 있는 경우.
- 공급자라고 하는 엔터티가 마스터 이고 전화번호와 메일주소 위치가 각각 변경되는 내용이 이력형태로 관리되는 데이터 모델의 경우, 반정규화 하여 마스터 엔터티에 최신 이력의 전화번호와 메일주소 위치를 저장할 경우 간단하게 조회할 수 있다.
- 데이터베이스서버가 분리되어 분산데이터베이스가 구성되어 있을 때 속성 반정규화를 통해 성능을 향상시킬 수 있다.
* 반정규화를 적용할 때 중요한 것은 데이터를 입력, 수정, 삭제할 때는 성능이 떨어지는 점을 기억해야하며, 데이터 무결성 유지에 주의해야한다.
'자격증을 위한 > SQLD' 카테고리의 다른 글
| [SQLD] 1과목 데이터 모델링의 이해 - 2장 5절:데이터베이스 구조와 성능 (0) | 2015.08.24 |
|---|---|
| [SQLD] 1과목 데이터 모델링의 이해 - 2장 4절:대량 데이터에 따른 성능 (0) | 2015.08.24 |
| [SQLD] 1과목 데이터 모델링의 이해 - 2장 2절:정규화와 성능 (0) | 2015.08.23 |
| [SQLD] 1과목 데이터 모델링의 이해 - 2장 1절:성능 데이터 모델링의 개요 (0) | 2015.08.23 |
| [SQLD] 1과목 데이터 모델링의 이해 - 1장 5절:식별자 (0) | 2015.08.23 |
설정
트랙백
댓글
글
1과목 데이터 모델링의 이해 - 2장 1절:성능 데이터 모델링의 개요
1.성능 데이터 모델링의 정의
- 데이터 모델링을 할 때 어떤 작업 유형에 따라 성능 향상을 도모해야 하는지 목표를 분명하게 해야 정확한 성능향상 모델링을 할 수 있다.
- 성능 데이터 모델링이란 데이터베이스 성능향상을 목적으로 설계단계의 데이터 모델링 때부터 정규화, 반정규화, 테이블통합, 테이블 분할, 조인구조, PK, FK 등 여러 가지 성능과 관련된 사항이 데이터 모델링에 반영될 수 있도록 하는 것.
- 성능 데이터 모델링은 정규화를 통해서도 수행할 수 있고 인덱스의 특징을 고려해서 컬럼의 순서를 변형할 수 있다. 대량의 데이터 특성에 따라 정규화된 모델이라도 테이블을 수직 또는 수평 분할하여 적용하는 방법과 논리적인 테이블을 물리적인 테이블로 전환할 때 데이터 처리의 성격에 따라 변환하는 방법도 성능 데이터 모델의 범주에 포함될 수 있다.
2. 성능 데이터 모델링 수행 시점
- 프로젝트 수행에 있어서 사전에 할수록 비용이 적게든다.
- 분석/설계 단계에서 데이터 모델에 성능을 고려한 데이터 모델링을 수행할 경우 성능저하에 따른 재업무 비용을 최소화 할 수 있는 기회를 갖게된다.
- 만약 어떤 트랜잭션이 해당 비즈니스 처리에 핵심적이고 사용자 업무처리에 있어 중요함을 가지고 있고 성능이 저하되면 안되는 특징을 가지고 있다면 프로젝트 초기에 운영환경에 대비한 테스트 환경을 구현하고 그곳에 트랜잭션을 발생시켜 실제 성능을 테스트 해야한다.
3.성능 데이터 모델링 고려사항.
1) 데이터 모델링을 할 때 정규화를 정확하게 수행해야 한다.
- 정규화된 모델이 주요 관심사별로 분산시키는 효과가 있기 때문에 그 자체로 성능을 향상시키는 효과가 있다.
2) 데이터베이스의 용량산정을 수행한다.
- 용량산정을 수행하면 어떤 엔터티(테이블)에 데이터가 집중되는지 파악을 할 수 있다.
3) 데이터베이스에 발생되는 트랜잭션의 유형을 파악한다.
- CRUD 메트릭스를 보고 파악하는 방법과 시퀀스 다이어그램을 보면 트랜잭션 유형을 파악하기에 용이하다.
4) 용량과 트랜잭션의 유형에 따라 반정규화를 수행한다.
- 용량산정과 트랜잭션의 유형 데이터를 근거로 정확하게 테이블 반정규화를 적용한다.
5) 이력모델의 조정, PK/FK조정, 슈퍼타입/서브타입 조정 등을 수행한다.
- 대량의 데이터가 처리되는 이력모델에 대해 성능고려를 하고 PK/FK의 순서가 인덱스 특성에 따라 성능에 미치는 영향도가 크기 때문에 PK/FK를 성능이 우수한 순서대로 칼럼의 순서를 조정해야한다.
6) 성능관점에서 데이터 모델을 검증한다.
- 전체적으로 성능에 대한 충분한 고려가 되었는지를 데이터 모델 검토를 통해 다시한번 확인한다.
'자격증을 위한 > SQLD' 카테고리의 다른 글
| [SQLD] 1과목 데이터 모델링의 이해 - 2장 3절:반정규화와 성능 (0) | 2015.08.23 |
|---|---|
| [SQLD] 1과목 데이터 모델링의 이해 - 2장 2절:정규화와 성능 (0) | 2015.08.23 |
| [SQLD] 1과목 데이터 모델링의 이해 - 1장 5절:식별자 (0) | 2015.08.23 |
| [SQLD] 1과목 데이터 모델링의 이해 - 1장 4절:관계 (0) | 2015.08.18 |
| [SQLD] 1과목 데이터 모델링의 이해 - 1장 3절: 속성 (0) | 2015.08.18 |
RECENT COMMENT