개발 공부

WITH절

SQL을 작성하다 보면 반복되는 서브 쿼리를 이곳저곳 복붙 해서 사용해야 하는 경우가 있다. 이곳저곳 흩어져 있기 때문에 가독성도 떨어지고, 만약 서브 쿼리를 수정해야 한다면 위치를 찾아 각각 다 고쳐줘야 한다.

WITH 절은 이러한 서브 쿼리의 단점을 해결하기 위해 서브 쿼리를 마치 함수처럼 이름을 가지도록 사용하는 방법이다.

약간 VIEW와 쓰임새가 비슷해보이지만 차이점은 WITH 절은 VIEW처럼 DBMS에 저장하여 사용하는 것이 아닌 실행할 쿼리문 내에 정의되어 있어, 해당 쿼리가 실행될 때마다 파싱 되고 실행계획을 세운다.

WITH절 기본

WITH절의 기본 구조 예제는 아래와 같다.

WITH
WITH_SUB AS (
	... -- 서브쿼리 작성
)
SELECT * FROM WITH_SUB; -- WITH절을 사용한 메인 쿼리 예시

WITH절은 여러 개 선언할 수 있으며, WITH절에서 다른 WITH 절을 사용할 수 있다.

WITH
WITH_SUB1 AS (
	... -- 서브쿼리 작성
),
WITH_SUB2 AS (
	SELECT * FROM WITH_SUB1 -- 다른 WITH절을 사용한 WITH절 
)
SELECT * FROM WITH_SUB2;

Recursive WITH절

WITH절을 가지고 자신이 자신을 호출하는 Recursive 한 방식을 사용할 수 있다. 이를 Recursive WITH절이라고 한다.

Recursive WITH 절을 사용하려면 UNION ALL을 가지고 초기값을 정해주는 초기 서브 쿼리와, 이후에 행위를 작성하는 Recurisive 서브 쿼리로 나뉜다. 단 항상 재귀함수에서 그랬듯이 무한루프에 빠지지 않도록 종료조건을 조건절로 표현을 해야한다.

아래는 1~9까지의 합을 구하는 예제이다.

WITH
SUM(NUM, RESULT) AS(
	-- 초기값을 설정해주는 초기 서브쿼리이다. 즉 NUM=1, RESULT=1 이다.
	SELECT 1,1 FROM DUAL 
	UNION ALL
	-- 이후 행위를 정의한 Recurisive 서브쿼리이다. NUM의 값은 1씩 증가시키며, RESULT 값에는 계속 (NUM + 1)을 더한다.
	SELECT NUM+1, (NUM+1) + RESULT FROM SUM WHERE NUM < 9-- 종료조건으로 NUM < 9를 작성했다.
)
SELECT NUM ,RESULT FROM SUM;

계층형 WITH절

오라클에서는 계층형 쿼리를 위해 CONNECT BY문을 지원한다. 하지만 이 문법은 오라클에서만 사용 가능하다. WITH절은 ANSI SQL에 포함되어 있기 때문에 여러 DBMS에서 지원한다. 따라서 WITH절은 DBMS가 변경되어도 쿼리를 변경할 필요가 없다는 장점이 있기때문에 계층형 쿼리를 작성할 때는 CONNECT BY보다는 WITH절을 사용하자.

아래 예제는 WITH절을 이용한 계층형 쿼리 예제이다.

WITH
WITH_TAB (LVL, NAME, EMP_ID, MGR_ID) AS
(
  -- 초기값을 설정해주는 초기 서브쿼리이다. 즉 LVL=1이고, MGR_ID가 NULL인 값이 최초 값이다.
  SELECT 1, NAME, EMP_ID, MGR_ID
  FROM HR.EMPLOYEES
  WHERE MGR_ID IS NULL -- 오라클의 START_WITH와 동일한 조건
  UNION ALL
  -- 이후 행위를 정의한 Recurisive 서브쿼리이다. 초기값의 EMP_ID의 값이 MGR_ID와 같은 행을 찾아 LVEL + 1을 해준다.
  SELECT B.LVL + 1 AS LVL, A.NAME, A.EMP_ID, A.MGR_ID
  FROM HR.EMPLOYEES A, WITH_TAB B
  WHERE A.MGR_ID = B.EMP_ID -- 오라클의 CONNECT BY PRIOR와 동일한 조건
)
SELECT LVL, NAME, EMP_ID, MGR_ID FROM WITH_TAB;

아래는 위와 같은 결과를 나타내는 오라클의 계층형 쿼리이다.

SELECT LEVEL, NAME, EMP_ID, MGR_ID
FROM HR.EMPLOYEES
START WITH MGR_ID IS NULL
CONNECT BY PRIOR EMP_ID = MGR_ID;

SQLD, SQLP 문제에서 자주 나오는 헷갈리는 문장 정리

CHECK

ALTER TABLE EMP ADD CONSTRAINT EMP_CK CHECK(LENGTH(NAME) > 2);

Oracle의 컬럼 제약조건인 CHECK는 조건을 만족하지 못하는 값이 들어올 경우 에러가 발생한다.

NULL은 CHECK 조건을 무시하여 항상 조건을 통과한다.

DML문

INSERT

• ORACLE은 VARCHAR에 ''을 입력하면 NULL로 입력된다.
• SQL Server는 VARCHAR에 ''을 입력하면 ''로 입력된다.

DELETE

• FROM 키워드는 생략 가능하다. ex) DELETE FROM 주문; = DELETE 주문

SELECT

• 두 개 이상의 테이블을 참조하는 FROM절에 테이블에 대한 ALIAS를 사용했을 때, 테이블들이 중복된 칼럼을 가지고 있을 경우 반드시 ALIAS명을 사용해야 함. 

로깅을 하고 싶지 않다면 NOLOGGING 옵션을 사용한다.

ALTER TABLE EMP NOLOGGING;

ESCAPE

LIKE 연산자에서 '%'와 '_'는 특수한 용도로 사용되는 문자인데, 만약 LIKE을 통해 검색하고자 하는 단어에 '%'와 '_'를 포함할 경우에는 'ESCAPE'를 사용하면 된다. 원하는 특수문자를 앞에 작성하고 '%'와 '_'를 사용하면 된다. 아래 예시는 '#'이라는 특수한 문자를 지정하여 '_'를 포함하는 NAME을 검색한다.

SELECT * FROM EMP WHERE NAME LIKE '%#_%' ESCAPE '#';

IN, NOT IN

IN 절에 NULL이 있을 경우, 무시함

NOT IN 절에 NULL이 있을 경우, 항상 true로 리턴하게 되어 NOT IN 결과가 항상 0건임.

INDEX

인덱스는 내림차순으로 생성 및 정렬된다.

인덱스 구조에 따른 특징

• 클러스터 인덱스
  • 인덱스의 리프 데이터가 곧 데이터 페이지다.
• 비트맵 인덱스
  • 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하는 구조이다.
  • 시스템에서 사용될 질의를 시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해 설계 되었다.
• B-tree 인덱스
  • 브랜치 블록과 리프 블록으로 구성되며 브랜치 블록은 분기를 목적으로 하고 리프 블록은 인덱스를 구성하는 칼럼의 값으로 정렬된다.

ORDER BY

ORDER BY COUNT(*)은 가능하다.

ORACLE 은 NULL 을 가장 큰 값으로 취급하여 ORDER BY 시 맨 뒤로 정렬되고 SQL SERVER 반대로 가장 앞으로 정렬한다.

NULL의 순서를 조절하고 싶으면 ORDEY BY 마지막에 NULLS FIRST 또는 NULLS LAST를 사용한다.

ORDER BY 1, COL1 과 같이 숫자와 컬럼을 혼용하여 사용할 수 있다

Random Access

Netsted Loop는 이중포문이기 때문에 n * m 번만큼 랜덤 엑세스가 가장 많이 발생한다.

SQL Server - IDENTITY

IDENTITY [ ( seed , increment ) ]

• SQL Server에서 사용할 수 있는 데이터타입으로 데이터를 식별할 수 있는 식별자 역할을 한다.
• seed : 첫번째 행이 입력될 때의 시작값
• increment: 증가되는 값
• 해당 컬럼에 값을 넣을 경우 Error 발생 (Error 발생을 막기 위해서는 IDENTITY_INSERT 를 OFF 로 하면 되나 이런 경우 IDENTITY 를 쓴 의미가 없어짐)

Sub Query (서브 쿼리)

서브쿼리는 SELECT, FROM, HAVING, ORDER BY절 등에서 사용 가능

서브쿼리 자체에는 ORDER BY절을 사용할 수 없음

연관 서브쿼리는 서브쿼리가 메인쿼리 컬럼을 포함하고 있는 형태의 서브 쿼리이다.

비연관 서브쿼리는 주로 메인쿼리에 값을 제공하기 위한 목적으로 사용한다.

인라인 뷰

• FROM 절에 사용한 서브 쿼리.
• Dynamic View(동적 뷰)라고도 함.

중첩된 서브쿼리

• WHERE 절에 사용한 서브 쿼리

스칼라 서브 쿼리

• 한 레코드당 정확히 하나의 값을 반환하는 서브 쿼리.
• JOIN으로 동일한 결과를 추출할 수도 있음.

Single Row(단일 행) 서브쿼리

• 실행 결과가 항상 1건 이하인 서브쿼리
• =, <, <= 등 단일행 비교 연산자와 함께 사용됨

Multi Row(다중 행) 서브쿼리

• 실행 결과가 여러 건인 서브쿼리
• IN, ALL 등 다중행 비교 연산자와 함께 사용됨

Multi Column(다중 컬럼) 서브쿼리

• 여러 컬럼을 반환하는 쿼리
• 메인 쿼리의 조건절에 여러 컬럼을 동시에 비교할 수 있음. 단 컬럼 개수와 컬럼의 위치가 동일해야 함
• Oracle에서만 지원하고, Server SQL에서는 지원하지 않음

Transaction

Dirty Read : 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것

Non-Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상이다. 트랜잭션 격리성이 낮은 경우 발생한다.

Phantom Read : 한 트랜잭션 내에서 같은 쿼리를 두번 수행했는데, 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상을 말한다. 트랜잭션 격리성이 낮은 경우 발생한다. 

저장점

• 저장점을 정의하면 롤백 할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 저장점까지 트랜잭션의 일부만 롤백할 수 있음
• 동일한 이름의 저장점이 중복된다면, 가장 마지막의 저장점이 적용됨.
• 아래는 SP2로 롤백하고 커밋하므로 SP2 이후에 쿼리문장은 적용되지 않는 예제임

  -- ORACLE 
  SAVEPOINT SP1;
  ...
  SAVEPOINT SP2;
  ...
  ROLLBACK TO SP2;
  COMMIT; 
  
  -- SQL Server
  SAVE TRANSACTION SP1;
  ...
  SAVE TRANSACTION SP2;
  ...
  ROLLBACK TRANSACTION SP1;
  COMMIt;

