๊ธฐ๋ณธ DML ํŠœ๋‹

Last modified: 2026-04-22 01:17:36 +0900 View History

DML ์„ฑ๋Šฅ์— ์˜ํ–ฅ์„ ๋ฏธ์น˜๋Š” ์š”์†Œ

์ธ๋ฑ์Šค์™€ DML ์„ฑ๋Šฅ

img
img
SQL> create table source
  2  as
  3  select b.no, a.*
  4  from   (select * from emp where rownum <= 10) a
  5        ,(select rownum as no from dual connect by level <= 100000) b;

SQL> create table target
  2  as
  3  select * from source where 1 = 2;

SQL> alter table target add
  2  constraint target_pk primary key(no, empno);

SQL> set timing on;
SQL> insert into target
  2  select * from source;

1000000 ๊ฐœ์˜ ํ–‰์ด ๋งŒ๋“ค์–ด์กŒ์Šต๋‹ˆ๋‹ค.

๊ฒฝ   ๊ณผ: 00:00:04.95

SQL> truncate table target;

SQL> create index target_x1 on target(ename);

SQL> create index target_x2 on target(deptno, mgr);

SQL> insert into target
  2  select * from source;

1000000 ๊ฐœ์˜ ํ–‰์ด ๋งŒ๋“ค์–ด์กŒ์Šต๋‹ˆ๋‹ค.

๊ฒฝ   ๊ณผ: 00:00:38.98

๋ฌด๊ฒฐ์„ฑ ์ œ์•ฝ๊ณผ DML ์„ฑ๋Šฅ

SQL> drop index target_x1;

SQL> drop index target_x2;

SQL> alter table target drop primary key;

SQL> truncate table target;

SQL> insert into target
  2  select * from source;

1000000 ๊ฐœ์˜ ํ–‰์ด ๋งŒ๋“ค์–ด์กŒ์Šต๋‹ˆ๋‹ค.

๊ฒฝ   ๊ณผ: 00:00:01.32
PK ์ œ์•ฝ/์ธ๋ฑ์Šค ์ผ๋ฐ˜ ์ธ๋ฑ์Šค(2๊ฐœ) ์†Œ์š”์‹œ๊ฐ„
O O 38.98์ดˆ
O X 4.95์ดˆ
X X 1.32์ดˆ

์กฐ๊ฑด์ ˆ๊ณผ DML ์„ฑ๋Šฅ

SQL> set autotrace traceonly exp

SQL> update emp set sal = sal * 1.1 where deptno = 40;

---------------------------------------------------------------------------
| Id | Operation        | Name    | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|  0 | UPDATE STATEMENT |         |    1 |     7 |     2   (0)| 00:00:01 |
|  1 |  UPDATE          | EMP     |      |       |            |          |
|  2 |   INDEX RANGE SCAN| EMP_X01 |    1 |     7 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL> delete from emp where deptno = 40;

---------------------------------------------------------------------------
| Id | Operation        | Name    | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|  0 | DELETE STATEMENT |         |    1 |    13 |     1   (0)| 00:00:01 |
|  1 |  DELETE          | EMP     |      |       |            |          |
|  2 |   INDEX RANGE SCAN| EMP_X01 |    1 |    13 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

์„œ๋ธŒ์ฟผ๋ฆฌ์™€ DML ์„ฑ๋Šฅ

SQL> update emp e set sal = sal * 1.1
  2  where exists
  3    (select 'x' from dept where deptno = e.deptno and loc = 'CHICAGO');

---------------------------------------------------------------------------------
| Id | Operation                      | Name     | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
|  0 | UPDATE STATEMENT               |          |    5 |    90 |     5 (20)|
|  1 |  UPDATE                        | EMP      |      |       |           |
|  2 |   NESTED LOOPS                 |          |    5 |    90 |     5 (20)|
|  3 |    SORT UNIQUE                 |          |    1 |    11 |     2  (0)|
|  4 |     TABLE ACCESS BY INDEX ROWID| DEPT     |    1 |    11 |     2  (0)|
|  5 |      INDEX RANGE SCAN          | DEPT_X01 |    1 |       |     1  (0)|
|  6 |    INDEX RANGE SCAN            | EMP_X01  |    5 |    35 |     1  (0)|
---------------------------------------------------------------------------------

SQL> delete from emp e
  2  where exists
  3    (select 'x' from dept where deptno = e.deptno and loc = 'CHICAGO');

---------------------------------------------------------------------------------
| Id | Operation                      | Name     | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
|  0 | DELETE STATEMENT               |          |    5 |   120 |     4 (25)|
|  1 |  DELETE                        | EMP      |      |       |           |
|  2 |   HASH JOIN SEMI               |          |    5 |   120 |     4 (25)|
|  3 |    INDEX FULL SCAN             | EMP_X01  |   14 |   182 |     1  (0)|
|  4 |    TABLE ACCESS BY INDEX ROWID| DEPT     |    1 |    11 |     2  (0)|
|  5 |     INDEX RANGE SCAN           | DEPT_X01 |    1 |       |     1  (0)|
---------------------------------------------------------------------------------

