전문가칼럼

DBMS, DB 구축 절차, 빅데이터 기술 칼럼, 사례연구 및 세미나 자료를 소개합니다.

알면 유용한 오라클 기능들

전문가칼럼
DBMS별 분류
Oracle
작성자
dataonair
작성일
2015-07-01 00:00
조회
14346




◎ 연재기사 ◎


물탱크 구조로 알아본 오라클의 블록 옵션 ‘PCTFREE와 PCTUSED’


이산가족 찾기 생방송을 통해 배우는 DB 원리


개발자에게 맞는 DB 공부방법 찾기: 물리적 분류와 논리적 분류 그리고 인덱스


데이터베이스 인덱스의 오해와 진실


쉬운 것이 올바른 것이다. ‘인덱스 끝장리뷰’ (상)


쉬운 것이 올바른 것이다. ‘인덱스 끝장리뷰’ (하)


누구도 알려주지 않았던 ‘오라클 인덱스 생성도’의 비밀


누구도 알려주지 않았던 ‘오라클 쿼리 작성의 비법’


퀴리 최적화 및 튜닝을 위한 오라클 공정쿼리 작성법


만능 쿼리와 한 방 쿼리


오라클 옵티마이저 ‘CBO와 RBO’ 이해하기


재미있는 DB 이야기 ‘60갑자와 쿼리’


그림으로 배우는 ‘오라클 조인의 방식’ 이야기


반드시 알아야 하는 오라클 힌트절 7가지


오라클 플랜을 보는 법


개발자들의 영원한 숙제 ‘NULL 이야기’


알면 유용한 오라클 기능 ‘GATHER_PLAN_STATISTICS’


알면 유용한 오라클 기능들


오라클 DICTIONARY를 활용한 DB툴 프로그램 ‘FreeSQL’


이제는 말할 수 있다: 주식 자동매매 프로그램(상)


이제는 말할 수 있다: 주식 자동매매 프로그램(하)


개발자들이 자주 접하는 오라클 에러 메세지


재미있는 DB 이야기 ‘사라진 날짜를 찾아라’


오라클 랜덤 함수와 사용자 정의 함수


그림으로 배우는 ‘공정쿼리와 인덱스 생성도’


이병국의 개발자를 위한 DB 이야기: 디폴트 세팅의 함정과 오라클 파라미터


재미있는 DB 이야기 ‘놀라운 마방진의 세계’


오라클 운반 최소 단위 BLOCK


이병국의 개발자를 위한 DB 이야기: 이세돌과 알파고의 세기의 대결


이병국의 개발자를 위한 DB 이야기(30회) : DB 엔지니어의 가볍게 읽는 세상 이야기


이병국의 개발자를 위한 DB 이야기: 튜닝(31회) : 개발자를 위한 DB 튜닝 실전(1편)


이병국의 개발자를 위한 DB 이야기: 튜닝(32회) : 개발자를 위한 튜닝 실전(2편)


이병국의 개발자를 위한 DB 이야기: 튜닝(33회) : 개발자를 위한 튜닝 실전(3편)


이병국의 개발자를 위한 DB 이야기: 튜닝(34회) : 개발자를 위한 DB 튜닝 실전(4편)


이병국의 개발자를 위한 DB 이야기: 튜닝(35회) : 개발자를 위한 튜닝 실전(5편)


이병국의 개발자를 위한 DB 이야기: 페이징 처리에 대한 이해 (36회)


보기 좋은 떡이 먹기도 좋다 - 좋은 쿼리 좋은 성능


테이블의 수직분할과 수평분할에 대한 이해


DB 성능 제고를 위한 채번의 이해와 방식별 장단점 비교


이병국의 개발자를 위한 DB 이야기: 마지막회 : ‘개발자를 위한 DB 이야기’ 연재를 마치며



이병국의 개발자를 위한 DB 이야기: 유용한 기능 (18회)

알면 유용한 오라클 기능들



성공과 실패의 경험을 나누자, 용기와 희망을 나누자

개발업무를 시작으로 IT계에 입문했던 필자가 10년 가까이 DB 엔지니어로서 활동하면서 얻은 경험과 지식을 나누고자 한다. DB를 자주 접하는 SW 개발자뿐 아니라, DB 전문가를 꿈꾸는 대학생에서 DB 분야에 입문한 지 1~2년 된 기 입문자가 쉽게 이해할 수 있도록 비유를 통해 쉽게 접근해볼 계획이다. 물론 전문가들이라도 다시 한번 개념을 정립하는 의미에서 필요한 내용이 될 수 있다.