UNION ALL

중복 로우를 허용하는 합집합

칼럼 ALIAS는 첫번째 SQL 모듈 기준으로 표현 됨

정렬 기준은 마지막 SQL 모듈에 표시하면 됨

VIEW(뷰)

View는 데이터 딕셔너리에 SQL문 형태로 저장하되 실행 시에 참조된다.

참조한 테이블이 변경되면 View도 변경 된다.

View를 통해 입력, 수정, 삭제에는 제약이 있다.

한번 생성된 View는 Alter문을 통해 변경할 수 없고, 삭제 후 재 생성해야 한다.

하나의 테이블에 여러 개의 View를 생성 가능하다.

특정 컬럼만 조회할 수 있도록 할 수 있기에 보안 기능이 있다.

데이터 관리가 수월해지고, SELECT 문장이 간단하다.

View는 인덱스를 만들 수 없다.

계층형

ORACLE의 PRIOR은 CONNECT BY 뿐만 아니라, SELECT, WHERE절에서도 사용 가능함

CONNECT_BY_ISLEAF : leaf 데이터면 1, 아니면 0을 반환한다.

CONNECT_BY_ISCYCLE : 중복참조를 체크하는 함수로 CONNECT BY NOCYCLE일 경우에만 사용 가능하다. 서로가 서로를 부모로 가지는 경우를 찾을 때 사용하고, 중복참조 이면 1을 아니면 0을 반환한다. (루트까지의 경로에 존재하는 데이터를 의미한다라고도 사용함.)

CONNECT_BY_ROOT : 루트 노드의 정보

SYS_CONNECT_BY_PATH : 하위 레벨의 칼럼 표시

SQL Server는 계층형 질의문은 CTR(Common Table Expression)를 재귀 호출함으로써 계층 구조를 전개

비교연산자

CHAR형

• 길이가 다르다면 작은 쪽에 SPACE 를 추가하여 길이를 같게 한 후에 비교한다

VARCHAR형

• 서로 다른 문자가 나올 때까지 비교한다
• 길이가 다르다면 짧은 것이 끝날 때까지만 비교한 후에 길이가 긴 것이 크다고 판단한다
• 길이가 같고 다른 것이 없다면 같다고 판단한다

조인

NATURAL JOIN

• 동일한 타입과 이름을 가진 컬럼을 조인 조건으로 이용하는 조인으로 반드시 조인하는 대상의 테이블들은 서로 같은 이름과 타입의 컬럼이 존재해야한다.
• NATURAL JOIN에 사용된 컬럼은 식별자를 사용할 수 없다.

  -- EMP와 DEPT 테이블에 DEPOTNO 컬럼이 각각 존재하므로 DEPTNO로 NATURAL JOIN한다.
  -- 단, DEPNO를 가지고 식별자를 사용하였으므로 (EMP.DEPTNO) 아래 쿼리는 에러가 발생
  SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
  FROM EMP NATURAL JOIN DEPT;

USING JOIN

• 두 개의 테이블의 컬럼명이 같을 경우 조인 조건을 길게 적지 않고 간단하게 적을 수 있도록 하는 역할
• NATURAL JOIN과 동일하게 USING JOIN에 사용된 컬럼은 식별자를 사용할 수 없다.

  SELECT DEPT_NO
  FROM EMP INNER JOIN DEPT USING(DEPT_NO)

FULL OUTER JOIN

• = LEFT OUTER JOIN UNION RIGHT OUTER JOIN (UNION이므로 중복을 제거함)

오라클 (+) 부호

• 해당 테이블에 null을 허용한다는 의미이다.
• 등호의 오른쪽 테이블에 (+)가 붙으면 오른쪽 테이블에 null을 허용하는 것이므로 LEFT OUTER JOIN
• 등호의 왼쪽 테이블에 (+)가 붙으면 왼쪽 테이블에 null을 허용하는 것이므로 RIGHT OUTER JOIN이다.

참조문

DELETE(MODIFY) (액션들) : 참조무결성에서 부모가 삭제될 경우, 행할 액션을 지정

• CASCADE : 부모 삭제시, 자식도 삭제
• SET NULL : 부모 삭제시, 해당 필드 NULL로 설정
• SET DEFAULT : 부모 삭제시, 해당 필드의 기본값 설정
• RESTRICT : 자식 테이블에 PK 값이 없는 경우만 부모의 값을 삭제 가능
• NO ACTION : 참조 무결성을 해치는 삭제가 불가능. 즉 자식에 해당하는 값이 있을 경우, 부모 삭제 불가능

INSERT (액션들) : 참조무결성에서 자식이 삽입될 경우, 행할 액션을 지정

• AUTOMATIC : 자식을 삽입했는데 부모의 값이 없을 경우, 부모값을 생성하고, 자식값을 삽입
• SET NULL : 자식을 삽입했는데 부모의 값이 없을 경우, 해당 필드를 NULL로 설정
• SET DEFAULT : 자식을 삽입했는데 부모의 값이 없을 경우, 해당 필드를 기본값 설정
• DEPENDENT : 자식을 삽입했는데 부모의 값이 없을 경우에는 삽입 불가능. 즉, 부모에 값이 없으면 자식은 삽입 불가능
• NO ACTION : 참조 무결성을 해치는 입력이 불가능. 

ETC

Oracle의 경우 Select시 조회되는 Header Column명은 디폴트로 대문자로 표현한다.  

절차형 SQL

절차형 프로그래밍 언어인 C언어처럼 SQL에도 절차 지향적인 프로그래밍이 가능하도록 벤더별로 프로그래밍 언어와 같은 절차형 SQL을 제공한다.

오라클에서는 PL(Procedural Language)/SQL을 제공하며 SQL Server에서는 T-SQL을 제공한다.

본 포스팅은 오라클의 PL/SQL을 아주 간단하게만 설명한다.

PL/SQL 구조

PL/SQL은 기본적으로 BLOCK 구조로 되어있다. BLOCK 안에는 기본적으로 DECLARE, BEGIN, EXCEPTION으로 구성되어 있으며 마지막에는 END로 BLOCK의 끝을 표현한다.

DECLARE : 선언부라고 하며 BEGINE ~ END에서 사용할 변수(상수)에 대하여 정의하는 부분이다. 정의할 게 없다면 생략 가능하다.

• 변수란 데이터의 임시 저장영역으로 반복해서 재사용하기 위해 사용한다.
• 변수를 사용할 수 있는 범위는 해당 블록 시작(BEGIN)에서 블록의 끝(END)에서만 사용가능하다.
• 변수의 명칭은 반드시 문자로 시작해야 하며, 문자나 숫자, 특수문자를 포함할 수 있다. 단 변수명은 30 bytes 이하로 명명해야 한다.
• 특정 값으로 미리 초기화 가능하다. 
• 데이터를 할당하려면 ':='을 사용한다.
• 상수로 사용하려면 변수명 다음에 CONSTANT 키워드를 사용하면 된다.
• 변수의 데이터 타입은 직접 지정할 수 있지만, 다른 테이블에서 참조해서 지정할 수 있다.
• 아래는 변수 선언의 예시이다.

  DECLARE
  	V_PI CONSTANT NUMBER := 3.14;			-- 상수로 선언하였으며 변경 불가능하다.
  	v_name VARCHAR2(100) := '홍길동';			-- 변수로 선언하였으며 디폴트 값으로 미리 '홍길동'을 할당
  	v_addr VARCHAR2(100); 				-- 변수로 선언하였으며 디폴트 값을 선언하지 않았다.
  	v_you VHARCHAR2(100) := '&your_name'; 		-- 해당 블록을 실행할때마다 사용자한테 입력받는다.​
  	v_deptId DEPT.DEPT_ID%TYPE			-- DEPT 테이블의 DEPT_ID 컬럼의 데이터타입을 참조한다.
  	v_temp	DEPT%ROWTYPE		-- DEPT 테이블의 선언된 컬럼의 모든 데이터타입 아무거나 들어갈 수 있다.

BEGIN : 실행 부라고 하며 처리하고자 하는 SQL문과 필요한 로직을 기술하는 부분이다.

EXCEPTION : 예외처리 부라고 하며 실행 도중에 에러가 발생할 경우에 에러를 처리하는 로직을 기술하는 부분이다. 생략 가능하다.

EXCEPTION
WHEN 예외명1 THEN
	예외처리문장;
WHEN 예외명2 OR 예외명3 THEN
	예외처리문장;
WHEN OTHERS THEN
	나머지_예외처리문장;

PL/SQL 특징

DECLARE, BEGIN, EXCEPTION, END와 같은 예약어는 마지막에 세미콜론(;)을 붙이지 않는다. 이외의 명령어는 세미콜론(;)을 붙인다.

BLOCK안에 새로운 BLOCK을 포함할 수 있다. 이를 Nested Block이라고 표현한다.

IF, LOOP 등의 조건을 사용하여 절차적인 프로그래밍이 가능하다.

여러 SQL 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.

PL/SQL로 작성된 코드는 PL/SQL 엔진이 처리하고, 일반적인 SQL 문장은 SQL 실행기가 처리한다.

PL/SQL에서는 DML 문장은 직접적으로 쿼리를 작성하여 사용할 수 있다. 하지만 DDL, DCL, TCL은 직접적으로 지원하지 않는다. 동적 SQL(런타임에 문자열로 작성되는 SQL)을 사용하면 가능하긴 하다. 동적 SQL은 execute immediate를 이용한다.

BEGIN
	execute immediate 'TRUNCATE TABLE 사원';
END

PL/SQL을 저장해서 쉽게 사용할 수 있다. 저장하여 사용할 경우 Stored PL/SQL Block이라고 한다. 저장하지 않고 사용할 경우에는 Anonymous PL/SQL Block이라고 한다.

PL/SQL SELECT

• PL/SQL의 SELECT는 SELECT 한 데이터를 INTO 뒤에 변수에 값을 저장한다.
• SELECT 컬럼 항목의 수와 INTO 변수 항목의 수는 동일해야 한다.
• 반드시 로우의 결과는 1건만 나타나야 한다.
• 아래 예시는 사원 ID가 '1'인 사원의 사원명이 v_name에 저장된다.

  DECLARE
  	v_name VARCHAR2(10);
  
  BEGIN
  	SELECT 사원명 INTO v_name FROM 사원 WHERE 사원ID = '1'; 
  
  END​

PL/SQL DML (INSERT, UPDATE, DELETE)

