[정보처리기사] 2.데이터 입출력 구현

01 논리 데이터 저장소 확인

1. 논리 데이터 모델 검증

1.1 논리 데이터 모델링 개념

: 비즈니스 정보의 구조, 규칙 표현할 수 있는 기법

 

1.2 논리 데이터 모델링 특성

정규화 : 모든 데이터를 정규화하여 모델링

포용성 : 모든 엔티티 타입, 속성, 관계, 프로세스 포함

완전성 : 모든 규칙과 관계를 완전하고 정확하게 표현

독립성 : 성능, 제약사항에 독립적인 모델

 

1.3 논리 데이터 모델링 속성

개체 : 관리할 대상이 되는 실체

속성 : 관리할 정보의 구체적 항목

관계 : 개체 간 대응 관계

 

1.4 개체-관계 모델

1) 개체-관계(E-R) 모델 개념

    : 정보를 개체, 속성, 관계로 기술한 모델

2) 개체-관계(E-R) 다이어그램 기호

 

1.5 정규화

1) 정규화의 개념

관계형 데이터베이스의 설계에서 중복 최소화하여 데이터를 구조화하는 프로세스

 

2) 이상 현상

데이터 중복성으로 인해 릴레이션을 조작할 때 발생하는 비합리적 현상

삽입이상 : 정보 저장 시 정보의 불필요한 세부정보를 입력해야하는 경우

삭제 이상 : 정보 삭제 시 원치 않는 다른 정보가 같이 삭제되는 경우

갱신 이상 : 중복 데이터 중에서 특정 부분만 수정되어 중복된 값이 모순을 일으키는 경우

 

3) 정규화의 단계

1정규형 : 원자값으로 구성

2정규형 : 부분 함수 종속 제거

3정규형 : 이행함수 종속 제거

보이스-코드 정규형 : 결정자 함수면서 후보키가 아닌 것 제거

4정규형 : 다치 중속성 제거

5정규형 : 조인 종속성 제거

 

4) 정규화의 상세단계

1차 정규화 : 속성값은 원자값을 가져야함

2차 정규화 : 부분 관계를 별도의 테이블로 두어 부분 함수 종속 관계를 없애야함

3차 정규화 : A->B 이고, B->C이면서 A->C 관계를 이행함수 종속 관계라 하고 이를 분리하여 이행함수 관계를 제거

보이스-코드 정규화 : A,B 가 C에 영향 주고, C가 B에게 영향 줌. C는 결정자지만 후보키는 아님

                                B와 C를 분리하여 후보키 역할을 하도록 테이블 분리

4차 정규화 : A마다 B값 다수, A마다 C값 다수로 존재하는 경우를 다치 종속 관계

5차 정규화 : A,B / A,C / B,C 테이블을 각각 만들어줘서 조인했을 때 원래 테이블로 복원가능하게 함

 

02 물리 데이터 저장소 설계

1. 물리 데이터 모델 설계

1.1 물리 데이터 모델링 개념

: 논리 모델을 적용하고자하는 기술에 맞도록 상세화하는 과정

 

1.2 물리 데이터 모델링 변환 절차

논리 데이터 저장소 > 물리 데이터 저장소

1) 개체 > 테이블

2) 속성 > 컬럼

3) UID > 기본키

4) 관계 > 외래키

5) 컬럽 유형,길이 정의

6) 반 정규화 수행

 

2. 물리 데이터 저장소 구성

2.1 테이블 제약조건 설계

1) 참조무결성 제약조건

: 릴레이션과 릴레이션 사이에 참조 일관성을 보장하기 위한 조건

두 개의 릴레이션이 기본키, 외래키로 참조 관계를 형성하면 참조하는 외래키는 항상 참조되는 릴레이션의 기본키로 존재

- 제한(RESTRICT) : 참조무결성 원칙을 위배하는 연산 거절

- 연쇄(CASCADE) : 참조되는 릴레이션에서 튜플을 삭제하고 참조되는 릴레이션에도 함께 삭제

- 널 값(SET NULL) : 참조되는 릴레이션의 튜플만 삭제하고 참조하는 릴레이션에는 NULL값을 넣어줌

 

2.2 인덱스 설계

1) 인덱스 개념

: 검색 연산의 최적화를 위해 데이터베이스 내 열에 대한 정보를 구성한 데이터구조

 

2) 인덱스 적용 기준

분포도 = (1/컬럼 값의 종류) * 100

분포도 = (컬럼값의 평균 Row 수) / (테이블의 총 Row 수) * 100

 

3) 인덱스 컬럼 선정

- 분포도가 좋은 컬럼은 단독적으로 생성

- 자주 조합되는 컬럼은 결합 인덱스로 생성 (컬럼 순서 선정에 유의)