전체적으로 DB의 기본 원리와 개념을 이해하고 테이블, 인덱스, 쿼리, 튜닝, 플랜 등 개발자들이 알아야 하는 DB 전분야에 대해 쉽게 이해하도록 설명하겠다. DB 기술서적이나 번역서보다는 조금 더 부드럽게 접근할 계획이다. 그렇다고 흔히 서점에서 만날 수 있는 개발자 위주의 SQL 소개서도 아니다. 이 연재는 시리즈로 나갈 것이다. 연재를 끝까지 읽는 독자라면, 준전문가 수준의 DB 원리를 아는 것을 목표로 한다.



그대들 복 받기를 바라거든 우선 이 여섯 가지 도둑부터 잡으시게나 - 일연 스님

일연은 『삼국유사』를 지은 것으로 우리에게 잘 알려진 고려 말기의 스님이다. 일연 스님은 중국 중심의 역사적 사관을 버리고 우리의 역사를 주체적으로 바라보았다. 『삼국유사』는 지배 계층의 시각으로 바라본 『삼국사기』에 비해 평민의 시각에서 주체적으로 바라본 우리의 역사서라고 할 수 있다. 일연 스님은 깊은 선리와 높은 법담으로 가는 곳마다 칭송을 받았다고 한다. 그중에서 필자의 마음에 와 닿는 말씀이 있다.



여섯 가지 도둑
일연 스님
세상에 제일 고약한 도둑은
바로 자기 몸 안에 있는 여섯 가지 도둑일세.
눈 도둑은 보이는 것마다 가지려고 성화를 하지.
귀 도둑은 그저 듣기 좋은 소리만 들으려 하네.
콧구멍 도둑은 좋은 냄새는 제가 맡으려 하지.
혓바닥 도둑은 온갖 거짓말에다 맛난 것만 먹으려 하지.
제일 큰 도둑은 훔치고, 못된 짓 골라 하는 몸뚱이 도둑.
마지막 도둑은 생각 도둑.
이놈은 싫다. 저놈은 없애야 한다.
혼자 화내고 떠들며 난리를 치지.
그대들 복 받기를 바라거든
우선 이 여섯 가지 도둑부터 잡으시게나.



불가에서 육근(눈, 귀, 코, 혀, 몸, 마음)은 세상을 왜곡시키고 마음을 어지럽히는 도둑놈이라 했다. 일연 스님은 이 여섯 가지 도둑을 모두 물리치고 선하게 살아야 복을 받는다고 하였는데, 돌이켜 보면 필자 또한 마음속에 위의 여섯 가지 도둑 모두를 두둑이 갖고 있어 보인다. 매일 매일 반성하고 선한 마음으로 살려 하지만 여섯 가지 도둑은 제집 드나들듯 마음속을 헤집고 다닌다.

내 마음속에는 도사리고 있는 도둑들을 잡아내고 말겠다고 다짐하면서 다시 ‘오라클’ 잡기에 나서 보자. ^^;

지난 연재에서 알면 유용한 오라클 기능 GATHER_PLAN_STATISTICS에 대해 알아보았다. 이어서 이번 연재에서도 유용한 오라클 기능들에 대해 소개한다. 많은 개발자가 이미 알고 있는 기능이 아닌 다소 접해보지 못한 내용으로 꾸며 보았다.



COMMIT 이전의 상태로 되돌리는 기능 FLASHBACK

오라클에서 삭제 혹은 갱신된 레코드는 COMMIT 하기 전에는 ROLLBACK 명령어를 사용하면 원복이 가능했다. 만약 이미 COMMIT 명령어가 실행되어 완전히 삭제 혹은 갱신되었다면, 지금까지는 레코드 원복이 불가능했다. 하지만 Oracle 10g 이후부터는 flashback 기능을 제공해 Commit 이전 상태의 레코드도 조회할 수 있게 됐으며, 레코드 원복까지도 가능해졌다.

오라클은 10g부터 flashback을 통하여 commit/rollback 여부와 상관없이 한시적으로 데이터를 백업하는 기능을 지원해, 특정 시점의 데이터를 원복 가능하게 되었다. 이러한 flashback 기능은 오라클 서버에 부하를 주므로 사전에 설정된 제한 시간만큼만 지원된다. 오라클 파라미터 db_flashback_retention_target에서 설정된 시간을 확인할 수 있다.

select * from v$parameter where name = 'db_flashback_retention_target‘

조회된 value 값은 분 단위 시간을 의미한다. 제한된 설정 시간 내에서 우리는 다음과 같은 쿼리를 사용하여 과거 시점의 데이터를 조회하거나 원복할 수 있다.

-- 1시간전 시점의 고객 테이블을 조회하는 쿼리
SELECT * FROM 고객 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL ‘1’ HOUR)