• 기본적인 DML문을 BEGIN 문장에 사용하면 된다.
• 사용자로부터 입력을 받고자 한다면 선언부에 &을 사용하여 입력받는다.
• 아래 예시는 사원 테이블에 INSERT를 하는 예시이다.

  DECLARE
  	v_id	VARCHAR2(4) := '&id';
  	v_name	VARCHAR2(10) := '&name';
      
  BEGIN
  	INSERT INTO 사원 VALUES(v_id, v_name);
  
  END

PL/SQL DML 제어문

IF 조건문

IF (조건) THEN
	실행문장;
ELSE
	실행문장;
END IF;


IF (조건) THEN
	실행문장;
ELSEIF (조건) THEN
	실행문장;
END IF;​

CASE 조건문

CASE 변수
	WHEN 조건1 THEN 결과1
	WHEN 조건1 THEN 결과1
	...
	WHEN 조건N THEN 결과N
ELSE 디폴트결과
END;

LOOP 반복문

LOOP
	문장;
    EXIT 조건;
ENd LOOP;

WHILE 반복문

WHILE 조건 LOOP
	문장;
END LOOP;

FOR 반복문

FOR 변수 IN 시작숫자..끝숫자 LOOP
	문장;
END LOOP;

Stored PL/SQL Block

PL/SQL을 마치 함수(프로그램)처럼 정의해서 사용할 수 있다. 이를 Stored PL/SQL Block이라고 한다. 저장하지 않고 이름 없이 사용하는 것은 Anonymous PL/SQL Block이라고 한다. 아래는 두 개의 차이점을 정리한 내용이다.

Stored PL/SQL Block의 종류는 Procedure, Function, Package, Trigger가 있다.

Procedure(프로시저)

• 가장 기본적인 유형으로 한 번 컴파일된 후 데이터베이스에 저장되어 사용되고, 다른 프로그램에서 호출 가능하다.
• 아래는 데이터베이스에 Procedure를 생성하는 방법이다.

  CREATE PROCEDURE p_test -- 프로시저 명칭을 입력한다.
  (
  	파라미터1 IN 데이터타입,
  	리턴값1 OUT 데이터타입,
  	둘다가능 IN OUT 데이터타입
  )
  IS
  	변수1 데이터타입;
  
  PL/SQL Block​

• 파라미터1은 호출하는 곳에서 전달해줘야 하는 파라미터를 의미하므로 IN을 사용한다. 생략 가능하다.
• 리턴값1은 호출하는 곳에서 리턴 값을 받을 리턴값을 의미하므로 OUT을 사용한다.
• 둘 다 가능은 파라미터와 리턴 값이 된다는 의미로 IN OUT을 사용한다.
• Procedure을 호출하는 방법은 아래와 같다.
  

  EXEC p_test() -- 인자가 없는 Procedure일 경우
  
  EXEC p_test(파라미터1, 리턴값1, 둘다가능) -- 인자가 있는 Procedure일 경우

Function(함수)

• Function은 반드시 리턴 값이 존재하는 Block이다.
• 아래는 데이터베이스에 Function을 생성하는 방법이다.

  CREATE FUNCTION f_test -- 함수 명칭을 입력한다.
  (
  	파라미터1 IN 데이터타입,
  	리턴값1 OUT 데이터타입,
  	둘다가능 IN OUT 데이터타입
  )
  RETURN 데이터타입
  IS
  	변수1 데이터타입;
  
  PL/SQL Block​

• 위에서 설명한 Procedure와 거의 동일하다. 차이점은 반드시 리턴 값이 존재하므로 RETURN을 사용한다. 그리고 리턴 값은 Blobk문에서 RETURN을 사용하여 리턴한다.
• Function은 일반적으로 알고 있는 오라클 함수처럼 사용할 수 있다.
• Procedure와 Function은 자율적으로 트랜잭션을 처리할 수 있다.

Package(패키지)

• Package는 연광성이 있는 변수, 상수, PL/SQL Block 등을 논리적으로 하나의 그룹으로 만드는 특수한 형태이다. Package를 만들고 그 안에 여러 가지 항목을 작성하여 사용하면 된다.
• Package는 인터페이스와 같이 사용방법을 기술한 선언부(스펙)와 실제 구현이 담겨 있는 바디부로 나뉜다.
• 아래는 데이터베이스에 Package를 생성하는 방법이다.
  

  -- 선언부
  CREATE PACKAGE pack_test
  IS
    상수명 CONSTANT 데이터타입;
    예외명 EXCEPTION;
    변수명 데이터타입;
  
    PROCEDURE 프로시저명칭 (변수타입들..);
    FUNCTION 함수명칭 (변수타입들..) RETURN 반환타입;
  END pack_test;
  
  
  -- 바디부
  CREATE PACKAGE BODY pack_test
  IS
    상수명 CONSTANT 데이터타입;
    예외명 EXCEPTION;
    변수명 데이터타입;
  
    PROCEDURE 프로시저명칭 -- 프로시저 생성구문
        ...
    END 프로시저명칭;
  
    FUNCTION 함수명칭 -- 함수 생성구문
        ...
    END 함수명칭;
  END pack_test;

   
• 호출하는 방법은 패키지명. 명칭처럼 사용하면 된다. ex) pack_test.getList;

Trigger(트리거)

• Trigger는 특정 이벤트가 발생할 때 자동으로 수행되는 Block을 의미한다. 따라서 트리거는 직접적으로 실행할 수는 없다.
• 자동으로 수행하게 하여 데이터의 무결성과 일관성을 유지하도록 한다.
• 트리거는 TCL을 이용하여 트랜잭션을 제어할 수 없다.
• 특정 이벤트가 발생하기 전(BEFORE EVENT), 특정 이벤트가 발생 후(AFTER EVENT) 등을 설정할 수 있다.
• 이벤트에는 INSERT, UPDATE, DELETE, DDL, DROP, TRUNCATE 등을 사용할 수 있다.
• DELETE ON TRIGGER 의 경우,  :OLD 는 삭제 전 데이터를 , :NEW 는 삭제 후 데이터를 나타낸다.
• UPDATE TRIGGER 에서, :OLD 에는 수정 전, :NEW 에는 수정 후 값이 들어간다.
• 아래는 Trigger를 생성하는 방법이다.

  CREATE [OR REPLACE] TRIGGER trigger_name
  BEFORE | AFTER
       trigger_event ON table_name
       [FOR EACH ROW]
       [WHEN (condition)]
       PL/SQL block
       
       
  CREATE TRIGGER 인서트제약트리거
  BEFORE UPDATE ON 사원연봉
  BEGIN
  	IF (:NEW.입력자) NOT IN ('대표') THEN
  		RAISE_APPLICATION_ERROR(-9999,'대표만 수정할 수 있습니다.');
  	END IF;
  END

CURSOR(커서)

커서를 사용하면 처리된 SQL문장의 결과 집합에 접근할 수 있다. 

묵시적 커서는 바로 이전에 실행된 SQL문에 대하여 커서를 가지게 되어 특별한 키워드를 이용해 결과 집합에 대한 정보를 얻을 수 있다.

SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT

명시적 커서는 직접 작성한 SQL 문장을 커서로 만들어 사용하는 방법이다. 순서는 다음과 같다.

1. 커서 선언 : 사용할 커서에 이름을 부여하고 SQL문을 작성한다.

   CURSOR 커서명[(매개변수들)]
   IS
   SQL문장;

2. 커서 열기 : 선언한 커서를 OPEN한다. 매개변수가 존재할 경우 매개변수를 함께 전달한다.

   OPEN 커서명[(매개변수들)];​

3. 커서 패치 : 해당 SQL문을 실행하여 원하는 작업을 한다.

   -- 일반적으로 패치한 데이터를 모두 접근하기 위해 루프를 사용한다.
   LOOP
   FETCH 커서명 INTO 변수들; -- 수행한 SQL문에 데이터를 변수에 저장한다.
   EXIT WHEN 커서명%NOTFOUND; -- 더 이상 데이터가 없을 경우 루프를 종료한다.
   END LOOP;​

4. 커서 닫기 : 커서 사용을 완료하면 닫아준다.

   CLOSE 커서명;​

윈도우 함수

SELECT WINDOW_FUNCTION([컬럼]) OVER 
( [PARTITION BY 컬럼] [ORDER BY 컬럼] [WINDOWING 절] )
FROM 테이블명 ;

윈도우 함수는 로우 간의 관계를 구할 수 있도록 만들어 놓은 함수이다. 로우 간의 순위, 집계, 순서, 비율 등을 구할 수 있는 함수를 제공한다.  인자가 있는 함수도 있고 없는 함수도 있다

ORDER BY 절을 사용하여 어떤 컬럼 기준으로 순위를 정할지 지정할 수 있다. 순위 함수가 아닐 경우에는 정렬로 사용한다.  아래는 ORDER BY를 사용하여 모든 로우에 대해서 매출액 기준으로 순위를 구하는 예시이다.

SELECT 부서번호, 사원번호, 사원명, 매출액
	,RANK() OVER (ORDER BY 매출액 DESC) AS 순위
FROM 사원
ORDER BY 순위;

모든 로우간의 관계를 구할 수도 있으며, PARTITION BY 절을 사용하여 특정 그룹 조건을 기준으로도 관계를 구할 수 있다. 아래는 PARTITION BY를 사용하여 같은 부서 안에서 매출액 기준으로 순위를 구하는 예시이다.

SELECT 부서번호, 사원번호, 사원명, 매출액
	,RANK() OVER (PARTITION BY 부서번호 ORDER BY 매출액 DESC) AS 순위
FROM 사원
ORDER BY 부서번호, 순위;

WINDOWING 절을 사용하여 윈도우 함수에 포함시킬 로우의 조건(범위)을 지정할 수 있다. 크게 ROWS와 RANGE를 사용할 수 있다.

ROWS : 현재 ROW 위치에서 물리적인 범위

RANGE : 현재 ROW 값을 기준으로 논리적인 범위

예를 들어 설명하기 위해 먼저 UNBOUNDED PRECEDING이라는 것을 가지고 ROWS와 RANGE를 비교하여 설명한다.

• ROWS UNBOUNDED PRECEDING : ROWS UNBOUNDED PRECEDING은 현재 행과 현재 행 위에 있는 모든 행들을 함수에 사용하라는 의미이다. 아래 예시는 부서 번호 기준으로 행이 출력되며 아래로 내려가면서 매출액을 계속 SUM 하여 매출액 합계를 구하는 쿼리이다. 즉, 첫 번째 행은 자신의 매출액만 표현되고, 맨 아래 행은 모든 매출액의 합이 출력된다.

  SELECT 부서번호, 사원명, 매출액
  	,SUM(매출액) OVER (ORDER BY 부서번호 ROWS UNBOUNDED PRECEDING) AS 매출액합계
  FROM 사원;

  
  

  부서번호	사원명	매출액	매출액 합계
  1	A	500	500
  1	B	1000	1500
  2	C	700	2200
  3	D	300	2500
  3	E	600	3100
  3	F	900	4000