SQL> insert into emp
  2  select e.*
  3  from   emp_t e
  4  where exists
  5         (select 'x' from dept where deptno = e.deptno and loc = 'CHICAGO');

---------------------------------------------------------------------------------
| Id | Operation                      | Name     | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
|  0 | INSERT STATEMENT               |          |    5 |   490 |     6 (17)|
|  1 |  LOAD TABLE CONVENTIONAL       | EMP      |      |       |           |
|  2 |   HASH JOIN SEMI               |          |    5 |   490 |     6 (17)|
|  3 |    TABLE ACCESS FULL           | EMP_T    |   14 |  1218 |     3  (0)|
|  4 |    TABLE ACCESS BY INDEX ROWID| DEPT     |    1 |    11 |     2  (0)|
|  5 |     INDEX RANGE SCAN           | DEPT_X01 |    1 |       |     1  (0)|
---------------------------------------------------------------------------------

Redo ๋กœ๊น…๊ณผ DML ์„ฑ๋Šฅ

Undo๋กœ๊น…๊ณผ DML ์„ฑ๋Šฅ

img

Undo์˜ ์šฉ๋„์™€ MVCC ๋ชจ๋ธ

Lock๊ณผ DML ์„ฑ๋Šฅ

์ปค๋ฐ‹๊ณผ DML ์„ฑ๋Šฅ

๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค Call๊ณผ ์„ฑ๋Šฅ

๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค Call

select cust_nm, birthday from customer where cust_id = :cust_id

call       count       cpu    elapsed       disk      query    current       rows
-------  -------  --------  ---------  ---------  ---------  ---------  ---------
Parse          1      0.00       0.00          0          0          0          0
Execute     5000      0.18       0.14          0          0          0          0
Fetch       5000      0.21       0.25          0      20000          0      50000
-------  -------  --------  ---------  ---------  ---------  ---------  ---------
total      10001      0.39       0.40          0      20000          0      50000

Misses in library cache during parse: 1
img

์ ˆ์ฐจ์  ๋ฃจํ”„ ์ฒ˜๋ฆฌ

SQL> create table source
  2  as
  3  select b.no, a.*
  4  from   (select * from emp where rownum <= 10) a
  5        ,(select rownum as no from dual connect by level <= 100000) b;

SQL> create table target
  2  as
  3  select * from source where 1 = 2;

-- SOURCE ํ…Œ์ด๋ธ”์— ๋ ˆ์ฝ”๋“œ 1M๊ฐœ๊ฐ€ ์ž…๋ ฅ๋ผ ์žˆ์„ ๋•Œ

SQL> set timing on;

SQL> begin
  2      for s in (select * from source)
  3      loop
  4          insert into target values ( s.no, s.empno, s.ename, s.job, s.mgr
  5                                   , s.hiredate, s.sal, s.comm, s.deptno );
  6      end loop;
  7
  8      commit;
  9  end;
 10  /

๊ฒฝ   ๊ณผ: 00:00:29.31

-- ๋ฃจํ”„ ์ฒ˜๋ฆฌ๋ฅผ ๋ชจ๋‘ ์™„๋ฃŒํ•˜๊ณ  ์ปค๋ฐ‹ํ•˜์ง€ ์•Š๊ณ , ๋ฃจํ”„ ๋‚ด๋ถ€์—์„œ ์ปค๋ฐ‹

SQL> begin
  2    for s in (select * from source)
  3    loop
  4
  5        insert into target values ( s.no, s.empno, s.ename, s.job, s.mgr
  6                                 , s.hiredate, s.sal, s.comm, s.deptno );
  7
  8        commit;
  9
 10    end loop;
 11
 12  end;
 13  /

๊ฒฝ   ๊ณผ: 00:01:00.50

-- ๋ฃจํ”„ ์•ˆ์ชฝ์— ์•„๋ž˜ ์ฝ”๋“œ ์ถ”๊ฐ€
if mod(i, 100000) = 0 then  -- 10๋งŒ ๋ฒˆ์— ํ•œ ๋ฒˆ์”ฉ ์ปค๋ฐ‹
    commit;
end if;

public class JavaLoopQuery {
    public void execute() throws Exception {
        String SQLStmt = "select no, empno, ename, job, mgr"
                       + ", to_char(hiredate, 'yyyymmdd hh24miss'), sal, comm, deptno "
                       + "from   source";

        PreparedStatement stmt = con.prepareStatement(SQLStmt);
        ResultSet rs = stmt.executeQuery();
        while(rs.next()){
            long    no       = rs.getLong(1);
            long    empno    = rs.getLong(2);
            String  ename    = rs.getString(3);
            String  job      = rs.getString(4);
            Integer mgr      = rs.getInt(5);
            String  hiredate = rs.getString(6);
            long    sal      = rs.getLong(7);
            long    comm     = rs.getLong(8);
            int     deptno   = rs.getInt(9);

            insertTarget(con, no, empno, ename, job, mgr, hiredate, sal, comm, deptno);
        }
        rs.close();
        stmt.close();
    }