-- 20분전 시점의 고객 테이블을 조회하는 쿼리
SELECT * FROM 고객 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL ‘20’ MINUTE)

-- 2015년 01월 10일 10시 20분 시점의 고객 테이블을 조회하는 쿼리
SELECT * FROM 고객 AS OF TIMESTAMP TO_DATE(‘201501101020’, ‘YYYYMMDDHH24MI’)

-- 30분전 시점의 고객 테이블을 조회하는 쿼리
SELECT * FROM 고객 AS OF TIMESTAMP SYSDATE - 30 / (24 * 60)

만약 사용 권한이 없다면 DBA에게 요청하여 DBMS_FLASHBACK 패키지에 대한 EXECUTE 권한을 부여받아야 한다.

만약 20분 전에서 10분 전 사이에 삭제된 레코드를 원복하고 싶다면, 다음과 같은 쿼리 구문을 사용하면 된다.

INSERT INTO 고객
SELECT * FROM 고객 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL ‘20’ MINUTE)
MINUS
SELECT * FROM 고객 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL ‘10’ MINUTE)

또한 특정 시점에 존재했던 레코드를 조회해 새로운 테이블을 생성-추가할 수도 있다.

CREATE TABLE 고객_BACKUP
AS
SELECT * FROM 고객 AS OF TIMESTAMP TO_DATE(‘201501101020’, ‘YYYYMMDDHH24MI’)

물론 이외에도 다양한 방법으로 원복이 가능하다.



오라클에서 스케줄 작업 사용법

스케줄 작업을 하려면 SNP_PROCESS가 활성화돼 있어야 한다. 만약 활성화돼 있지 않다면 다음 과정을 거친다.

INIT.ORA 파일의 JOB_QUEUE_PROCESSES 파라미터를 수정하고 오라클을 재구동한다. 스케줄 작업을 생성하기에 앞서서 스케줄 수행 시간 단위에 대해 간단히 알아보자.

1분 간격으로 스케줄 할 때 SYSDATE + 1 / 24 / 60
5분 간격으로 스케줄 할 때 SYSDATE + 5 / 24 / 60
1시간 간격으로 스케줄 할 때 SYSDATE + 1 / 24
매일 오전 1시에 스케줄 할 때 TRUNC(SYSDATE) + 1 + 1 / 24
매일 오후 11시간에 스케줄 할 때 TRUNC(SYSDATE) + 23 / 24
매월 첫번째 일요일 01시에 스케줄 할 때 TRUNC(NEXT_DAY(LAST_DAY(SYSDATE),'일')) + 1 / 24
매월 마지막일 오후 11시에 스케줄 할 때 TRUNC(LAST_DAY(SYSDATE)) + 23 / 24

네트워크 트래픽을 집계하는 P_네트워크 프로시저가 이미 생성돼 있다고 가정하자. P_네트워크려 한다면, 다음과 같이 스케줄을 생성하면 된다. USER_JOBS 테이블에 네트워크 스케줄 작업을 생성(INSERT) 한다(스케줄은 5분 간격으로 실행).



DECLARE
V_JOB_NO NUMBER; -- JOB_NO 1 : 네트워크 스케줄 JOB 번호
BEGIN
DBMS_JOB.SUBMIT(V_JOB_NO, 'P_네트워크;', SYSDATE, 'SYSDATE + 5 / 24 / 60');
END;



조직 및 인사 정보를 동기화하는 P_동기화 프로시져가 이미 생성 되어 있다고 가정하자. P_동기화 프로시져를 매일 오전1시에 스케줄 작업을 진행하려 한다면 아래와 같이 스케줄을 생성하면 된다. USER_JOBS 테이블에 동기화 스케줄 작업을 생성(INSERT) 한다. (스케줄은 매일 오전 1시에 실행)



DECLARE
V_JOB_NO NUMBER; -- JOB_NO 2 : 동기화 스케줄 JOB 번호
BEGIN
DBMS_JOB.SUBMIT(V_JOB_NO, 'P_동기화;', SYSDATE, 'TRUNC(SYSDATE) + 1 + 1 / 24');
END;



USER_JOBS 테이블에서 생성된 스케줄의 JOB 번호를 구하여 아래의 명령어로 스케줄을 구동한다.

EXECUTE DBMS_JOB.RUN(1); -- JOB_NO 1 : 네트워크 스케줄 실행
EXECUTE DBMS_JOB.RUN(2); -- JOB_NO 2 : 동기화 스케줄 실행

만약 구동된 동기화 스케줄 작업을 중단하려면 아래와 같이 하면 된다.