• RANGE UNBOUNDED PRECEDING : RANGE UNBOUNDED PRECEDING은 현재 행의 값을 기준으로 이하의 값을 함수에 사용하라는 의미이다. 아래 예시는 부서 그룹내에서 자신의 매출액보다 작은 매출액을 모두 SUM하여 매출액 합계를 구하는 쿼리이다. 즉, 자신의 값 + 자신보다 작은 모든값의 합이 출력된다.

  SELECT 부서번호, 사원명, 매출액
  	,SUM(매출액) OVER (PARTITION BY 부서번호 ORDER BY 매출액 RANGE UNBOUNDED PRECEDING) AS 매출액합계
  FROM 사원;

  부서 번호	사원명	매출액	매출액 합계
  1	A	500	500
  1	B	1000	1500
  2	C	700	700
  3	D	300	600
  3	E	300	600
  3	F	900	900

UNBOUNDED PRECEDING 이외에 WINDOWING 절은 아래와 같이 다양하게 존재한다.

• ROWS 숫자(N) PRECEDING : 현재 행과 바로 위의 행 N 개의 값을 함수에 사용하라는 의미이다. 아래 예시는 부서 번호 기준으로 행이 출력되며 첫 번째 행은 자신의 매출액만 표현되고, 두 번째 행부터는 자신 행의 바로 위의 행의 매출액과 자신의 매출액을 더한 매출액이 표현된다.

  SELECT 부서번호, 사원명, 매출액
  	,SUM(매출액) OVER (ORDER BY 부서번호 ROWS 1 PRECEDING) AS 매출액합계
  FROM 사원;

  부서 번호	사원명	매출액	매출액 합계
  1	A	500	500 (0 + 500)
  1	B	1000	1500 (500 + 100)
  2	C	700	1700 (1000 + 700)
  3	D	300	1000 (700 + 300)
  3	E	600	900 (300 + 600)
  3	F	900	1500 (600 + 900)

위에서 설명한 WINDOWING 절은 사실 BETWEEN이라는 구문이 생략되었다. 위에서 설명한 절을 BETWEEN으로 표현하면 다음과 같다.

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
• ROWS BETWEEN 숫자(N) PRECEDING AND CURRENT ROW

BETWEEN은 윈도우의 시작과 끝 위치를 지정하는 것으로 생략 가능하다. 생략한다면 위와 같이 CURRENT ROW, 즉 현재 행을 끝 위치로 지정하는 것이다. 따라서 위에 결과를 보면 항상 현재 행에서 멈춘 것을 확인할 수 있다.

현재 행이 아닌 맨 마지막 행을 끝 위치로 표현하고 싶다면 AND 뒤에 UNBOUNDED FOLLOWING을 사용하면 된다.

자신보다 위에 있는 N개만큼을 함수에 포함하는 숫자(N) PRECEDING 절처럼 FOLLOWING도 숫자(N) FOLLOWING 과 같이 사용하여 현재 행을 기준으로 끝 위치를 N개만큼 지정할 수 있다.

윈도우 순위 함수

RANK

SELECT 사원번호, 사원명, 매출액
	,RANK() OVER (ORDER BY 매출액 DESC) AS 순위
FROM 사원
ORDER BY 순위

• ORDER BY에 기술한 컬럼 기준으로 순위를 구하는 함수이다.
• 동일한 값에 대해서는 동일한 순위를 부여하고, 다음 순위는 비워둔다.

DENSE_RANK

SELECT 사원번호, 사원명, 매출액
	,DENSE_RANK() OVER (ORDER BY 매출액 DESC) AS 순위
FROM 사원
ORDER BY 순위

• ORDER BY에 기술한 컬럼 기준으로 순위를 구하는 함수이다.
• 동일한 값에 대해서는 동일한 순위를 부여하고, 다음 순위는 RANK 함수와 다르게 비워두지 않는다.

ROW_NUMBER

SELECT 사원번호, 사원명, 매출액
	,ROW_NUMBER() OVER (ORDER BY 매출액 DESC) AS 순위
FROM 사원
ORDER BY 순위

• ORDER BY에 기술한 컬럼 기준으로 순위를 구하는 함수이다.
• 동일한 값이라도 고유한 순위를 부여한다. 오라클의 경우 ROWID가 작은 행이 우선순위가 높다.
• 만약 동일한 값에 대해서 순서를 관리하고 싶다면 ORDER BY절에 다른 조건의 컬럼을 추가해야 한다.

윈도우 집계 함수

윈도우 집계 함수에는 SUM, MAX, MIN, AVG, COUNT가 있으며 특별하게 주의해야 할 점이 없으므로 설명은 생략한다.

윈도우 순서 함수

윈도우 순서 함수에는 FIRST_VALUE, LAST_VALUE, LAG, LEAD가 있다. FIRST_VALUE와 LAST_VALUE는 각각 집계 함수의 MAX와 MIN 함수와 동일한 결과를 출력한다.

LAG를 이용해 파티션 별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다. 반대로 LEAD는 이후 몇 번째 행의 값을 가져올 수 있다.

LAG

SELECT 사원번호, 사원명, 매출액
	,LAG(매출액, 1, 9999) OVER (ORDER BY 매출액 DESC) AS 내_바로_위_매출액
FROM 사원;

• LAG의 두 번째 인자는 이전 몇 번째 행의 값을 가져올지를 정하는 인수이며, 세 번째 인자는 이전 몇 번째 행의 값을 가져올 수 없거나 NULL일 경우에 세팅할 값을 설정한다.

LEAD

• LAG와는 반대로 이후 몇 번째 행의 값을 가져올 수 있다. 함수 사용 방법은 LAG와 동일하다.

윈도우 비율 함수

CUME_DIST

SELECT 사원번호, 사원명, 매출액
	,CUME_DIST() OVER (ORDER BY 매출액 DESC) AS 누적분포율
FROM 사원;

• CUME_DIST는 누적 분포를 계산하는 것이다.
• 즉 윈도우 중에 자신의 위치가 어느 정도인지 0~1 사이로 표현한다.
• NULL 값도 계산에 포함한다. 그리고 가능한 가장 낮은 값으로 NULL을 취급한다.

PERCENT_RANK

SELECT 사원번호, 사원명, 매출액
	,PERCENT_RANK() OVER (ORDER BY 매출액 DESC) AS 백분율순위
FROM 사원;

• 백분율의 순위를 계산한다.

NTILE

SELECT 사원번호, 사원명, 매출액
	,NTILE(2) OVER (ORDER BY 매출액 DESC) AS 타일번호
FROM 사원;

• 값을 N개의 타일(등분)하여 그룹핑 번호를 반환한다.

AWS 문서 참고 : https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/c_Window_functions.html

단일행 함수

• 한 번에 한 로우씩 처리하여  한 로우에 결과를 변경하는 함수이다.
• 단일행 함수는 SELECT, WHERE, ORDER BY, UPDATE의 SET 절에서 사용 가능하다.
• 단일행 함수는 1:M 조인이어도 M쪽에서 출력된 행이 하나씩 단일행 함수의 입력값으로 사용되므로 사용가 가능하다.
• 단일행 함수는 입력되는 값에 따라 문자 함수, 숫자 함수, 날짜 함수, 변환 함수, 일반 함수로 구분된다.

문자 함수

UPPER(컬럼)

• 모든 문자를 대문자로 변경(UPPER)

LOWER(컬럼)

• 모든 문자를 소문자로 변경(LOWER)

INITCAP(컬럼)

• 첫(INIT) 글자는 대문자(CAP), 나머지는 소문자로 변경

LPAD(컬럼, 자릿수, '빈자리를 채울 문자')

• 두 번째 인자인 자리 수만큼 문자열의 부족한 자리를 왼쪽(L)에서부터 세 번째 인자로 채운다(PAD).

RPAD(컬럼, 자릿수, '빈자리를 채울 문자')

• 두 번째 인자인 자리 수만큼 문자열의 부족한 자리를 오른쪽(R)에서부터 세 번째 인자로 채운다(PAD).

LTRIM(컬럼, '제거할 문자열')

• 문자열의 왼쪽(L)에 두 번째 인자인 제거할 문자열이 존재하면 제거(TRIM)한다. '제거할 문자'는 옵션으로 디폴트 값은 공백이다.
• '제거할 문자'를 동일한 값으로 여러 개 입력하여도 한 문자로 판단한다.
• 예를 들어 LTRIM(컬럼, '0') 이나 LTRIM(컬럼 '0000000')이나 동일하다.

RTRIM(컬럼, '제거할 문자열')

• 문자열의 오른쪽(R)에 두 번째 인자인 제거할 문자열이 존재하면 제거(TRIM)한다. 나머지 특징은 LTRIM과 동일하다.

TRIM( [BOTH | LEADING | TRAILING] '제거할 문자' FROM '문자열')

• 문자열의 양쪽(BOTH), 앞쪽(LEADING) 또는 뒤쪽(TRAILING)에 제거할 문자가 존재하면 제거(TRIM)한다.
• 문자열만 입력한다면 양쪽의 공백을 제거한다.
• BOTH, LEADING, TRAILING 셋 중 아무것도 입력하지 않는다면, 디폴트 값은 BOTH이다.
• '제거할 문자'는 '문자열'이 아니고 '문자'이다. 즉 char 하나만 입력받는다. 문자열을 입력하게 되면 에러가 발생한다.

SUBSTR(컬럼, 시작 위치, 길이)

• 문자열에서 두 번째 인자인 시작 위치에서 세 번째 인자인 길이만큼 문자열을 잘라낼 때(SUBSTR) 사용하는 함수이다.
• 시작 위치의 인덱스는 1부터 시작한다.
• 시작 위치는 음수도 될 수 있다. 시작 위치가 -1이라면 맨 끝 문자가 시작 위치이다. 그리고 스캔하는 방향을 항상 오른쪽으로 한다. 아래 설명할 INSTR 함수랑 다르다.

INSTR(컬럼, 찾는 글자, 시작 위치, 횟수)

• 문자열에서 특정 문자의 위치를 찾을 때(INSTR) 사용하는 함수이다. 찾을 때의 시작 위치를 입력받으며, 몇 번째로 해당 글자를 만났을 때 위치를 반환할지도 입력받는다.
• 시작 위치와 횟수는 입력을 받지 않을 경우, 각각 디폴트 값은 1, 1이다.
• 찾는 문자가 없으면 0을 반환한다.
• 시작 위치는 음수도 될 수 있다. 시작 위치가 -1이라면 맨 끝 문자가 시작 위치이다. 그리고 스캔하는 방향은 항상 왼쪽이다. 위에 설명한 SUBSTR 함수랑 다르다.

