퐁숑퐁숑

우선 MERGE 란 뜻은 무엇인가? 충돌나지 않게 합친다는 개념이다.   SVN에서 MERGE는 소스들의 충돌을 방지하고 적절하게  누락되지 않게 통합하기 위해 사용한다.  오라클에서 MERGE 또한 같은 개념이다. TABLE에 존재하는 데이터는 그대로 변경만 하고 없는 데이터는 삽입을 하여 적절하게 통합하기 위한 예약어이다. 즉,

MERGE INTO 구문은 대상 테이블 해당 KEY에 맞는 데이터가 이미 존재하면 UPDATE!!,존재하지 않으면 INSERT를 하여 테이블 ROW가 충돌나지 않으며 데이터를 UPDATE,INSERT 등의 작업을 한번에 해줄 수 있다. 

#오라클 MERGE INTO 문법#

MERGE INTO [1. 테이블 명 혹은 VIEW명] - Update또는 Insert할 테이블 명 혹은 뷰

USING [2. 조회서브쿼리]      --(만약 INTO절의 동일 테이블이라면  dual 사용)

ON [1과2의 조인 조건]  - 조인 조건의 KEY와 일치여부[UPDATE/INSERT 조건은 바로 ON절에 의해 결정]

WHEN MATCHED THEN   -일치되는 경우 UPDATE

UPDATE SET -- ※조인조건(on)절에 사용한 컬럼은 UPDATE가 불가하다!!

[컬럼1] = [값1],

[컬럼2] = [값2]

...

WHEN NOT MATCHED THEN -일치 안 되는 경우 INSERT

INSERT(컬럼1, 컬럼2...)

VALUES(값1, 값2...)

※오라클 9i 버전 이상부터 사용이 가능하다.

※조건절에 사용한 컬럼은 UPDATE가 불가하다!!

그리고 놀라운 사실!! UPDATE 구문만 가능한게 아니라 상황에 따라서는 DELETE 구문도 가능하다! 잘 쓸 경우는 없지만. 존재하는 ROW는 삭제하고 존재하지 않는 ROW는 삽입해야하는 경우라든지 다양하게 응용 하여 한번에 삽입 삭제 변경 처리가 가능하다.

오라클에서 MERGE  INTO 구문은 자주 사용하는 쿼리 구문이니 익혀두고  이용하면 좋을 듯 하다. 단, 9i 이상 오라클에서 사용 가능하다고 했지만 9i에서의 Merge 구문은 불완전 (DELETE구문도 10g 부터 사용가능 )하다. 또한 구문상 제약사항이 많기때문에 ON 구문에서 조건식을 잘못 넣으면 작동이 안된다; 10g 이상부터 구문을 자유롭게 이용할 수있다. 또한 Trigger 발생이 안된다는 것도 주의해야 하겠다. 그리고 당연한 애기겠지만 단순 INSERT 구문이나 UPDATE구문을 써도 문제가 안되는데 궂이 MERGE INTO를 남발? 하는것도 성능이 떨어지기 때문에(100~200만건 이상 TABLE에서는 느려지는듯 ㅜㅜ) 꼭 필요할 때 잘 쓰도록 하자^^

DBMS_JOB이란 오라클에서 주기적으로 수행되는 백업작업이나, 쿼리나 프로시져등의 JOB을 시간단위나 일 단위나 월 단위 등 주기적인 예약 작업으로 등록하여 동작할 수 있도록 하는 스케줄러이다.UNIX에서 CRONTAB을 돌리 듯이 DB에서 스케쥴러를 등록하여 일정 주기별로 원하는 작업을 할 수 있다는 점에서 유사한데 차이점은 CRON JOB은 OS가 직접 관리하고 실행하는 반면 DBMS_JOB에 등록된 JOB은 오라클이 관리한다.

[출처] [Oracle]오라클 잡 스케줄러 생성 - Job Scheduler (DBMS_JOB 패키지)|작성자 티시포스

위의 출처에 잘 정리되어져 있어서 블로그에 정리해 담아보았다.