    public void insertTarget( long    p_no
                            , long    p_empno
                            , String  p_ename
                            , String  p_job
                            , int     p_mgr
                            , String  p_hiredate
                            , long    p_sal
                            , long    p_comm
                            , int     p_deptno) throws Exception {
        String SQLStmt = "insert into target "
                       + "(no, empno, ename, job, mgr, hiredate, sal, comm, deptno) "
                       + "values (?, ?, ?, ?, ?, to_date(?, 'yyyymmdd hh24miss'), ?, ?, ?)";
        PreparedStatement st = con.prepareStatement(SQLStmt);
        st.setLong   (1, p_no      );
        st.setLong   (2, p_empno   );
        st.setString (3, p_ename   );
        st.setString (4, p_job     );
        st.setInt    (5, p_mgr     );
        st.setString (6, p_hiredate);
        st.setLong   (7, p_sal     );
        st.setLong   (8, p_comm    );
        st.setInt    (9, p_deptno  );
        st.execute();
        st.close();
        }
        ...
        ...
    }

/* ์ˆ˜ํ–‰์‹œ๊ฐ„ 218.392์ดˆ */

One SQL์˜ ์ค‘์š”์„ฑ

SQL> insert into target
  2  select * from source;

1000000 ๊ฐœ์˜ ํ–‰์ด ๋งŒ๋“ค์–ด์กŒ์Šต๋‹ˆ๋‹ค.

๊ฒฝ   ๊ณผ: 00:00:01.46

Array Processing ํ™œ์šฉ

SQL> declare
  2    cursor c is select * from source;
  3    type typ_source is table of c%rowtype;
  4    l_source typ_source;
  5
  6    l_array_size number default 10000;
  7
  8    procedure insert_target( p_source  in typ_source) is
  9    begin
 10      forall i in p_source.first..p_source.last
 11        insert into target values p_source(i);
 12    end insert_target;
 13
 14 begin
 15   open c;
 16   loop
 17     fetch c bulk collect into l_source limit l_array_size;
 18
 19     insert_target( l_source );
 20
 21     exit when c%notfound;
 22   end loop;
 23
 24   close c;
 25
 26   commit;
 27 end;
 28 /

๊ฒฝ   ๊ณผ: 00:00:03.99

public class JavaArrayProcessing {
    public void execute() throws Exception {
        int    arraySize = 10000;
        long   no       [] = new long  [arraySize];
        long   empno    [] = new long  [arraySize];
        String ename    [] = new String[arraySize];
        String job      [] = new String[arraySize];
        int    mgr      [] = new int   [arraySize];
        String hiredate [] = new String[arraySize];
        long   sal      [] = new long  [arraySize];
        long   comm     [] = new long  [arraySize];
        int    deptno   [] = new int   [arraySize];

        String SQLStmt = "select no, empno, ename, job, mgr"
                       + ", to_char(hiredate, 'yyyymmdd hh24miss'), sal, comm, deptno "
                       + "from   source";

        PreparedStatement st = con.prepareStatement(SQLStmt);
        st.setFetchSize(arraySize);

        ResultSet rs = st.executeQuery();

        int i = 0;
        while(rs.next()){
            no      [i] = rs.getLong(1);
            empno   [i] = rs.getLong(2);
            ename   [i] = rs.getString(3);
            job     [i] = rs.getString(4);
            mgr     [i] = rs.getInt(5);
            hiredate[i] = rs.getString(6);
            sal     [i] = rs.getLong(7);
            comm    [i] = rs.getLong(8);
            deptno  [i] = rs.getInt(9);

            if(++i == arraySize) {    // 10,000๋ฒˆ์— ํ•œ ๋ฒˆ์”ฉ insertTarget ์‹คํ–‰
                insertTarget(i, no, empno, ename, job, mgr, hiredate, sal, comm, deptno);
                i = 0;
            }
        }

        if(i > 0) insertTarget(i, no, empno, ename, job, mgr, hiredate, sal, comm, deptno);

        rs.close();
        st.close();
    }