REPLACE(컬럼, '찾을 문자' '변환 문자')

• 문자열에서 특정 문자를 찾아 변환한다(REPLACE).

숫자 함수

ABS(숫자)

• 절댓값을 구한다.

MOD(숫자, 나눌숫자)

• 나머지를 구한다. 숫자 % 나눌숫자

SIGN(숫자)

• 양수이면 1, 음수이면 -1, 0이면 0을 반환하는 함수이다.

ROUND(숫자, 자릿수)

• 반올림한다.
• 자릿수는 음수도 입력할 수 있다. 음수를 입력하면 소수점이 아닌 정수형 자릿수를 나타낸다.

TRUNC(숫자, 자릿수)

• 내림한다.
• 자릿수는 음수도 입력할 수 있다. 음수를 입력하면 소수점이 아닌 정수형 자릿수를 나타낸다.

CEIL(숫자)

• 주어진 숫자와 가장 가까우면서 큰 정수를 구한다.
• 소수점을 다 지우고 +1 하면 된다.

FLOOR(숫자)

• 주어진 숫자와 가장 가까우면서 작은 정수를 구한다.
• 소수점을 다 지우면 된다.

POWER(숫자, 승)

• 숫자^승 값을 반환한다.

EXP(숫자)

• e^숫자 값을 반환한다.

LOG(밑, 진수)

• 로그를 구한다.

LN(진수)

• 밑이 e인 로그를 구한다.

SIN(라디안), COS(라디안), TAN라디안)

• 삼각함수를 구한다.

CHR(숫자)

• 숫자와 대응되는 아스키코드의 문자를 반환한다.

날짜 함수

SYSDATE

• 현재의 날짜와 시간을 출력하는 함수이다.

EXTRACT( [YEAR | MONTH | DAY | HOUR | MINUTE | SECOND] FROM 날짜 )

• 입력된 날짜에서 추출하고자 하는 정보를 반환하는 함수
• 변환 함수에서 설명할 TO_CHAR와 비슷하다.

ADD_MONTHS(날짜, 더할 숫자)

• 입력된 날짜에 숫자만큼의 달을 더하는 함수이다.

MONTHS_BETWEEN(날짜1, 날짜2)

• 두 날짜 사이의 개월 수를 출력하는 함수이다.
• 날짜 1이 더 커야지 결과 값은 양수로 표현된다.

변환 함수

TO_CHAR(숫자 또는 날짜, 형식)

• 숫자 또는 날짜의 값을 원하는 형식의 문자열로 변환하는 함수이다.
• 9는 자리를 차지한다는 의미이다.

  TO_CHAR(1234,'99999') -- ' 1234'
  TO_CHAR(1234,'9999.99') -- '1234.00'​

• 0은 해당 자리가 비어있다면 0으로 표현하라는 의미이다.

• TO_CHAR(1234,'09999') -- '01234'​​

• 날짜 함수인 EXTRACT(YEAR FROM SYSDATE)는 아래와 동일하다.

  TO_CHAR(SYSDATE, YYYY)​

TO_NUMBER(문자)

• 문자를 숫자로 변환하는 함수이다.
• 숫자로 변환할 수 없을 경우 에러가 발생한다.

TO_DATE(문자, 날짜 형식)

• 문자를 날짜로 변환하는 함수이다.
• 날짜 형식을 입력하지 않으면 입력받은 날짜 문자 형식대로 변환된다.

CAST(변환 대상 AS 데이터 타입)

• 변환 대상을 특정한 데이터 타입으로 변환하는 함수이다.

일반 함수

NVL(컬럼, 변환 값)

• 첫 번째 인자의 값이 NULL일 경우, 두 번째 값으로 표현한다.

NULLIF(표현식1, 표현식2)

• 첫 번째 인자와 두 번째 인자의 값이 같다면 NULL을 반환한다. 만약 다르다면 첫 번째 인자를 반환한다.

COALESCE(표현식1, 표현식2, ... , 표현식N)

• 입력받은 인자를 앞에서부터 확인하면서 NULL이 아니라면 해당 인자를 반환한다. 만약 모두 NULL이라면 NULL을 반환한다.

DECODE(표현식, 비교대상1, 반환값1, 비교대상2, 반환값2, ... , 비교대상N, 반환값N, 디폴트 값)

• 첫 번째 인자를 앞에서부터 비교대상과 비교하고 같다면 해당 반환 값을 반환한다. 만약 모든 비교대상과 다르다면 맨 마지막 인자인 디폴트 값을 반환한다.
• 디폴트 값은 입력하지 않으면 NULL이다.

CASE 표현식

      WHEN 비교대상(조건문)1 THEN 반환값1

      WHEN 비교대상(조건문)2 THEN 반환값2

                    ....

      WHEN 비교대상(조건문)N THEN 반환값N

      ELSE 디폴트값

END

• 표현식을 앞에서부터 비교대상과 비교하고 같다면 해당 반환 값을 반환한다. 만약 모든 비교대상과 다르다면 맨 마지막 인자인 디폴트 값을 반환한다.
• 비교 값만 입력할 경우 DECODE와 동일하게 동작한다.
• BETWEEN 10 AND 20과 같이 조건문을 입력할 수도 있다.

DDL

-- 테이블 생성 1 제약조건까지 한번에
CREATE TABLE EMP (
	NO VARCHAR2(100) NOT NULL
	,NAME VARCHAR2(100) DEFAULT '홍길동' NOT NULL
	,ADDR VARCHAR2(100) NULL
	,CONSTRAINT EMP_PK PRIMARY KEY (NO)
);

-- 테이블 생성 2 제약조건은 ALTER문으로 
CREATE TABLE EMP (
	NO VARCHAR2(100) NOT NULL
	,NAME VARCHAR2(100) DEFAULT '홍길동' NOT NULL
	,ADDR VARCHAR2(100) NULL
);
ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY (NO);

-- 테이블 생성 3 PK를 칼럼 선언할때 한번에
CREATE TABLE EMP (
	NO VARCHAR2(100) PRIMARY KEY
	,NAME VARCHAR2(100) DEFAULT '홍길동' NOT NULL
	,ADDR VARCHAR2(100) NULL)
);

-- 다른 테이블에서 데이터까지 복사해서 만들기
CREATE TABLE EMP AS SELECT * FROM EMP2; -- 모든 컬럼
CREATE TABLE EMP AS SELECT NAME FROM EMP2; -- 특정 컬럼
CREATE TABLE EMP AS SELECT * FROM EMP2 WHERE 1=2; -- 데이터를 제외한 모든 컬럼

-- 참조관계 정의
-- 1. 부모
CREATE TABLE 부서 (부서번호 CHAR(10), 부서명 CHAR(10), PRIMARY KEY(부서번호));
-- 2. 자식 (CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION)
CREATE TABLE 직원 (직원번호, CHAR(10), 부서명 CHAR(10), PRIMARY KEY(직원번호),
FOREIGN KEY(부서명) REFERENCES 부서(부서명) ON DELETE CASCADE);

-- 인덱스 생성
CREATE INDEX IDX_EMP ON EMP (NAME);

-- 제약조건 생성
ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY (NO);
ALTER TABLE EMP ADD CONSTRAINT EMP_CK CHECK(LENGTH(NAME) > 2);

-- 인덱스 생성

-- 칼럼 추가
ALTER TABLE EMP ADD (PHONE VARCHAR2(100));

-- 칼럼 명 변경
ALTER TABLE EMP RENAME COLUMN PHONE TO PHONE_NUM;

-- 칼럼 타입 변경
ALTER TABLE EMP MODIFY (PHONE_NUM VARCHAR2(200));

-- 칼럼 삭제
ALTER TABLE EMP DROP COLUMN PHONE_NUM; 


-- 테이블 전체 삭제
DROP TABLE EMP;

-- 테이블은 삭제하지 않음. 데이터 전체 삭제. 롤백 불가능, 자동 커밋, 데이터 저장공간 모두 Release
TRUNCATE FROM EMP;

-- 테이블 이름 변경
RENAME EMP TO EMP_AFTER;

DML

-- 데이터 삽입 : 전체 컬럼 데이터 입력시
INSERT INTO EMP VALUES ('1', '홍길동', '영등포');

-- 데이터 삽입 : 특정 컬럼 데이터 입력시
INSERT INTO EMP (NO, NAME) VALUES ('1', '홍길동');

-- 여러 행을 한번에 삽입
INSERT INTO EMP
	SELECT NO, NAME, ADDR FROM EMP2 WHERE ADDR = '영등포';

-- 데이터 수정 
UPDATE EMP SET ADDR = '강남' WHERE NAME = '홍길동';

-- 데이터 일부 삭제
DELETE FROM EMP WHERE NO = '1';

-- 데이터 전체 삭제. 롤백가능, 직접 커밋, 데이터 저장공간은 그대로 살아있음
DELETE FROM EMP;

-- 여러 테이블의 데이터를 한 테이블에 모으기
MERGE INTO 타겟테이블
	USING 소스테이블
	ON (타겟테이블.컬럼1 = 소스테이블.컬럼1)
	WHEN MATCHED THEN
		... -- ON절 조건이 성립한다면 해당 쿼리 수행
	WHEN NOT MATCHED THEN
		... -- ON절 조건이 성립하지 않는다면 해당 쿼리 수행

DCL

-- 계정 생성
CREATE USER 사용자 IDENTIFIED BY 패스워드;

-- 사용자 생성 권한 부여
GRANT CREATE USER TO 사용자;

-- 로그인 권한 부여
GRANT CREATE SESSION TO 사용자;

-- 테이블 생성 권한 부여
GRANT CREATE TABLE TO 사용자;

-- 다른 사용자에게 테이블 SELECT 권한 부여
GRANT SELECT ON 나의테이블 TO 상대방

-- 여러 개의 권한을 뭉친 하나의 ROLE 생성
CREATE ROLE NEW_ROLE;
GRANT CREATE SESSION, CREATE TABLE TO NEW_ROLE;

-- 생성한 ROLE을 사용자에게 부여
GRANT NEW_ROLE TO 사용자;


-- 릴레이션 권한
-- 1. 사용자1이 다른 유저에게 테이블에 대해서 권한을 줄 수 있도록 설정
GRANT SELECT, INSERT, DELETE ON 테이블명 TO 사용자1 WITH GRANT OPTION;
-- 2. 사용자1이 사용자2에게 권한 부여
GRANT SELECT, INSERT, DELETE ON R TO 사용자2;
-- 3. 사용자1의 테이블명의 DELETE 권한을 제거
REVOKE DELETE ON 테이블명 FROM 사용자1;
-- 4. 사용자1의 테이블명의 INSERT 권한 제거 및 사용자2도 자동으로 제거됨
REVOKE INSERT ON 테이블명 FROM 사용자1 CASCADE;