* DBMS_JOB 패키지 내 프로시저 구성은 다음과 같다.

    1. SUBMIT : DB에 새로운 JOB을 추가하는 프로시저

    2. REMOVE : DB에 추가된 JOB을 삭제하는 프로시저 

    3. CHANGE : DB에 저장되어 있는 JOB의 field들을 변경하는 프로시저

    4. WHAT : JOB이 수행하는 작업을 변경하는 프로시저

    5. NEXT_DATE : JOB이 Schedule되어 Timer에 의해 자동으로 실행될 때를 변경하는 프로시저

    6. INTERVAL : JOB 실행 주기 파라미터를 변경하는프로시저

    7. BROKEN  : DB에 저장되어 있는 JOB의 상태를 정상 or Broken 상태로 설정하는 프로시저

    8.  RUN : JOB을 현재 시점에서 즉시 수행시키는 프로시저

1. SUBMIT

DB에 새로운 JOB을 추가하는 프로시저

오라클 기본 셋팅 확인

 여기에 'JOB_QUEUE_PROCESSES'의 값이 '0' 이면 스케줄러가 작동하지 않는다. (기본셋팅)

아래와 같이 변경

변경이 되었으면 JOB SCHEDULER를 생성한다.

DBMS_JOB 패키지 내 프로시저 이용

* job : 실행할 job number

* what - 실행할 PL/SQL 프로시저(procedure) 명 혹은 psm 문장의 sequence

* next_date - job을 언제 처음 시작할 것인지 지정한다. date type으로 evaluate되는 문자열 입력(SYSDATE)

* interval - job을 수행한 후, 다음 실행시간까지의 시간을 지정한다. 위 셋팅은 매일 오전 3시 마다 실행한다.

* no_parse - true 이면 submit시에 job을 parsing하지 않는다.

마지막으로 로그(Job Number)를 찍어줌으로써 스케줄러가 정상적으로 돌아가는지 확인할 수 있다.

* job Interval 설정

SYSDATE+7 : 7일에 한번 씩 job 수행

SYSDATE+1/24 : 1시간에 한번 씩 job 수행

SYSDATE+30/ : 30초에 한번 씩 job 수행(24: 시간 당, 1440(24x60):분 당, 86400(24x60x60):초 당 )

TRUNC(SYSDATE, 'MI')+8/24 : 최초 job 수행시간이 12:29분 일 경우 매시 12:29분에 job 수행

TRUNC(SYSDATE+1) : 매일 밤 12시에 job 수행

TRUNC(SYSDATE+1)+3/24 : 매일 오전 3시 job 수행

NEXT_DAY(TRUNC(SYSDATE),'MONDAY')+15/25 : 매주 월요일 오후 3시 정각에 job 수행

TRUNC(LAST_DAY(SYSDATE))+1 : 매월 1일 밤 12시에 job 수행

TRUNC(LAST_DAY(SYSDATE))+1+8/24+30/1440 : 매월 1일 오전 8시 30분

* job queue 정보 VIEWING

SELECT * FROM DBA_JOBS

SELECT * FROM USER_JOBS

SELECT * FROM ALL_JOBS

2. REMOVE

DB에 추가된 job을 삭제하는 프로시저

* job : 삭제할 job number

3. CHANGE

DB에 저장되어 있는 job의 field들을 변경하는 프로시저

* job : 실행할 job number

* what : 실행할 PL/SQL procedure 혹은 psm 문장의 sequence

* next_date : job을 다음 수행할 시간

* interval : job을 수행 후 nex_date를 update하기 위한 expression. date type으로 evaluate되는 문자열

4. WHAT

job이 수행하는 작업을 변경하는 프로시저

* job : 실행할 job number

* what : 실행할 PL/SQL procedure 혹은 psm 문장의 sequence

5. NEXT_DATE

job이 schedule되어 Tibero에 의해 자동으로 실행될 때를 변경하는 프로시저

* job : 실행할 job number

* next_date : job이 schedule되어 실행될 시간

6. INTERVAL

job 실행주기 파라미터를 변경하는 프로시저

* job : 실행할 job number

* interval : job을 수행 후 next_date를 update하기 위한 expression. date type으로 evaluate되는 문자열

7. BROKEN

DB에 저장되어 있는 job의 상태를 정상 or Broken 상태로 설정하는 프로시저

* job : 실행할 job number

* broken : job이 broken 된 경우 true, 정상 상태인 경우 false

8. RUN

job을 현재 session에서 즉시 수행시키는 프로시저.

job이 broken되어 있어도 실행하고, 실행에 성공한 경우 job을 정상 상태로 변경한다.

* job : 실행할 job number

Index의 정의를 보면

1) 조회속도를 향상시키기 위한 데이터베이스 검색 기술