    public void insertTarget( int      length
                            , long []  p_no
                            , long []  p_empno
                            , String[] p_ename
                            , String[] p_job
                            , int  []  p_mgr
                            , String[] p_hiredate
                            , long []  p_sal
                            , long []  p_comm
                            , int  []  p_deptno) throws Exception {
        String SQLStmt = "insert into target "
                    + "(no, empno, ename, job, mgr, hiredate, sal, comm, deptno) "
                    + "values (?, ?, ?, ?, ?, to_date(?, 'yyyymmdd hh24miss'), ?, ?, ?)";

        PreparedStatement st = con.prepareStatement(SQLStmt);

        for(int i=0; i < length; i++){
            st.setLong   (1, p_no      [i]);
            st.setLong   (2, p_empno   [i]);
            st.setString (3, p_ename   [i]);
            st.setString (4, p_job     [i]);
            st.setInt    (5, p_mgr     [i]);
            st.setString (6, p_hiredate[i]);
            st.setLong   (7, p_sal     [i]);
            st.setLong   (8, p_comm    [i]);
            st.setInt    (9, p_deptno  [i]);
            st.addBatch();    // insert ํ•  ๊ฐ’๋“ค์„ ๋ฐฐ์—ด์— ์ €์žฅ
        };
        st.executeBatch();   // ๋ฐฐ์—ด์— ์ €์žฅ๋œ ๊ฐ’์„ ํ•œ ๋ฒˆ์— insert
        st.close();
    }
...
...
}

/* ์‹คํ–‰์‹œ๊ฐ„ 11.813์ดˆ */

์ธ๋ฑ์Šค ๋ฐ ์ œ์•ฝ ํ•ด์ œ๋ฅผ ํ†ตํ•œ ๋Œ€๋Ÿ‰ DML ํŠœ๋‹

-- ๋ฐ์ดํ„ฐ 1000๋งŒ๊ฑด ์ถ”๊ฐ€ ๋ฐ ์ธ๋ฑ์Šค ์„ค์ •

SQL> create table source
  2  as
  3  select b.no, a.*
  4  from   (select * from emp where rownum <= 10) a
  5        ,(select rownum as no from dual connect by level <= 1000000) b;

SQL> create table target
  2  as
  3  select * from source where 1 = 2;

SQL> alter table target add
  2  constraint target_pk primary key(no, empno);

SQL> create index target_x1 on target(ename);

SQL> set timing on;

SQL> insert /*+ append */ into target
  2  select * from source;

10000000 ๊ฐœ์˜ ํ–‰์ด ๋งŒ๋“ค์–ด์กŒ์Šต๋‹ˆ๋‹ค.

๊ฒฝ   ๊ณผ: 00:01:19.10

SQL> commit;

PK ์ œ์•ฝ๊ณผ ์ธ๋ฑ์Šค ํ•ด์ œ1 - PK ์ œ์•ฝ์— Unique ์ธ๋ฑ์Šค๋ฅผ ์‚ฌ์šฉํ•œ ๊ฒฝ์šฐ

-- PK์ œ์•ฝ๊ณผ ์ธ๋ฑ์Šค ํ•ด์ œ
-- ์ธ๋ฑ์Šค Drop

SQL> truncate table target;

SQL> alter table target modify constraint target_pk disable drop index;

SQL> alter index target_x1 unusable;

SQL> insert /*+ append */ into target
  2  select * from source;

10000000 ๊ฐœ์˜ ํ–‰์ด ๋งŒ๋“ค์–ด์กŒ์Šต๋‹ˆ๋‹ค.

๊ฒฝ   ๊ณผ: 00:00:05.84

SQL> commit;

SQL> alter table target modify constraint target_pk enable NOVALIDATE;

๊ฒฝ   ๊ณผ: 00:00:06.77

SQL> alter index target_x1 rebuild;

๊ฒฝ   ๊ณผ: 00:00:08.26

PK ์ œ์•ฝ๊ณผ ์ธ๋ฑ์Šค ํ•ด์ œ1 - PK ์ œ์•ฝ์— Non-Unique ์ธ๋ฑ์Šค๋ฅผ ์‚ฌ์šฉํ•œ ๊ฒฝ์šฐ

SQL> set timing off;
SQL> truncate table target;

SQL> alter table target drop primary key drop index;

SQL> create index target_pk on target(no, empno);  -- Non-Unique ์ธ๋ฑ์Šค ์ƒ์„ฑ

SQL> alter table target add
  2  constraint target_pk primary key (no, empno)
  3  using index target_pk;        -- PK ์ œ์•ฝ์— Non-Unique ์ธ๋ฑ์Šค ์‚ฌ์šฉํ•˜๋„๋ก ์ง€์ •

SQL> alter table target modify constraint target_pk disable keep index;

SQL> alter index target_pk unusable;

SQL> alter index target_x1 unusable;

SQL> set timing on;
SQL> insert /*+ append */ into target
  2  select * from source;

10000000 ๊ฐœ์˜ ํ–‰์ด ๋งŒ๋“ค์–ด์กŒ์Šต๋‹ˆ๋‹ค.

๊ฒฝ   ๊ณผ: 00:00:05.53
SQL> commit;

๊ฒฝ   ๊ณผ: 00:00:00.00

SQL> alter index target_x1 rebuild;

๊ฒฝ   ๊ณผ: 00:00:07.24
SQL> alter index target_pk rebuild;