6.0 DML 성능 영향 요소

• 기본적인 DML 튜닝을 설명하기 전 DML 성능에 영향을 미치는 요소에 대해 설명한다.
• 인덱스
  • 인덱스를 위한 데이터를 생성하거나 삭제하는 작업은 DML 성능에 영향을 준다.
  • INSERT, DELETE는 인덱스 조작을 한 번만 수행한다.
  • UPDATE는 인덱스 조작을 두 번 수행한다. (DELETE, INSERT)
  • 시스템마다 다르지만
    • 인덱스 1개에 100만 건 데이터를 넣을 때는 5초
    • 인덱스 3개에 100만 건 데이터를 넣을 때는 40초

• 무결성 제약
  • PK, FK, Check, Not Null 같은 제약도 DML 성능에 영향을 미친다.
  • 시스템마다 다르지만
    • PK가 없으면 100만건 데이터를 넣을 때는 1.3초
    • PK가 있으면 100만건 데이터를 넣을 때는 4.95초

• 조건절
  
  • 조건절을 확인하기 위하여 SELECT를 한다.
  • 따라서 SELECT 할 때와 동일하게 인덱스를 잘 활용해서 DML을 수행해야 한다.

• 서브 쿼리
  • 조건절과 마찬가지로 SELECT 할 때와 동일하게 인덱스를 잘 활용해서 DML을 수행해야 한다.

• Redo 로깅
  • Redo : 모든 변경 사항을 기록하는 로그이다.
    1. 물리적으로 디스크가 깨질 경우, 데이터베이스를 복구하기 위해 사용
    2. 정전 등으로 버퍼 캐시가 사라질 경우, 캐시를 복구하기 위해 사용
    3. 커밋은 느리므로 혹시 모를 상황을 대비해 트랜잭션에 의한 변경사항을 저장해놓는다.
  • DML을 수행할 때마다 로그를 쌓으므로 성능에 영향을 미친다.

• Undo 로깅
  • Undo : 오라클 9i부터 Rollback을 Undo로 표현
  • 변경된 블록을 이전 상태로 되돌리는 데 필요한 정보를 로깅해야 하므로 성능에 영향을 미친다.
• Lock
  • Lock은 DML 성능에 아주 큰 영향을 주는 요소이다.
  • 길게 잡으면 DML 성능이 안 좋아지고, 짧게 잡으면 데이터 품질이 안 좋아진다.
  • 6.4장에서 자세히 설명한다.
• 커밋
  • Lock과 함께 커밋도 DML 성능에 간접적으로 영향을 미친다.
  • 커밋의 내부 메커니즘은 다음과 같다.
    1. DML문을 수행하면 버퍼 캐시가 정전 등으로 사라질 수 있으므로 Redo 로깅을 먼저 해놓는다.
    2. Redo 로그 파일에 기록하기 전에 파일 자체도 I/O이므로 느리다. 따라서 로그 버퍼에 변경사항을 먼저 기록한다.  
    3. 버퍼 캐시에 변경된 블록을 기록한다.
    4. 커밋을 하게 되면
    5. LGWR 프로세스가 Redo 로그 버퍼 내용을 Redo 로그 파일에 일괄 저장한다.
    6. DBWR 프로세스가 버퍼 캐시에 변경된 블록을 데이터 파일에 일괄 저장한다.

6.1 기본 DML 튜닝

• DB는 SQL을 수행하면 Parse, Execute, Fetch Call이 발생한다.
  • Parse Call : 1.1장에서 설명한 파싱과 최적화를 수행하는 단계이다.
  • Execute Call : 실제로 SQL을 실행하는 단계이다. DML일 경우 해당 단계에서 마무리된다.
  • Fetch Call : SELECT문에서 사용자에게 결과를 전송하는 단계이다. 전송할 데이터가 많다면 여러 번 호출된다.

• Call은 발생 위치에 따라 User, Recursive Call로 나뉜다.
  • User Call : DBMS 외부로부터 인입되는 Call. WAS가 앞단에 있다면 WAS가 DBMS를 호출할 때 발생한다.
  • Recursive Call : DBMS 내부에서 발생하는 Call. 함수/프러시저/트리거에 내장된 SQL을 실행할 때 발생한다.

• Call은 성능에 영향을 주며, 네트워크를 경유하는 User Call이 성능에 미치는 영향이 크다.
  • 100만 건 기준으로 Recurisive Call만 발생하는 for문의 insert문을 짠 PL/SQL은 30초 걸림
  • User Call (자바로 짠 코드)는 220초 걸림
  • 하나의 SQL인 Insert Into Select은 한번의 Call만 발생하므로 1.4초 걸린다. 따라서 ONE SQL을 최대한 사용하는 것이 좋다.

• One SQL로 작성하기 힘들 때는 Array Processing을 활용
  • 100만 건 기준으로 Recurisive Call만 발생하는 for문의 insert문을 짠 PL/SQL 4초
  • User Call (자바로 짠 코드)는 12초

    for(int i = 0 ; i < length; i++){ 
    	... 
    	st.addBatch(); 
    } 
    
    st.executeBatch();​​

• 인덱스 및 제약 해제를 통한 대량 DML 튜닝
  • 6.0장에서 설명했듯이 인덱스와 무결성 제약은 DML 성능에 영향을 주는 요소이다.
  • 1000만 건의 데이터를 입력하게 되면
    • PK 인덱스 + 일반 인덱스 존재 : 약 1분 19초
    • 인덱스 및 제약사항이 없을 경우  :  약 5.8초
  • 애플리케이션이 실행 중에 자주 발생하는 테이블에 대한 인덱스 및 제약을 잠시 동안 해제하기는 어렵다.
  • 대량 데이터를 적재하기 위한 배치 프로그램에서는 이들 기능을 해제함으로써 DML 성능을 크게 높일 수 있다.
  • 해당 DML이 테이블의 데이터를 5% 이상 수정할 경우 사용하는 것을 추천한다.

    -- PK 제약사항 및 PK 인덱스 드랍
    ALTER TABLE 테이블 MODIFY CONSTRAINT PK명 DISABLE DROP INDEX;
    
    -- 일반 인덱스 비활성화
    ALTER INDEX 인덱스명 UNUSABLE;
    
    -- PK 제약사항 및 PK 인덱스 재생성
    ALTER TABLE 테이블 MODIFY CONSTRAINT PK명 ENABLE NOVALIDATE;
    
    -- 일반 인덱스 활성화
    ALTER INDEX 인덱스명 REBUILD;
    ​

• 뷰 : 하나 이상의 테이블이나 다른 뷰의 데이터를 볼 수 있게 하는 데이터베이스 객체. 실제 데이터는 뷰를 구성하는 테이블에 담겨 있지만 마치 테이블처럼 사용할 수 있다. 
• 조인 뷰 : FROM 절에 두 개 이상의 테이블을 가진 뷰

  CREATE OR REPLACE VIEW EMP_DEPT_VIEW AS
  	SELECT 
  		E.ROWID AS EMP_RID
  		,E.*
  		,D.ROW_ID AS DEPT_RID
  		,D.DNAME
  		,D.LOC
  	FROM EMP E, DEPT D
  	WHERE E.DEPTNO = D.DEPTNO;​

• 수정 가능 조인 뷰 : 입력, 수정, 삭제가 허용되는 조인 뷰이다. 키 보존 테이블 설정을 해야만 가능하다.(이해가 되지 않는다.)

  -- 연봉이 1500 이하인 직원의 휴대폰번호를 '1234'로 변경
  -- 하지만 현 상태에서는 ORA-01779 라는 에러가 발생한다.
  -- ORA-01779는 키-보존 테이블을 알 수 없어 발생하는 에러이다.
  UPDATE EMP_DEPT_VIEW SET EMP_PHONE = '1234' WHERE SAL <= 1500;
  ​

6.2 Direct Path I/O 활용

• 버퍼 캐시의 장점
  • 일반적으로 SQL문을 실행하면 1장에서 설명한 버퍼 캐시를 확인해보고 작업을 진행한다.
  • 반복적으로 동일한 블록을 찾는 경우에 버퍼 캐시는 성능을 높여주는 아주 좋은 기능이다. 

• 버퍼 캐시의 단점
  • 버퍼 캐시를 탐색하는 것도 락에 의해서 느릴 수 있다.
  • 반복적으로 동일한 블록을 찾을 경우가 없을 경우에는 오히려 버퍼 캐시를 한번 찾아보는 행위는 성능에 나쁜 영향을 미친다.

• 오라클은 버퍼 캐시를 사용할 필요가 없을 경우를 위해 버퍼 캐시를 경유하지 않고 곧바로 데이터 블록을 읽고 쓸 수 있는 Direct Path I/O 기능을 제공한다.
• 버퍼 캐시가 동작하도록 할 수 있는 방법에는 총 6가지가 존재한다.

1. Direct Path Insert를 수행할 때

• 일반적인 Insert와 Direct Path Insert의 순서는 다음과 같다. 

  순서	일반적인 Insert	Direct Path Insert
  1	FreeList에서 데이터를 입력할 수 있는 블록을 찾음	FreeList를 찾아보지 않고, 맨 뒤에 순차적으로 쌓는다.
  2	FreeList에서 할당받은 블록을 버퍼 캐시에서 찾음	블록을 버퍼 캐시에서 탐색하지 않는다.
  3	버퍼 캐시에 없으면 데이터 파일에서 읽어 버퍼 캐시에 적재한다.	버퍼 캐시에 적재하지 않고, 데이터 파일에 직접 기록한다.
  4	Undo 기록	Undo 기록하지 않음
  5	Redo 기록	Redo를 하지 않도록 할 수 있음

• 버퍼 캐시를 참조하지 않고, Undo, Redo를 로깅하지 않을 수 있어 매우 빠르다.

• Direct Path Insert를 유도할 수 있는 방법은 네 가지가 존재한다.
  1. INSERT /*+ append */ INTO SELECT ... 처럼 append 힌트로 유도한 경우
  2. INSERT /*+ parallel(C 4) */ INTO 고객 C 처럼 parallel 힌트로 유도한 경우
  3. CREATE TABLE ... AS SELECT 문을 사용할 경우
  4. 데이터를 적재할 수 있는 툴인 SQL*LOADER를 사용할 때 direct 옵션을 true로 준 경우

• Direct Path Insert를 사용할 때 주의할 점은 두 가지가 존재한다.
  1. 6.4장에서 설명할 Exclusive 모드 TM Lock이 걸려 다른 트랜잭션은 해당 테이블에 DML을 수행할 수 없다. 따라서 해당 테이블에 대해 DML 수행이 없을 때 작업을 진행해야 한다.
  2. FreeList 참조하면 삭제된 여유공간을 재활용할 수 있지만, Direct Path Insert는 항상 맨 뒤에 Insert를 하므로 사이즈가 줄지 않고 계속 늘어난다.

