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

Chapter 01 논리 데이터 저장소 확인

1. 논리 데이터 모델 검증 ★★★

• 논리 데이터 모델링 개념 : 데이터베이스 설계 프로세스의 기초 설계 단계로 비즈니스 정보의 구조와 규칙을 명확하게 표현할 수 있는 기법. 개념 모델로부터 업무 영역의 업무 데이터 및 규칙을 구체적으로 표현한 모델

• 논리데이터 모델링 특성(정포완독) ★
  • 정규화, 포용성, 완전성, 독립성
• 논리데이터 모델링 속성(개속관) ★
  • 개체, 속성, 관계

• 개체-관계(E-R)모델 : 데이터와 관계의 표현. 요구사항으로부터 얻어낸 정보들을 개체, 속성, 관계로 기술한 모델

• E-R 다이어그램 ★
  • 개체 : 사각형
  • 관계 : 마름모
  • 속성 : 타원
  • 다중 값 속성 : 이중타원
  • 관계-속성 연결 : 선
• 정규화 개념 ★
  • 관계형 데이터베이스의 설계에서 중복을 최소화하여 데이터를 구조화하는 프로세스
• 이상 현상(삽삭갱) ★
  • 삽입이상
  • 삭제이상
  • 갱신이상
• 정규화 단계(원부이결다조) ★
  • 1NF : 원자값으로 구성
  • 2NF : 부분 함수 종속 제거(완전 함수적 종속 관계)
  • 3NF : 이행함수 종속 제거
  • BCNF : 결정자 함수 종속 제거(모든 결정자가 후보키가 됨)
  • 4NF : 다중값 종속성 제거
  • 5NF : 조인 조속성 제거
    
    

Chapter 02 물리 데이터 저장소 설계

1. 물리 데이터 모델 설계 ★★★

• 물리 데이터 모델링 개념 : 논리모델을 적용하고자 하는 기술에 맞도록 상세화해가는 과정

• 물리 데이터 모델링 변환 절차★ : 개체를 테이블로, 속성을 컬럼으로, UID를 기본키로, 관계를 외래키로, 컬럼 유형과 길이 정의, 반정규화 수행

  • 반정규화 : 중복 테이블 추가, 테이블 조합, 테이블 분할, 테이블 제거, 컬럼 중복화. 데이터 모델을 통합하는 데이터베이스 성능향상 기법이다.

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

• 테이블 제약조건 설계
  • 참조무결성 제약조건★ : 참조의 일관성 보장. 참조하는 외래키의 값은 항상 참조되는 릴레이션에 기본키로 존재
    • 제한, 연쇄, 널 값
• 익덱스 설계
  • 인덱스 개념 : 검색 연산의 최적화를 위해 데이터베이스 내 열에 대한 정보를 구성한 데이터구조
  • 인덱스 적용 기준
    • 적정 분포도 10~15%
    • 분포도 = (1/컬럼 값의 종류)*100
    • 분포도 = (컬럼 값의 평균 row 수)/(테이블의 총 row수)*100
  • 인덱스 컬럼 선정 기준
    • 분포도가 좋은 컬럼은 단독 생성
    • 자주 조합되는 컬럼은 결합 인덱스로 생성
    • 수정이 빈번하지 않은 컬럼 선정
  • 설계 시 고려 사항
    • 지나치게 많은 인덱스는 오버헤드로 작용
    • 인덱스는 추가 저장공간 필요
    • 인덱스와 테이블의 저장공간 적절히 분리
• 뷰 설계
  • 뷰 속성★ : REPLACE, FORCE, NOFORCE, WITH CHECK OPTION, WITH READ ONLY
• 클러스터 설계
  • 클러스터 적용 기준
    • 분포도가 넓을수록 유리
    • 액세스 효율 향상을 위한 물리적 저장 방법
    • 대량의 범위 자주 액세스
    • 테이블의 빈번한 조인
  • 클러스터 설계 시 고려 사항
    • 검색효율 상승, 입력/수정/삭제 시 부하 증가
    • UNION, DISTINCT, ORDER BY, GROUP BY가 빈번한 컬럼
    • 수정이 자주 발생하지 않는 컬럼
    • 처리 범위 문제 -> 단일 테이블
    • 조인 문제 -> 다중 테이블
