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

데이터 조작 프로시저 작성

데이터 저장소에 연결을 수행하는 프로시저

데이터 저장소로부터 데이터를 읽어 오는 프로시저

데이터 변경 내용 또는 신규 입력된 데이터를 데이터 저장소에 저장하는 프로시저

 

SQL 분류

1) 데이터 정의어(DDL)

명령	설명	예시
CREATE	오브젝트 생성	CREATE TABLE dept1 (deptno number(2),  dname varchar2(14),  loc varchar2(13) );
DROP	오브젝트 삭제	DROP TABLE dept1;
RENAME	오브젝트 이름 변경	RENAME dept1 TO dept_copy;
ALTER	오브젝트 구조 변경	ALTER TABLE dept_copy MODIFY (loc varchar2(14));
TRUNCATE	모든행 삭제	TRUNCATE TABLE dept_copy;

 

2) 데이터 조작어(DML)

명령	설명	예시
INSERT	데이터 입력	INSERT INTO dept_copy(deptno, hr_limit) VALUES(92, 10);
UPDATE	데이터 수정	UPDATE dept_copy SET hr_limit=20 WHERE hr_limit is null;
DELETE	데이터 삭제	DELETE dept_copy WHERE deptno > 90;

 

3) 데이터 정의어(DCL)

명령어	설명	예시
ROLE	권한 묶음용	CREATE ROLE manager; GRANT create table, create view TO manager; GRANT manager TO scott;
GRANT	권한 및 롤 부여	GRANT connect, resource to scott; GRANT SELECT ON emp TO scott[PUBLIC] [With Grant/Admin Option];
REVOKE	권한 및  회수	REVOKE connect, resouce FROM scott; REVOKE SELECT ON emp FROM scott;

 

4) 트랜잭션 제어어(TCL)

명령	설명	예시
COMMIT	트랜잭션을 완료하여 데이터 변경사항을 최종 반영	COMMIT;
ROLLBACK	데이터를 변경사항을 이전 상태로 되돌리는 명령어	ROLLBACK;
SAVEPOINT	특정지점 지정	SAVEPOINT A;

 

절차형 데이터 조작 프로시저

최근의 프로그래밍 언어의 특성을 수용한, SQL의 확장 가능

사용시 장점

1) Compile 이 필요 없어 script 생성 및 변경 후 바로 실행이 가능

2) 프로그램 개발의 모듈화가 가능

3) 식별자를 선언할 수 있음

4) 절차적 언어 구조로 된 프로그램을 작성할 수 있음

5) ERROR 처리가 가능

6) 성능 향상을 기대할 수 있음

 

<장점>

(1) 사용자가 이해하기 쉬운 단어로 구성

(2) 쉽게 배울 수 있음

(3) 복잡한 로직을 간단하게 작성할 수 있음

(4) ANSI에 의해 문법이 표준화되어 있음

 

<단점>

(1) 반복처리를 할 수 없음.(Loop)

(2) 비교처리를 할 수 없음.(IF)

(3) Error 처리를 할 수 없음. (예외처리)

(4) SQL문을 캡슐화 할 수 없음.

(5) 변수 선언을 할 수 없음.

(6) 실행할 때마다 분석 작업 후 실행

(7) Network Traffic을 유발

 

PL/SQL의 생성과 실행

1. 반복처리를 할 수 있음.(Loop)

2. 비교처리를 할 수 있음.(IF)

3. Error 처리를 할 수 있음. (예외처리)

4. SQL문을 캡슐화 할 수 있음.

5. 변수 선언을 할 수 있음.

6. 실행할 때마다 분석된 결과를 살행만 하기 때문에 성능이 빠름.

7. Network Traffic이 감소

 

 

PL/SQL 구조

1) 선언부 (DECLARE, Optional)

실행부에서 참조할 모든 변수, 상수, CURSOR, EXCEPTION을 선언

 

2) 실행부 (BEGIN/END, Mandatory)

BEGIN과 END 사이에 기술되는 영역

데이터베이스 데이터를 처리할 SQL문과 PL/SQL 블록을 기술

 

3) 예외 처리부 (Exception, Optional)

실행부에서 에러가 발생했을 때 수행될 문장을 기술

 

PL/SQL의 종류

 

PL/SQL을 활용한 저장형 객체 활용

(1) Stored Function 특성

      1) 값을 계산하고 결과값을 반환하기 위해서 많이 사용

      2) 대부분 구성이 프로시저오 유사하지만 IN 파라미터만 사용할 수 있음

      3) 반드시 반환될 값의 데이터 타입을 RETURN문을 선언해야 함

      4) PL/SQL 블록 내에서 RETURN문을 통해서 반드시 반환해야 함

 