2. 병렬 DML을 수행할 때

• 위에서 설명한 Direct Path Insert를 INSERT에서 사용하는 방법이므로 UPDATE와 DELETE는 사용할 수 없다.
• 다만 병렬 DML로 UPDATE와 DELETE를 수행할 경우 Direct Path Insert 방식을 사용할 수 있다.

• 병렬 DML을 사용하기 위해서는 아래와 같이 병렬 DML을 활성화해야 한다.

  ALTER SESSION SET ENABLE PARALLEL DML;​

• parallel 힌트를 사용하여 병렬 DML을 사용하면 된다.

  UPDATE /*+ full(c) parallel(c 4) */ 고객 c ㄴㄸㅆ 고객상태 = '휴먼'
  WHERE 최종거래일시 < '20200101';
  
  DELETE /*+ full(c) parallel(c 4) */ FROM 고객 c
  WHERE 탈퇴일시 < '20200101';​

• 병렬 DML을 수행할 경우 Direct Path Insert 단점과 동일하게 Exclusive 모드 TM Lock이 걸리므로 잘 사용해야 한다.

3. parallel 또는 parallel_index 힌트를 사용하여 병렬 쿼리로 Full Scan을 수행할 때

• 병렬 DML처럼 SELECT를 병렬 쿼리로 Full Scan 할 경우 발생

  SELECT /*+ full(t) parallel(C 4) */ * FROM 고객 C;​

4. Temp 세그먼트 블록들을 읽고 쓸 때

5. direct 옵션을 true로 활성화하고 export 할 때

6. nocache 옵션을 지정한 LOB 컬럼을 읽을 때

6.3 파티션을 활용한 DML 튜닝

• 파티셔닝 : 테이블 또는 인덱스 데이터를 특정 컬럼 값에 따라 별도 세그먼트에 나눠서 저장하는 것
  • 관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경 → 가용성 향상
  • 성능적 측면 : 파티션 단위 조회 및 DML, 경합 또는 부하분산

• 파티션에는 Range, 리스트, 해시 방식이 있다.
  • Range 방식: 값의 범위에 따라 파티셔닝

    create table ...
    
    partition by range(주문일자) (
    	partition P2017_Q1 values less than ('20170401')
    	, partition P2017_Q2 values less than ('20170701')
    	, partition P2017_Q3 values less than ('20170101')
    	, partition P2017_Q4 values less than ('20180101')
    	, partition P2018_Q1 values less than ('20180401')
    	, partition P9999_MX values less than (MAXVALUE)
    );

  • Hash 방식 : 파티션 키 값을 해시 함수에 입력. 파티션 개수를 사용자가 결정

    create table ...
    
    partition by hash(고객ID) partitions 4;​

  • List 방식 : 그룹핑 기준에 따라 데이터를 분할 저장하는 방식

    create table ...
    
    partition by list(지역분류) (
    	partition P_지역1 values ('서울')
    	, partition P_지역2 values ('경기', '인천')
    	, partition P_기타 values (DEFALUT)
    );​

• 테이블 파티션 : 테이블을 파티셔닝
  • 비 파티션 테이블 : 파티셔닝 하지 않은 테이블로 일반적인 테이블이다.
  • 파티션 테이블 : 파티셔닝 한 테이블

• 인덱스 파티션 : 인덱스를 파티셔닝. 테이블 파티션과 인덱스 파티션을 혼동하지 말자.
  
  • 비 파티션 인덱스
    • 파티셔닝 하지 않은 인덱스로 일반적인 인덱스이다.
    • 만약 파티션 된 테이블의 파티션 구성을 변경하게 되면 비 파티션 인덱스는 재생성되어야 하며 그동안 서비스가 중단되므로 조심해야 한다.
    • 아래는 파티션 테이블일 때 비 파티션 인덱스의 예시이다.
    
  • 로컬 파티션 인덱스

    -- 인덱스 생성문 마지막에 LOCAL 옵션을 주면 로컬 인덱스로 생성된다.
    create index ... LOCAL;

    
    
    • 테이블 파티션과 1:1로 매핑되는 인덱스 파티션. 즉 테이블 파티션 개수만큼 인덱스 파티션이 존재함.
    • 테이블 파티션의 키가 동일하게 로컬 인덱스에 상속된다. 따라서 테이블 파티션의 키와 인덱스 파티션의 키는 동일하다.
      • 테이블 파티션의 키가 '계절'이라면 인덱스 파티션의 키도 '계절'이다.
    • 오라클에서 관리해주므로 테이블 파티션 구성을 변경하더라도 서비스에 영향을 거의 주지 않음
    • 아래는 파티션 테이블일 때 로컬 인덱스의 예시이다.
    
  • 글로벌 파티션 인덱스

    -- 인덱스 생성문 마지막에 GLOBAL 옵션을 준 후, 파티션을 생성 로직을 넣으면 글로벌 파티션 인덱스로 생성된다.
    create index ... GLOBAL
    partition by range(주문금액) (
    	partition P_01 values less than (10000)
    	, partition P_MX values less than (MAXVALUE)
    );

    • 테이블 파티션과는 별개로 인덱스의 파티션을 다른 방식으로 만든다.
    • 테이블 파티션이 없어도 생성 가능
    • 테이블 파티션 구성을 변경하는 순간 글로벌 인덱스를 재생성해 줘야 하므로 서비스를 중단해야 함.
    • 아래는 파티션 테이블일 때 글로벌 파티션 인덱스의 예시이다.
    
• Prefixed vs Nonprefixed
• Prefixed : 인덱스 파티션 키 컬럼이 인덱스 컬럼 구성에서 왼쪽 선두에 위치할 경우
• Nonprefixed : 인덱스 파티션 키 컬럼이 인덱스 컬럼 구성에서 왼쪽 선두에 위치하지 않거나 인덱스 컬럼에 아예 속하지 않는 경우
• 로컬 Prefixed
  • 테이블 파티션의 키가 '계절'이라면 인덱스 파티션의 키도 '계절'이다.
  • 인덱스의 컬럼 구성을 '계절' + '온도'로 한 로컬 파티션 인덱스를 만들었다면, 파티션 키인 '계절'이 인덱스 컬럼 구성 선두에 위치하므로 이를 로컬 Prefixed라고 한다.
• 로컬 Nonprefixed
  • 테이블 파티션의 키가 '계절'이라면 인덱스 파티션의 키도 '계절'이다.
  • 인덱스의 컬럼 구성을 '온도'로 한 로컬 파티션 인덱스를 만들었다면, 파티션 키인 '계절'이 인덱스 컬럼에 아예 속하지 않으므로 이를 로컬 Nonprefixed라고 한다.
• 글로벌 Prefixed
  
  • 로컬 Prefixed와 설명 동일
• 글로벌 Nonprefixed
  • 글로벌 파티션 인덱스는 Prefixed 파티션만 지원한다.

• Unique 인덱스를 파티셔닝 하려면, 파티션 키(=테이블 파티션의 키 = 인덱스 파티션의 키)가 모두 인덱스 구성 컬럼이어야 한다. 이는 당연한 제약 사항이다.
  • 예를 들면 Unique 인덱스가 '주문번호'이고 파티션의 키가 '주문일자'인 로컬 파티션이 존재한다고 가정하자.
  • 인덱스 파티션의 키가 '주문일자'이므로 테이블 파티션도 '주문일자'를 기준으로 파티셔닝이 되어있다.
  • '주문번호'가 1234인 데이터가 들어오게 되면 중복 값이 있는지 확인하기 위해 모든 인덱스의 파티션을 다 확인해야 한다. 왜냐하면 인덱스 파티션의 키는 '주문일자' 이므로 '주문번호'가 1234인 값이 어디에 있는지 알 수가 없기 때문이다.
  • 이렇게 되면 데이터를 Insert 할 때마다 모든 인덱스 파티션을 다 찾아봐야 한다는 게 말이 안 되기 때문에 DBMS 자체에서 당연히 존재하는 제약사항이다.

• 파티션을 활용한 대량 UPDATE 튜닝
  • 6.1장에서 설명한 '인덱스 및 제약 해제를 통한 대량 DML 튜닝'은 해당 DML이 테이블의 데이터를 5% 이상 수정할 경우 사용하는 것을 추천하지만, 테이블의 데이터가 엄청 많다면, 인덱스를 삭제하고 재생성하는 시간도 무시할 수 없으며 약간의 부담이 존재한다.
  • 만약에 테이블이 파티셔닝이 돼 있고, 인덱스 파티션도 로컬 파티션이라면 '인덱스 및 제약 해제를 통한 대량 DML 튜닝' 보다 더 좋은 방법이 있다. 특정 파티션에 대한 임시 세그먼트를 만들어 원본 파티션과 바꿔치기하는 방식이다.

    -- 1. 임시 테이블을 만든다. nologging 모드로 생성하면 더 빠르다.
    CREATE TABLE 임시 nologging AS SELECT * FROM 실제테이블 WHERE 1 = 2;
    
    -- 2. 데이터를 수정하고자 하는 데이터를 insert
    -- append 힌트로 Direct Path Insert 유도하여 빠르게 insert 하면 좋다.
    INSERT /*+ append */ INTO 임시
    SELECT 컬럼1, 컬럼2, 컬럼3, (CASE WHEN 데이터변경컬럼 = '1' THEN '2' ELSE '3' END) 데이터변경컬럼
    FROM 실제테이블
    WHERE 거래일자 < '20210101'; -- 특정 테이블 파티션의 조건으로
    
    -- 3. 임시 테이블에 실제 테이블과 동일하게 인덱스 생성. nologging 모드로 생성하면 더 빠르다.
    -- 인덱스를 나중에 만드는 이유는 6.0에서 설명했듯이 DML 성능에 영향을 주기 때문에 나중에 만든다.
    CREATE INDEX ...
    
    
    -- 4. 실제 테이블의 변경하려고 하는 파티션과 임시 테이블을 교체한다.
    ALTER TABLE 실제테이블 EXCHANGE PARTITION P202012 WITH TABLE 임시
    INCLUDING INDEXES WITHOUT VALIDATION;
    
    -- 5. 임시 테이블 드랍
    DROP TABLE 임시;
    
    -- 6. nologging으로 인덱스를 만들었다면 logging으로 전환한다.
    ​