๊ฒฝ   ๊ณผ: 00:00:05.27
SQL> alter table target modify constraint target_pk enable novalidate;

๊ฒฝ   ๊ณผ: 00:00:00.06

์ˆ˜์ •๊ฐ€๋Šฅ ์กฐ์ธ ๋ทฐ

update ๊ณ ๊ฐ c
set    ์ตœ์ข…๊ฑฐ๋ž˜์ผ์‹œ = (select max(๊ฑฐ๋ž˜์ผ์‹œ) from ๊ฑฐ๋ž˜
                     where  ๊ณ ๊ฐ๋ฒˆํ˜ธ = c.๊ณ ๊ฐ๋ฒˆํ˜ธ
                     and    ๊ฑฐ๋ž˜์ผ์‹œ >= trunc(add_months(sysdate, -1)))
     , ์ตœ๊ทผ๊ฑฐ๋ž˜ํšŸ์ˆ˜ = (select count(*) from ๊ฑฐ๋ž˜
                     where  ๊ณ ๊ฐ๋ฒˆํ˜ธ = c.๊ณ ๊ฐ๋ฒˆํ˜ธ
                     and    ๊ฑฐ๋ž˜์ผ์‹œ >= trunc(add_months(sysdate, -1)))
     , ์ตœ๊ทผ๊ฑฐ๋ž˜๊ธˆ์•ก = (select sum(๊ฑฐ๋ž˜๊ธˆ์•ก) from ๊ฑฐ๋ž˜
                     where  ๊ณ ๊ฐ๋ฒˆํ˜ธ = c.๊ณ ๊ฐ๋ฒˆํ˜ธ
                     and    ๊ฑฐ๋ž˜์ผ์‹œ >= trunc(add_months(sysdate, -1)))
where exists (select 'x' from ๊ฑฐ๋ž˜
              where  ๊ณ ๊ฐ๋ฒˆํ˜ธ = c.๊ณ ๊ฐ๋ฒˆํ˜ธ
              and    ๊ฑฐ๋ž˜์ผ์‹œ >= trunc(add_months(sysdate, -1)))

-- ์ด๋ ‡๊ฒŒ ์ˆ˜์ • ๊ฐ€๋Šฅ
update ๊ณ ๊ฐ c
set   (์ตœ์ข…๊ฑฐ๋ž˜์ผ์‹œ, ์ตœ๊ทผ๊ฑฐ๋ž˜ํšŸ์ˆ˜, ์ตœ๊ทผ๊ฑฐ๋ž˜๊ธˆ์•ก) =
      (select max(๊ฑฐ๋ž˜์ผ์‹œ), count(*), sum(๊ฑฐ๋ž˜๊ธˆ์•ก)
       from   ๊ฑฐ๋ž˜
       where  ๊ณ ๊ฐ๋ฒˆํ˜ธ = c.๊ณ ๊ฐ๋ฒˆํ˜ธ
       and    ๊ฑฐ๋ž˜์ผ์‹œ >= trunc(add_months(sysdate, -1)))
where exists (select 'x' from ๊ฑฐ๋ž˜
              where  ๊ณ ๊ฐ๋ฒˆํ˜ธ = c.๊ณ ๊ฐ๋ฒˆํ˜ธ
              and    ๊ฑฐ๋ž˜์ผ์‹œ >= trunc(add_months(sysdate, -1)))

update ๊ณ ๊ฐ c
set    (์ตœ์ข…๊ฑฐ๋ž˜์ผ์‹œ, ์ตœ๊ทผ๊ฑฐ๋ž˜ํšŸ์ˆ˜, ์ตœ๊ทผ๊ฑฐ๋ž˜๊ธˆ์•ก) =
       (select max(๊ฑฐ๋ž˜์ผ์‹œ), count(*), sum(๊ฑฐ๋ž˜๊ธˆ์•ก)
        from   ๊ฑฐ๋ž˜
        where  ๊ณ ๊ฐ๋ฒˆํ˜ธ = c.๊ณ ๊ฐ๋ฒˆํ˜ธ
        and    ๊ฑฐ๋ž˜์ผ์‹œ >= trunc(add_months(sysdate, -1)))
where exists (select /*+ unnest hash_sj */ 'x' from ๊ฑฐ๋ž˜
              where  ๊ณ ๊ฐ๋ฒˆํ˜ธ = c.๊ณ ๊ฐ๋ฒˆํ˜ธ
              and    ๊ฑฐ๋ž˜์ผ์‹œ >= trunc(add_months(sysdate, -1)))

