DB - Oracle(효율적인 SQL 작성법)

View

• 사용자에게 접근이 허용된 자료만 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된, 이름을 가지는 가상 테이블
• 실제 테이블처럼 행과 열을 가지고 있지만, 실제로 데이터를 저장하고 있지는 않는 가상 테이블
• 데이터 보정작업, 처리과정 시험 등 임시적인 작업을 위한 용도 활용
• 복잡한 쿼리를 단순화
• 쿼리 재사용 가능

특징

• 기본 테이블과 같은 형태의 구조로 기본 테이블 조작법과 비슷함
• 가상 테이블로 물리적으로 구현되어 있지 않음
• 데이터의 논리적 독립성 제공
• 필요한 데이터만 정의해서 처리할 수 있어 관리가 용이하고 명령문이 간단해짐
• 뷰를 통해서만 데이터에 접근하게 하면 뷰에 표출되지 않는 데이터 보호 가능
• 기본 테이블의 기본키를 포함한 속성(열) 집합으로 뷰를 구성해야 삽입, 삭제, 갱신, 연산 가능
• 다른 뷰 정의에 기초가 될 수 있음
• 뷰가 정의된 기본 테이블이나 뷰를 삭제하면 해당 테이블이나 뷰를 기반으로 정의된 다른 뷰도 삭제됨

장점

• 논리적 데이터 독립성 제공
• 동일 데이터에 대해 동시에 여러 사용자의 상이한 응용이나 요구를 지원
• 사용자의 데이터 관리를 간단하게 해줌
• 접근 제어를 통한 자동 보안

단점

• 자체 인덱스를 가질 수 없음
• 이미 정의된 뷰 수정 불가
• 뷰로 구성된 내용에 대한 삽입, 삭제, 갱신, 연산에 제약
  ㄴ 단순 뷰인 경우 INSERT, UPDATE, DELETE가 자유로움 (NOT NULL 컬럼 주의)
  ㄴ 함수, UNION, GROUP BY 등을 사용한 뷰인 경우 INSERT, UPDATE, DELETE 불가능 (조인만 사용한 복합 뷰인 경우 제한적으로 가능)

View 생성문

MySQL

CREATE VIEW 뷰이름 AS

SELECT 필드이름1, 필드이름2, ...

FROM 테이블이름

WHERE 조건

Oracle

CREATE OR REPLACE VIEW [스키마.][뷰 NAME] AS

SELECT문;

SQL 튜닝

SQL문을 최적화하여 빠른 시간 내에 원하는 결과값을 얻기 위한 작업

접근 방법

• 부하의 감소 : 일반적으로 접근하는 튜닝 방식, 동일한 부하를 보다 효율적인 방법으로 수행
• 부하의 조정 : 부하 정도에 따라 업무를 조정하는 접근 방법, 일반 업무(OLTP-Transaction)를 분리
• 부하의 병렬 수행 : 부하가 많이 걸리는 부분에 병렬 서비스를 실행하여 응답 시간 단축

옵티마이저(Optimizer)

• 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진

SQL 실행 시 옵티마이저(Optimizer)에서 여러 가지 실행계획을 세움
시스템 통계정보를 활용하여 각 실행계획의 예상 비용 산정
각 실행계획 비교
효율이 높은 실행계획 판별 및 해당 실행계획으로 쿼리 수행

![[Pasted image 20231226134105.png]]

옵티마이저의 종류

실행 계획을 세우는 방식에 따라 규칙 기반 옵티마이저, 비용 기반 옵티마이저으로 나눔
![[Pasted image 20231226134129.png]]

실행계획(DBMS_XPLAN)

• 사용자가 SQL을 실행하여 데이터를 추출하려고 할 때 옵티마이저가 수립하는 작업 절차

  실행 계획 순서 읽는 법

• 1.* 들여쓰기가 깊은 순서대로 읽기
• 2.* 같은 들여 쓰기가 존재하면 위 -> 아래 순으로 읽기

SCAN의 종류와 속도

SCAN : 데이터를 읽는 작업
SCAN을 수행하는 방식을 일컬어 접근 경로라고 함