• 파티션을 활용한 대량 DELETE 튜닝
  • 전제조건 : 테이블이 파티셔닝이 돼 있고, 인덱스 파티션도 로컬 파티션
  • 파티션에 할당된 데이터를 모두 삭제할 때

    ALTER TABLE 테이블명 DROP PARTITION p201412;​

     
  • 파티션에 할당된 데이터를 소수만 삭제할 때

    DELETE FROM 테이블명
    WHERE 거래일자 < '20210101' -- 파티션 조건
    AND 상태 = '1' -- 이외 조건

     
  • 파티션에 할당된 데이터를 대다수 삭제할 때

    -- 1. 임시 테이블을 만드는데 삭제하지 않을 데이터만 남긴다. 
    -- nologging 모드로 생성하면 더 빠르다.
    CREATE TABLE 임시 nologging AS SELECT * FROM 실제테이블 WHERE 거래일자 < '20210101' AND 상태 = '1';
    
    -- 2. 삭제 대상 테이블 파티션을 truncate 한다.
    ALTER TABLE 실제테이블 TRUNCATE PARTITION p202012;​
    
    -- 3. 임시 테이블의 데이터를 실제 테이블에 Insert 한다.
    INSERT INTO 실제테이블 SELECT * FROM 임시;
    
    -- 4. 임시 테이블 드랍
    DROP TABLE 임시;
    

• 파티션을 활용한 대량 INSERT 튜닝
  
  • 전제조건 : 테이블이 파티셔닝이 돼 있고, 인덱스 파티션도 로컬 파티션
  • '인덱스 및 제약 해제를 통한 대량 DML 튜닝'과 마찬가지로 파티션 단위로 인덱스 및 제약을 해제하면 된다.

    -- 파티션의 인덱스 비활성화
    ALTER INDEX 인덱스명 MODIFY PARTITION P202012 UNUSABLE;
    
    -- 파티션의 인덱스 활성화
    ALTER INDEX 인덱스명 REBUILD PARTITION P202012;
    ​​

6.4 Lock과 트랜잭션 동시성 제어 (오라클 기준)

• 오라클은 DML, DDL, 래치, 버퍼, 라이브러리 캐시 등 다양한 종류의 Lock을 사용한다.
• 이 중 애플리케이션 개발 측면에서는 DML Lock이 중요하다.

• DML Lock : 다중 트랜잭션이 동시에 액세스 하는 사용자 데이터의 무결성을 보호하기 위한 Lock
  • DML 로우 Lock : 두 개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지

    	SELECT	INSERT	UPDATE	DELETE
    로우 LOCK	X	인덱스 존재 시 O	O	O

  • DML 테이블 Lock (TM Lock) : 테이블 구조를 다른 트랜잭션이 변경하는 것을 방지
    • 테이블 Lock이라고 해서 테이블 전체에 Lock이 걸리는 의미가 아님.
    • 테이블 Lock에는 여러 가지 모드가 있고, 어떤 모드를 사용했는지에 따라 후행 트랜잭션이 수행할 수 있는 작업의 범위가 결정됨.

5.1 소트 연산에 대한 이해

• 소트 수행 과정
  • 소트는 PGA에 할당한 Sort Area에서 수행하며 PGA가 부족하면 Temp 테이블 스페이스를 사용
  • 메모리 소트 : PGA 내에서 소트 완료할 경우
  • 디스크 소트 : Temp 테이블 스페이스까지 사용하여 소트를 완료할 경우
  • 소트는 최대한 발생하지 않도록 SQL을 작성해야 하고, 소트가 불가피하다면 메모리 내에서 수행을 완료할 수 있도록 해야 함

• 아래는 소트 오퍼레이션 종류에 대해 설명한다.

• Sort Aggregate : 전체 로우를 대상으로 집계를 수행할 때 나타난다.

  -- Sort Area에 변수를 하나 만들어 데이터를 읽으면서 최대값 비교하며 최대값을 찾아 리턴 
  SELECT MAX(SAL) FROM 직원;​​

  
  
  • MAX, MIN, SUM, AVG 함수를 사용할 때 나타남
  • 실제로 데이터를 정렬하진 않고 Sort Area 사용하는 정도

• Sort Order By : 데이터를 정렬할 때 나타나는 기본적인 소트 오퍼레이션.

  SELECT * FROM 직원 ORDER BY SAL DESC;​​

• Sort Group By : 그룹별 집계에 나타나는 오퍼레이션

  -- 부서번호를 기준으로 ORDER BY 하므로 SORT GROUP BY 오퍼레이션 사용
  SELECT DEPT_NO, MAX(SAL) FROM 직원 GROUP BY DEPT_NO ORDER BY DEPT_NO;
  
  -- ORDER BY가 없으므로 HASH GROUP BY 오퍼레이션 사용
  SELECT DEPT_NO, MAX(SAL) FROM 직원 GROUP BY DEPT_NO;​​

  • 각각의 그룹별로 위 Sort Aggregate에서 했던 방식대로 사용
  • 각각의 그룹을 대표하는 값에 대해 ORDER BY가 없다면 Hash Group By라는 오퍼레이션을 사용함.
  • ORDER BY가 없을 경우, 그룹을 대표하는 값에 따라 소트 하지 않는다는 것을 명심

• Sort Unique : 중복 레코드를 제거하는 소트 오퍼레이션

  -- 서브쿼리의 중복을 제거하기 위해 발생
  SELECT /*+ ordered use_nl(dept) */
  	*
  FROM DEPT
  WHERE
  	DEPT_NO IN (SELECT /*+ unnest */ DEPT_NO FROM EMP WHERE JOB = '개발자');
  
  
  -- UNION 하여 중복을 제거하기 위해 발생
  SELECT JOB FROM EMP WHERE DEPT_NO = 10
  UNION
  SELECT JOB FROM EMP WHERE DEPT_NO = 20​

  • 만약 유일성이 보장된다면 (PK 또는 유니크 인덱스) 이 오퍼레이션은 생략됨.
  • Distinct 연산자에서도 사용됨.

• Sort Join : 4.2장에서 배운 소트 머지 조인을 수행할 때 나타남

  MERGE JOIN
  	SORT (JOIN)
  		TABLE ACCESS (BY INDEX ROWID) OF '사원' TABLE
  			INDEX (RAGNE SCAN) OF '사원_X1' (INDEX)
  	SORT (JOIN)
  		TABLE ACCESS (BY INDEX ROWID) OF '고객' TABLE
  			INDEX (RAGNE SCAN) OF '고객_X1' (INDEX)​​

• Window Sort : 윈도우 함수(분석 함수)를 수행할 때 나타남

  SELECT MGR , ENAME , SAL , SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM FROM EMP;​​

   
  • 윈도우 함수 : 행과 행간의 관계를 쉽게 정의 및 분석할 수 있는 함수
  • RANK, SUM, MAX, MIN , AVG 등등 

5.2 소트가 발생하지 않도록 SQL 작성

• Union vs Union All
  • Union은 중복을 제거하기 위해 소트 연산을 함
  • Union All은 중복 제거를 하지 않으므로 소트를 하지 않음
  • 중복일 수 없는 데이터들의 집합을 Union으로 처리하면 낭비이므로 데이터가 중복되지 않는다면 Union All을 사용해야 함
  • 소트 연산이 일어나지 않도록 Union All을 사용하며 중복을 피하는 방법도 있음

    SELECT
    	..
    WHERE 결제일자 = '20180316'
    UNION ALL
    SELECT
    	..
    WHERE 주문일자 = '20180316'
    AND 결제일자 <> '20180316' -- 또는 LNNVL(결제일자 = '20180316')​

• Exists 활용
  • DISTINCT는 중복을 제거하기 위해 소트 연산을 함

    -- 이 경우 B 데이터를 모두 읽고, 중복을 제거하는 작업이 필요
    SELECT DISTINCT A.*
    FROM A, B
    WHERE A.x = :x
    AND B.y = A.y
    AND B.z between :dt1 and :dt2
    
    -- 이 경우, B 데이터가 한건이라도 존재하는지만 확인하므로 효율적임
    SELECT A.*
    FROM A
    WHERE A.x = :x
    AND EXISTS(
    	SELECT 'x' FROM B
    	WHERE B.y = A.y
    	AND B.z between :dt1 and :dt2
    )​

• 해시 조인은 소트가 발생하므로 NL 조인으로 변경해주는 것이 효과적일 수 있다.

5.3 인덱스를 이용한 소트 연산 생략

• 인덱스는 항상 키 컬럼 순으로 정렬된 상태를 유지하므로, 인덱스를 이용해서 소트 연산이 생략 가능

• Top N 쿼리 : 전체 결과 집합 중 상위 N개 레코드만 선택하는 쿼리 
  • 인덱스를 탄다면, 부분 범위 처리로 딱 10개만 만나면 멈춘다.
  • 실행 계획에는 COUNT(STOPKEY)라고 표현되며 이를 Top N Stopkey 알고리즘이라고 칭함.
  • 페이징 처리를 할 때 Top N 쿼리를 사용하는데 반드시 아래에 ROWNUM을 사용해야 한다.

• 인덱스를 이용한 최대/최솟값 구하기
  • 조건절 컬럼 + MIN/MAX 함수 인자의 컬럼이 모두 인덱스에 포함되어 있어야지 소트를 하지 않는다.
  • 이런 경우를 First Row Stopkey 알고리즘이 사용되었다고 표현한다.

• Top N 쿼리를 이용한 최대/최솟값 구하기
  • 인덱스가 모두 포함되지 않을 경우는 Top N 쿼리로 사용하면 된다. ROWNUM ≤ 1;

• 이력 조회 : 다른 포스팅으로 대신합니다. [SQL] 변경 이력 테이블 생성 및 조회 방법
  

• Sort Group By 생략

  -- 인덱스 : REGION
  SELECT REGION, AVG(AGE)
  FROM 고객
  GROUP BY REGION

   
  • GROUP BY에 선두 컬럼 인덱스가 있다면, 인덱스를 통해, Sort Group By 연산을 생략 가능.
  • Sort Group By Nosort라고 표현됨

5.4 Sort Area를 적게 사용하도록 SQL 작성

• 소트 연산이 불가피하다면 메모리 내에서 처리를 완료하도록 노력해야 함.

• 소트 데이터 줄이기 : Sort Area에 저장을 적게 해야 하므로, SELECT 절에 필요한 항목만 넣어야 한다.

  -- 모든 컬럼을 SORT AREA에 담으므로 아래보다 느리다.
  SELECT * FROM 직원 ORDER BY 나이;
  
  -- 이름만 SORT AREA에 담으므로 위보다 빠르다.
  SELECT 이름 FROM 직원 OERDER BY 나이;​
  ​

• TOP-N 쿼리가 인덱스를 사용하지 못해도, Sort Area를 적게 사용한다. 그러니 TOP-N은 좋다!
  • Top N의 자세한 설명은 다음 포스팅을 참고. [SQL] Top N을 파헤쳐 보자.