(2) Stored Procedure

연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL 블록을 DB에 저장하기 위해 생성

PL/SQL의 가장 대표적인 구조

개발자가 자주 실행해야 하는 업무 흐름을 미리 저장하고 필요시 호출하여 실행

 

(3) Stored Package

패키지는 오라클 데이터베이스에 저장되어 있는 서로 관련있는 PL/SQL 프로시저와 함수들의 집합

 

(4) Trigger

• 트리거 생성시 설정한 테이블에 대해 누군가가 INSERT, UPDATE, DELETE 문을 실행하면 그 작업을 실행시킨 후, 또는 실행시키기 전에 묵시적으로 Trigger에 정의한 로직을 실행시킴
• Trigger는 Table과는 별도로 DATABASE에 저장됨
• Trigger는 VIEW에 대해서가 아니라 TABLEdp 관해서만 정의할 수 있음
• 데이터베이스의 감시, 보안, 연속적인 오퍼레이션의 자동 처리 등 응용기능

SQL*PLUS 활용

SQL	SQL*PLUS
데이터베이스와 통신하는 언어	SQL 명령어를 서버에 전송하는 Tool
ANSI 표준에 기초	Oracle사 제공 Tool
데이터와 테이블에 대한 정의가 가능	데이터에 대한 어떤 정의도 불가능
SQL buffer를 사용	SQL buffer를 사용하지 않음
여러 행 입력 가능	여러 행 입력할 수 없음
명령어 실행시 종료문자(;) 사용	명령어 실행시 종료문자(;) 사용 안함
키워드를 축약할 수 없음	키워드를 축약할 수 있음

 

JDBC를 이용해 데이터 저장소에 연결하기

DB와 연결하기 위해 DBMS에서 제공하는 jar 파일 드라이버를 메모리에 적재함.

해당 드라이버를 사용하여 DB를 연결함.

 

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

SQL 성능개선 순서

1) 문제 있는 SQL 식별

애플리케이션의 성능을 관리하거나 모니터링하기 위한 툴인 APM(Application Performance Management) 등을 활용

 

2) 옵티마이저(Optimizer) 통계 확인

옵티마이저(Optimizer)는 개발자가 작성한 SQL을 가장 빠르고 효율적으로 수행할 최적의 처리 경로를 생성해주는 데이터베이스 핵심 모듈

 

데이터 조작 프로시저를 최적화하기 위한 것

개발자가 작성한 SQL을 가장 빠르고 효율적으로 수행할 최적의 최적 경로를 생성해주는 데이터베이스 핵심모듈

 

최적의 처리 경로를 생성하기 위해 옵티마이저가 활용하는 통걔정보를 주기적을 현행화 함

Analyze Object_type Object_name Operation STATISTICS;

ANALYZE TABLE emp COMPUTE STATISTICS;

 

(1) Object_name : TABLE, INDEX, CLUSTER 중 선택하여 기술

(2) Operation

        (가) COMPUTE

                : 정의된 Object_name에 대하여 통계정보를 정확하게 계산하는 방법

                  가장 정확한 통계를 얻을 수 있지만 처리 속도는 가장 느림

        (나) ESTIMATE

                : 데이터 딕셔너리의 값과 데이터 샘플링 정보를 기반으로 통계치를 예상하는 방법

                  COMPUTE보다 덜 정확하지만 처리 속도가 훨씬 빠름

        (다) DELETE : 정의된 Object_name에 대한 모든 통계 정보를 삭제함

 

3) 실행계획 검토

처리량이 작은 Table을 Driving 테이블로 지정되었는지 확인

 

4) SQL문 재구성

     (1) 가능한 한 where = 을 많이 써서 범위를 줄여 처리속도가 빠르도록 함

     (2) 칼럼 변경 연산자를 쓰지 않도록 함

     (3) 불필요한 검색을 하지 않도록 함

     (4) 옵티마이저가 비정상적인 실행계획을 수립하여 처리한다면 힌트로서 옵티마이저이 액세스 경로 및 조인 순서를 제

           어할 수 있도록 함

 

5)  인덱스 재구성

성능에 중요한 액세스 경로를 고려하여 인덱스화함

 

6) 실행계획 유지관리

데이터베으스 버전 업그레이드나, 데이터의 시스템 이동 등 시스템 환경의 변경 사항 발생시에도 실행계획이 유지되고 있는지 모니터링하고 관리

SELECT /* + INDEX(e empfirstname_idx) */ FROM employees e WHERE FIRST_NAME="Mj"