update ๊ณ ๊ฐ c
set    (์ตœ์ข…๊ฑฐ๋ž˜์ผ์‹œ, ์ตœ๊ทผ๊ฑฐ๋ž˜ํšŸ์ˆ˜, ์ตœ๊ทผ๊ฑฐ๋ž˜๊ธˆ์•ก) =
       (select nvl(max(๊ฑฐ๋ž˜์ผ์‹œ), c.์ตœ์ข…๊ฑฐ๋ž˜์ผ์‹œ)
             , decode(count(*), 0, c.์ตœ๊ทผ๊ฑฐ๋ž˜ํšŸ์ˆ˜, count(*))
             , nvl(sum(๊ฑฐ๋ž˜๊ธˆ์•ก), c.์ตœ๊ทผ๊ฑฐ๋ž˜๊ธˆ์•ก)
        from   ๊ฑฐ๋ž˜
        where  ๊ณ ๊ฐ๋ฒˆํ˜ธ = c.๊ณ ๊ฐ๋ฒˆํ˜ธ
        and    ๊ฑฐ๋ž˜์ผ์‹œ >= trunc(add_months(sysdate, -1)))

์ˆ˜์ •๊ฐ€๋Šฅ ์กฐ์ธ ๋ทฐ

update
( select /*+ ordered use_hash(c) no_merge(t) */
         c.์ตœ์ข…๊ฑฐ๋ž˜์ผ์‹œ, c.์ตœ๊ทผ๊ฑฐ๋ž˜ํšŸ์ˆ˜, c.์ตœ๊ทผ๊ฑฐ๋ž˜๊ธˆ์•ก
       , t.๊ฑฐ๋ž˜์ผ์‹œ, t.๊ฑฐ๋ž˜ํšŸ์ˆ˜, t.๊ฑฐ๋ž˜๊ธˆ์•ก
  from   (select ๊ณ ๊ฐ๋ฒˆํ˜ธ
               , max(๊ฑฐ๋ž˜์ผ์‹œ) ๊ฑฐ๋ž˜์ผ์‹œ, count(*) ๊ฑฐ๋ž˜ํšŸ์ˆ˜, sum(๊ฑฐ๋ž˜๊ธˆ์•ก) ๊ฑฐ๋ž˜๊ธˆ์•ก
          from   ๊ฑฐ๋ž˜
          where  ๊ฑฐ๋ž˜์ผ์‹œ >= trunc(add_months(sysdate, -1))
          group by ๊ณ ๊ฐ๋ฒˆํ˜ธ) t
       , ๊ณ ๊ฐ c
  where  c.๊ณ ๊ฐ๋ฒˆํ˜ธ = t.๊ณ ๊ฐ๋ฒˆํ˜ธ
)
set ์ตœ์ข…๊ฑฐ๋ž˜์ผ์‹œ = ๊ฑฐ๋ž˜์ผ์‹œ
  , ์ตœ๊ทผ๊ฑฐ๋ž˜ํšŸ์ˆ˜ = ๊ฑฐ๋ž˜ํšŸ์ˆ˜
  , ์ตœ๊ทผ๊ฑฐ๋ž˜๊ธˆ์•ก = ๊ฑฐ๋ž˜๊ธˆ์•ก

SQL> create table emp  as select * from scott.emp;
SQL> create table dept as select * from scott.dept;

SQL> create or replace view EMP_DEPT_VIEW as
  2  select e.rowid emp_rid, e.*, d.rowid dept_rid, d.dname, d.loc
  3  from emp e, dept d
  4  where  e.deptno = d.deptno;

SQL> update EMP_DEPT_VIEW set loc = 'SEOUL' where job = 'CLERK';

-- ORA-01779: cannot modify a column which maps to a non key-preserved table ์—๋Ÿฌ ๋ฐœ์ƒ
SQL> update EMP_DEPT_VIEW set comm = nvl(comm, 0) + (sal * 0.1) where sal <= 1500;

-- ORA-01752: cannot delete from view without exactly one key-preserved table ์—๋Ÿฌ ๋ฐœ์ƒ
SQL> delete from EMP_DEPT_VIEW where job = 'CLERK';

SQL> alter table dept add constraint dept_pk primary key(deptno);

SQL> update EMP_DEPT_VIEW set comm = nvl(comm, 0) + (sal * 0.1) where sal <= 1500;

4 rows updated.

ํ‚ค ๋ณด์กด ํ…Œ์ด๋ธ”

SQL> select ROWID, emp_rid, dept_rid, empno, deptno from EMP_DEPT_VIEW;

ROWID              EMP_RID            DEPT_RID           EMPNO   DEPTNO
------------------ ------------------ ------------------ ------ --------
AAAMt4AAGAAAEWSAAA AAAMt4AAGAAAEWSAAA AAAMt5AAGAAAEWaAAB   7369       20
AAAMt4AAGAAAEWSAAB AAAMt4AAGAAAEWSAAB AAAMt5AAGAAAEWaAAC   7499       30
AAAMt4AAGAAAEWSAAC AAAMt4AAGAAAEWSAAC AAAMt5AAGAAAEWaAAC   7521       30
AAAMt4AAGAAAEWSAAD AAAMt4AAGAAAEWSAAD AAAMt5AAGAAAEWaAAB   7566       20
AAAMt4AAGAAAEWSAAE AAAMt4AAGAAAEWSAAE AAAMt5AAGAAAEWaAAC   7654       30
AAAMt4AAGAAAEWSAAG AAAMt4AAGAAAEWSAAG AAAMt5AAGAAAEWaAAA   7782       10
......             ......             ......             ...      ...