- 수정이 빈번하지않은 컬럼 선정

 

4) 설계 시 고려사항

- 인덱스가 많으면 오버헤드로 작용

- 인덱스는 추가적인 저장공간이 필요

- 넓은 범위를 인덱스 처리 시 더 많은 오버헤드 발생 가능

 

2.3 뷰 설계

1) 뷰 속성

REPLACE : 뷰가 이미 존재하는 경우 재생성

FORCE : 존재 여부 관계없이 뷰 생성

NOFORCE : 기본 테이블이 존재할 때 뷰 생성

WITH CHECK OPTION : 서브 쿼리 내의 조건을 만족하는 행만 변경

WITH READ ONLY : 데이터 조작어 작업 불가

 

2) 뷰 설계 시 고려 사항

- 뷰 사용에 따라 수행속도에 문제 발생가능

- 뷰의 조건은 최적의 액세스 경로를 사용할 수 있도록

 

2.4 클러스터 설계

1) 적용 기준

- 인덱스의 단점을 해결, 분포도 넓을수록 유리

- 액세스 효율 향상을 위한 물리적 저장 방법

- 대량의 범위를 자루 액세스하는 경우 적용

 

2) 클러스터 설계 시 고려 사항

- 검색 효율을 높여주나 입력, 수정, 삭제 시 부하 증가

 

2.5 파티션 설계

1) 파티션의 종류

- 레인지 파티셔닝

 : 연속적인 숫자나 날짜 기준, 손쉬운 관리기법 제공하여 관리 시간 단축이 가능

- 해쉬 파티셔닝

 : 파티션 키의 해시 함수 값에 의한 파티셔닝 기법

- 리스트 파티셔닝

 : 특정 파티션에 저장될 데이터에 대한 명시적 제어 가능

  분포도 비슷하고 데이터가 많은 SQL에 컬럼의 조건이 많이 들어오는 경우

- 컴포지트 파티셔닝

 : 범위 분할에 이후 해시 함수를 적용하여 재분할

 

2) 파티션의 장점

성능 향상 : 데이터 액세스 범위를 줄여 성능 향상

가용성 향상 : 전체 데이터의 훼손 가능성이 감소, 데이터 가용성 향상

백업 기능 : 분할 영역을 독립적으로 백업, 복구 가능

경합 감소 : 디스크 스트라이핑으로 입출력 성능을 향상

 

2.6 디스크 구성 설계

- 정확한 용량을 산정하여 디스크 사용의 효율을 높임

- 업무량이 집중되어 잇는 디스크를 분리하여 설계

- 입출력 경합을 최소화하여 데이터 접근 성능 향상

 

03 데이터 조작 프로시저 작성

1. 데이터 조작 프로시저 개발

1.1 프로시저의 개념

: SQL을 이용해 생성된 데이터를 조작하는 프로그램

 DB 내에 저장되고 일정 조건이 되면 자동으로 수행됨

 

1.2 PL/SQL의 개념

: 표준 SQL을 기본으로 Oracle에서 개발한 데이터 조작 언어

 

1.3 PL/SQL 작성 절차

1) 데이터 저장소 연결

드라이버 로딩 > 데이터베이스 연결 > 쿼리 전달 > 결과 수신

import oracle.jdbc.driver.OracleDriver;

String url = "jdbc:oracl:thin:@localhost:1521:ORCL";
conn = DriverManager.getConnection(rul,"scott","tiger");

pstm = conn.prepareStatement(sql);

ResultSet rs = pstmt.executeQuery();

2) 데이터 저장소 정의

생성,수정,삭제

CREATE TALBE EMPLOYEE
(
    EMP_NO NUMBER,
    EMP_NAME VARCHAR2(10),
    START_DATE DATE NOT NULL,
    CONSTRAINT EMP_NOPK PRIMARY KEY(EMP_NO)
);

ALTER TALBE DEPT MODIFY (DEPT_NAME VARCHAR2(20));

DROP TABLE DETPT

3) 데이터 조작 프로시저 작성

파라미터 선언

선언부

실행부

예외처리부

CREATE OR REPLACE PROCEDURE INPUT_EMP
( p_name IN VARCHAR2,
  p_empno IN NUMBER)
IS
BEGIN
  INSERT INTO EMPLOYEE(NAME,EMPNO)
  		 VALUES (p_name,p_empno);
COMMIT;
END;
EXEC INPUT_EMP('hong il-dhong','12111');

4) 데이터 검색 프로시저 작성

