(정보처리기사 실기) 데이터 입출력 구현

논리 데이터 저장소 확인

논리 데이터 모델링 : 개념 모델로부터 데이터 및 규칙을 구체적으로 표현

논리 데이터 모델링 특성 : 정규화, 포용성, 완전성, 독립성

논리 데이터 모델링 속성 : 개체, 속성, 관계

개체-관계 모델(E-R 모델) : 현실 세계에 존재하는 데이터와 관계를 사람이 이해할 수 있는 형태로 표현

 

정규화 : 데이터베이스 설계시 중복을 최소화

이상현상 : 릴레이션 조작시 발생하는 비합리적인 현상 (삽입, 삭제, 갱신 이상이 있다.)

 

정규화 단계

1정규형 : 원자값

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

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

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

4정규형 : 다치 종속 제거

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

 

물리 데이터 저장소 설계

물리 데이터 모델링 : 논리 모델을 적용하고자 하는 기술에 맞게 상세화

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

1. 개체를 테이블로 변환

2. 속성을 컬럼으로 변환

3. UID를 기본키로 변환

4. 관계를 외래키로변환

5. 컬럼 유형과 길이 정의

6. 반 정규화 수행

 

참조 무결성 제약 조건 

-릴레이션 사이의 참조의 일관성을 보장

-참조하는 외래키의 값은 항상 참조하는 릴레이션의 기본키

옵션 ON DELETE { RESTRICT | CASCADE | SET NULL }

- 제한 : 참조 무결성 위배 연산 거절

- 연쇄 : 참조 튜플 함께 삭제

- 널 값 : 튜플을 삭제하고 NULL값이 들어가야 할때 NOT NULL 옵션이라면 연산 거절

 

인덱스 

-검색 연산의 최적화를 위해 정보를 구성한 데이터 구조

-신속한 조회 가능

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

-너무 많은 인덱스는 오버헤드 발생

 

인덱스 컬럼 선정

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

-자주 조합되는 컬럼은 결합 인덱스

 

뷰 : 최적의 액세스 경로를 사용

뷰 속성 (REPLACE, FORCE, NOFORCE, WITH CHECK OPTION, WITH READ ONLY)

 

클러스터 : 요소를 몇개 모은 집합

- 인덱스의 단점 해결

- 분포도가 넓을수록 유리

-빈번하게 조인 발생시 활용

- 액세스 효율 향상 방법

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

-UNION, DISTINCT, ORDER BY, GROUP BY가 빈번한 컬럼이면 검토 대상

-처리 범위가 넓어 문제가 발생 -> 단일 테이블 클러스터링

-조인이 많아 문제가 발생 -> 다중 테이블 클러스터링

 

파티션 : 성능 향상, 가용성 향상, 백업가능, 경합 감소

파티션의 종류 

-레인지 파티셔닝 : 숫자나 날짜를 기준, 손쉬운 관리

-해시 파티셔닝 : 해시 함수값 이용, 균등한 데이터 분할

-리스트 파티셔닝 : 명시적 제어 가능, 분포도 비슷

-컴포지트 파티셔닝 : 범위분할 후 해시 함수를 이용한 재분할, 

 

데이터 조작 프로시저 

데이터 조작 프로시저

- 저장된 프로시저 : 배치작업 수행, 복잡한 트랙잭션 수행, PL/SQL 문을 DB에 저장

- 저장된 함수 : 프로시저와 비슷하나 결과값을 되돌려 받을 수 있음

- 저장된 패키지 : 프로시저나 함수를 효율적으로 관리

- 트리거 : 특정한 DML(삽입,삭제,갱신 등) 발생시 자동 실행

 

PL/SQL 작성 절차

1. 드라이버 로딩 : JAR파일 드라이버를 메모리에 적재

2. 데이터베이스 연결 : 드라이버를 사용하여 DB 연결

3. 쿼리 전달 : 쿼리 전달을 위해 Statement, PreparedStatement 객체 생성

4. 결과 수신 : 쿼리의 수행으로 인한 반환 값 수신

 

프로시서 작성

CREATE OR REPLACE PROCEDURE
-- Procedure명
-- 파라미터1 데이터타입 [ IN | OUT | INOUT]
IS[AS]
BEGIN
EXCEPTION
END;

 

생성 

CREATE TABLE 테이블명

수정

ALTER TABLE 테이블명 MODIFY

삭제

DROP TABLE 테이블명

 

실행

EXEC OR EXECUTE명령을 통해 실행

SET SERVEROUTPUT ON
EXEC emp_info;

 

PL/SQL 테스트

DBMS_OUTPUT 패키지 활용

- 메시지를 버퍼에 저장하고 버퍼로부터 메시지를 읽어오기 위한 인터페이스 패키지

DBMS_OUTPUT.DISABLE : 메시지 버퍼 내용 삭제

DBMS_OUTPUT.ENABLE : 메시지 버퍼 내용 할당

DBMS_OUTPUT.PUT : 메시지의 마지막 라인 끝에 신규라인 문자 추가

DBMS_OUTPUT.GET_LINE : 하나의 라인을 읽어옴

DBMS_OUTPUT.GET_LINES : 라인을 모두 읽어옴

 

DBMS_OUTPUT 패키지 실행 방법

- PL/SQL의 처리 결과를 화면에 출력하기 위해 사용 

SET SERVEROUTPUT ON

PL/SQL 오류 발생시 

'SHOW ERRORS' 명령어를 통해 오류내용 확인

 

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

쿼리 성능 개선 : SQL 실행 계획을 분석, 수정을 통해 최소의 시간으로 최대의 효과를 내도록 하는 것

쿼리 성능 개선 절차

1. 문제있는 SQL 식별 (APM 활용)

2. 옵티마이저 통계 확인

3. SQL문 재구성 (범위가 아닌 특정값 지정으로 속도 향상)

4. 인덱스 재구성

5. 실행계획 유지관리(데이터베이스 버전 업그레이드 등을 관리)

 

APM : 성능 모니터링 도구

옵티마이저  : SQL을 가장 빠르고 효율적으로 최적의 처리경로를 생성해주는 데이터베이스 모듈

 

옵티마이저 유형

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

- 통계 정보 없이 실행

- 규칙(우선순위)기반

- 원하는 처리경로 유도 가능

 

비용기반 옵티마이저(CBO)

- 통계 정보를 이용

- 비용(수행시간)기반

- 성능 보장가능

 

SQL 실행시 옵티마이저의 역할

- 쿼리 변환

- 비용산정

- 계획 생성

 

힌트(HINT) 사용

- 옵티마이저의 실행 계획을 원하는대로 변경 

- 옵티마이저는 항상 최적의 실행 계획을 세울 수 없기 떄문에 힌트를 사용

 

주요 힌트

/*+ RULE */ : 규칙 기반 방식 지정

/*+ CHOOSE */ : 옵티마이저 디폴트 적용

/*+ INDEX */ : 인덱스를 강제적으로 사용

/*+ USE_HASH */ : 테이블의 조인이 HASH 조인이 되도록 유도

/*+ USE_MERGE */ : 테이블의 조인이 Sort Merge 형식이 되도록 유도

/*+ USE_NL */ : 테이블의 조인이 Neseted Loop 형식이 되도록 유도