14 rows selected.

SQL> alter table dept drop primary key;

-- ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
SQL> select rowid, emp_rid, dept_rid, empno, deptno from EMP_DEPT_VIEW;

ORA-01779 ์˜ค๋ฅ˜ ํšŒํ”ผ

SQL> alter table dept add avg_sal number(7,2);

-- ORA-01779: cannot modify a column which maps to a non key-preserved table
SQL> update
  2  (select d.deptno, d.avg_sal as d_avg_sal, e.avg_sal as e_avg_sal
  3   from (select deptno, round(avg(sal), 2) avg_sal from emp group by deptno) e
  4      , dept d
  5   where d.deptno = e.deptno )
  6  set d_avg_sal = e_avg_sal ;

SQL> update /*+ bypass_ujvc */
  2  (select d.deptno, d.avg_sal d_avg_sal, e.avg_sal e_avg_sal
  3   from (select deptno, round(avg(sal), 2) avg_sal from emp group by deptno) e
  4      , dept d
  5   where d.deptno = e.deptno )
  6  set d_avg_sal = e_avg_sal ;

-- ์–ด๋–ค ๋ฒ„์ „์—์„œ๋„ ์‹คํ–‰ ๋ถˆ๊ฐ€
update (
  select o.์ฃผ๋ฌธ๊ธˆ์•ก, o.ํ• ์ธ๊ธˆ์•ก, c.๊ณ ๊ฐ๋“ฑ๊ธ‰
  from   ์ฃผ๋ฌธ_t o, ๊ณ ๊ฐ_t c
  where  o.๊ณ ๊ฐ๋ฒˆํ˜ธ = c.๊ณ ๊ฐ๋ฒˆํ˜ธ
  and    o.์ฃผ๋ฌธ๊ธˆ์•ก >= 1000000
  and    c.๊ณ ๊ฐ๋“ฑ๊ธ‰ = 'A')
set ํ• ์ธ๊ธˆ์•ก = ์ฃผ๋ฌธ๊ธˆ์•ก * 0.2, ์ฃผ๋ฌธ๊ธˆ์•ก = ์ฃผ๋ฌธ๊ธˆ์•ก * 0.8

-- 12c์—์„œ๋Š” ์•„๋ž˜์™€ ๊ฐ™์ด ๊ฐ€๋Šฅ
update (
  select o.์ฃผ๋ฌธ๊ธˆ์•ก, o.ํ• ์ธ๊ธˆ์•ก
  from   ์ฃผ๋ฌธ_t o
       , (select ๊ณ ๊ฐ๋ฒˆํ˜ธ from ๊ณ ๊ฐ_t where ๊ณ ๊ฐ๋“ฑ๊ธ‰ = 'A' group by ๊ณ ๊ฐ๋ฒˆํ˜ธ) c
  where  o.๊ณ ๊ฐ๋ฒˆํ˜ธ = c.๊ณ ๊ฐ๋ฒˆํ˜ธ
  and    o.์ฃผ๋ฌธ๊ธˆ์•ก >= 1000000)
set ํ• ์ธ๊ธˆ์•ก = ์ฃผ๋ฌธ๊ธˆ์•ก * 0.2, ์ฃผ๋ฌธ๊ธˆ์•ก = ์ฃผ๋ฌธ๊ธˆ์•ก * 0.8

MERGE๋ฌธ ํ™œ์šฉ

Optional Clauses

-- UPDATE/INSERT๋ฅผ ์„ ํƒ์ ์œผ๋กœ ์ฒ˜๋ฆฌ
merge into customer t using customer_delta s on (t.cust_id = s.cust_id)
when matched then update
    set t.cust_nm = s.cust_nm, t.email = s.email, ... ;

merge into customer t using customer_delta s on (t.cust_id = s.cust_id)
when not matched then insert
    (cust_id, cust_nm, email, tel_no, region, addr, reg_dt) values
    (s.cust_id, s.cust_nm, s.email, s.tel_no, s.region, s.addr, s.reg_dt);

-- ์ˆ˜์ •๊ฐ€๋Šฅ ์กฐ์ธ๋ทฐ
update
  (select d.deptno, d.avg_sal as d_avg_sal, e.avg_sal as e_avg_sal
   from (select deptno, round(avg(sal), 2) avg_sal from emp group by deptno) e
      , dept d
   where d.deptno = e.deptno )
set d_avg_sal = e_avg_sal ;

-- MERGE๋กœ ๋Œ€์ฒด ๊ฐ€๋Šฅ
merge into dept d
using (select deptno, round(avg(sal), 2) avg_sal from emp group by deptno) e
on (d.deptno = e.deptno)
when matched then update set d.avg_sal = e_avg_sal;

