Oracle/ 서브쿼리를 이용한 DML문장 & 다이렉트 로드

<서브쿼리를 이용한 DML문장>

 

• 다량의 DML작업을 수행하는 경우 서브 쿼리문을 이용
• IMSERT 작업에 대량 데이터를 입력할 시 서브쿼리를 이용하면 매우 효율적
• 메모리를 통하지않고 데이터를 직접 디스크에 입력하는 다이렉트 로드를 같이 사용하면 효율성 배가

 

서브쿼리를 이용한 DML문
INSERT INTO [+APPEND] 테이블 [NOLOGGING]
[(컬럼, 컬럼, . . )]
SELECT 문장;

- 서브쿼리(SELECT문)에 검색된 행을 입력 값으로 사용한다.
- 한번에 많은 행을 입력할수있다.
- 컬럼리스트와 SELECT문의 컬럼이 1:1로 대응 되어야 한다.
- SELECT문에 사용한 서브 쿼리문과는 달리 괄호를 쓰지 않는다.
- [+ APPEND] : 다이렉트 로드함으로 대량의 입력작업을 더 빠르게 작업한다.
- [NOLOGGING] : 로그정보를 남기지 않음으로 입력작업이 빨라진다. 그러나 장애가 발생시 복구불가능

UPDATE 테이블
SET 컬럼명 = (SELECT 문장),
    컬럼명 = (SELECT 문장), ...
[WHERE 조건];

UPDATE 테이블
SET (컬럼, 컬럼, ...) = (SELECT 문장)
[WHERE 조건];

 

- UPDATE 문의 SET 절에는 단일 행 서브쿼리문이나 다중 열 서브 쿼리문을 이용할 수 있다.
- 다중열인 경우도 반드시 결과 행은 하나여야 한다.
- 이 때 검색되는 Data는 반드시 수정되는 컬럼명과 1:1 대응돼야 한다.
- SET 절에는 단일행 서브쿼리만 허용되지만 WHERE절의 서브 쿼리는 연산자에 따라 달라진다.

 

 

-- emp2 테이블에 사번과 연봉을 입력한다
 DESC emp2;
 SELECT * FROM emp2;
 
INSERT INTO emp2(eno, asal)
SELECT eno, sal*12+NVL(comm,0) FROM emp;
   
ROLLBACK;

 

-> 데이터 타입이 일치하지 않으면 에러 발생

 

-데이터 입력이 가능한 경우

1) 데이터 타입이 같고 저장 컬럼의 크기가 입력 데이터 크기보다 클 경우

2) 데이터 타입이 달라도 자동 형변환이 가능하고 저장 컬럼의 크기가 입력 데이터 크기보다 클 경우

- '10' => 10

 

--   숫자형 => 문자형 컬럼
INSERT INTO emp2(eno, asal)
SELECT eno, hdate FROM emp;

INSERT INTO emp2(eno, asal)
SELECT eno, ename FROM emp;


--아래 경우는 자동형변환 되어 들어간다
INSERT INTO emp2(eno, asal)
SELECT eno, dno FROM emp;
 
SELECT * FROM emp2; 


--각 사원의 정보와 근무지를 emp3 테이블에 저장하라
SELECT * FROM emp3;
DESC emp3;

 

 

 

 

 

 

<다이렉트 로드>

 

• 일괄적인 대량의 DML 작업이 메모리에서 일어나면 비효율이 발생
• 이를 피하기 위해 대량 DML 작업에서는 메모리를 통하지 않고 디스크에 직접 작업을 수행
• ==>다이렉트 로드(Direct Load)
• DML 작업을 다이렉트로드하기 위해서는 /*+ APPEND */를 문장에 삽입

 

/**/