EXECUTE DBMS_JOB.BROKEN(2, TRUE); -- JOB_NO 2 : 동기화 스케줄 중단

만약 생성된 동기화 스케줄 작업을 삭제하려면 아래와 같이 하면 된다.

EXECUTE DBMS_JOB.REMOVE(2); -- JOB_NO 2 : 동기화 스케줄 삭제



SAMPLE 혹은 SAMPLE BLOCK를 이용한 SAMPLE SCAN

테이블을 접근하는 방식에는 풀 스캔 방식과 랜덤 액세스 방식이 있다. 풀 스캔은 테이블을 직접 접근해 모든 블록을 읽는 방식이고, 랜덤 액세스는 인덱스를 이용해 테이블에 접근하는 방식이다.

Oracle 8.1 이후부터 데이터를 접근할 때 Sample Scan 방식을 추가로 제공하고 있다. 샘플(SAMPLE) 스캔을 이용하면, 데이터를 랜덤하게 샘플링할 수 있다. 아래 예제를 살펴보자.

SELECT * FROM 고객 SAMPLE BLOCK(10) WHERE 지역 = ‘인제’ ORDER BY 고객명

SAMPLE 구간에 사용 가능한 값은 0에서 100 사이이다. 더 정확하게 표현하면 0.000001보다 크거나 같고 100보다 작아야 한다. 위의 쿼리에서 SAMPLE BLOCK(10)은 블록 단위로 주어진 값의 비율만큼 읽어 오는 것을 의미한다. 이와 유사하게 다음과 같이 사용하는 경우도 있다.

SELECT * FROM 고객 SAMPLE(10) WHERE 지역 = ‘인제’ ORDER BY 고객명

위의 쿼리에서 SAMPLE(10)은 레코드 단위로 주어진 값의 비율만큼 읽어 오는 것을 의미한다. 일반적으로 플랜에서 Cost 값은 SAMPLE보다 SAMPLE BLOCK이 더 낮다. Get Block 값도 SAMPLE 보다 SAMPLE BLOCK이 당연히 더 낮다. 두 가지 중에서 비용 측면을 더 고려하여 사용한다면 SAMPLE BLOCK 사용을 권한다.

규모가 작은 테이블보다는 큰 테이블에서 더 정확한 샘플링을 할 수 있으며, 작은 테이블에서의 사용은 무의미할 수도 있다. 대용량 테이블에서 아래와 같은 방법으로 레코드 수를 빠르게 확인 할 수도 있다.

SELECT COUNT(*) * 100 FROM 고객 SAMPLE BLOCK(1)

1% 샘플링해 구한 레코드 카운터 값에 곱하기 100을 하였으므로 전체 레코드와 유사한 결과값을 얻을 수 있다. 같은 쿼리를 여러 번 실행하여도 동일한 결과값을 리턴하지는 않고 유사한 값을 리턴한다는 것을 명심하자.



종을 횡으로 구현하는 함수 WM_CONCAT

문자열을 결합하는 함수 CONCAT는 오라클에서 자주 사용하는 함수로서 대부분의 개발자들이라면 익히 알고 있을 것이다. 문자열 결합 연산자 ||와 동일한 의미로서 그 사용법은 다음과 같다.

SELECT CONCAT(컬럼1, 컬럼2) FROM 테이블
SELECT 컬럼1 || 컬럼2 FROM 테이블

하지만 오늘 소개 하려는 함수는 WM_CONCAT이다. 이 함수는 종으로 된 컬럼 값을 횡으로 구현하는 기능을 가지고 있다. 아래 예제를 살펴보자.



---------------------------------------------------------------------
번호 국가 도시 인구(단위: 만)
---------------------------------------------------------------------
1 한국 서울 1,010
2 중국 베이징 1,961
3 한국 부산 351
4 일본 도쿄 1,329
5 한국 안동 16
---------------------------------------------------------------------
SELECT 국가, WM_CONCAT(도시) AS 도시들, SUM(인구) AS 총인구
FROM 도시인구현황
WHERE 인구 > 10
GROUP BY 국가
ORDER BY 총인구 DESC
-------------------------------------------------------------
국가 도시들 총 인구(단위: 만)
-------------------------------------------------------------
중국 베이징 1,961
한국 서울,부산,안동 1,377
일본 도쿄 1,329
-------------------------------------------------------------



횡을 종으로 구현하는 함수 REGEXP_SUBSTR

문자열을 분리하는 함수 SUBSTR 오라클에서 자주 사용하는 함수로서 대부분의 개발자들이라면 익히 알고 있을 것이다. 그 사용법은 다음과 같다.