CREATE OR REPLACE PROCEDURE SEARCH_PROC
(
 P_REG DATE IN VARCHR2,
 P_ITEM_CODE IN VARCHAR2,
 P_COMPANY_CODE IN VARCHAR2
)
IS 
BEGIN
	SELECT A.REG_DATE, B.ITEM_CODE, B.COMPANY_CODE
    FROM COMPANY A LEFT OUTER JOIN ITEM B
    	ON A.COMPANY_CODE = B.COMPANY_CODE
    WHERE P_COMPANY_CODE = A.COMPANY_CODE
    	AND P_REG_DATE = B.REG_DATE
        AND P_ITEM_CODE = B.ITEM_CODE
    RETURN;
END;

 

2. 데이터 조작 프로시저 테스트

2.1 PL/SQL 테스트

1) DBMS_OUTPUT 패키지 활용

: 메세지를 버퍼에 저장한 후 버퍼 메세지를 읽어오기 위해 사용

 

2) DBMS_OUTPUT 패키지 활용사례

CREATE OR REPLACE PROCEDURE TYPE_TEST
( p_empno IN emp.empno%TYPE)
IS
v_empno emp.empno%TYPE;
BEGIN
  DBMS_OUTPUT.ENABLE;
  SELECT EMPNO
  	INTO v_empno
    FROM EMP
  WHERE EMPNO = p_empno;


DBMS_OUTPUT.PUT_LINE ( v_empno);
END;

3) DBMS_OUTPUT 패키지 실행 방법

SET SERVEROUTPUT ON
EXECUTE Type_Test(1004);

2.2 저장 객체 테스트

1) 저장된 함수

VAR salary NUMBER;
EXECCUTE: salary := supdate_sal(1004);
PRINT salary;

2) 저장된 프로시저

SET SERVEROUTPUT ON;
EXEC update_sal(1004);

3) 저장된 패키지

SET SERVEROUTPUT ON;
EXEC emp_info.all_emp_info;

4) 트리거

SET SERVEROUTPUT ON;

 

04 데이터 조작 프로시저 최적화

1. 데이터 조작 프로시저 성능개선

1.1 쿼리 성능 개선(튜닝)의 개념

: 데이터베이스에서 프로시저에 있는 SQL 실행 계획은 분석, 수정을 통해

 최소 시간으로 원하는 결과를 얻도록 프로시저를 수정

 

1.2 쿼리 성능 개선 절차

문제있는 SQL 식별 : APM 등을 활용하여 애플리케이션 성능 관리 및 모니터링 도구 활용

옵티마이저 통계 확인 : SQL을 빠르고 효율적으로 수행할 최적 처리경로를 생성해주는 데이터베이스 핵심모듈

SQL문 재구성 : 범위 -> 특정값 지정으로 범위 줄여 처리속도 향상, 옵티마이저 접근 경로 및 조인 순서 제어

인덱스 재구성 : 액세스 경로를 고려하여 인덱스 생성

실행계획 유지관리  : 데이터베이스 버전 업그레이드, 데이터 전환 등 환경 변경 시에도 유지되는지 관리

 

1.3 옵티마이저 통계 확인

1) 옵티마이저의 개념

: SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 엔진

-> 실행계획

2) 옵티마이저의 유형

규칙기반 옵티마이저(RBO)

: 통계 정보가 없는 상태에서 사전 등록 된 규칙에 따라 질의 실행 계획을 선택

 - 인덱스 구조, 연산자, 조건절 형태

비용기반 옵티마이저(CBO) : 통계 정보로부터 모든 접근 경로를 고려한 질의실행 계획을 선택

 - 레코드 개수, 블록 개수, 평균 행 길이, 컬럼값 수

3) SQL 수행과정 내 옵티마이저 역할

쿼리 변환 : SQL을 좀 더 일반적이고 표준화된 형태로 변환

비용 산정 : 쿼리 명령어 각 단계의 선택도, 카디널리티, 비용 계산

계획 생성 : 하나의 쿼리 수행 시 후보군이 될만한 실행계획들을 생성

4) 힌트 사용

SQL 성능 개선의 핵심 부분

옵티마이저의 실행 계획을 원하는 대로 변경할 수 있게 함

 

1.4 SQL문 재구성

특정 값 지정 : 범위가 아닌 특정 값 지정하여 범위 줄임

별도의 SQL 사용 : UNION ALL 연산자 사용

힌트 사용 : 옵티마이저가 비정상적인 실행 계획을 수립시 힌트로 액세스 결오, 조인 순서 제어

HAVING 미사용 : 인덱스가 걸려있는 컬럼은 HAVING 사용 시 인덱스 미사용

인덱스만 질의 사용 : 가능한 인덱스만 이용해 질의 수행

 

1.5 인덱스 재구성

자주 쓰는 컬럼 선정

SORT 명령어 생략

분포도를 고려

변경 적은 컬럼 선정

결합 인덱스 사용

 

> 다음글 보러가기