Conditional Operations

-- on ์ ˆ์— ๊ธฐ์ˆ ํ•œ ์กฐ์ธ๋ฌธ ์™ธ์— ์ถ”๊ฐ€๋กœ ์กฐ๊ฑด์ ˆ ๊ธฐ์ˆ  ๊ฐ€๋Šฅ
merge into customer t using customer_delta s on (t.cust_id = s.cust_id)
when matched then update
    set t.cust_nm = s.cust_nm, t.email = s.email, ...
    where reg_dt >= to_date('20000101', 'yyyymmdd')
when not matched then insert
    (cust_id, cust_nm, email, tel_no, region, addr, reg_dt) values
    (s.cust_id, s.cust_nm, s.email, s.tel_no, s.region, s.addr, s.reg_dt)
    where reg_dt < trunc(sysdate) ;

DELETE Clause

merge into customer t using customer_delta s on (t.cust_id = s.cust_id)
when matched then
    update set t.cust_nm = s.cust_nm, t.email = s.email, ...
    delete where t.withdraw_dt is not null  -- ํƒˆํ‡ด์ผ์‹œ๊ฐ€ null์ด ์•„๋‹Œ ๋ ˆ์ฝ”๋“œ ์‚ญ์ œ
when not matched then insert
    (cust_id, cust_nm, email, tel_no, region, addr, reg_dt) values
    (s.cust_id, s.cust_nm, s.email, s.tel_no, s.region, s.addr, s.reg_dt);

ํ™œ์šฉ ์˜ˆ์‹œ

-- SQL์„ ํ•ญ์ƒ 2๋ฒˆ์”ฉ ์ˆ˜ํ–‰
select count(*) into :cnt from dept where deptno = :val1;

if :cnt = 0 then
  insert into dept(deptno, dname, loc) values(:val1, :val2, :val3);
else
  update dept set dname = :val2, loc = :val3 where deptno = :val1;
end if;

-- ์ด ๊ฒฝ์šฐ, SQL์„ ์ตœ๋Œ€ 2๋ฒˆ์”ฉ ์ˆ˜ํ–‰
update dept set dname = :val2, loc = :val3 where deptno = :val1;

if sql%rowcount = 0 then
  insert into dept(deptno, dname, loc) values(:val1, :val2, :val3);
end if;

-- ์ด ๊ฒฝ์šฐ, SQL์„ ํ•œ ๋ฒˆ๋งŒ ์ˆ˜ํ–‰
merge into dept a
using (select :val1 deptno, :val2 dname, :val3 loc from dual) b
on    (b.deptno = a.deptno)
when matched then
  update set dname = b.dname, loc = b.loc
when not matched then
  insert (a.deptno, a.dname, a.loc) values (b.deptno, b.dname, b.loc);

์ˆ˜์ •๊ฐ€๋Šฅ ์กฐ์ธ ๋ทฐ vs MERGE ๋ฌธ

MERGE INTO EMP T2
USING (SELECT T.ROWID AS RID, S.ENAME
       FROM   EMP T, EMP_SRC S
       WHERE  T.EMPNO = S.EMPNO
       AND    T.ENAME <> S.ENAME) S
ON (T2.ROWID = S.RID)
WHEN MATCHED THEN UPDATE SET T2.ENAME = S.ENAME;

-- ์„ฑ๋Šฅ์„ ๊ณ ๋ คํ•˜๋ฉด, ์•„๋ž˜์™€ ๊ฐ™์ด ์ž‘์„ฑํ•ด์•ผ ํ•จ
-- ๋ฐ์ดํ„ฐ ๊ฒ€์ฆ์šฉ SELECT ๋ฌธ์„ ๋”ฐ๋กœ ํ•˜๋‚˜ ๋” ๋งŒ๋“ค์–ด์•ผ ํ•จ
MERGE INTO EMP T
USING EMP_SRC S
ON (T.EMPNO = S.EMPNO)
WHEN MATCHED THEN UPDATE SET T.ENAME = S.ENAME
WHERE T.ENAME <> S.ENAME;

-- ์ˆ˜์ •๊ฐ€๋Šฅ ์กฐ์ธ๋ทฐ๊ฐ€ ๋” ํŽธํ•  ์ˆ˜ ์žˆ์Œ
-- SELECT๋ฌธ ๋จผ์ € ๋งŒ๋“ค์–ด ๋ฐ์ดํ„ฐ ๊ฒ€์ฆ์„ ๋งˆ์นœ ํ›„, ๋ฐ”๊นฅ์— UPDATE
UPDATE (
  SELECT S.ENAME AS S_ENAME, T.ENAME AS T_ENAME
  FROM   EMP T, EMP_SRC S
  WHERE  T.EMPNO = S.EMPNO
  AND    T.ENAME <> S.ENAME
)
SET T_ENAME = S_ENAME;