FULL TABLE SCAN : 테이블의 전체 데이터를 읽어 조건에 맞는 데이터를 추출
ROWID SCAN : ROWID를 기준으로 데이터를 추출하며 단일 행에 접근하는 방식 중에서 가장 빠름
INDEX SCAN : 인덱스를 활용하여 해당하는 데이터 추출

테이블에 데이터가 많지 않아 INDEX를 타는 것이 시간이 더 많이 걸리거나 추출할 데이터가 많을 경우 FULL TABLE SCAN이 효율적임
많은 데이터가 있는 테이블에서 원하는 데이터를 추출해야 하는 상황일 경우 INDEX SCAN이 효율적임

PL/SQL 블록 (Anonymous Block)의 구조 및 특징

• Declare, Begin, Exception, End로 구성
• 프로시저, 사용자 정의 함수, 트리거도 비슷한 문법

구조

DECLARE <선택사항>
    선언부 - BEGIN ~ END에서 사용할 변수나 인수에 대한 정의 및 데이터 타입 선언
BEGIN
    처리할 SQL 문과 필요한 로직이 정의되는 실행 부분
EXCEPTION
    BEGIN ~ END 에서 실행되는 SQL문에 발생된 에러를 처리하는 부분

• BEGIN (DECLARE) ~ END가 PL/SQL의 블럭을 의미하며 다중 선언 가능
• IS(선언문)는 PL/SQL을 이용하여 작성되는 프로시저, 사용자 정의 함수에서 사용되는 문법이며 DECLARE와 마찬가지로 문장의 시작을 의미하면서 동시에 변수가 선언되는 영역
  • IS가 DECLARE의 역할까지 대신하므로 IS를 사용하면 DECLARE는 사용할 수 없음
• := : 대입연산자
• = : 값 비교 연산자
• DBMS 정의 에러 혹은 사용자 정의 에러 사용 가능
• PL/SQL은 Oracle에 내장되어 있어 지원하는 경우 어떤 서버로든 프로그램을 옮길 수 있음

PL/SQL 작성법

DECLARE  
    변수 선언  
BEGIN 
    비즈니스 로직 
EXCEPTION 
    익셉션처리 
END; 

    DECLARE

        V_CNT NUMBER :=0;            /* COUNT용 변수 */
        V_ERR VARCHAR2(10) := '';        /* 에러코드 = 1: 정상, -1: 오류, 0: NO DATA */
        V_MSG VARCHAR2(1000) := '';        /* 에러메시지 */

        E_PERSON_EXCEPTION EXCEPTION;

    BEGIN
        V_CNT := 0;
        DBMS_OUTPUT.PUT_LINE(V_CNT || '정상');

        RAISE NO_DATA_FOUND;    /* 강제로 익셉션 발생 */



    EXCEPTION
        WHEN E_PERSON_EXCEPTION THEN
            V_ERR := '-1';
            V_MSG := '예기치 못한 오류 발생 / ' || SQLCODE || ' : ' || SQLERRM;
            DBMS_OUTPUT.PUT_LINE(V_MSG);
        WHEN OTHERS THEN
            V_ERR := '-1';
            V_MSG := '예기치 못한 오류 발생 / ' || SQLCODE || ' : ' || SQLERRM;
            DBMS_OUTPUT.PUT_LINE(V_MSG);
    END;

PL/SQL과 SQL/PSM

• PL/SQL : Oracle 계열 DBMD에서 사용하는 SQL용 프로그래밍 언어
• SQL/PSM : mySQL 계열 DBMS에서 사용하는 SQL용 프로그래밍 언어

PL/SQL (Oracle's Procedural Language extension SQL, 절차형 SQL)
- 응용 프로그램에서의 데이터베이스 처리 향상을 위해 SQL 문장에서 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR) 등을 지원하며 오라클 자체에 내장되어 있는 프로시저 언어
- PL/SQL을 이용하여 다양한 저장 모듈을 개발할 수 있음

