[2020 정보처리기사 실기 암기] 2-2. 데이터 조작 프로시저 작성, 데이터 조작 프로시저 최적화

[데이터 조작 프로시저 작성]

 

* 프로시저 : SQL로 생성된 데이터를 조작하는 프로그램으로 DB 내부에 저장되고,

                 일정 조건을 만족하면 자동으로 실행됨

 

* PL/SQL

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

오라클 기반의 모든 프로시저 작성에 사용되며 표준 SQL의 확장 기능이 우수

 

* 데이터 저장소의 연결 절차

1. 드라이버 로딩 : DB와 연결을 위해 DBMS에서 제공하는 JAR파일 드라이버를 메모리에 적재

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

3. 쿼리 전달 : 쿼리를 DB로 전달하기 위해 statement, preparedstatement객체 생성

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

 

* 자바 환경의 경우 구축된 데이터베이스로의 연결은 JDBC를 통해 연결한다.

 

* DBMS_OUTPUT 패키지

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

 

* SET SERVEROUTPUT : PL/SQL의 처리 결과를 화면에 출력하기 위한 SQL 명령어

 

* PL/SQL 명령어 SHOW ERRORS : PL/SQL의 처리 결과에 대한 오류 내용 출력

 

 

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

 

* SQL 성능 개선의 핵심부분으로 옵티마이저의 실행 계획을 원하는 대로 변경할 수 있게 한다.

힌트가 항상 최선의 실행 계획을 수립할 수 없어서 명시적인 힌트를 통해 실행계획을 변경한다.

 

* 주요 옵티마이저의 힌트

/*+RULE*/ : 규칙 기반 접근 방식을 사용하도록 지정

/*+CHOOSE*/ : 오라클 옵티마이저 디폴트 값에 따름

/*+INDEX(테이블명 인덱스명)*/ : 지정된 인덱스를 강제적으로 쓰게끔 지정

/*+USE_HASH(테이블명)*/ : 지정된 테이블들의 조인이 hash-join형식으로 일어나도록 유도

/*+USE_MERGE(테이블명)*/ : 지정된 테이블들의 조인이 sort-merge형식으로 일어나도록 유도

/*+USE_NL(테이블명)*/ : 지정된 테이블들의 조인이 nested-loop 형식으로 일어나도록 유도

 

* 옵티마이저가 생성한 SQL 처리 경로를 실행계획이라고 부름

사용자가 작성한 SQL이 요구한 데이터를 추출하기 위해 옵티마이저가 작업의 방법과 순서를 결정하는 것을 실행계획이라 함

 

* 데이터 베이스 옵티마이저

사용자가 SQL로 결과를 요구하면, 처리경로는 DBMS에 내장된 옵티마이저가 자동으로 생성해주고

생성한 SQL 처리경로를 실행 계획이라고 부름

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

 

* 옵티마이저가 힌트를 사용하는 이유

옵티마이저가 비정상적인 실행계획 수립 시 힌트로서 액세스 경로 및 조인 순서를 제어할 수 있도록 하기 때문

 

* 규칙 기반 옵티마이저 : 통계 정보가 없는 상태에서 사전 등록된 규칙에 따라 질의 실행 계획을 선택하는 옵티마이저

                우선 순위 기반/ 평가기준은 인덱스 구조, 조건절 형태, 연산자 등

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

                수행 시간 기반/

                평가기준은 레코드 수, 블록 수, 평균 행 길이, 컬럽 값 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 등