: /*는 평소에는 주석으로 사용되지만 + 기호를 붙여줌으로써 주석이 아닌 오라클에 옵션을 전달하는 용도로 사용

 

- 오라클은 안전한 시스템 운영을 위해 장애 발생 시 복구할 수 있도록 DB에 변경을 가하는 모든 작업의 내용을

Redo Log영역에 로그 정보로 보관

 

- 대량의 DML작업은 작업 성능을 위해 로그 정보를 남기지 않고 NOLOGGING 옵션을 사용 -> 장애에 대비한 백업을 필요

==> 대량의 작업에서 다이렉트 로드와 NOLOGGING 방식을 사용하면 작업 성능은 비약적으로 향상되지만 작업의 안전도는 떨어지게 된다.

 

- 다이렉트 로드를 이용해서 대량의 데이터를 테이블에 입력한 경우 트랜잭션을 마무리 하지 않고 검색하게 되면 'ORA-12838: 병렬로 수정한 후 객체를 읽거나 수정할 수 없습니다'라는 에러를 만나게 된다.

==> COMMIT 을 수행한 이후 검색

 

--서브 쿼리를 이용한 UPDATE 문을 수행한다.

SELECT *
FROM emp
WHERE ename='윤고은';

--윤고은의 급여와 보너스를 각각 김연아의 급여와 손하늘의 보너스와 동일하게 수정한다
UPDATE emp
SET sal=(SELECT sal
         FROM emp
         WHERE ename='김연아'),
    comm = (SELECT comm
            FROM emp
            WHERE ename='손하늘')
WHERE ename='윤고은';

SELECT * FROM emp;



--제갈민과 동일한 부서의 사원들의 급여를
--제갈민의 급여와 동일하게 수정한다
SELECT * FROM emp
WHERE dno = (SELECT dno
            FROM emp
            WHERE ename = '제갈민');

UPDATE emp
SET sal=(SELECT sal
            FROM emp
            WHERE ename = '제갈민')
WHERE dno = (SELECT dno
            FROM emp
            WHERE ename = '제갈민');
            

--이초록의 급여, 보너스를 김연아와 동일하게 수정한다
UPDATE emp
SET (sal, comm) =(SELECT sal, comm
         FROM emp
         WHERE ename='김연아')
WHERE ename='이초록';


ROLLBACK;

 

 

 

 

 

<Exrecise>

--1. 일반 화학 과목을 수강하는 학생의 성적을 4.5만점 기준으로 수정한다
UPDATE student  SET avr = (avr/8*9)
WHERE sno IN(SELECT sno
                      FROM score
                      WHERE cno IN (SELECT cno
                                             FROM course
                                             WHERE cname='일반화학'));



--2. 화학과 교수의 과목중에 학점이 3학점 미만인 과목을 모두 3학점으로 수정한다
UPDATE course SET st_num = 3
WHERE st_num<3
AND pno IN(SELECT pno
                      FROM professor
                      WHERE section ='화학');



--3. 학생의 기말고사 성적을 모두 st_score 테이블에 저장한다
INSERT /*+ APPEND */  INTO st_score NOLOGGING
(sno, sname, major, syear, cno, cname, result)
SELECT student.sno, sname, major, syear, course.cno, cname, result
FROM student
JOIN score ON student.sno = score.sno
JOIN course ON score.cno=course.cno;

SELECT * FROM st_score;

COMMIT;

DELETE FROM st_score;



--4. st_score 테이블에 각 학생의 평점을 학과별, 과목별로 입력한다. 
--검색할 때 대부분 학과별 검색을 수행하기 때문에 이들 데이터가 학과별로 몰려있으면 성능을 향상시킬 수 있다

INSERT /*+ APPEND */  INTO st_score NOLOGGING
(sno, sname, major, syear, cno, cname, result)
SELECT student.sno, sname, major, syear, course.cno, cname, result
FROM student
JOIN score ON student.sno = score.sno
JOIN course ON score.cno = course.cno
ORDER BY major, cname;



--5. 화학과 학생이 수강하는 과목을 강의하는 교수의 부임일자를 1년 늦도록 수정한다.

UPDATE professor SET hiredate =(hiredate+365)
WHERE pno IN(SELECT pno
                      FROM course
                      WHERE cno IN (SELECT cno 
                                              FROM st_score
                                              WHERE major='화학'));