• 저장모듈
  • PL/SQL 문장을 DB서버 내에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램
  • 독립적으로 실행되거나 다른 프로그램으로 부터 실행될 수 있는 완전한 실행 프로그램.
  • Oracle의 저장 모듈에는 프로시저, 사용자 정의함수, 트리거가 있음
  • Block 구조로 되어 있어 다수의 SQL 문을 한 번에 DB로 보내서 처리하므로 수행 속도를 향상 시킬 수 있음
  • 서버로의 통신량을 줄일 수 있고 기능별로 모듈화를 할 수 있음

• 매개 변수를 받을 수 있는 PL/SQL Block
• 프로시저 내의 변수는 Scalar 변수라고 하여 임시 데이터 1개만 저장할 수 있으며, 모든 형태의 데이터 유형 지정 가능
• PL/SQL에서 사용하는 프로시저 내의 SELECT 문장은 반드시 결과 값이 있어야 하며 결과는 1개여야 함
  • 조회 결과가 없거나 2개 이상인 경우에는 에러가 발생
  • 특정 로직을 처리하면서 결과는 있어야 하지만 결과 값을 함수처럼 반환하지는 않음

STORED FUNCTION(SF) 작성법

• RETURN 값이 있는 DB에 저장된 함수

• SELECT 구문에서 호출하여 사용 가능

  CREATE OR REPLACE FUNCTION UNI.SF_SCOR200_REPEAT_FG
  (
       I_YY           IN      VARCHAR2 ,
       I_SHTM_CD      IN      VARCHAR2 ,
       I_STD_NO      IN      VARCHAR2 ,
       I_SBJT_ID      IN      VARCHAR2 ,
       I_DECS_NO      IN      VARCHAR2
   )
   RETURN VARCHAR2
  
  IS
  
  /*******************************************************************************************************
   FUNCTION 명       : SF_SCOR200_REPEAT_FG
   FUNCTION 설명     : 학번에 따라 재이수 신청여부 계산 후 리턴
   사용화면          :
   작성자/작성일     : 정형택 / 2012-04-19
   관련테이블        : SCOR200
   수정자/수정일     : 정형택 / 2012-04-19
   수정내용          :
   RETURN           : Y - 재이수 신청, N - 재이수 미신청
  *******************************************************************************************************/
      V_RESULT          VARCHAR ( 1 ) ;
  
  BEGIN
  
      --RETURN 'N';
  
      SELECT     CASE WHEN A.RE_CPTN_APLY_YN = 'Y' AND SUBSTR ( A.STD_NO , 1 , 3 ) >= '211' THEN 'Y' ELSE 'N' END      AS RESULT
      INTO     V_RESULT
      FROM     SCOR200 A
      WHERE    A.YY           = I_YY
      AND        A.SHTM_CD      = I_SHTM_CD
      AND        A.STD_NO      = I_STD_NO
      AND        A.SBJT_ID      = I_SBJT_ID
      AND        A.DECS_NO      = I_DECS_NO
      ;
  
       RETURN V_RESULT;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
          V_RESULT := 'X';    /* 해당 학생의 성적데이터가 없을 경우 강제로 X 리턴*/
          RETURN V_RESULT;
      WHEN OTHERS THEN
          V_RESULT := 'X';    /* 예기치 못한 오류일 경우 강제로 X 리턴*/
          RETURN V_RESULT;
  
  END SF_SCOR200_REPEAT_FG;

사용자 정의 함수

• 실행방법
  • 함수명() 으로 실행
• 특징
  • SQL 로직을 묶은 명령문으로 프로시저와 비슷하나 반드시 결과 값을 return문으로 반환

    생성 문법

    [CREATE|REPLACE] FUNCTION 함수명 [(
    argument...
    )]   
    RETURN datatype -- 반환되는 값의 datatype   
    [IS|AS]   
      [선언부]   
    BEGIN  
      [실행부 - PL/SQL Block]   
    [EXCEPTION]   
      [EXCEPTION 처리]   
    RETURN 변수; -- 리턴문 필수   
    END;

프로시저

• 자주 실행해야 하는 특정 작업을 절차적 언어를 이용해 작성한 프로그램 모듈(block)로 필요할 때 호출하여 사용 가능

• 실행방법 : EXECUTE 프로시저명() 으로 실행

