티스토리 뷰
아래 링크를 통해 가입시 바이낸스 코인 거래수수료 25% 평생할인!
25% lifetime discount on Binance Coin transaction fees when you sign up through the link below!
https://accounts.binance.com/en/register?ref=286562663
Log In | Binance
login-description
accounts.binance.com
바이낸스 추천코드로 가입하고 수수료 25% 평생 할인 받으세요! (바이낸스 가입 레퍼럴코드, 추천
바이낸스 추천코드로 가입하고 수수료 25% 평생 할인 받으세요! 바이낸스 가입코드, 레퍼럴코드, 추천코드, 할인코드 : ( 286562663 ) 아래 링크를 통해 가입시 25% 할인 받을 수 있습니다 http
pink24.tistory.com
[제4장] 튜닝활용
================
CASE 함수
=========
IF SAL > 2000
THEN REVISED_SALARY = SAL
ELSE REVISED_SALARY = 2000
END-IF.
○ CASE 사용
CASE WHEN SAL > 2000
THEN SAL
ELSE 2000 END as REVISED_SALARY
○ DECODE 사용
DECODE(SIGN(SAL - 2000), 1, SAL, 2000) as REVISED_SALARY
또는
DECODE(SIGN(2000 - SAL),-1, SAL, 2000) as REVISED_SALARY
그룹함수(DECODE(...))
=====================
- SUM(DECODE(...)), AVG(DECODE(...)), COUNT(DECODE(...)),
MAX(DECODE(...)), MIN(DECODE(...))
○ SUM(DECODE(DEPTNO,10,SAL,0)) vs. SUM(DECODE(DEPTNO,10,SAL))
결과는 서로 같다.
여기서 DECODE의 4번째 인자가 0으로 되어 있는 경우에는 나중에 SUM에
의해서 0을 더하게 되고, DECODE의 4번째 인자가 없는 경우에는 NULL로
처리되기 때문에 나중에 SUM을 할 때 제외된다.
따라서 성능은 SUM(DECODE(DEPTNO,30,SAL))이 빠르다.
○ SUM(NVL(SAL,0)) vs. SUM(SAL)
결과는 서로 같다.
왜냐하면 SUM은 SAL의 값이 NULL인 것은 제외하기 때문이다.
따라서 성능은 SUM(SAL)이 빠르다.
■ PIVOT 함수로 구현(11g)
SELECT /*+ FULL(EC_APPLY) */
COURSE_CODE,
NVL(SUM(DECODE(YEAR,'1999',DEPOSIT_AMOUNT)),0) Y1999,
NVL(SUM(DECODE(YEAR,'2000',DEPOSIT_AMOUNT)),0) Y2000,
NVL(SUM(DECODE(YEAR,'2001',DEPOSIT_AMOUNT)),0) Y2001,
NVL(SUM(DECODE(YEAR,'2002',DEPOSIT_AMOUNT)),0) Y2002
FROM EC_APPLY
WHERE COURSE_CODE < 1000
AND YEAR BETWEEN '1999' AND '2002'
GROUP BY COURSE_CODE;
WITH B AS (
SELECT /*+ FULL(EC_APPLY) */
COURSE_CODE,
YEAR,
SUM(DEPOSIT_AMOUNT) AS S_AMT
FROM EC_APPLY
WHERE COURSE_CODE < 1000
AND YEAR BETWEEN '1999' AND '2002'
GROUP BY COURSE_CODE, YEAR)
SELECT COURSE_CODE, NVL(A,0) AS Y1999, NVL(B,0) AS Y2000,
NVL(C,0) AS Y2001, NVL(D,0) AS Y2002
FROM B
PIVOT(SUM(S_AMT) FOR YEAR IN ('1999' AS A, '2000' AS B,
'2001' AS C, '2002' AS D))
ORDER BY COURSE_CODE;
Cartesian Product의 적용
========================
○ Cartesian Product의 발생
① WHERE절 없는 조인 수행
② 조인을 위한 조건 없이 조인 수행
○ Cartesian Product 적용 대상
① FROM 절과 WHERE 조건이 같으면서 UNION ALL을 사용한 SQL
② 데이터베이스에 없는 데이터를 있는 것처럼 할 때
③ 데이터 모델링이 잘못 되었을 때
○ Cartesian Product는 "데이터 복제"라는 개념을 활용하기 위해서
사용함. "데이터 복제"로 인해 발생하는 Disk I/O는 발생하지 않음.
하지만 이를 잘못 사용하는 경우 오히려 데이터를 부풀리는 원인이
됨으로써 퍼포먼스 향상을 달성하지 못할 수도 있음.
따라서 반드시 뷰를 통해 데이터를 먼저 읽어들일 수 있도록 해야 한다.
---------------
○ '2017'년의 날짜 만드는 SQL
WITH EC_YMD AS
(SELECT (TO_DATE('20170101','YYYYMMDD') + (LEVEL - 1)) DT
FROM DUAL
CONNECT BY LEVEL <= TO_DATE('20171231','YYYYMMDD')
- TO_DATE('201709101','YYYYMMDD') + 1)
SELECT TO_CHAR(DT,'YYYYMMDD') FROM EC_YMD;
* Cartesian product 활용
WITH B AS (
SELECT '2017'||B.DAY||A.DAY AS YMD,
TO_CHAR(LAST_DAY(TO_DATE('2017'||B.DAY,'YYYYMM')),
'YYYYMMDD') AS L_DAY, ROWNUM
FROM EC_DAY A, EC_DAY B
WHERE B.DAY <= '12')
SELECT YMD FROM B
WHERE YMD <= L_DAY;
* Pivot 함수(11g)으로 2017년 9월의 Calendar
WEEK_GBN SUN MON TUE WED THU FRI SAT
---------- ---- ---- ---- ---- ---- ---- ----
1 01 02
2 03 04 05 06 07 08 09
3 10 11 12 13 14 15 16
4 17 18 19 20 21 22 23
5 24 25 26 27 28 29 30
WITH v AS (
SELECT TO_DATE('20170901','YYYYMMDD') + LEVEL - 1 AS dt
FROM dual
CONNECT BY LEVEL <= TO_CHAR(LAST_DAY(TO_DATE('201709','YYYYMM')),'DD'))
SELECT *
FROM (SELECT dt,
n,
CASE WHEN n < n1 THEN TO_NUMBER(r) + 1
ELSE TO_NUMBER(r) END AS week_gbn
FROM (SELECT TO_CHAR(dt,'DD') AS dt,
TO_CHAR(dt,'W') AS r,
TO_CHAR(dt,'D') AS n,
TO_CHAR(TO_DATE('20170901','YYYYMMDD'),'D') AS n1
FROM v))
PIVOT (MAX(dt) FOR n IN (1 AS sun, 2 AS mon, 3 AS tue,
4 AS wed, 5 AS thu, 6 AS fri, 7 AS sat))
ORDER BY week_gbn;
○ 테이블 구조 변환
SELECT A.ID, B.GBN,
CASE WHEN B.GBN = 1 THEN TEL1
WHEN B.GBN = 2 THEN TEL2
ELSE TEL3 END AS TEL
FROM (SELECT ID, TEL1, TEL2, TEL3, ROWNUM
FROM EC_PERSONAL) A,
(SELECT ROWNUM AS GBN
FROM COPY_T
WHERE COL1 <= 4) B
ORDER BY 1, 2;
WITH B AS (
SELECT ID, TEL1, TEL2, TEL3
FROM EC_PERSONAL)
SELECT ID, GRP, NO
FROM B
UNPIVOT (NO FOR GRP IN (TEL1 AS 1, TEL2 AS 2, TEL3 AS 3))
ORDER BY ID, GRP;
○ Cartesian Product 적용 대상 중에는 ROLLUP, CUBE, GROUPING SETS
를 사용할 수 있는 것이 다소 있음.
→ 이러한 기능을 사용할 때 SQL이 더욱 간결해지고, 또한 Cartesian
product적용을 잘못하게 되었을 때 발생하는 문제를 근본적으로 대처
할 수 있다.
(참고) ROLLUP, CUBE, GROUPING SETS로 구현할 수 있는 것은 모두
Cartesian Product로 구현할 수 있다.
ROLLUP, CUBE로 구현할 수 있는 것은 GROUPING SETS로 모두
구현할 수도 있다.
------------------------------------------------------------
○ ROLLUP(DNAME, JOB)을 Cartesian product로 구현
SELECT DNAME,JOB,
COUNT(*) as CNT,
SUM(SAL) as TOT
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME,JOB)
ORDER BY 1, 2;
SELECT CASE WHEN B.CHK IN (1, 2) THEN A.DNAME END AS DNAME,
CASE WHEN B.CHK = 1 THEN A.JOB END AS JOB,
SUM(CNT) AS T_CNT,
SUM(TOT) AS T_TOT
FROM (SELECT DNAME,JOB,
COUNT(*) as CNT,
SUM(SAL) as TOT
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, JOB) A,
(SELECT ROWNUM AS CHK
FROM COPY_T
WHERE ROWNUM <= 3) B
GROUP BY CASE WHEN B.CHK IN (1, 2) THEN A.DNAME END,
CASE WHEN B.CHK = 1 THEN A.JOB END
ORDER BY 1, 2;
------------------------------------------------------------
○ CUBE(DNAME, JOB)를 Cartesian product로 구현
SELECT DNAME,JOB,
COUNT(*) as CNT,
SUM(SAL) as TOT
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY CUBE(DNAME,JOB)
ORDER BY 1, 2;
SELECT DECODE(CHK,1,DNAME,2,DNAME) AS DNAME,
DECODE(CHK,1,JOB,3,JOB) AS JOB,
SUM(CNT) AS T_CNT,
SUM(TOT) AS T_TOT
FROM (SELECT DNAME,JOB,
COUNT(*) as CNT,
SUM(SAL) as TOT
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, JOB) A,
(SELECT ROWNUM AS CHK
FROM COPY_T
WHERE ROWNUM <= 4) B
GROUP BY DECODE(CHK,1,DNAME,2,DNAME),
DECODE(CHK,1,JOB,3,JOB)
ORDER BY 1, 2;
------------------------------------------------------------
GUBUN JOB_DEPT T_CNT
------------------ --------------- ----------
직군별 ANALYST 2
직군별 CLERK 4
직군별 MANAGER 3
직군별 PRESIDENT 1
직군별 SALESMAN 4
부서별 10 3
부서별 20 5
부서별 30 6
총인원 14
○ ROLLUP(DEPTNO, JOB)으로 구현
SELECT CASE WHEN G1 = 0 AND G2 = 0 THEN '직군별'
WHEN G1 = 0 AND G2 = 1 THEN '부서별'
ELSE '총인원' END AS GUBUN,
CASE WHEN G1 = 0 AND G2 = 0 THEN JOB
WHEN G1 = 0 AND G2 = 1 THEN TO_CHAR(DEPTNO) END AS JOB_DEPT,
SUM(CNT) AS T_CNT
FROM (SELECT DEPTNO, JOB, COUNT(*) AS CNT,
GROUPING(DEPTNO) AS G1,
GROUPING(JOB) AS G2
FROM EMP
GROUP BY ROLLUP(DEPTNO,JOB)) A
GROUP BY G1||G2,
CASE WHEN G1 = 0 AND G2 = 0 THEN '직군별'
WHEN G1 = 0 AND G2 = 1 THEN '부서별'
ELSE '총인원' END,
CASE WHEN G1 = 0 AND G2 = 0 THEN JOB
WHEN G1 = 0 AND G2 = 1 THEN TO_CHAR(DEPTNO) END
ORDER BY G1||G2;
---------------------------------------------------------
* GROUPING SETS로 ROLLUP(DNAME, JOB)을 구현
SELECT DNAME,JOB,
COUNT(*) as CNT,
SUM(SAL) as TOT
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS((DNAME, JOB), (DNAME), ())
ORDER BY 1, 2;
* GROUPING SETS로 CUBE(DNAME, JOB)을 구현
SELECT DNAME,JOB,
COUNT(*) as CNT,
SUM(SAL) as TOT
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS((DNAME, JOB), (DNAME), (JOB), ())
ORDER BY 1, 2;
○ ROLLUP(DNAME,(JOB,MGR))을 카테시안 곱으로 구현
SELECT DNAME, JOB, MGR, SUM(SAL) AS "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME,(JOB,MGR))
ORDER BY DNAME, JOB;
SELECT DECODE(GBN,1,DNAME,2,DNAME) AS DNAME,
DECODE(GBN,1,JOB) AS JOB,
DECODE(GBN,1,MGR) AS MGR,
SUM(TOT_SAL) AS "Total Sal"
FROM (SELECT DNAME, JOB, MGR, SUM(SAL) AS TOT_SAL
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, JOB, MGR) A,
(SELECT ROWNUM AS GBN
FROM COPY_T
WHERE ROWNUM <= 3) B
GROUP BY GBN,
DECODE(GBN,1,DNAME,2,DNAME),
DECODE(GBN,1,JOB),
DECODE(GBN,1,MGR)
ORDER BY 1, 2;
* GROUPING SETS로 구현
SELECT DNAME, JOB, MGR, SUM(SAL) AS "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS((DNAME, JOB, MGR),
(DNAME),
())
ORDER BY DNAME, JOB;
------------------------------------------------------------
○ GROUP BY DNAME, CUBE(JOB), ROLLUP(MGR)을 카테시안 곱으로 구현
SELECT DNAME, JOB,MGR,SUM(SAL) AS "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, CUBE(JOB), ROLLUP(MGR)
ORDER BY GROUPING(DNAME)||GROUPING(JOB)||GROUPING(MGR), DNAME;
* Cartesian product로 구현
SELECT DNAME,
DECODE(GBN,1,JOB,2,JOB) AS JOB,
DECODE(GBN,1,MGR,3,MGR) AS MGR,
SUM(TOT_SAL) AS "Total Sal"
FROM (SELECT DNAME, JOB, MGR, SUM(SAL) AS TOT_SAL
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, JOB, MGR) A,
(SELECT ROWNUM AS GBN
FROM COPY_T
WHERE ROWNUM <= 4) B
GROUP BY GBN,
DNAME,
DECODE(GBN,1,JOB,2,JOB),
DECODE(GBN,1,MGR,3,MGR)
ORDER BY GBN, 1, 2;
* GROUPING SETS로 구현
SELECT DNAME, JOB,MGR,SUM(SAL) AS "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS((DNAME, JOB, MGR),
(DNAME, JOB),
(DNAME, MGR),
(DNAME))
ORDER BY GROUPING(DNAME)||GROUPING(JOB)||GROUPING(MGR), DNAME;
------------------------------------------------------------
○ GROUP BY GROUPING SETS ((DNAME,JOB,MGR),(DNAME,JOB),(JOB,MGR))의 구현
SELECT DNAME, JOB,MGR,SUM(SAL) AS "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS ((DNAME,JOB,MGR),(DNAME,JOB),(JOB,MGR))
ORDER BY GROUPING(DNAME)||GROUPING(JOB)||GROUPING(MGR)||DNAME;
* Cartesian product로 구현
SELECT DECODE(GBN,1,DNAME,2,DNAME) AS DNAME,
JOB,
DECODE(GBN,1,MGR,3,MGR) AS MGR,
SUM(TOT_SAL) AS "Total Sal"
FROM (SELECT DNAME, JOB, MGR, SUM(SAL) AS TOT_SAL
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, JOB, MGR) A,
(SELECT ROWNUM AS GBN
FROM COPY_T
WHERE ROWNUM <= 3) B
GROUP BY GBN,
DECODE(GBN,1,DNAME,2,DNAME),
JOB,
DECODE(GBN,1,MGR,3,MGR)
ORDER BY GBN, 1, 2;
Analytic Functions
==================
○ 복잡한 비즈니스 로직을 구현하고 대량의 데이터를 효율적으로 처리할 수
있게 함 (v8.1.x 이상)
○ 대다수의 Self join과 Self subquery 형태의 SQL을 제거할 수 있는
수단이 될 수 있음
○ v8.1.x 에서의 문제는 PL/SQL과 Pro*C 상에서 사용하려할 땐
반드시 Dynamic SQL 이어야 한다.
○ Syntax
SELECT Analytic_fn(argument) OVER(PARTITION BY~ ORDER BY~ WINDOWING)
FROM table
WHERE ...;
- PARTITION BY ~ 데이터를 소그룹별로 구분하기 위한 기준을
지정할 때 사용
- ORDER BY ~ Sort 기준을 지정할 때 사용
- WINDOWING절 ~ 함수가 스스로 갖는 데이터 제어 범위임
SEQ
----------
1 ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING
2 ROWS BETWEEN 2 번째 앞 AND 3 번째 뒤
3 --------- ---------
4
5 <--- 3 PRECEDING
6 <--- 2 PRECEDING
7 <--- 1 PRECEDING
8 <------------------- CURRENT ROW
9 <--- 1 FOLLOWING
10 <--- 2 FOLLOWING
11 <--- 3 FOLLOWING
12
13
14
YMD
--------
20170901
20170901
20170901 RANGE BETWEEN 3 PRECEDING AND 4 FOLLOWING
20170902 RANGE BETWEEN 3 일 전 AND 4 일 후
20170902
20170902
20170903
20170903
20170904 <--- 3 PRECEDING
20170905 <--- 2 PRECEDING
20170905 <--- 2 PRECEDING
20170905 <--- 2 PRECEDING
20170906 <--- 1 PRECEDING
20170907 <----- CURRENT ROW
20170907
20170907
20170908 <--- 1 FOLLOWING
20170909 <--- 2 FOLLOWING
20170910
20170911
20170912
20170913
20170914
YMD
--------
20170931
20170930
20170929
20170928
20170927
20170927
20170927
20170926
20170926 <----- CURRENT ROW
20170926
20170926
20170925
20170924
20170923
20170922
20170922
20170922
20170922
eg. RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC)
SELECT C.DEPTNO, C.ENAME, C.SAL, C.RANK
FROM (SELECT A.DEPTNO, A.ENAME, A.SAL,
(SELECT count(*) + 1
FROM EMP B
WHERE B.DEPTNO = A.DEPTNO
AND B.SAL > A.SAL) AS RANK
FROM EMP A) C
ORDER BY C.DEPTNO ASC, C.RANK ASC;
eg. DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC)
SELECT C.DEPTNO, C.ENAME, C.SAL, C.RANK
FROM (SELECT A.DEPTNO, A.ENAME, A.SAL,
(SELECT count(DISTINCT SAL) + 1
FROM EMP B
WHERE B.DEPTNO = A.DEPTNO
AND B.SAL > A.SAL) AS RANK
FROM EMP A) C
ORDER BY C.DEPTNO ASC, C.RANK ASC;
eg. ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC)
SELECT A.DEPTNO, A.ENAME, A.SAL,
ROWNUM - (SELECT COUNT(*) FROM EMP B
WHERE B.DEPTNO < A.DEPTNO) AS RNO
FROM (SELECT DEPTNO, ENAME, SAL
FROM EMP
ORDER BY DEPTNO ASC, SAL DESC) A;
eg. MAX(SAL) OVER(PARTITION BY MGR)
SELECT A.MGR, A.ENAME, A.SAL
FROM EMP A, (SELECT MGR, MAX(SAL) AS MAX_SAL
FROM EMP
GROUP BY MGR) B
WHERE (A.MGR = B.MGR OR A.MGR IS NULL)
AND A.SAL = B.MAX_SAL;
eg. FIRST_VALUE(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC
ROWS UNBOUNDED PRECEDING)
SELECT D.DEPTNO, D.ENAME, D.SAL, C.LGG AS FIRST_NAME
FROM
(SELECT A.DEPTNO,
LISTAGG(A.ENAME,',') WITHIN GROUP(ORDER BY A.ENAME DESC) AS LGG
FROM EMP A, (SELECT DEPTNO, MAX(SAL) AS M_SAL
FROM EMP
GROUP BY DEPTNO) B
WHERE A.DEPTNO = B.DEPTNO
AND A.SAL = B.M_SAL
GROUP BY A.DEPTNO) C, EMP D
WHERE C.DEPTNO = D.DEPTNO
ORDER BY 1 ASC, 3 DESC;
eg. TO_CHAR(LEAD(HIREDATE,1,TO_DATE('99991231','YYYYMMDD'))
OVER(ORDER BY HIREDATE),'YYYYMMDD')
SELECT A.ENAME, A.HIREDATE, NVL(B.HIREDATE,'99991231') AS "NextHire"
FROM (SELECT ROWNUM AS SEQ,
ENAME, HIREDATE
FROM (SELECT ENAME, TO_CHAR(HIREDATE,'YYYYMMDD') HIREDATE
FROM EMP
ORDER BY 2)) A,
(SELECT ROWNUM AS SEQ,
ENAME, HIREDATE
FROM (SELECT ENAME, TO_CHAR(HIREDATE,'YYYYMMDD') HIREDATE
FROM EMP
ORDER BY 2))B
WHERE A.SEQ + 1 = B.SEQ(+);
* User defined function 사용
CREATE OR REPLACE FUNCTION F_NEXT(A_YMD IN VARCHAR2)
RETURN VARCHAR2
IS
H_YMD VARCHAR2(10) := NULL;
BEGIN
SELECT TO_CHAR(YMD,'YYYY-MM-DD') INTO H_YMD
FROM (SELECT HIREDATE AS YMD
FROM EMP
WHERE TO_CHAR(HIREDATE,'YYYYMMDD') > A_YMD
ORDER BY HIREDATE ASC)
WHERE ROWNUM = 1;
RETURN H_YMD;
END;
/
SELECT ENAME, TO_CHAR(HIREDATE,'YYYY-MM-DD') HIREDATE,
F_NEXT(TO_CHAR(HIREDATE,'YYYYMMDD')) AS NEXT_YMD
FROM (SELECT ENAME, HIREDATE FROM EMP
ORDER BY HIREDATE ASC);
eg. RATIO_TO_REPORT(SAL) OVER()
* Scalar subquery
SELECT ENAME, SAL,
SAL/(SELECT SUM(SAL) FROM EMP WHERE JOB = 'SALESMAN') AS RR
FROM EMP
WHERE JOB = 'SALESMAN';
* Self join
SELECT A.ENAME, A.SAL, A.SAL/B.T_SAL
FROM EMP A, (SELECT JOB, SUM(SAL) AS T_SAL
FROM EMP
WHERE JOB = 'SALESMAN'
GROUP BY JOB) B
WHERE A.JOB = B.JOB;
Multi-table INSERT
===================
* INSERT ... SELECT ... 와 같은 DML 명령을 사용해서
어떤 테이블에 있는 데이터를 다른 테이블에 전송하고자 함.
만약에 원천 테이블의 데이터를 전송해야 할 목표 테이블이
10개라고 가정하면 INSERT... SELECT ... 를 10번 수행해야 함.
→ 한 번에 할 수 있음!!!
① 무조건부 INSERT ALL
② 조건부 INSRET ALL
③ 조건부 INSERT FIRST
MERGE
=====
- MERGE문은 조건에 따라서 데이터의 입력/수정/삭제 작업을 한번에 할 수 있다.
- 해당 행이 존재하는 경우 UPDATE(DELETE포함 할 수 있음)를 수행하고, 새로운
row일 경우 INSERT를 수행한다.
- 대상 테이블에 대한 UPDATE/INSERT 조건은 ON절에 의해 결정된다.
- MERGE문에서 CLOB 사용시 업데이트 할 내용이 2000bytes가 넘을 때
ORA-00600 오류가 발생하며, patch set 11.2.0.2 버전으로 해결할 수 있다.
* 흔히 보면, 데이터가 존재하는 경우 UPDATE를 수행하고 반면에
데이터가 없을 경우엔 INSERT를 수행하는 작업들이 많이 있음.
- COUNT(*) 사용에 의한 if조건 처리
- Correlated subquery 활용
① EXISTS를 갖는 UPDATE문
② NOT EXISTS를 갖는 INSERT문
→ 한 번에 할 수 있음!!!
바이낸스 코인 거래수수료 25% 평생할인받기
바이낸스 추천코드로 가입하고 수수료 25% 평생 할인 받으세요! (바이낸스 가입 레퍼럴코드, 추천
바이낸스 추천코드로 가입하고 수수료 25% 평생 할인 받으세요! 바이낸스 가입코드, 레퍼럴코드, 추천코드, 할인코드 : ( 286562663 ) 아래 링크를 통해 가입시 25% 할인 받을 수 있습니다 http
pink24.tistory.com
비트코인 바이낸스 거래소 선물거래, 마진거래방법 가이드 - 바이낸스 회원가입(계정생성)
비트코인 바이낸스 선물거래, 마진거래 방법 feat.바이낸스 회원가입
바이낸스 거래소 선물 및 마진거래 방법! ------------------------------------------------------------------------ 1. 바이낸스 회원가입 2. 국내코인거래소에서 코인을 구매 후 바이낸스 지갑으로 전송. 3...
pink24.tistory.com
비트코인 바이낸스 마진거래방법, 선물거래방법 - USDT
바이낸스 선물, 마진거래 방법 (USDT)
Usdt 거래에 필요한 기존 단계들은 아래 링크드린 이전 포스팅을 참고해주세요 https://pink24.tistory.com/9 비트코인 바이낸스 선물거래, 마진거래 방법 feat.바이낸스 회원가입 바이낸스 거래소 선물
pink24.tistory.com