2)색인이라는 뜻으로 해당 테이블의 조회결과를 빠르게 하기 위해 사용.

즉 인덱스가 필요한 이유는 인덱스를 생성해 줌으로써 조회 속도를 빠르게 할 수 있다.

INDEX를 테이블의 특정 컬럼에 한개이상 주게 되면 INDEX TABLE이 따로 만들어 지고,

인텍스 컬럼의 로우값과 rowid 값이 저장되며 로우값은 정렬된 트리 구조로 저장시켜 두었다가

검색시 좀더 빠르게 해당 데이타를 찾는데 도움을 준다.

*테이블을 생성하고 컬럼을 만든 후 데이타를 삽입하면 하나의 ROW가 생성되며 이 ROW는 절대적인 주소를 가지게 되는데 이를 ROWID라고 한다.

하지만 DML 명령을 사용 할때는 원본 TABLE은 물론 INDEX TABLE에도 데이타를 갱신시켜 주어야 하기 때문에 UPDATE, INSERT, DELETE (DML) 명령을 쓸때 속도가 느려진다는 단점이 있다. 

그렇기 때문에 무조건 인덱스를 생성한다고 좋은 것 많은 아니고 꼭 필요할 때만 분별하여 생성해 주어야 하는게 KEY POINT라 할 수 있겠다. 그럼 언제 인덱스(INDEX)를 생성해 주면 좋을까?

우선 데이터가 많이 쌓일거라고 예상되는 경우 혹은 많이 쌓여 있어 현재 화면에서 조회 속도가 너무 느릴때 인덱스 생성을 한다. 그리고 조회결과가 전체 데이터수의 3~5% 미만일 경우에는 인덱스 스캔이 효율적이고 적은 COST로 빠르게 데이터를 찾아낼 수 있다.(전체범위 3~5% 이상 되면 인덱스 스캔보다 풀스캔이 훨씬 유리)

쿼리 플랜을 떠보면 다음과 같은 단어로 확인 할 수 있다

인덱스 스캔 = Index Scan

풀 스캔 = Full Scan

반대로 INDEX생성이 불필요한 경우는?

1) 데이터가 적은(수천건 미만) 경우에는 인덱스를 설정하지 않는게 오히려 성능이 좋다.

2) 조회 보다 삽입, 수정, 삭제 처리가 많은 테이블

3) 조회결과가 전체행의 15% 이상 읽어들일 것으로 예상될때

INDEX 생성 문법은 간단하다

1) 단일 인덱스 지정

CREATE INDEX 인덱스 명 ON table_name (컬럼명)

2) 다중 인덱스(복합 인덱스) 지정

CREATE INDEX 인덱스 명 ON table_name (컬럼명1,컬럼명2,컬럼명3)

※ 복합 인덱스로 지정해준 테이블에서 복합 인덱스를 타게 하려면 복합 인덱스로 준 컬럼

을 조회쿼리에서 모두 조회조건에 사용해야 인덱스를 탈 확률이 높아진다.

예를 들면 다음과 같다

create index index_make1_dx on account(id);

create index index_make2_dx on account(name, date, dept_name);

※ 인덱스는 NOT NULL 값에 대해서만 생성되기 때문에 null 여부를 체크하는 비교문은 인덱스를 사용하지 못함.

INDEX 가능 컬럼

인덱스는 모든 컬럼에 적용가능하다.

그런데 오라클은 가공시킨 컬럼에도 적용가능하다. 아래 참고

CREATE INDEX IDX_NAME ON TABLE_NAME(ROUND(PRICE1-PRICE2));

ROUND(PRICE1-PRICE2) 는 컬럼은 아니지만 컬럼을 가공해서 만든 것이다.

이런 가공컬럼은 다음과 같은 SQL 쿼리로 인덱스를 탈수 있다.

SELECT * FROM TABLE_NAME WHERE ROUND(PRICE1-PRICE2) > 0

※ 인덱스 줄때의 가공컬럼과 같아야 한다.

SQL 쿼리의 INDEX SCAN 유무 체크 방법

1. 상용 DB 관리도구를 이용하는 방법

PL/SQL Developer, Toad 같은 도구에서 SQL문을 작성하고 실행하면 Explain plan 에서 확인 가능하다.

INDEX를 사용해야 할 컬럼?

1) where절이나 조인 조건에서 자주 사용되는 열에 생성

2) 열은 광범위한 값을 포함 할 때

3) 열은 많은수의 null값을 포함 할 때