• 특징

  • BEGIN ~ END 절 내에서 COMMIT, ROLLBACK과 같은 트랜잭션 명령어 사용 가능
  • 하지만 트랜잭션 관리는 서비스단에서 수행해야하기 때문에 사용하지 지양

프로시저 생성 문법

CREATE \[OR REPLACE\] PROCEDURE 프로시저명 
(argument1 \[MODE\] data\_type1, argument2 \[MODE\] data\_type2, ... ...) 
IS\[AS\] ... 
BEGIN ... 
EXCEPTION ... 
END; /

• CREATE
  • 생성
• [OR REPLACE]
  • 같은 프로시저가 있을 때 기존의 프로시저를 무시하고 새로운 내용으로 덮어 쓴다
• MODE
  • 매개변수의 역할 결정
  • IN : 운영체제에서 프로시저로 전달될 변수
  • OUT : 운영체제로 반환될 결과
  • INOUT : IN과 OUT 두 가지 기능 모두 수행
• IS
  • PL/SQL Block 시작
  • 프로시저 내 (BEGIN문 뒤 나오는 SQL문)에서 사용할 변수 선언
  • LOCAL 변수는 IS와 BEGIN 사이에서 선언
• [AS]
  • IS와 기능적 차이는 없음
  • IS (임베디드) = 블록 / 하위 프로그램 또는 패키지 내 엔티티용
  • AS (독립형) = 블록 외부, 하위 프로그램, 패키지 내 엔티티용
• EXCEPTION
  • BEGIN~END 사이에서 실행되는 SQL문 실행 도중 발생한 에러를 처리하는 예외 처리 부
• END;
  • 실행문의 종료
• /
  • DB에게 프로시저를 컴파일하라는 명령

트리거

• 실행방법
  • 특정 쿼리 실행 시 DB에 의해 자동 실행
• 특징
  • BEGIN ~ END 절 내에서 트랜잭션 사용 불가능

    트리거 생성 문법

    CREATE [OR REPLACE] TRIGGER 트리거명
    [BEFORE|AFTER]
    [INSERT|UPDATE|DELETE] ON 테이블명
    [FOR EACH ROW] 
    [WHEN 조건] 
    [DECLARE 변수 선언]
    BEGIN
    트리거 본문 코드
    END;

[OR REPLACE] : 해당 트리거 명으로 등록된 트리거가 있을 경우, 새로 생성하는 트리거로 대체
BEFORE or AFTER : 이벤트가 발생하기 전 후 지정
INSERT or UPDATE or DELETE : 이벤트 유형 지정
[FOR EACH ROW] : 각 행에 대해 트리거 적용 여부 지정
[WHEN 조건] : 트리거가 실행되는 조건 기입
[DECLARE 변수 선언] : 트리거 내에서 사용할 변수 선언

패키지

• 서로 관련 있는 PL/SQL 프로시저와 함수들의 집합
• 선언부와 본문으로 나누어짐
  • 선언부에 패키지에서 사용될 PL/SQL 프로시저나 함수, 커서, 변수, 예외절을 선언하면 패키지 전체에 적용됨
  • 바디에서 선언부에서 선언된 기능들의 실행을 정의 (프로시저, 함수의 로직을 정의)
    • 패키지 헤더 컴파일 후 컴파일 해야함
  • 패키지의 헤더(CREATE PACKAGE)와 바디(CREATE PACKAGE BODY)를 생성하면 이를 프로시저로 실행하거나 EXEC 절을 통해 패키지를 직접 실행하여 사용
    • EXEC 패키지명 / EXEC 패키지명.프로시저명
• MariaDB에서는 패키지를 공식적으로 지원하지 않으나 SQL 모드를 오라클로 설정하면 패키지 생성 가능

인덱스(Index)

• 검색 속도를 향상 시키기 위한 데이터 구조
• 테이블 내의 특정 열(칼럼)에 인덱스를 생성
• 인덱스를 사용하여 해당 주소값의 데이터를 빠르게 검색할 수 있도록 함
• 사용자의 필요에 의해 직접 생성 또는 데이터 무결성을 확인하기 위해 기본 키와 유일 키는 자동 생성됨
• 너무 많은 인덱스는 DB 성능에 좋지 않음