SELECT SUBSTR(주문일자, 1, 6) AS 주문년월 FROM 테이블

하지만 오늘 소개 하고자 하는 함수는 REGEXP_SUBSTR이다. 이 함수를 이용하여 횡으로 된 값을 종으로 구현할 수 있다. 구분자 콤마(,)에 주의해 아래 예제를 살펴보자.



---------------------------------------------------------------------
번호 이름 취미리스트 우수고객
---------------------------------------------------------------------
1 심인술 축구,영화 Y
2 김윤호 자전거,낚시 N
3 김의석 여행,스키 Y
---------------------------------------------------------------------SELECT 이름, REGEXP_SUBSTR(취미리스트, '[^,]+', 1, LEVEL) AS 취미
FROM 고객
WHERE 우수고객 = ‘Y’
CONNECT BY REGEXP_SUBSTR(취미리스트, '[^,]+', 1, LEVEL) IS NOT NULL
GROUP BY 이름, 취미리스트, LEVEL
ORDER BY 이름, 취미-------------------------------------------------------------
이름 취미
-------------------------------------------------------------
김의석 스키
김의석 여행
심인술 영화
심인술 축구
-------------------------------------------------------------



REGEXP_SUBSTR 함수는 횡을 종으로 구현하는 용도로도 사용되지만, 일반적으로는 아래와 같이 문자열을 분리하는 용도로 더 많이 사용된다.



------------------------------------------------------------------------------------------
번호 이름 전화번호 주소
------------------------------------------------------------------------------------------
1 김상미 02-274-3328 서울 동대문구 신설동 123번지
2 이도윤 02-272-2723 서울 강동구 강일동 272번지
3 허은미 02-392-8989 서울 서초구 우면동 237번지
------------------------------------------------------------------------------------------
SELECT 이름
, REGEXP_SUBSTR(전화번호, '[^-]+', 1, 1) AS 지역
, REGEXP_SUBSTR(전화번호, '[^-]+', 1, 2) AS 국번
, REGEXP_SUBSTR(전화번호, '[^-]+', 1, 3) AS 전화번호
, REGEXP_SUBSTR(주소, '[^ ]+', 1, 1) AS 시도명
, REGEXP_SUBSTR(주소, '[^ ]+', 1, 2) AS 시군구명
, REGEXP_SUBSTR(주소, '[^ ]+', 1, 3) AS 읍면동명
FROM 고객------------------------------------------------------------------------------------------
이름 지역 국번 전화번호 시도명 시군구명 읍면동명
------------------------------------------------------------------------------------------
김상미 02 274 3328 서울 동대문구 신설동
이도윤 02 272 2723 서울 강동구 강일동
허은미 02 392 8989 서울 서초구 우면동
------------------------------------------------------------------------------------------



다음 연재에서는 오렌지나 토드 같은 DB 툴을 개발하는 방법론에 대해 설명한다.



용기를 갖자 오라클 DB뿐 아니라 대부분의 DB 구성 알고리즘은 어느 날 ‘하늘에서 뚝 떨어져 새로 만들어진 것’이 아니라 실생활에서 이용되는 혹은 이미 상식 수준에서 인지되는 그런 보편적인 원리를 바탕으로 만들어졌으므로 쉽게 접근하고 이해할 수 있다. 서두에서 말했듯이 ‘레몬시장이론’을 상기하며 DB를 지레짐작으로 어려워하지 말고 용기를 내고 하나씩 터득해 나가기를 바란다. 이 글은 DB 전문가 수준의 이해를 요구하지는 않는다. 단지 DB에 대해서 더 친숙하고 더 쉽게 이해하고 접근하길 바랄 뿐이다. 이 글을 읽으면서 궁금하거나 의문 나는 점이 있으면, 댓글을 달아주실 것을 적극 바란다. 아무리 어렵고 힘든 일이더라고 ‘관계’와 ‘소통’으로 풀어나갈 수 있음을 다시 한 번 믿으며...



[지난 문제의 정답과 풀이]

원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제



지난 연재에 출제한 ‘원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제’에 대한 정답과 해설은 아래와 같다. 문제를 풀면서 DB 원리를 하나씩 배우고 이해할 수 있다.



column_img_1969.jpg

column_img_1970.jpg

[이번 호 문제]

원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제



각 연재의 말미에 간단하면서도 재미있고 생각해 보는 문제를 출제하려 한다. 모든 문제는 DB의 원리를 이해할 수 있는 문제로 출제할 예정이다. 문제를 풀면서 DB 원리를 하나씩 배우고 이해할 수 있다. 정답과 그에 대한 설명은 다음 연재에서 한다.

column_img_1971.jpg