4) 조회결과가 전체행의 2-4% 보다 적게 읽어들일 것으로 예상될 때

--테이블이 클때 적은 양의 로우를 검색할때 인덱스를 준다다. 적은 양을 검색하는데 테이블을 전체 풀스캔하면 시간이 오래 걸려서 꼭 index를 줘야 한다다.

INDEX를 사용하지 말아야할 컬럼?

1) 테이블에 데이타가 작은 경우

2) where절에 자주 사용되지 않는 열은 사용되지 않는다.

3) 조회결과가 전체행의 2-4% 이상을 읽어들일것으로 예상될때

4) 테이블이 자주 갱신될때

INDEX 생성시 고려사항해야 할 사항들은 무엇일까?

인덱스가 적용된 컬럼이 조건식에서 인덱스를 탈수있게 하려면 해당컬럼을 가공하지않거나 연산을 하지 않은 상태에서 비교해야 인덱스를 탄다.

예를들어 연락처컬럼의경우(010-1234-5678) 010 만 따로 문자열을 잘라(가공) 조건검색하면 인덱스를 타지 않는다.

왜냐하면 인덱스 컬럼에 변형이 일어나면 상대값과 비교되기 전에 먼저 가공이 된 후에 비교된다.하지만 인덱스는 가공되기 전의 값으로 생성되어 있기 때문에 당연히 인덱스를 사용할 수 없게 된다. 여기에서 외부적(External) 변형이란 사용자가 인덱스를 가진 컬럼을 어떤 SQL함수나 사용자 지정함수(User Defined Stored Function), 연산, 결합(||) 등으로 가공을 시킨 후에 발생되는 것이며 이러한 경우는 인덱스를 탈수 없어 변형이 일어나지 않도록 제대로 기술해야 한다.

그렇기 때문에 010과 1234과 5678를 각각의 컬럼으로 만들어 저장한 후 각각의 컬럼에 인덱스를 주면 아무런 가공없이 조건 검색이 가능하므로 인덱스를 탈수 있다.

테이블 컬럼에 인덱스가 있따면 테이블 컬럼을 변경하는것보다 비교값을 변경하여

비교해주는데 좋다. 왜냐면 그래야 인덱스를 타기 때문이다.

WHERE to_char(joindate, 'yyyymmdd') = '20150131'

WHERE joindate = TO_DATE('20150131','yyyymmdd')

인덱스를 타지 않는 CASE

SELECT * FROM ACCOUNT WHERE A_DAY+1>2;

SELECT * FROM ACCOUNT WHERE SUBSTR(A_STRDAY,1,1)='월';

SELECT * FROM EMP WHERE EMP_ID = NVL(EMP_ID,'10');

아래는 인덱스를 타는 CASE

SELECT * FROM ACCOUNT WHERE A_STRDAY='월요일';

SELECT * FROM ACCOUNT WHERE A_DAY>2;

SELECT * FROM EMP WHERE EMP_ID = NVL('10','20');

SELECT * FROM ACCOUNT WHERE A_STRDAY like '월요일%';

※ 첫번째 쿼리부터 인덱스효과가 크게 나타나는 순서

INDEX 타는 경우와 안타는 경우

안타는 경우

1. SELECT * FROM emp WHERE empno <> '1010035';

오라클에서 exists를 이용하여 타게 할수있다?

SELECT * FROM emp WHERE not exists

(select empno FROM emp WHERE empno = '1010035' and a.empno = b.empno);

INDEX 삭제 방법은 간단하다.

DROP INDEX INDEX_NAME;

※ TABLE이 삭제되면 INDEX도 삭제된다.

※ 인덱스의 소유자와 DROP ANY INDEX권한을 가진 사람만 인덱스 삭제가 가능합니다.

?이상으로 인덱스에 대한 정리를 마치고자 한다. 나는 위에 정리된 인덱스 개념처럼 조회되는 데이터 양은 3~5%밖에 안되며 엄청 느린.. 그리고  Explain plan 에서 확인 했을때 FULL SCAN을 타고 있는 상황에서 해당 컬럼에 INDEX를 생성해 주어 4분 이상 돌아가던 쿼리 속도를 4초 이내로 줄여 개선 할 수 있었다. 뿌듯한 성과였다. 이제 단순 개발 뿐만이 아니라 시스템의 성능과 품질을 고려할 수 있는 개발자가 되기위한  한걸음 도약이였다.