• 파티션 설계
  • 파티션의 종류(레해리컴) ★
    • 레인지 파티셔닝 : 연속적인 숫자나 날짜 기준, 쉬운 관리 기법으로 관리 시간 단축
    • 해시 파티셔닝 : 파티션 키의 해시 함수값 기준, 균등한 데이터 분할
    • 리스트 파티셔닝 : 명시적 제어, 분포도 비슷, 데이터 많은 SQL에서 컬럼의 조건이 많은 경우
    • 컴포지트 파티셔닝 : 범위 분할 이후 해시함수 적용 재분할
  • 파티션의 장점(성가백합) ★
    • 성능 향상
    • 가용성 향상
    • 백업 기능
    • 경합 감소(디스크 스트라이핑(병렬 사용))
      
      

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

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

• 프로시저
  • SQL을 이용하는 데이터 조작 프로그램
  • DB 내부 저장. 조건 만족 시 자동 수행
  • 데이터 조작 프로시저 : 저장된 프로시저, 저장된 함수, 저장된 패키지, 트리거
• PL/SQL
  • 표준 SQL을 기본으로 Oracle에서 개발한 DML(데이터 조작 언어)
  • Oracle기반의 모든 프로시저 작성에 사용되며 표준 SQL의 확장 기능이 우수
• PL/SQL 작성 절차
  • 드라이버 로딩 -> 데이터베이스 연결 -> 쿼리 전달 -> 결과 수신
• 데이터 저장소 정의 ★
  • 생성 : CREATE TABLE ~ CONSTRAINT
  • 수정 : ALTER TABLE ~ MODIFY
  • 삭제 : DROP TABLE
• 데이터 조작 프로시저 작성 ★
  

    CREATE OR REPLACE PROCEDURE INPUT_EMP
    프로시저 명
    파라미터 1 데이터타입[IN|OUT|INOUT]
    IS[AS]
        선언부
    BEGIN
        실행부
    EXCEPTION
        예외처리부
    END;
  

  
  

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

• PL/SQL 테스트 ★
  • DBMS_OUTPUT 패키지 : 메시지를 버퍼에 저장하고 버퍼로부터 메시지를 읽어오기 위한 인터페이스
    • DBMS_OUTPUT.DISABLE
    • DBMS_OUTPUT.ENABLE
    • DBMS_OUTPUT.PUT
    • DBMS_OUTPUT.GET_LINE
    • DBMS_OUTPUT.GET_LINES
  • SETSERVEROUTPUT ON : 처리결과 화면 출력
  • SHOW ERRORS : 오류내용 확인
• 저장 객체 테스트
  • 저장된 함수 : 변수 선언 후 EXECUTE
  • 저장된 프로시저, 저장된 패키지 : SETSERVEROUTPUT ON 후 EXECUTE
  • 트리거 : DBMS_OUTPUT.PUT_LINE 출력하여 SET SERVEROUTPUT ON 실행
    
    

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

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

• 쿼리 성능 개선(튜닝) : 데이터베이스에서 프로시저에 있는 SQL 실행 계획을 분석, 수정을 통해 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 작업

• 쿼리 성능 개선 절차
  • 문제 있는 SQL식별(APM) -> 옵티마이저 통계 확인 -> SQL문 재구성 -> 인덱스 재구성 -> 실행계획 유지관리
• 옵티마이저
  • SQL 수행의 최적의 처리경로를 생성해 주는 DBMS의 핵심엔진
  • SQL 성능개선의 핵심
  • 옵티마이저가 생성한 SQL 실행경로 : 실행계획
• 옵티마이저 유형 ★
  • 규칙기반(RBO) : 통계정보X, 우선순위, 사용자가 원하는 처리 경로로 유도가 쉬움
  • 비용기반(CBO) : 통계정보O, 수행시간, 옵티마이저의 이해도가 낮아도 성능보장 가능

• 옵티마이저 역할 : 쿼리 변환, 비용 산정, 계획 생성

• 힌트 ★
  • 옵티마이저의 실행 계획 원하는대로 변경
  • /+ RULE/ : RBO 지정
  • /+ CHOOSE/ : 디폴트에 따름
  • /+ INDEX(테이블명 인덱스명)/ : 지정 인덱스 강제 사용
  • /+ USE_HASH(테이블명)/ : Hash join
  • /+ USE_MERGE(테이블명)/ : Sort Merge join
  • /+ USE_NL(테이블명)/ : Nested Loop join