[정보처리기사 실기] Ⅱ. 데이터 입출력 구현 - 프로시저(Procedure)

Ⅱ. 데이터 입출력 구현 - 데이터 조작 프로시저.

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

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

프로시저(Procedure)

SQL을 이용 데이터를 조작하는 프로그램.

데이터베이스 내부에 저장되고 일정한 조건이 되면 자동으로 수행.

PL/SQL

표준 SQL기반 오라클에서 개발한 데이터 조작 언어.

PL/SQL 작성 절차

데이터저장소(DB) 연결

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

데이터 저장소 정의

데이터 조작 프로시저 작성

구문요소 : 프로시저명, 기능구현, 프로시저 호출.

데이터 검색 프로시저 작성

구문요소 : 프로시저명, 데이터 조회.

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

PL/SQL 테스트
DBMS_OUTPUT(=>메시지를 버퍼에 저장) 패키지 활용.
실행 예시)
SQL> SET SERVEROUTPUT ON 
SQL> EXECUTE 프로시저명('파라미터');
[ 결 과 ]
번호 : 100
이름 : 홍길동
급여 : 200

저장객체 테스트 예시

저장된 함수 [Stored Function]

SQL> VAR salary NUMBER;    //함수의 반환 값을 저장할 변수 선언 
SQL> EXECUTE salary := update_sal(1004);    //EXECUTE 문을 이용해 함수를 실행
SQL> PRINT salary;  //SQL에서 선언된 변수의 출력은 PRINT 문을 사용

저장된 프로시저 [Stored Procedure]

SQL> SET SERVEOUTPUT ON;    //SET SERVEROUTPUT ON 실행 
SQL> EXEC update_sal(1004); 
//EXEC 문을 이용해 실행 / 실행 이후 프로시저에서 처리하는 대로 관련 데이터 수정사항 확인 

저장된 패키지 [Stored Package]

SQL> SET SERVEOUTPUT ON;    //SET SERVEROUTPUT ON 실행 
SQL> EXEC emp_info.all_emp_info; //EXEC 문을 이용해 실행 / 프로시저 실행 결과를 확인. 

트리거 [Trigger]

SQL> SET SERVEOUTPUT ON;    //트리거가 처치될 조건에 부합되는 SQL을 실행하여 데이터 처리결과를 확인.

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

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

쿼리 성능 개선(튜닝)

: SQL 실행계획을 분석, 수정을 통해 최소시간으로 원하는 결과를 도출하게 프로시저를 수정하는 작업.

쿼리 성능 개선(튜닝) 절차

문제있는 SQL 식별

애플리케이션 성능 관리 및 모니터링 도구인 APM등을 활용.

옵티마이저 통계 확인

SQL문 재구성

인덱스 재구성

실행계획 유지 관리

옵티마이저(Optimizer)

옵티마이저 : SQL을 수행시 최적의 처리 경로를 생 성해주는 DBMS 내부 엔진.

실행계획 : 옵티마이저가 생성한 SQL 처리경로.

유형

규칙기반 옵티마이저(RBO) : 사전 등록된 규칙에 따라 실행계획 선택.

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

역할

쿼리 변환(Query Transformer) : SQL을 일반적이고 표준화된 형태로 변환.

비용 산정(Estimator) : 쿼리단계의 선택도, 카디널리티, 비용계산 / 실행계획의 총 비용 계산.

계획 생성(Plan Generator) : 후보군이 될 실행계획 생성.

힌트사용 : 항상 최선의 실행계획을 수립할 수 없어 힌트로 실행계획 변경.

/*+ RULE */
규칙기반 접근 방식 사용 지정.

/*+ CHOOSE */
오라클 옵티마이저 기본값에 따름.

/*+ INDEX(테이블인덱스명) */
지정된 인덱스 강제 사용.

/*+ USE_HASH(테이블명) */
지정된 테이블이 Hash Join 형식으로 일어나게 유도.

/*+ USE_MERGE(테이블명) */
지정된 테이블이 Sort Merge 형식으로 일어나게 유도.

/*+ USE_NL(테이블명) */
지정된 테이블이 Nested Loop 형식으로 일어나게 유도.

힌트 [Hint]

실행하려는 SQL문에 사전에 정보를 주어 SQL문 실행에 빠른 결과를 가져오는 효과를 만드는 문법.

해시조인 [Hash Join]

해싱 함수 기법을 활용하여 조인을 수행하는 방식

정렬 합병 조인 [Sort-Merge Join]

조인의 대상범위가 넓을 경우 발생하는 임의접근을 줄이기 위한 경우나 연결고리에 마땅한 인덱스가 존재하지 않을 경우 해결위한 Join 방법.

중첩 반복 조인 [Nested-Loop Join]

2개 이상의 테이블에서 하나의 집합을 기준으로 순차적으로 상대방 Row를 결합하여 원하는 결과를 조합하는 방식.

SQL문 재구성

특정값 지정 : < , > 가 아닌 = 이용

별도의 SQL 사용 : 다양한 작업에 하나의 SQL 사용시 각작업에 최적화 안됨 / 하나의 SQL사용시 UNION ALL 이용.

힌트 사용

HAVING 미사용 : HAVING 사용시 인덱스 미사용됨.

인덱스만 질의 사용 : 옵티마이저가 최적의 경로를 찾도록!

인덱스 재구성

자주쓰는 컬럼 선정

SORT 명령어 생략 : Sort 명령어 생략을 위한 컬럼 추가.

분포도 고려 : 분포도가 좋은 컬럼은 단독 인덱스 생성.

변경적은 컬럼 선정 : 데이터 변경이 적은 컬럼에 인덱스 생성.

결합인덱스 사용 : 잦은 인덱스 조합시 결합인덱스 생성.

정보처리기사 필기 합격 후 실기대비 정리 및 책없이 간단히 보기위해 작성하였습니다.
2020년 수제비 정보처리기사 책 기반으로 정리 하였습니다.

[정보처리기사 실기] Ⅱ. 데이터 입출력 구현 - 프로시저(Procedure). (feat.수제비)

이전[정보처리기사 실기] Ⅱ. 데이터 입출력 구현 - 물리데이터 저장소 설계

다음[정보처리기사 실기] Ⅱ. 기사실기 요약 / 정리 / 단답 / Tip / 알고가기!