시퀀스(Sequence)

• 일련 번호를 생성하기 위한 객체
• 주로 기본 키 값으로 사용되며 자동으로 값을 생성하는 데 사용
• 테이블과 독립적으로 여러 테이블에서 공통으로 사용 가능

생성 방법

CREATE SEQUENCE 시퀀스 명 
START WITH 1 -- 시작 값 : 맨 처음 실행할 때 한 번만 적용(수정 불가) 
INCREMENT BY 1 -- 정수값만큼 증감 
MAXVALUE 9999 -- 최대값 
MINVALUE 1 -- 최소값 
NOCYCLE -- 반복 여부 
NOCACHE; -- 캐시 생성 여부

사용방법

-- 시퀀스값 넣기(NEXTVAL) 
INSERT INTO TEST(NO, NAME, NICKNAME) VALUES(시퀀스 명.NEXTVAL, '이름', '별명); 

-- 현재 시퀀스값 확인하기(CURRVAL) 
SELECT 시퀀스 명.CURRVAL FROM DUAL;

SQL문 작성 규칙(개발표준)

• SQL문은 모두 대문자료 표기
• SELECT 절의 모든 컬럼에 ALIAS 달기
• 절(SELECT, FROM, WHERE, ORDER BY 등)은 개행하여 작성
• 주석은 /* */ 의 형태로 기술(IBATIS에서는 -- 주석이 컴파일 되지 않음)
• FROM절에 여러 테이블을 병렬 나열할 경우 테이블 ALIAS를 순서대로 A, B, C, D.. 순으로 부여
• Subquery에서 테이블 명 사용 시 역순 Z, Y, X... 순으로 부여

효율적인 쿼리 작성 방식

• 실행계획 활용
• 인덱스 칼럼 타입 변형 X
• 조인 컬럼의 데이터 타입이 맞추기
• 긍정문 비교(부정형 비교는 인덱스를 이용하지 못함)
• LIKE 연산자 사용 시 '%' 뒤에만 사용(ORACLE 9i 이상은 상관없음)
• OR 대신 union all로 분리(OR 조건은 인덱스를 사용하지 않음 )
• SF(Strored Function)은 대상 건 별 한번씩 수행되어 많은 부하 발생

   SELECT 
       (SELECT SF_VSSL_NM(A.VSSL_KEY) FROM DUAL) 
   FROM CT_CRG_F 
   ...;

  • 위의 형식으로 스칼라 서브쿼리를 사용하여 SF를 호출하면 호출한 데이터를 저장해두고 동일한 데이터는 호출하지 않음(Oracle 10g 이상부터)
  • SCALAR SUBQUERY는 모든 조인이나 결과가 나온 이후 마지막으로 실행하여 사용을 최소화

WITH문 활용(Oracle에서만 사용 가능하므로 사용X)

• 반복적으로 사용되는 집합을 WITH절에서 선언하여 반복 ACCESS 제거

MERGE INTO(Oracle에서만 사용 가능하므로 사용X)

• 특정한 내역에 대하여 해당 조건으로 검색한 결과값이 존재하면 UPDATE 문 수행,
  존재하지 않으면 INSERT문을 수행

다중 UPDATE

• 여러 건을 동시에 UPDATE할 경우 사용
• 기존에는 PL/SQL을 이용하여 처리할 수도 있으나 쿼리로 간단하게 처리할 경우 사용

  UPDATE EMP A
  SET    (
              A.ENAME
            , A.DEPTNO
         )
         =
         (
              A.ENAME
            , '10'
         )
  WHERE  A.EMPNO, A.DEPTNO IN ( SELECT EMPNO, DEPTNO FROM EMP B WHERE B.EMPNO LIKE '77' || '%')
  ;

다중 INSERT

• 여러 건의 데이터를 한번에 INSERT할 경우 사용

  INSERT INTO EMP A
  (
   EMPNO
    ,  ENAME
    ,  DEPTNO
  )
  SELECT  EMPNO
        , ENAME
        , DEPTNO
  FROM    EMP1 B
  WHERE   EMPNO LIKE '7' || '%'
  ;