ORACLE/OWI2009. 3. 1. 16:52

Enqueue 와 Latch

반응형

DBMS의 가장 주된 기능 중에 하나는 동일 자원에 대한 동시 액세스를 관리하는 것이며, 이를 위해 오라클이 사용하는 대표적인 제어 구조가 Enqueue와 Latch이다. Enqueue와Latch는 모두 특정 자원에 대한 접근을 serialize하는 것이 목적이라는 점에서는 같은 Lock의 일종이지만 관리방식이나 용도에서 차이가 있다.

Enqueue는 이름에서 보듯 Queue를 통해 관리된다. 대상 자원에 대한 Owner, Waiter, Converter Queue를 관리하면서 먼저 요청한 순서대로 Lock을 획득하도록 하는 구조이며, Exclusive 모드뿐 아니라 다양한 수준의 공유를 허용한다. 대표적인 것이 테이블 데이터를 Update할 때 사용되는 TM, TX Enqueue이다.

반면에, Latch는 Enqueue에 비해 훨씬 단순한 구조로서 매우 짧은 시간 내에 획득되고 해제된다. Queue를 통해 관리되지 않으므로 먼저 Request한 프로세스가 먼저 latch를 획득한다는 보장이 없으며, 대부분의 경우 Exclusive 모드로만 획득된다. Latch는 주로 SGA의 특정 메모리 구조체에 대한 액세스(library cache latch, cache buffers chains latch) 혹은 메모리 할당 시(shared pool latch) 사용되거나 오라클의 중요한 코드가 동시에 수행되지 않도록 하기 위한 용도로(redo writing latch) 사용된다. Latch는 Enqueue보다는 하위 level에서 Locking 자체의 부하를 최소화하며 작동하는 제어 메커니즘이라고 할 수 있으며, 실제로 Enqueue 역시 내부적으로는 Latch (enqueues, enqueue hash chains latch )에 의해 운영된다는 점을 생각하면 둘 사이의 차이를 쉽게 이해할 수 있을 것이다.

 

■ Enqueue

Enqueue 정보는 내부적으로 Enqueue Resource 배열과 Enqueue Lock 배열에 저장된다. 특정 자원에 대한 Lock이 요청되면 대상을 하나의 Resource로 정의하여 할당하고 그Resource에 대해 관련 Lock 정보를 Owner, Waiter, Converter가운데 하나로서 Link시키는 방식으로 운영되며, 이러한 정보는V$RESOURCE 와 V$LOCK View를 통해 조회해 볼 수 있다. V$RESOURCE와V$LOCK은1:M관계로 하나의 Resource에 대하여 여러 건의 Lock 레코드가 Owner (LMODE>0, REQUEST=0), Waiter (LMODE=0 ,REQUEST>0), Converter (LMODE>0, REQUEST>0) 중 하나로서 대응된다. 

Enqueue Wait이 발생하는 것은 다른 세션이 이미 나보다 먼저 해당 자원에 대한 Lock을 잡고 있으므로 인해 내가 원하는 모드로 Lock을 할당 받을 수 없기 때문이다. 자신이 필요로 하는 Lock의 획득에 실패한 세션은 Owner가 작업을 완료하고 자신을 깨워줄 때까지(세마포어를 포스트해줄 때까지) Waiter 혹은 Converter Queue에서 대기하게 되며, 기다려도 소식이 없으면3초 간격으로 timeout에 의해 일어나 혹시 Deadlock 상황이 아닌지 점검해 본 후 다시 Sleep에 빠져들기를 반복하게 된다. 튜닝관련 자료를 보다 보면 가끔 Enqueue에 대한 Wait이 많은 경우에 Enqueue_resource 나 Enqueue_lock 파라미터를 증가시켜 주어야 한다는 가이드를 보게 되는 경우가 있는데 이 파라미터들은 Enqueue resource 와 lock 배열의 크기를 늘려줄 뿐 특정 Enqueue 자원에 대한 동시 경합을 해소시키는 것과는 상관이 없다. Enqueue Wait를 해소하기 위한 구체적인 방법은 Enqueue type에 따라 달라지지만 결국은 Enqueue를 불필요하게 요청하는 경우가 없는지를 살펴 Enqueue에 대한 요청을 최소화하고 Enqueue를 점유하는 시간을 최대한 단축시키는 것이다. TX Enqueue에 대한 Wait은 대상 자원에 대한 Lock을 소유하고 있는 세션과 그 세션이 수행 중인 SQL을 찾아 트랜잭션이 장시간 지속되고 있는 이유가 무엇인지 애플리케이션 측면에서 조사해야 하며, SQ enqueue는 Sequence값 할당 시 발생하는 경합이므로 cache값을 늘려줌으로써 완화시킨다거나 ST Enqueue의 경합이 존재할 경우에는 Locally managed tablespace를 사용하거나 Initial, Next 등의 extent 크기를 적당한 값으로 조정하여 실시간 공간할당을 감소시켜주는 등의 방법들이 Enqueue Wait에 대처하는 대표적인 사례이다. 지난 호에서 소개한 Session Waiter 스크립트는 Enqueue Wait 이벤트에 대해서 Enqueue type과 모드를 함께 표시하여 주도록 하고 있으며, 참고로 Enqueue type별 누적 Wait현황을 확인하고자 하면 아래 SQL을 수행하면 된다.

SELECT q.ksqsttyp type, q.ksqstget gets, q.ksqstwat waits, round(q.ksqstwat/q.ksqstget,3) waitratio

FROM sys.x$ksqst q

WHERE q.inst_id = userenv(‘Instance’)

AND q.ksqstget > 0

ORDER BY waits DESC

/

■ Latch

오라클 운영 시에 하위레벨에서 내부적으로 처리되는 다양한 조작들이 latch의 관할 하에 수행되는데 V$LATCHNAME을 조회해보면 (9i기준으로) 239 종류나 되는 Latch가 존재하는 것을 확인할 수 있다. 이 가운데 우리가 자주 접하게 되는 latch는 다음과 같은 정도이며 각Latch의 기능은 관련 SGA별 Wait를 다룰 때 간단하게나마 소개하도록 하겠다. 

Shared pool

library cache latch, shared pool latch, row cache objects

Buffer Cache

cache buffers chains latch, cache buffers lru latch, cache buffer handle

Redo log

redo allocation latch, redo copy latch, redo writing latch

OPS

dlm resource hash list

 

▷ Willing to wait 모드와 No-wait 모드

Latch 획득 방식은 No-wait 과 Willing to wait의 두 가지 모드로 구분할 수 있다.

Willing to wait 모드는 Latch의 획득에 실패하면 좀더 시간을 끌면서 해당 Latch를 잡을 때까지 재시도를 해보는 방식을 말한다. 일차적으로는 CPU를 놓지 않고 정해진 횟수(_SPIN_COUNT(기본값 2000) 파라미터 값 만큼 반복(스핀)하며 latch 획득을 시도한다)만큼 Spinning을 한 후 재시도를 해보다가 그래도 실패하면 CPU를 놓고 Sleep하다가 timeout되어 재시도하는 작업을 반복하면서 Latch의 획득을 노력하게 된다. Latch가 sleep에 들어가게 되면 ‘latch free’ wait event 대기가 시작된다. sleep의 지속 시간은 sleep 횟수가 늘어갈수록 점점 길어지게 되는데, 따라서 V$LATCH의Gets 와 Sleeps의 비율과 함께 Sleep1~sleep4 항목에서 몇 차 Sleep까지 발생했는지 여부도 각 Latch Wait의 심각성을 판단하는 요소 가운데 하나가 된다.

No-wait 모드는 Willing to wait과는 달리 더 이상 미련을 두지 않고 해당 Latch에 대한 획득을 포기하는 것이다. No-wait 모드가 사용되는 경우는 두 가지가 있는데, 하나는 동일한 기능을 하는 Latch가 여러 개 존재하여 그 중에 하나만 획득하면 충분하여서 특정 Latch에 미련을 가질 필요가 없는 경우이다. 물론, 이 때에도 같은 기능의 모든 Latch에 대한 시도가 실패로 끝날 경우에는 Willing to wait 모드로 요청을 할 것이다. No-wait 모드가 사용되는 다른 한가지 경우는 dead lock을 피하기 위해서 이다. 오라클은 기본적으로 latch dead lock 상황을 피하기 위하여 모든 Latch에 level을 부여하여 정해진 순서를 따라서만 Latch를 획득하도록 하고 있는데, 필요에 의해 이 규칙을 어기고 Latch를 획득하고자 할 경우 일단 No-wait 모드로 시도를 해보는 것이다. 다행히 Latch를 잡으면 좋은 것이고 비록 latch를 잡을 수 없더라도 무한정 기다림으로써 dead lock 상태에 빠지는 일은 피할 수 있는 것이다. No-wait 모드의 Latch작업에서는 당연히 Latch 관련 wait이 발생하지 않으며, redo copy latch를 제외하고는 Willing to wait 모드로 Latch를 획득하는 경우가 훨씬 많다.

 

▷ Parent latch와Child latch

Latch 가운데에는 동일 기능을 하는 Child latch들의 set으로 운영되는 Latch도 있으며 하나의 Latch로만 운영되는 Latch도 있다. 전자의 대표적인 예로는 cache buffers chains (버퍼 캐시 블록 들을 같은 이름의 다수의 Latch가 나누어 담당)가 있으며, 후자의 예로는 shared pool latch (shared pool내에서 메모리 할당을 위해 획득해야 하는 Latch로 시스템에 하나만 존재)가 있다. 이와 같은 Latch 관련 통계 정보는 Parent latch 와 Child latch의 개념으로 관리가 되는데 Latch set에서 개별 Child latch에 대한 통계정보는 V$LATCH_CHILDREN View를 통해 조회할 수 있으며, 단일 Latch 혹은 Latch set의 마스터 Latch (parent)에 대한 통계정보는 V$LATCH_PARENT View를 통해 조회할 수 있다.

 

지금까지 한 회 분량을 할애하여 Enqueue와Latch에 대해 요약해본 이유는, 많은 Waiting이SGA내의 공유자원 (Block, Cursor등)에 대한 경합으로 인해 발생하며 이러한 경합은 다시 해당 자원에 대한 동시 액세스를 제어하는 Enqueue 와 Latch에 대한 경합으로 흔히 드러나게 되므로 오라클의 Wait Event를 모니터링 하기 위해서는 Enqueue 와 Latch의 구조와 작동원리에 대해 이해하는 것이 필수적이기 때문이다.
반응형
저작자표시 비영리

'ORACLE > OWI' 카테고리의 다른 글

Latch의 발생과 경합의 원인  (0) 2009.03.09
Oracle에서 Latch 가 성능 저하의 원인이 아닙니다  (0) 2009.03.01
Direct path read  (0) 2009.02.08
LIBRARY CACHE LOCK WAIT EVENT가 나타날 때의 해결방법  (0) 2008.12.22
oracle lock 종류  (0) 2008.12.22
Posted by [PineTree]
ORACLE/ADMIN2009. 2. 25. 13:31

PCTFREE, PCTUSED

반응형
PCTFREE, PCTUSED
PCTFREE 및 PCTUSED는 DataBase 내부의 공간(Data Block)을 효과적으로 다루기 위해 사용되어 지는 저장 매개변수(Storage Parameter) 이다. 대부분 Data Block 내부의 free space를 다루기 위해서는 PCTFREE와  PCTUSED는 함께 설정 되어 지며 새로운 자료의 Insert나 Data Block 내부에 이미 존재하는 자료의 Update를 위한 두 가지 목적으로 주로 이용된다. PCTFREE의 기본값은 10, PCTUSED의 기본값은 40이다. 만약 CREATE TABLE, CREATE INDEX, CREATE CLUSTER등과 같은 DDL의 사용시 PCTFREE 및 PCTUSED를 적절히 구사 한다면 DataBase의 Performance를 높일수 있을 것이다.

PCTFREE의 경우 Data Block에 이미 존재하고 있는 행(Row)에 Update등이 일어 날 경우를 대비하여 예약 시켜놓을 Data Block의 퍼센트를 지정하는 것이며 0~99사이의 정수가 사용 가능 하다. PCTUSED의 경우는 Data Block의  여유공간 퍼센트가 PCTFREE에 도달하는 경우 사용된 공간이 PCTUSED 아래로 떨어지기 전에는 새로운 데이터를 삽입(Insert) 할 수 없다는 것을 가리키는 매개 변수 이다. 예를 들면   PCTFREE와 PCTUSED 값의 합은 100 보다 적거나 같은 정수이어야 한다. 다음의 경우를 보자. 만약 임의의 테이블의 주소 컬럼이 가변길이 100 바이트로 구성 되었다고 가정 하자. 처음에 데이터가 Insert  되면서 100 바이트 중 10바이트를 사용하였지만 나중에 변동이 생겨 테이블의 데이터를 100바이트로 Update해야 한다고 생각을 해 보자. 이런 경우 처럼 나중에 테이블의 컬럼등이 Update될때 이전 보다 훨씬 사이즈가 늘어 날 경우를 대비하여 전체 Data Block에서 몇 퍼센트를 비워 놓을 건지를 지정하는 매개변수가 PCTFREE 인 것이다. 적은 PCTFREE의 경우 변경을 위한 작은 공간을 예약하는 것이며 Insert와 같은 명령 수행시 Data Block을 완전히 채울 수 있으며 큰 PCTFREE경우 나중에 변경을 위해 큰 공간을 예약하며 같은 양의 데이터가 Insert 된다면 적은 PCTFREE보다 많은 Data Block을 요구하게 될것이다.  Oracle 9i의 경우 데이터베이스 생성시 segment관리를 auto 옵션을 이용하여 자동으로 지정한다면 Data Block의 free space관리를 위해 bitmap을 이용 할 것이다. 즉 bitmap block에는 Data Block의 free space 가 얼마인지를 가르키고 있다.

다음의 예를 보자. 만약 PCTFREE가 20이고 PCTUSED가 40이라고 할 때 신규 행(Row)은 free space가 20% 될때 까지 입력 된다. 기존의 행에 수정을 하면 예약 해둔 빈 공간을 사용하게 되며 새로운 행은 사용된 공간이 40% 아래로 되어야만 Insert가 가능하게 된다. 40% 아래로 떨어져 새로운 행을 입력 한다면 80%까지만 입력되고 20%는 Update를 위해 예약을 해 두는 것이다.

만 약 테이블을 설계할 때 빈번한 Update가 발생 될 가능성이 크며 컬럼의 사이즈가 늘어날 확률이 높다면 PCTFREE 매개변수를 크게 하고 PCTUSED 매개변수는 낮게 설정 해야 할 것이다. PCTFREE는 20, PCTUSED는 40 정도가 적당 할 것이다. 또한 어떤 테이블은 읽기 전용이며 테이블의 사이즈가 적당히 크다면 PCTFREE는 낮은 수를 PCTUSED는 큰 수를 지정 하면 될 것 이다.

? 아래 예문은 오라클 9i의 새로운 특징인 auto segment space 관리의 예문이다. 결국 오라클 9i의 경우 automatic segment space management 와 manual segment space management의 두가지 방법을 제공함을 알 수 있다.

SQL> connect / as sysdba
연결되었습니다.
SQL> create tablespace auto
  2  datafile 'C:\oracle\oradata\wink\auto01.dbf'
  3  size 10m
  4  segment space management auto;

테이블 영역이 생성되었습니다.

? TableSpace에 관한 정보를 제공해 주는 뷰를 통해 내용을 확인 하자.

SQL> select TABLESPACE_NAME,
  2         SEGMENT_SPACE_MANAGEMENT
  3  from dba_tablespaces;

TABLESPACE_NAME                SEGMEN
------------------------------ ------
SYSTEM                         MANUAL
TEST                           MANUAL
AUTO                           AUTO
….

? 아래 예문은 create table 명령이다. 지금까지 보았던 것과는 약간 다른 모습 이다. pctfree, pctused를 사용 했으며 , 저장 매개변수를 지정 하였다. 저장 매개 변수는 6.2절에 자세히 나와 있으니 참고 하기 바란다.

SQL> connect scott/tiger
SQL> create table myAddrBook (
  2     id number(5) not null primary key,
  3     name varchar2(20) not null,
  4     address varchar2(100)
  5  )
  6  pctfree 10
  7  pctused 50
  8  tablespace users
  9  storage (
10     initial 50k
11     next 50k
12     maxextents 10
13     pctincrease 25);

테이블이 생성되었습니다.
반응형
저작자표시 비영리

'ORACLE > ADMIN' 카테고리의 다른 글

TABLE별 용량을 파악해보자!  (0) 2009.03.13
유용한 DICTIONARY 뷰 ..  (0) 2009.03.06
oracle em 구성  (0) 2009.02.12
INSERT /*+ APPEND */ VS CTAS  (0) 2009.02.06
Query Rewrite와 MView Refresh.  (0) 2009.02.05
Posted by [PineTree]
ORACLE/SQL2009. 2. 24. 16:13

SQL 실행순서

반응형
SQL 실행 순서입니다.
기본적인 내용인데 모르는 분이 많아서 글로 남깁니다.
(괄호가 실행 순서를 나타낸다.)


SELECT       <<- (5)
FROM          << - (1)
WHERE         <<- (2)
GROUP BY    <<- (3)
HAVING        <<- (4)
ORDER BY    << - (6)
반응형
저작자표시 비영리

'ORACLE > SQL' 카테고리의 다른 글

Oracle 관리 및 SQL Plus 정리  (0) 2009.06.10
ORACLE START WITH, CONNECT BY  (3) 2009.04.02
다중 행(Multiple-Row) 서브쿼리 IN, NOT IN, ANY, ALL, EXISTS  (0) 2009.02.24
Oracle 내부함수  (0) 2009.02.24
데이터 복사하기 위한 INSERT스크립트 생성  (0) 2009.02.24
Posted by [PineTree]
ORACLE/SQL2009. 2. 24. 14:55

다중 행(Multiple-Row) 서브쿼리 IN, NOT IN, ANY, ALL, EXISTS

반응형


☞ 다중 행(Multiple-Row) 서브쿼리

 ◈ 하나 이상의 행을 RETURN하는 SUBQUERY를 다중 행 SUBQUERY라고 합니다.

 ◈ 복수 행 연산자(IN, NOT IN, ANY, ALL, EXISTS)를 사용 할 수 있습니다.


☞ IN 연산자의 사용 예제
 
  부서별로 가장 급여를 많이 받는 사원의 정보를 출력하는 예제
 
SQL>SELECT empno,ename,sal,deptno  
        FROM emp
        WHERE sal IN(SELECT MAX(sal)
                              FROM emp
                              GROUP BY deptno);

     EMPNO ENAME             SAL     DEPTNO
---------- ----------   ---------- ----------
      7698 BLAKE             2850           30
      7788 SCOTT            3000           20
      7902 FORD              3000           20
      7839 KING               5000           10

 


☞ ANY 연산자의 사용 예제

  ANY연산자는 서브쿼리의 결과값중 어느 하나의 값이라도 만족이 되면 결과값을 반환 합니다.

SQL>SELECT ename, sal
        FROM emp
        WHERE deptno != 20
             AND sal > ANY(SELECT sal FROM emp WHERE job=’SALESMAN’);  
 
ENAME             SAL
---------- ----------
ALLEN            1600
BLAKE            2850
CLARK            2450
KING              5000
TURNER          1500
MILLER           1300

6 개의 행이 선택되었습니다.

 


☞ ALL 연산자의 사용 예제

  ALL연산자는 서브쿼리의 결과값중 모든 결과 값이 만족 되야만 결과값을 반환 합니다.
 
SQL>SELECT ename, sal
        FROM emp
        WHERE deptno != 20
             AND sal > ALL(SELECT sal FROM emp WHERE job=’SALESMAN’);
 
ENAME             SAL
---------- ----------
BLAKE            2850
CLARK            2450
KING             5000

3 개의 행이 선택되었습니다.

 


☞ EXISTS 연산자의 사용 예제

 - EXISTS 연산자를 사용하면 서브쿼리의 데이터가 존재하는가의 여부를 먼저 따져 존재하는
   값들만을 결과로 반환해 줍니다.
 - SUBQUERY에서 적어도 1개의 행을 RETURN하면 논리식은 참이고 그렇지 않으면 거짓 입니다.

예제)사원을 관리할 수 있는 사원의 정보를 보여 줍니다.
 
SQL>SELECT empno, ename, sal
        FROM emp e
        WHERE EXISTS (SELECT empno FROM emp WHERE e.empno = mgr) 

     EMPNO ENAME             SAL
---------- ---------- ----------
      7566 JONES             2975
      7698 BLAKE             2850
      7782 CLARK             2450
      7788 SCOTT            3000
      7839 KING               5000
      7902 FORD              3000
 
6 개의 행이 선택되었습니다.

  ================================================
    * 오라클 정보공유 커뮤니티 oracleclub.com
    * http://www.oracleclub.com
    * http://www.oramaster.net
    * 강좌 작성자 : 김정식 (oramaster _at_ naver.com)
  ================================================
반응형
저작자표시 비영리

'ORACLE > SQL' 카테고리의 다른 글

ORACLE START WITH, CONNECT BY  (3) 2009.04.02
SQL 실행순서  (0) 2009.02.24
Oracle 내부함수  (0) 2009.02.24
데이터 복사하기 위한 INSERT스크립트 생성  (0) 2009.02.24
TIMESTAMP -> DATE 변환  (0) 2009.02.24
Posted by [PineTree]
ORACLE/SQL2009. 2. 24. 10:59

Oracle 내부함수

반응형
출처 살아가는 이야기 | 서기
원문 http://blog.naver.com/tinylass/120018762546

1. 문자 함수
 1-1) CHR
 1-2) CONCAT 함수
 1-3) INITCAP 함수
 1-4) LOWER 함수
 1-5) LPAD 함수
 1-6) LTRIM 함수
 1-7) NLS_INITCAP 함수
 1-8) NLS_LOWER 함수
 1-9) NLSSORT 함수
 1-10) NLS_UPPER 함수
 1-11) REPLACE 함수
 1-12) RPAD 함수
 1-13) RTRIM 함수
 1-14) SOUNDEX 함수
 1-15) SUBSTR 함수
 1-16) TRANSLATE 함수
 1-17) TREAT 함수
 1-18) TRIM 함수
 1-19) UPPER 함수
 1-20) ASCII 함수
 1-21) INSTR 함수
 1-22) LENGTH 함수


2. 날짜 처리함수(datetime function)
 2-1) ADD_MONTHS 함수
 2-2) CURRENT_DATE 함수
 2-3) URRENT_TIMESTAMP 함수
 2-4) DBTIMEZONE 함수
 2-5) EXTRACT(datetime) 함수
 2-6) FROM_TZ 함수
 2-7) LAST_DAY 함수
 2-8) LOCALTIMESTAMP 함수 
 2-9) MONTHS_BETWEEN 함수
 2-10) NEW_TIME 함수
 2-11) NEXT_DAY 함수
 2-12) NUMTODSINTERVAL 함수
 2-13) NUMTOYMINTERVAL 함수
 2-14) ROUND(date) 함수
 2-15) SESSIONTIMEZONE 함수
 2-16) SYS_EXTRACT_UTC 함수
 2-17) SYSDATE 함수
 2-18) SYSTIMESTAMP 함수
 2-19) TO_DSINTERVAL 함수
 2-20) TO_TIMESTAMP 함수
 2-21) TO_TIMESTAMP_TZ 함수
 2-22) TO_YMINTERVAL 함수
 2-23) TRUNC(date) 함수
 2-24) TZ_OFFSET 함수


3.데이터 형 변환 함수(conversion function)
 3-1) ASCIISTR 함수
 3-2) BIN_TO_NUM 함수
 3-3) CAST 함수
 3-4) CHARTOROWID 함수
 3-5) COMPOSE 함수 
 3-6) CONVERT 함수
 3-7) HEXTORAW 함수
 3-8) NUMTODSINTERVAL 함수
 3-9) NUMTOYMINTERVAL 함수
 3-10) RAWTOHEX 함수
 3-11) RAWTONHEX 함수
 3-12) ROWIDTOCHAR 함수
 3-13) ROWIDTONCHAR 함수
 3-14) TO_CHAR(character) 함수
 3-15) TO_CLOB 함수
 3-16) TO_DSINTERVAL 함수
 3-17) TO_LOB 함수
 3-18) TO_MULTI_BYTE 함수
 3-19) TO_NCHAR(character) 함수
 3-20) TO_NCHAR(datetime) 함수
 3-21) TO_NCHAR(number) 함수
 3-22) TO_NCLOB 함수
 3-23) TO_NUMBER 함수
 3-24) TO_SINGLE_BYTE 함수
 3-25) TO_YMINTERVAL 함수
 3-26) TRANSLATE ... USING 함수
 3-27) UNISTR 함수


4. 기타함수(miscellaneous single row function)
 4-1) BFILENAME 함수
 4-2) COALESCE 함수
 4-3) DECODE 함수
 4-4) DEPTH 함수
 4-5) DUMP 함수
 4-6) EMPTY_BLOB 함수
 4-7) EMPTY_CLOB 함수
 4-8) EXISTSNODE 함수
 4-9) EXTRACT(XML) 함수
 4-10) EXTRACTVALUE 함수
 4-11) GREATEST 함수
 4-12) LEAST 함수
 4-13) NLS_CHARSET_DECL_LEN 함수
 4-14) NLS_CHARSET_ID 함수
 4-15) NLS_CHARSET_NAME 함수
 4-16) NULLIF 함수
 4-17) NVL2 함수
 4-18) PATH 함수
 4-19) SYS_CONNECT_BY_PATH 함수
 4-20) SYS_CONTEXT 함수
 4-21) SYS_DBURIGEN 함수
 4-22) SYS_EXTRACT_UTC 함수
 4-23) SYS_GUID 함수
 4-24) SYS_XMLAGG 함수
 4-25) SYS_XMLGEN 함수
 4-26) UID 함수
 4-27) USER 함수
 4-28) USERENV 함수
 4-29) VSIZE 함수
 4-30) XMLAGG 함수
 4-31) XMLCOLATTVAL 함수
 4-32) XMLCONCAT 함수
 4-33) XMLFOREST 함수
 4-34) XMLELEMENT 함수


5.그룹함수  Aggregate 함수
 5-1) AVG* 함수
 5-2) CORR* CORR* 함수
 5-3) COUNT* 함수
 5-4) COVAR_POP 함수
 5-5) COVAR_SAMP 함수
 5-6) CUME_DIST 함수
 5-7) DENSE_RANK 함수
 5-8) FIRST 함수
 5-9) GROUP_ID 함수
 5-10) Grouping 함수
 5-11) GROUPING_ID 함수
 5-12) LAST 함수
 5-13) MAX 함수
 5-14) MIN 함수
 5-15) PERCENTILE_CONT 함수
 5-16) PERCENTILE_DISC 함수
 5-17) PERCENT_RANK 함수
 5-18) RANK 함수
 5-19) REGR_(linear regression) function* 함수
 5-20) STDDEV 함수
 5-21) STDDEV_POP 함수
 5-22) STDDEV_SAMP 함수
 5-23) SUM 함수
 5-24) VAR_POP 함수
 5-25) VAR_SAMP 함수
 5-26) VARIANCE 함수
 5-27) Grouping sets 함수


6. Analytic 함수
 6-1) AVG* 함수
 6-2) CORR* CORR* 함수
 6-3) COUNT* 함수
 6-4) COVAR_SAMP 함수
 6-5) CUME_DIST 함수
 6-6) DENSE_RANK 함수
 6-7) FIRST 함수
 6-8) FIRST_VALUE 함수
 6-9) LAG 함수
 6-10) LAST_VALUE 함수
 6-11) LEAD 함수
 6-12) NTILE 함수
 6-13) RATIO_TO_REPORT 함수
 6-14) ROW_NUMBER 함수


7. 객체 참조 함수
 7-1) REF 타입


8. PseudoColumn을 의미하는 것
 8-1) ROWID 컬럼
 8-2) ROWNUM 컬럼

 

1-1) CHR 함수
--------------------------------------------------------------------------------

입력된 수의 바이너리 코드에 해당하는 문자를 반환한다.

【예제】
SQL> select chr(75)||chr(79)||chr(82)||chr(69)||chr(65)
   2    from dual;

CHR(7
-----
KOREA

SQL>

1-2) CONCAT 함수
--------------------------------------------------------------------------------

 입력되는 두 문자열을 연결하여 반환한다.
 입력되는 두 문자열의 타입이 다를 경우 다음과 같이 반환된다.

첫 번째 문자열 타입  두 번째 문자열 타입  반환되는 문자열 타입 CLOB  NCLOB  NCLOB NCLOB  NCHAR  NCLOB NCLOB  CHAR  NCLOB NCHAR  CLOB  NCLOB


【예제】
SQL> select concat('Republic of',' KOREA') from dual;

CONCAT('REPUBLICO
-----------------
Republic of KOREA

SQL>  


1-3) INITCAP 함수
--------------------------------------------------------------------------------

initcap('string‘) 함수는 입력 문자열 중에서
각 단어의 첫 글자를 대문자로 나머지는 소문자로 변환한 스트링을 반환한다

【예제】
SQL> select initcap('beautiful corea') from dual;

INITCAP('BEAUTI
---------------
Beautiful Corea

SQL>


1-4) LOWER 함수
--------------------------------------------------------------------------------

lower(string) 함수는 입력된 문자열을 소문자로 반환한다.

【예제】
SQL> select lower('Beautiful COREA') from dual;

LOWER('BEAUTIFU
---------------
beautiful corea

SQL>  


1-5) LPAD 함수
--------------------------------------------------------------------------------

lpad(char1,n,char2) 함수는
지정된 길이 n에서 문자 char1으로 채우고
남은 공간은 왼쪽부터 char2로 채워서 출력한다.

【형식】
lpad (char1, n [, char2] )

【예제】
SQL> select lpad ('Corea', 12, '*') from dual;

LPAD('COREA'
------------
*******Corea

SQL>


1-6) LTRIM 함수
--------------------------------------------------------------------------------

 LTRIM(문자열, 문자)함수는 문자열중
좌측으로부터 특정문자와 일치하는 문자를 제거하고 출력한다.

【형식】
ltrim(char [,set] )

【예제】
SQL> select LTRIM('xyxXxyLAST WORD','xy') from dual;

LTRIM('XYXXX
------------
XxyLAST WORD

SQL>

 

1-7) NLS_INITCAP 함수
--------------------------------------------------------------------------------

nls_initcap(‘string’) 함수는 입력 문자열 중에서
 각 단어의 첫 글자를 대문자로
           나머지는 소문자로 변환한 스트링을 반환한다.
 단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.
nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,
 여기서 sort는 linguistic sort sequence나 binary중의 하나이다.

【형식】
nls_initcap ( char [,'nlsparam'] )

【예제】
SQL> select nls_initcap('beautiful corea', 'nls_sort=binary')
  2  from dual;

NLS_INITCAP('BE
---------------
Beautiful Corea

SQL> select nls_initcap('beautiful corea','nls_sort=XDutch')
  2  from dual;

NLS_INITCAP('BE
---------------
Beautiful Corea

SQL>


1-8) NLS_LOWER 함수
--------------------------------------------------------------------------------

nls_lower(‘string’) 함수는 입력 문자열을 모두 소문자로 변환한 스트링을 반환한다. 
 단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.
nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,
 여기서 sort는 linguistic sort sequence나 binary중의 하나이다.

【형식】
nls_lower ( char [,'nlsparam'] )

【예제】
SQL> select nls_lower('CITTA''','nls_sort=XGerman') from dual;

NLS_LO
------
citta'

SQL>


1-9) NLSSORT 함수
--------------------------------------------------------------------------------

nlssort(‘string’) 함수는 입력 문자열을 소팅하여 스트링을 반환한다.
  단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.
nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,
 여기서 sort는 linguistic sort sequence나 binary중의 하나이다.

【형식】
nlssort ( char [,'nlsparam'] )

【예제】
SQL> select * from emp
  2    order by nlssort(name, 'nls_sort=XDanish');

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1104 jijoe             220        100
      1103 kim               250        100

SQL>

 

1-10) NLS_UPPER 함수
--------------------------------------------------------------------------------

nls_upper(‘string’) 함수는 입력 문자열을 모두 소문자로 변환한 스트링을 반환한다.
  단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.
nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,
 여기서 sort는 linguistic sort sequence나 binary중의 하나이다.

【형식】
nls_upper ( char [,'nlsparam'] )

【예제】
SQL> select nls_upper('gro?e') from dual;

NLS_U
-----
gro?e

SQL> select nls_upper('gro?e','nls_sort=XGerman')
  2  from dual;

NLS_UP
------
grosse

SQL>

 

1-11) REPLACE 함수
--------------------------------------------------------------------------------

이 함수는 문자열에서 지정한 문자를 다른 문자로 치환한다.
  치환될 문자를 지정하지 않으면 해당 문자를 삭제한다.

【형식】
replace (char, search_string [, replacement_string] )

【예제】
SQL> select replace('aaabb','a','b') from dual;

REPLA
-----
bbbbb

SQL> select replace('aaabb','a') from dual;

RE
--
bb

SQL>


1-12) RPAD 함수
--------------------------------------------------------------------------------

rpad(char1,n,char2) 함수는 지정된 길이 n에서 문자 char1으로 채우고
남은 공간은 오른쪽부터 char2로 채워서 출력한다.

【형식】
rpad (char1, n [, char2] )

【예제】
SQL> select rpad('Corea',12,'*') from dual;

RPAD('COREA'
------------
Corea*******

SQL>

 

1-13) RTRIM 함수
--------------------------------------------------------------------------------

 RTRIM(문자열, 문자)함수는 문자열중
 우측으로부터 특정문자와 일치하는 문자를 제거하고 출력한다.

【형식】
rtrim(char [,set] )

【예제】
SQL> select RTRIM('BROWINGyxXxy','xy') "RTRIM example" from dual;

RTRIM exam
----------
BROWINGyxX

SQL>


1-14) SOUNDEX 함수
--------------------------------------------------------------------------------

soundex(‘char’) 함수는 char과 같은 발음의 이름을 표현한다.

【예제】
SQL> select name from emp;

NAME
----------
Cho
Joe
kim
jijoe

SQL> select name from emp
  2  where soundex(name) = soundex('jo');

NAME
----------
Joe

SQL>

 

1-15) SUBSTR 함수
--------------------------------------------------------------------------------

substr(str,m,n) 함수는 문자열 str 중에서 특정 위치 m으로부터 특정 길이n 만큼의 문자를 출력한다.
  m이 0이나 1이면 문자열의 첫글자를 의미하고,
  n이 생략되면 문자열의 끝까지를 의미한다.
  m이 음수이면 뒤쪽으로부터의 위치를 의미한다.

 SUBSTRB는 character 대신 byte를 사용하고,
 SUBSTDC는 unicode를 사용하며,
 SUBSTR2는 UCS2 codepoint를 사용하고,
 SUBSTR4는 UCS4 codepoint를 사용한다.

【형식】
{SUBSTR|SUBSTRB|SUBSTRC|SUNBSTD2|SUBSTR4}
  ( string, position [,substring_length] )

【예제】
SQL> select substr('abcdesfg', 3,2) from dual;

SU
--
cd

SQL> select substr('abcdefg',3) from dual;

SUBST
-----
cdefg

SQL> select substr('abcdefg', -3,2) from dual; ☜ 뒤에서 3번째부터 2글자를 의미한다.
SU
--
ef

SQL>

 

1-16) TRANSLATE 함수
--------------------------------------------------------------------------------

TRANSLATE (‘char’,‘from_string’,‘to_string’) 함수는
 char 내에 포함된 문자중 from_string에 지정한 모든 각각의 문자를
                         to_string문자로 각각 변경한다.

【형식】
TRANSLATE ('char','from_string','to_string')

【예제】
SQL> select translate('ababccc','c','d') from dual;

TRANSLA
-------
ababddd

SQL> select translate('2KRW229',
  2  '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
  3  '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') from dual;

TRANSLA
-------
9XXX999

SQL> select translate('2KRW229',
  2  '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789')
  3  from dual;

TRAN
----
2229

SQL>

 

1-17) TREAT 함수
--------------------------------------------------------------------------------

TREAT 함수는 선언된 타입을 변경함으로써, 수퍼타입을 서브타입인 것처럼 처리할 수 있도록 한다.

【형식】
TREAT ( expr AS [ REF] [schema . ] type )

【예제】
SQL> select x.p.empno from person_table p;
select x.p.empno from person_table p
       *
ERROR at line 1:
ORA-00904: "X"."P"."EMPNO": invalid identifier
 
SQL> select treat(x.p as employee).empno empno,
  2               x.p.last_name last_name
  3   from person_table x;
 
     EMPNO LAST_NAME
---------- --------------------
           Seoul
      1234 Inchon
      5678 Arirang
 
SQL>

【예제】
SQL> select name, TREAT(VALUE(p) AS employee_t).salary salary
  2   FROM person p;

NAME                        SALARY
----------------------   ---------
Bob   
Joe                         100000
Tim                           1000

SQL>

 

1-18) TRIM 함수
--------------------------------------------------------------------------------

 이 함수는 LTRIM과 RTRIM 함수를 결합한 형태로
  문자값의 왼쪽 또는 오른쪽 부분에 정의한 문자를 절삭하여 출력한다.
LEADING은 LTRIM처럼 문자열 왼쪽의 문자를 지정하여 절삭하고,
 TRAILING은 RTRIM처럼 문자열 오른쪽 문자를 지정하여 절삭한다.
 BOTH는 왼쪽과 오른쪽 문자를 지정하여 절삭한다.

【형식】
TRIM ([{{{LEADING|TRAILING|BOTH} [trim_char] } | trim_char} FROM]
      trim_source )

【예제】
SQL> select trim (0 from 000123400) from dual;

TRIM
----
1234

SQL> select trim(trailing 'a' from 'abca') from dual;

TRI
---
abc

SQL> select trim(leading 'a' from 'abca') from dual;

TRI
---
bca

SQL> select trim(both 'a' from 'abca') from dual;

TR
--
bc

SQL>


1-19) UPPER 함수
--------------------------------------------------------------------------------

upper(string) 함수는 입력된 문자열을 대문자로 반환한다.

【예제】
SQL> select upper('Beautiful COREA') from dual;

UPPER('BEAUTIFU
---------------
BEAUTIFUL COREA

SQL>

 

1-20) ASCII 함수
--------------------------------------------------------------------------------

ASCII
ascii(‘char’) 함수는 주어진 char의 첫 글자의 아스키 값을 반환한다.
 char의 타입은 char, varchar2, nchar, nvarchar2중의 하나이어야 한다.

【예제】
SQL> select ascii('Korea') from dual;

ASCII('KOREA')
--------------
            75

SQL> select ascii('K') from dual;

ASCII('K')
----------
        75

SQL>

 

1-21) INSTR 함수
--------------------------------------------------------------------------------

이 함수는 문자 스트링 중에서
지정한 문자가 가장 처음 나타나는 위치를 숫자로 출력한다.

【형식】
{INSTR|INSTRB|INSTRC|INSTR2|INSTR4}
  ( string, substring [, position [,occurrence] ] )

【예제】
SQL> select instr('Corea','e') from dual;

INSTR('COREA','E')
------------------
                 4

SQL> select instr('corporate floor','or',3,2) from dual;

INSTR('CORPORATEFLOOR','OR',3,2)
--------------------------------
                              14

SQL> select instrb('corporate floor','or',5,2) from dual;

INSTRB('CORPORATEFLOOR','OR',5,2)
---------------------------------
                               14

SQL>


1-22) LENGTH 함수
--------------------------------------------------------------------------------

 LENGTH(char) 함수는 char의 길이를 반환한다.
LENGTHB는 character 대신 byte를 사용하고,
LENGTHC는 unicode를 사용하며,
LENGTH2는 UCS2 codepoint를 사용하고,
LENGTH4는 UCS4 codepoint를 사용한다.

【형식】
{LENGTH| LENGTHB| LENGTHC| LENGTH2| LENGTH4} (char)

【예제】
SQL> select length('Corea') from dual;

LENGTH('COREA')
---------------
              5

SQL> select lengthb('Corea') from dual;

LENGTHB('COREA')
----------------
               5

SQL>

 


2-1) ADD_MONTHS 함수
--------------------------------------------------------------------------------

ADD_MONTHS 
 ADD_MONTHS(d, n)는 날짜 d에 n 개월을 더한 일자를 반환한다.

【예제】
SQL> select current_date today, add_months(current_date,1) "next month"
  2  from dual;

TODAY     next mont
--------- ---------
29-JUL-04 29-AUG-04

SQL>

 

2-2) CURRENT_DATE 함수
--------------------------------------------------------------------------------


 이 함수는 현재 session의 날짜 정보를 반환한다.
【예제】
SQL> select current_date from dual;

CURRENT_D
---------
31-JUL-04

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
--------------------------------------------------------------------------
+09:00

SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select current_date from dual;

CURRENT_DATE
--------------------
31-JUL-2004 09:31:57

SQL> alter session set time_zone='-5:0';

Session altered.

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
--------------------------------------------------------------------------
-05:00

SQL>

 

2-3) URRENT_TIMESTAMP 함수
--------------------------------------------------------------------------------

 이 함수는 현재 session의 날짜와 시간 정보를 반환한다.
 current_timestamp는 time zone까지 출력되지만,
 localtimestamp는 time zone은 출력되지 않는다.
【예제】
SQL> select current_timestamp, localtimestamp,
  2  current_date from dual;

CURRENT_TIMESTAMP
--------------------------------------------------------------------------
LOCALTIMESTAMP
--------------------------------------------------------------------------
CURRENT_D
---------
04-AUG-04 11.17.40.768776 AM +09:00
04-AUG-04 11.17.40.768776 AM
04-AUG-04

SQL>

 

2-4) DBTIMEZONE 함수
--------------------------------------------------------------------------------


데이터베이스 timezone을 반환한다.
【예제】
SQL> select dbtimezone from dual;

DBTIME
------
-07:00

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
--------------------------------------------------------------------------
+09:00

SQL>

 

2-5) EXTRACT(datetime) 함수
--------------------------------------------------------------------------------

특정 날짜/시간 값이나 날짜 값을 가진 표현식으로부터
  원하는 날짜 영역을 추출하여 출력한다. 

【형식】
EXTRACT ({year|month|day|hour|minute|second|
         timezone_hour|timezone_minute|
         timezone_region|timezone_abbr}
 FROM {datetime_value_expr|interval_value_rxpr})

【예제】
SQL> select extract(year from date '2004-8-2') from dual;

EXTRACT(YEARFROMDATE'2004-8-2')
-------------------------------
                           2004

SQL>

 

2-6) FROM_TZ 함수
--------------------------------------------------------------------------------

이 함수는 timestamp 값을 timestamp with time zone 값으로 변환한다.

【형식】
FROM_TZ ( timestamp_value, time_zone_value)

【예제】
SQL> select from_tz(timestamp '2004-8-11 08:00:00','3:00') from dual;

FROM_TZ(TIMESTAMP'2004-8-1108:00:00','3:00')
--------------------------------------------------------------------------
11-AUG-04 08.00.00.000000000 AM +03:00

SQL>

 

2-7) LAST_DAY 함수
--------------------------------------------------------------------------------

 이 함수는 지정한 달의 마지막 날을 출력한다.

【형식】
LAST_DAY ( date )

【예제】
SQL> select sysdate, last_day(sysdate) "last day",
  2  last_day(sysdate)- sysdate "Days Left"
  3  from dual;

SYSDATE   last day   Days Left
--------- --------- ----------
04-AUG-04 31-AUG-04         27

SQL>

 

2-8) LOCALTIMESTAMP 함수
--------------------------------------------------------------------------------

이 함수는 timestamp의 현재 날짜와 시각을 출력한다.
 current_timestamp는 time zone까지 출력되지만,
 localtimestamp는 time zone은 출력되지 않는다.

【형식】
localtimestamp [(timestamp_precision)]

【예제】
SQL> select current_timestamp, localtimestamp,
  2  current_date from dual;

CURRENT_TIMESTAMP
--------------------------------------------------------------------------
LOCALTIMESTAMP
--------------------------------------------------------------------------
CURRENT_D
---------
04-AUG-04 11.17.40.768776 AM +09:00
04-AUG-04 11.17.40.768776 AM
04-AUG-04

SQL>
 
【예제】오류가 발생하는 이유를 잘 이해하자.
SQL> CREATE TABLE local_test(col1 TIMESTAMP WITH LOCAL TIME ZONE);

Table created.

SQL> INSERT INTO local_test VALUES
  2  (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'));
(TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'))
              *
ERROR at line 2:
ORA-01830: date format picture ends before converting entire input string

SQL> INSERT INTO local_test VALUES
  2  (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));

1 row created.

SQL> select * from local_test;

COL1
--------------------------------------------------------------------------
04-AUG-04 11.33.58.183398 AM

SQL>

 


2-9) MONTHS_BETWEEN 함수
--------------------------------------------------------------------------------

 MONTHS_BETWEEN(date1,date2) 함수는 date1과 date로 나타내는
 날짜와 날짜 사이의 개월 수를 출력한다.

【예제】
SQL> select months_between
  2  (to_date('02-02-2004','MM-DD-YYYY'),
  3   to_date('01-01-2003','MM-DD-YYYY') ) "Months"
  4  FROM dual;

    Months
----------
13.0322581

SQL>

 

2-10) NEW_TIME 함수
--------------------------------------------------------------------------------

NEW_TIME(date,zone1,zone2) 함수는 date, zone1 시간대를 zone2 시간대로 출력한다.
여기서 사용되는 zone은 다음 중의 하나이다.

 AST,ADT : Atlantic Standard or Daylight Time
 BST,BDT : Bering Standard or Daylight Time
 CST,CDT : Central Standard or Daylight Time
 EST,EDT : Eastern Standard or Daylight Time
 GMT : Greenwich Mean Time
 HST,HDT : Alaska-Hawaii Standard or Daylight Time
 MST,MDT : Mountain Standard or Daylight Time
 NST : Newfoundland Standard Time
 PST,PDT : Pacific Standard or Daylight Time
 YST,YDT : Yukon Standard or Daylight Time

【예제】
SQL> alter session set nls_date_format =
  2  'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select NEW_TIME(TO_DATE(
  2  '11-10-04 01:23:33', 'MM-DD-YY HH24:MI:SS'),
  3  'AST', 'PST') FROM DUAL;

NEW_TIME(TO_DATE('11
--------------------
09-NOV-2004 21:23:33

SQL>

 


2-11) NEXT_DAY 함수
--------------------------------------------------------------------------------

NEXT_DAY(date,char) 함수는 date로부터 char로 명시한 가장 최근의 날짜를 출력한다.

【예제】
SQL> select next_day('02-AUG-2004','MONDAY') from dual;

NEXT_DAY('02-AUG-200
--------------------
09-AUG-2004 00:00:00

SQL>

 


2-12) NUMTODSINTERVAL 함수
--------------------------------------------------------------------------------

 NUMTODSINTERVAL(n,'char_expr') 함수는 n을 interval day to second로 변환하여 출력한다.
 char_expr은 다음 중의 하나이다.
  ‘DAY’
  ‘HOUR’
  ‘MINUTE’
  ‘SECOND’

【예제】
SQL> select numtodsinterval(100,'MINUTE') from dual;

NUMTODSINTERVAL(100,'MINUTE')
--------------------------------------------------------------------------
+000000000 01:40:00.000000000

SQL>

【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename, hiredate,
  2  numtodsinterval(100,'day')+hiredate from emp;

ENAME      HIREDATE  NUMTODSIN
---------- --------- ---------
SMITH      17-DEC-80 27-MAR-81
ALLEN      20-FEB-81 31-MAY-81
WARD       22-FEB-81 02-JUN-81
JONES      02-APR-81 11-JUL-81
MARTIN     28-SEP-81 06-JAN-82
BLAKE      01-MAY-81 09-AUG-81
CLARK      09-JUN-81 17-SEP-81
SCOTT      19-APR-87 28-JUL-87
KING       17-NOV-81 25-FEB-82
TURNER     08-SEP-81 17-DEC-81
ADAMS      23-MAY-87 31-AUG-87
JAMES      03-DEC-81 13-MAR-82
FORD       03-DEC-81 13-MAR-82
MILLER     23-JAN-82 03-MAY-82

14 rows selected.

SQL>

 

2-13) NUMTOYMINTERVAL 함수
--------------------------------------------------------------------------------

NUMTOYMINTERVAL(n,'char_expr') 함수는 n을 interval year to month로 변환하여 출력한다.
 char_expr은 다음 중의 하나이다.
  ‘YEAR’
  ‘MONTH’ 

【예제】
SQL> select numtoyminterval(30,'month') from dual;

NUMTOYMINTERVAL(30,'MONTH')
---------------------------------------------------------------------------
+000000002-06

SQL>

【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename,hiredate,
  2  numtoyminterval(30,'month')+hiredate from emp;

ENAME      HIREDATE  NUMTOYMIN
---------- --------- ---------
SMITH      17-DEC-80 17-JUN-83
ALLEN      20-FEB-81 20-AUG-83
WARD       22-FEB-81 22-AUG-83
JONES      02-APR-81 02-OCT-83
MARTIN     28-SEP-81 28-MAR-84
BLAKE      01-MAY-81 01-NOV-83
CLARK      09-JUN-81 09-DEC-83
SCOTT      19-APR-87 19-OCT-89
KING       17-NOV-81 17-MAY-84
TURNER     08-SEP-81 08-MAR-84
ADAMS      23-MAY-87 23-NOV-89
JAMES      03-DEC-81 03-JUN-84
FORD       03-DEC-81 03-JUN-84
MILLER     23-JAN-82 23-JUL-84

14 rows selected.

SQL>

 


2-14) ROUND(date) 함수
--------------------------------------------------------------------------------

이 함수는 날짜를 주어진 형식으로 반올림하는 함수이다.
 날짜 형식이 없으면 가장 가까운 날을 출력한다.

【형식】
ROUND( date [,fmt] )

【예제】
SQL> select localtimestamp, round(sysdate,'year') from dual;

LOCALTIMESTAMP
--------------------------------------------------------------------------
ROUND(SYS
---------
04-AUG-04 01.26.24.197977 PM
01-JAN-05

SQL> select localtimestamp,round(sysdate,'day') from dual;

LOCALTIMESTAMP
--------------------------------------------------------------------------
ROUND(SYS
---------
04-AUG-04 01.29.57.839269 PM
08-AUG-04

SQL> select localtimestamp,round(sysdate) from dual;

LOCALTIMESTAMP
--------------------------------------------------------------------------
ROUND(SYS
---------
04-AUG-04 01.30.11.552050 PM
05-AUG-04

SQL>

 

2-15) SESSIONTIMEZONE 함수
--------------------------------------------------------------------------------

이 함수는 현재 세션의 시간대역을 출력한다.

【예제】
SQL> select sessiontimezone, current_timestamp from dual;

SESSIONTIMEZONE
--------------------------------------------------------------------------
CURRENT_TIMESTAMP
--------------------------------------------------------------------------
+09:00
04-AUG-04 01.37.13.355873 PM +09:00

SQL> select sessiontimezone, tz_offset(sessiontimezone) from dual;

SESSIONTIMEZONE
--------------------------------------------------------------------------
TZ_OFFS
-------
+09:00
+09:00

SQL>

 

2-16) SYS_EXTRACT_UTC 함수
--------------------------------------------------------------------------------

sys_extract_utc(datetime_with_timezone) 함수는
UTC(coordinated universal time: Greenwich mean time) 시각을 반환한다. 

【예제】
SQL> select systimestamp, sys_extract_utc(systimestamp) from dual;

SYSTIMESTAMP
--------------------------------------------------------------------------
SYS_EXTRACT_UTC(SYSTIMESTAMP)
--------------------------------------------------------------------------
06-AUG-04 02.41.39.258976 PM +09:00
06-AUG-04 05.41.39.258976 AM


SQL>

 

2-17) SYSDATE 함수
--------------------------------------------------------------------------------

이 함수는 오늘 현재 날짜와 시각을 출력한다.

【예제】
SQL> select sysdate, current_timestamp from dual;

SYSDATE
---------
CURRENT_TIMESTAMP
--------------------------------------------------------------------------
04-AUG-04
04-AUG-04 01.51.39.767156 PM +09:00

SQL> select to_char
  2  (sysdate, 'MM-DD-YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'MM
-------------------
08-04-2004 13:53:18

SQL>

 

2-18) SYSTIMESTAMP 함수
--------------------------------------------------------------------------------

이 함수는 시스템의 날짜를 출력한다.

【예제】
SQL> select sysdate,systimestamp,localtimestamp from dual;

SYSDATE
---------
SYSTIMESTAMP
--------------------------------------------------------------------------
LOCALTIMESTAMP
--------------------------------------------------------------------------
04-AUG-04
04-AUG-04 01.58.06.346528 PM +09:00
04-AUG-04 01.58.06.346552 PM

SQL>

 

2-19) TO_DSINTERVAL 함수
--------------------------------------------------------------------------------

이 함수는 문자열을 interval day to second 형식으로 변환하는 함수이다.

【형식】
to_dsinterval ( char [ ‘nlsparam’] )

【예제】
SQL> select sysdate,
  2  sysdate+to_dsinterval('003 17:00:00') as "3days 17hours later"
  3  from dual;

SYSDATE   3days 17h
--------- ---------
04-AUG-04 08-AUG-04

SQL>

 

2-20) TO_TIMESTAMP 함수
--------------------------------------------------------------------------------

이 함수는 문자열을 timestamp 형식으로 변환하여 출력한다 

【형식】
to_timestamp ( char [,fmt ['nlsparam'] ] )

【예제】
SQL> select to_timestamp('2004-8-20 1:30:00', 'YYYY-MM-DD HH:MI:SS')
  2  from dual;

TO_TIMESTAMP('2004-8-201:30:00','YYYY-MM-DDHH:MI:SS')
--------------------------------------------------------------------------
20-AUG-04 01.30.00.000000000 AM

SQL>

 


2-21) TO_TIMESTAMP_TZ 함수
--------------------------------------------------------------------------------

이 함수는 문자열을 timestamp with time zone 형식으로 변환하여 출력한다. 

【형식】
to_timestamp_tz ( char [,fmt ['nlsparam'] ] )

【예제】
SQL> select to_timestamp_tz('2004-8-20 1:30:00 -3:00',
  2  'YYYY-MM-DD HH:MI:SS TZH:TZM') from dual;

TO_TIMESTAMP_TZ('2004-8-201:30:00-3:00','YYYY-MM-DDHH:MI:SSTZH:TZM')
--------------------------------------------------------------------------
20-AUG-04 01.30.00.000000000 AM -03:00

SQL>

 

2-22) TO_YMINTERVAL 함수
--------------------------------------------------------------------------------

TO_YMINTERVAL ( char ) 함수는 문자열을 interval year to month 형식으로 변환하는 함수이다.

【예제】
SQL> select sysdate,
  2  sysdate+to_yminterval('01-03') "15Months later"
  3  from dual;

SYSDATE   15Months
--------- ---------
04-AUG-04 04-NOV-05

SQL>

 

2-23) TRUNC(date) 함수
--------------------------------------------------------------------------------

이 함수는 날짜를 절삭하여 출력한다.

【형식】
TRUNC ( date [.fmt] )

【예제】
SQL> select trunc(to_date('27-AUG-04','DD-MON-YY'), 'YEAR')
  2  from dual;

TRUNC(TO_
---------
01-JAN-04

SQL>

 

2-24) TZ_OFFSET 함수
--------------------------------------------------------------------------------

이 함수는 time zone의 offset 값을 출력한다.

【형식】
TZ_OFFSET ( {‘time_zone_name’ | SESSIONTIMEZONE | DBTIMEZONE |
             ‘{+|-} hh:mi’ } )

【예제】
SQL> select sessiontimezone, tz_offset('ROK') from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
TZ_OFFS
-------
+09:00
+09:00

SQL>

 


3-1) ASCIISTR 함수
--------------------------------------------------------------------------------

 asciistr('string')의 string의 아스키 문자로 반환한다.
Ä
【예제】
SQL> select ascii('ABÄCDE') from dual;
☜ ABÄCDE의 두번째 Ä는 A에 움라우트(Umlaut)가 붙은 글씨이다.

ASCIIS
------
ABDCDE
           
SQL>

 


3-2) BIN_TO_NUM 함수
--------------------------------------------------------------------------------

 이 함수는 2진수 벡터를 10진수로 변환한다.

SQL> select bin_to_num(1,0,1,0) from dual;

BIN_TO_NUM(1,0,1,0)
-------------------
                 10

SQL>

 

3-3) CAST 함수
--------------------------------------------------------------------------------

 데이터형식이나 collection 형식을 다른 데이터형식이나 다른 collection 형식으로 변환한다.

【예제】데이터형식인 경우
SQL> select current_date from dual;

CURRENT_D
---------
30-JUL-04

SQL> select cast(current_date as timestamp) from dual;

CAST(CURRENT_DATEASTIMESTAMP)
---------------------------------------------------------------------------
30-JUL-04 12.29.15.000000 PM

SQL>

 

3-4) CHARTOROWID 함수
--------------------------------------------------------------------------------

 이 함수는 char, varchar2, nchar, ncharvar2형 데이터 타입을 rowid 형 데이터 타입으로 변경한다.
【예제】
SQL> select name from emp
  2  where rowid = chartorowid('AAAHZ+AABAAAMWiAAF');

NAME
----------
jijoe

SQL> select rowid,name from emp;

ROWID              NAME
------------------ ----------
AAAHZ+AABAAAMWiAAA Cho
AAAHZ+AABAAAMWiAAB Joe
AAAHZ+AABAAAMWiAAC kim
AAAHZ+AABAAAMWiAAF jijoe

SQL>


여기서 rowid의 의미는 다음과 같다.
AAAHZ+  AAB  AAAMWi  AAA 객체번호  테이블스페이스번호  블록번호  행번호

 


3-5) COMPOSE 함수
--------------------------------------------------------------------------------

 입력된 스트링을 unicode로 나타낸다.

【예제】
SQL> select compose('aa' || unistr('\0308') ) from dual;

CO
--
aa

SQL>

 

3-6) CONVERT 함수
--------------------------------------------------------------------------------

입력된 문자열을 지정한 코드로 변환한다.
공용 문자셋은 살펴보자.
US7ASCII  US 7-bit ASCII 문자 WE8DEC  서유럽 8비트 문자 WE8HP  HP 서유럽 레이져젯 8비트 문자 F7DEC  DEC 프랑스 7비트 문자 WE8EBCDIC500  IBM 서유럽 EBCDIC 코드 페이지 500 WE8PC850  IBM PC 코드 페이지 850 WE8ISO8859P1  ISO 8859 서유럽 8비트 문자

【예제】
SQL> select convert('arirang','we8pc850') from dual;

CONVERT
-------
arirang

SQL>

 

3-7) HEXTORAW 함수
--------------------------------------------------------------------------------

HEXTORAW(char) 함수는 char, varchar2, nchar, nvarchar2 따위의 문자로 주어지는
 hexadecimal digit을 raw 값으로 변환한다.

【예제】
SQL> create table test(raw_col RAW(10));

Table created.

SQL> insert into test VALUES (HEXTORAW('7D'));

1 row created.

SQL> select * from test;

RAW_COL
--------------------
7D

SQL>

 


3-8) NUMTODSINTERVAL 함수
--------------------------------------------------------------------------------

 NUMTODSINTERVAL(n,'char_expr') 함수는 n을 interval day to second로 변환하여 출력한다.
 char_expr은 다음 중의 하나이다.
  ‘DAY’
  ‘HOUR’
  ‘MINUTE’
  ‘SECOND’

【예제】
SQL> select numtodsinterval(100,'MINUTE') from dual;

NUMTODSINTERVAL(100,'MINUTE')
--------------------------------------------------------------------------
+000000000 01:40:00.000000000

SQL>

【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename, hiredate,
  2  numtodsinterval(100,'day')+hiredate from emp;

ENAME      HIREDATE  NUMTODSIN
---------- --------- ---------
SMITH      17-DEC-80 27-MAR-81
ALLEN      20-FEB-81 31-MAY-81
WARD       22-FEB-81 02-JUN-81
JONES      02-APR-81 11-JUL-81
MARTIN     28-SEP-81 06-JAN-82
BLAKE      01-MAY-81 09-AUG-81
CLARK      09-JUN-81 17-SEP-81
SCOTT      19-APR-87 28-JUL-87
KING       17-NOV-81 25-FEB-82
TURNER     08-SEP-81 17-DEC-81
ADAMS      23-MAY-87 31-AUG-87
JAMES      03-DEC-81 13-MAR-82
FORD       03-DEC-81 13-MAR-82
MILLER     23-JAN-82 03-MAY-82

14 rows selected.

SQL>

 


3-9) NUMTOYMINTERVAL 함수
--------------------------------------------------------------------------------

NUMTOYMINTERVAL(n,'char_expr') 함수는 n을 interval year to month로 변환하여 출력한다.
 char_expr은 다음 중의 하나이다.
  ‘YEAR’
  ‘MONTH’ 

【예제】
SQL> select numtoyminterval(30,'month') from dual;

NUMTOYMINTERVAL(30,'MONTH')
---------------------------------------------------------------------------
+000000002-06

SQL>

【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename,hiredate,
  2  numtoyminterval(30,'month')+hiredate from emp;

ENAME      HIREDATE  NUMTOYMIN
---------- --------- ---------
SMITH      17-DEC-80 17-JUN-83
ALLEN      20-FEB-81 20-AUG-83
WARD       22-FEB-81 22-AUG-83
JONES      02-APR-81 02-OCT-83
MARTIN     28-SEP-81 28-MAR-84
BLAKE      01-MAY-81 01-NOV-83
CLARK      09-JUN-81 09-DEC-83
SCOTT      19-APR-87 19-OCT-89
KING       17-NOV-81 17-MAY-84
TURNER     08-SEP-81 08-MAR-84
ADAMS      23-MAY-87 23-NOV-89
JAMES      03-DEC-81 03-JUN-84
FORD       03-DEC-81 03-JUN-84
MILLER     23-JAN-82 23-JUL-84

14 rows selected.

SQL>

 

 


3-10) RAWTOHEX 함수
--------------------------------------------------------------------------------

RAWTOHEX(raw) 함수는 raw 값을 hexadecimal 값으로 변환한다.

【예제】
SQL> create table test(raw_col RAW(10));

Table created.

SQL> insert into test VALUES (HEXTORAW('7D'));

1 row created.

SQL> select * from test;

RAW_COL
--------------------
7D

SQL> select rawtohex(raw_col) from test;

RAWTOHEX(RAW_COL)
--------------------
7D

SQL>

 

3-11) RAWTONHEX 함수
--------------------------------------------------------------------------------

RAWTONHEX(raw) 함수는 raw 값을 nvarchar2형 hexadecimal 값으로 변환한다.

【예제】
SQL> create table test(raw_col RAW(10));

Table created.

SQL> insert into test VALUES (HEXTORAW('7D'));

1 row created.

SQL> select * from test;

RAW_COL
--------------------
7D

SQL> select rawtonhex(raw_col) from test;

RAWTONHEX(RAW_COL)
--------------------
7D

SQL>

 

3-12) ROWIDTOCHAR 함수
--------------------------------------------------------------------------------

RAWIDTOCHAR(rowid) 함수는 rowid 값을 varchar2 형식의 데이터로 변환한다.

【예제】
SQL> select rowid from test;

ROWID
------------------
AAAHbHAABAAAMXCAAA

SQL> select rowid from test
  2  where rowidtochar(rowid) like '%AABAA%';

ROWID
------------------
AAAHbHAABAAAMXCAAA

SQL>

여기서 rowid의 의미는 다음과 같다.
AAAHbH  AAB  AAAMXC  AAA 객체번호  테이블스페이스번호  블록번호  행번호

 


3-13) ROWIDTONCHAR 함수
--------------------------------------------------------------------------------

RAWIDTONCHAR(rowid) 함수는 rowid 값을 nvarchar2 형식의 데이터로 변환한다.

【예제】
SQL> select rowid from test;

ROWID
------------------
AAAHbHAABAAAMXCAAA

SQL> select rowid from test
  2  where rowidtochar(rowid) like '%AABAA%';

ROWID
------------------
AAAHbHAABAAAMXCAAA

SQL> select lengthb(rowidtonchar(rowid)), rowidtonchar(rowid)
  2  from test;

LENGTHB(ROWIDTONCHAR(ROWID)) ROWIDTONCHAR(ROWID
---------------------------- ------------------
                          36 AAAHbHAABAAAMXCAAA

SQL>


여기서 rowid의 의미는 다음과 같다.
AAAHbH  AAB  AAAMXC  AAA 객체번호  테이블스페이스번호  블록번호  행번호

 


3-14) TO_CHAR(character) 함수
--------------------------------------------------------------------------------

 이 함수는 nchar, nvarchar2, clob, nclob 형식의 데이터를
데이터베이스 character set으로 변환한다. 즉, 문자로 변환한다.

【형식】
 TO_CHAR( nchar| clob | nclob)

【예제】
SQL> select to_char('01110') from dual;

TO_CH
-----
01110

SQL>

 


3-15) TO_CLOB 함수
--------------------------------------------------------------------------------

이 함수는 LOB 컬럼에 있는 NCLOB나 또는 다른 문자 스트링을 CLOB로 변환한다.

【형식】
 TO_CLOBR({ lob_column | char})

【예제】
SQL> select to_clob('corea') from dual;

TO_CLOB('COREA')
--------------------------------------------------------------------------
corea

SQL>

 


3-16) TO_DSINTERVAL 함수
--------------------------------------------------------------------------------

이 함수는 문자열을 interval day to second 형식으로 변환하는 함수이다.

【형식】
to_dsinterval ( char [ ‘nlsparam’] )

【예제】
SQL> select sysdate,
  2  sysdate+to_dsinterval('003 17:00:00') as "3days 17hours later"
  3  from dual;

SYSDATE   3days 17h
--------- ---------
04-AUG-04 08-AUG-04

SQL>

 

3-17) TO_LOB 함수
--------------------------------------------------------------------------------

 TO_LOB(long_column) 함수는 LONG, LONG RAW 컬럼의 데이터를 LOB 값으로 변환한다.

【예제】
SQL> create table test2(zz clob);

Table created.

SQL> insert into test2
  2  (select to_lob(p.raw_col) from test p);

SQL>

 

3-18) TO_MULTI_BYTE 함수
--------------------------------------------------------------------------------

TO_MULTI_BYTE(char) 함수는 싱글 바이트 문자를 포함한 모든 문자열을 다중바이트 문자열로 변환한다.

【예제】
SQL> select dump(to_multi_byte('Corea')) from dual;

DUMP(TO_MULTI_BYTE('COREA'))
-----------------------------------------------------
Typ=1 Len=10: 163,195,163,239,163,242,163,229,163,225

SQL>

 


3-19) TO_NCHAR(character) 함수
--------------------------------------------------------------------------------

 이 함수는 문자스트링, clob, nclob 형식의 데이터를 national character set,
 즉 nchar으로 변환한다. 이는 translate ... using 문의 경우와 같다.

【형식】
TO_NCHAR({char|clob|nclob} [,fmt [,'nlsparam']])

【예제】
SQL> select to_nchar('Corea') from dual;

TO_NC
-----
Corea

SQL>

 

3-20) TO_NCHAR(datetime) 함수
--------------------------------------------------------------------------------

 이 함수는 date, timestamp, timestamp with time zone, timestamp with local time zone,
 interval month to year, interval day to second 형식의 데이터를
 nchar 형식의 데이터로 변환한다.

【형식】
TO_NCHAR({datetime|interval} [,fmt [,'nlsparam']])

【예제】
SQL> select to_nchar(sysdate) from dual;

TO_NCHAR(SYSDATE)
------------------------------
05-AUG-04

SQL>

 


3-21) TO_NCHAR(number) 함수
--------------------------------------------------------------------------------

 이 함수는 숫자를 nvarchar2 형식의 데이터로 변환한다.

【형식】
TO_NCHAR(n [,fmt [,'nlsparam']])

【예제】
SQL> select to_nchar(1234) from dual;

TO_N
----
1234

SQL> select to_nchar(rownum) from test;

TO_NCHAR(ROWNUM)
----------------------------------------
1

SQL>

 


3-22) TO_NCLOB 함수
--------------------------------------------------------------------------------

이 함수는 clob, 문자열 형식의 데이터를 nclob 형식의 데이터로 변환한다.

【형식】
TO_NCLOB({char|lob_column})

【예제】
SQL> select to_nclob('Corea') from dual;

TO_NCLOB('COREA')
--------------------------------------------------------------------------
Corea

SQL>

 


3-23) TO_NUMBER 함수
--------------------------------------------------------------------------------

이 함수는 숫자를 포함하는 char, varchar2, nchar, nvarchar2 형식의
 문자 데이터를 number 형식의 숫자 데이터로 변환한다.

【형식】
TO_NUMBER(char [,fmt [,'nlsparam']])

【예제】
SQL> select to_number('1234') from dual;

TO_NUMBER('1234')
-----------------
             1234

SQL>

 

3-24) TO_SINGLE_BYTE 함수
--------------------------------------------------------------------------------

TO_SINGLE_BYTE(char) 함수는 다중 바이트 문자열을 single byte 문자로 변환한다.

【예제】
SQL> select dump(to_multi_byte('Corea')) from dual;

DUMP(TO_MULTI_BYTE('COREA'))
-----------------------------------------------------
Typ=1 Len=10: 163,195,163,239,163,242,163,229,163,225

SQL> select dump(to_single_byte('Corea')) from dual;

DUMP(TO_SINGLE_BYTE('COREA'))
------------------------------
Typ=1 Len=5: 67,111,114,101,97

SQL> select to_single_byte(chr(65)) from dual;

T
-
A

SQL>

 


3-25) TO_YMINTERVAL 함수
--------------------------------------------------------------------------------

TO_YMINTERVAL ( char ) 함수는 문자열을 interval year to month 형식으로 변환하는 함수이다.

【예제】
SQL> select sysdate,
  2  sysdate+to_yminterval('01-03') "15Months later"
  3  from dual;

SYSDATE   15Months
--------- ---------
04-AUG-04 04-NOV-05

SQL>

 

3-26) TRANSLATE ... USING 함수
--------------------------------------------------------------------------------

이 함수는 텍스트 형식의 데이터를 지정한 문자 형식의 데이터로 변환한다.

【형식】
TRANSLATE ( text USING {CHAR_CS|NCHAR_CS} )

【예제】
SQL> select translate('Corea' USING char_cs) from dual;

TRANS
-----
Corea

SQL> select to_nchar('Corea') from dual;

TO_NC
-----
Corea

SQL>

 

3-27) UNISTR 함수
--------------------------------------------------------------------------------

UNISTR('string') 함수는 스트링 형식의 데이터를 nchar 형식의 데이터로 변환한다.

【예제】
SQL> select unistr('abc\00e5\00f1\00f6') from dual;

UNISTR
------
abc??o

SQL> select unistr('Corea') from dual;

UNIST
-----
Corea

SQL>

 


4-1) BFILENAME 함수
--------------------------------------------------------------------------------

 서버 파일 시스템 상에 실제로 위치한 LOB 바이너리 파일의 위치한 BFILE locator를 반환한다.
【형식】
bfilename ('디렉토리‘,’파일이름‘)

【예제】BFILE을 insert하는 예
SQL> connect system/manager

SQL> host mkdir /export/home/oracle/bfile

SQL> create directory bfile_dir as '/export/home/oracle/bfile';

Directory created.

SQL> grant read on directory bfile_dir to jijoe;

Grant succeeded.

SQL> connect jijoe/joe_password

SQL> create table bfile_doc (id number, doc bfile);

SQL> insert into bfile_doc

   1    values(1111,bfilename('bfile_dir','unix.hwp'));

1 row created.

SQL>

 

4-2) COALESCE 함수
--------------------------------------------------------------------------------

이 함수는 나열된 값 중에서 NULL이 아닌 첫 번째 값을 반환한다.  

【예제】
SQL> select coalesce('','','arirang','kunsan') from dual;

COALESC
-------
arirang

SQL>

 


4-3) DECODE 함수
--------------------------------------------------------------------------------

DECODE는 일반적인 프로그래밍 언어의 IF문을 SQL 문자 또는 PL/SQL 안으로 끌어들여 사용하기 위하여 만들어진 오라클 함수이다.
따라서 일반 프로그래밍 언어의 IF문이 수행 할 수 있는 기능을 포함하고 있다.
  select시의 decode 사용은 from 절만 빼고 어디에서나 사용할 수 있다.
  일반 프로그래밍과 decode 함수를 서로 비교하여 보자.
IF문 Decode 함수 IF A=B THEN RETURN 'T';END IF; DECODE(A,B,'T') IF A=B THENRETURN 'T';ELSIF A=C THENRETURN 'F';ELSERETURN 'X';END IF; DECODE(A,B,'T',C,'F','X')
【형식】
DECODE(검색컬럼,조건1,결과값1,
                  조건2,결과값2,...,기본값);

【예제】
SQL> connect jijoe/jijoe_password
SQL> create table aa(
  2  pid        number(12) primary key,
  3  addr varchar2(20),
  4  name varchar2(10));

SQL> insert into aa values(1234,'kunsan','jijoe')
SQL> insert into aa values(3456,'seoul','sunny')

SQL> select * from aa;

       PID ADDR                 NAME
---------- -------------------- ----------
      1234 kunsan               jijoe
      3456 seoul                sunny

SQL> select decode(pid,1234,name) name from aa;

NAME
----------
jijoe


SQL>
【예제】
SQL> desc ddd
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NO                                                 NUMBER(4)
 NAME                                               VARCHAR2(10)
 HIRDATE                                            DATE
 DEPTNO                                             NUMBER(5)

SQL> select * from ddd;

        NO NAME       HIRDATE       DEPTNO
---------- ---------- --------- ----------
         1 student1   01-JAN-04         10
         2 student2   01-FEB-04         10
         3 student3   01-MAR-04         20
         4 student4   01-MAY-04         30

SQL> select count(decode(to_char(hirdate,'MM'),'01',1)) "JAN",
  2         count(decode(to_char(hirdate,'MM'),'02',1)) "FEB",
  3         count(decode(to_char(hirdate,'MM'),'03',1)) "MAR",
  4         count(*) "Total"
  5  from ddd
  6  where to_char(hirdate,'MM') >= '01' AND
  7        to_char(hirdate,'MM') <= '06';

       JAN        FEB        MAR      Total
---------- ---------- ---------- ----------
         1          1          1          4

SQL>

 


4-4) DEPTH 함수
--------------------------------------------------------------------------------

DEPTH( correlation_integer) 함수는 UNDER_PATH나 EQUALS_PATH 조건과 함께 사용되는 보조함수이다.
 이 함수는 UNDER_PATH 조건에 상관관계의 수치를 반환한다.

【예제】
SQL> select * from resource_view;
SQL> select path(1), depth(2)
  2  from resource_view
  3  where under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 1)=1
  4    and under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 2)=1;

PATH(1)                                      DEPTH(2)
------------------------------------------ ----------
/xml.xsd                                            1
        

SQL>

 

4-5) DUMP 함수
--------------------------------------------------------------------------------

지정한 데이터의 위치와 길이 따위를 지정한 형식으로 반환한다.
【형식】
DUMP(expr [,반환형식[,시작위치[,길이]]] )

【예제】
SQL> select dump('Corea', 1016) from dual;

DUMP('COREA',1016)
-----------------------------------------------------
Typ=96 Len=5 CharacterSet=KO16KSC5601: 43,6f,72,65,61

SQL> select dump('Corea', 8,3,2) "Octal" from dual;

Octal
---------------------
Typ=96 Len=5: 162,145

SQL> select dump('Corea',16,3,2) "ASCII" from dual;

ASCII
-------------------
Typ=96 Len=5: 72,65

SQL>

 

4-6) EMPTY_BLOB 함수
--------------------------------------------------------------------------------

EMPTY_BLOB () 함수는 LOB 변수를 초기화하기 위하여 쓰이거나,
 또는 INSERT 문이나 UPDATE 문에서 empty LOB 위치를 반환한다.

【예시】
UPDATE print_media SET ad_photo = EMPTY_BLOB();

 

4-7) EMPTY_CLOB 함수
--------------------------------------------------------------------------------

EMPTY_CLOB () 함수는 LOB 변수를 초기화하기 위하여 쓰이거나,
 또는 INSERT 문이나 UPDATE 문에서 empty LOB 위치를 반환한다.

【예시】
UPDATE print_media SET ad_photo = EMPTY_CLOB();

 

4-8) EXISTSNODE 함수
--------------------------------------------------------------------------------

이 함수는 node의 존재여부를 확인하여 그 결과를 반환한다.
 0은 노드가 남아 있지 않은 경우이고,
 1은 아직 노드가 존재하는 경우이다.

【형식】
EXISTSNODE(XMLType_instance, XPath_string [,namespace_string] )

【예제】
SQL> select * from resource_view;
SQL> select res,any_path
  2  from resource_view
  3  where existsnode(res, 'xdbconfig.xml') =0;

26 rows selected.
SQL>

 


4-9) EXTRACT(XML) 함수
--------------------------------------------------------------------------------

이 함수는 existsnode와 유사한 함수이다.

【형식】
EXTRACT(XMLType_instance, XPath_string [,namespace_string] )

【예제】
SQL> select * from resource_view;
SQL> select extract(res,'xdbconfig.xml')
  2  from resource_view;
 
26 rows selected.
SQL>

 


4-10) EXTRACTVALUE 함수
--------------------------------------------------------------------------------

이 함수는 existsnode와 유사한 함수로써 node의 스칼라 값을 반환한다.

【형식】
EXTRACTVALUE(XMLType_instance, XPath_string [,namespace_string] )

【예제】
SQL> select * from resource_view;
SQL> select extractvalue(res,'xdbconfig.xml')
  2  from resource_view;
 
26 rows selected.
SQL>

 

4-11) GREATEST 함수
--------------------------------------------------------------------------------

GREATEST (expr,...) 함수는 expr 중에서 가장 큰 값을 반환한다.

【예제】
SQL> select greatest(20,10,30) from dual;

GREATEST(20,10,30)
------------------
                30

SQL>

 

4-12) LEAST 함수
--------------------------------------------------------------------------------

LEAST (expr,...) 함수는 expr 중에서 가장 작은 값을 반환한다.

【예제】
SQL> select least(20,10,30) from dual;

GREATEST(20,10,30)
------------------
                10

SQL> select least('bb','aa','cc') from dual;

GR
--
aa

SQL>

 

4-13) NLS_CHARSET_DECL_LEN 함수
--------------------------------------------------------------------------------

NLS_CHARSET_DECL_LEN (byte_count , char_set_id) 함수는 nchar로 선언된 폭을 반환한다.

【예제】
SQL> select nls_charset_decl_len
  2  (200, nls_charset_id('ja16eucfixed')) from dual

NLS_CHARSET_DECL_LEN(200,NLS_CHARSET_ID('JA16EUCFIXED'))
--------------------------------------------------------
                                                     100

SQL>

 


4-14) NLS_CHARSET_ID 함수
--------------------------------------------------------------------------------

nls_charset_id('text') 함수는 문자셋 이름에 대응하는 ID 번호를 반환한다.
 여기서 text는 서버에서 지원되는 CHAR_CS나 NCHAR_CS이다.

【예제】
SQL> select nls_charset_id('ja16euc') from dual;

NLS_CHARSET_ID('JA16EUC')
-------------------------
                      830

SQL>

 

4-15) NLS_CHARSET_NAME 함수
--------------------------------------------------------------------------------

nls_charset_name('number') 함수는 문자섹 ID 번호에 대응하는 문자의 이름을 반환한다.

【예제】
SQL> select nls_charset_name(830) from dual;

NLS_CHA
-------
JA16EUC

SQL> select nls_charset_name(1) from dual;

NLS_CHAR
--------
US7ASCII

SQL>

 


4-16) NULLIF 함수
--------------------------------------------------------------------------------

NULLIF(expr1, expr2) 함수는
 expr1과 expr2를 비교하여
       같으면 null을 반환하고,
       같지 않으면 expr1을 반환한다.

이는 CASE 문으로 쓰면 다음과 같다.
  CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

【예제】
SQL> select nullif('aa','AA') from dual;

NU
--
aa

SQL> select nullif('aa','aa') from dual;

NU
--


SQL>

 


4-17) NVL2 함수
--------------------------------------------------------------------------------

NVL2(expr1, expr2, expr3) 함수는
   expr1이 null이 아니면 expr2를 반환하고,
   expr1이 null이면 expr3을 반환한다.

【예제】
SQL> select nvl2('','Corea','Korea') from dual;

NVL2(
-----
Korea

SQL> select nvl2('aa','Corea','Korea') from dual;

NVL2(
-----
Corea

SQL>

 


4-18) PATH 함수
--------------------------------------------------------------------------------

PATH(correlation_path) 함수는 under_path나 equals_path의 보조함수로서,
 자원의 관계경로를 반환한다.

【예제】
SQL> select * from resource_view;
SQL> select path(1), depth(2)
  2  from resource_view
  3  where under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 1)=1?
  4    and under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 2)=1;

PATH(1)                                      DEPTH(2)
------------------------------------------ ----------
/xml.xsd                                            1
        

SQL>

 


4-19) SYS_CONNECT_BY_PATH 함수
--------------------------------------------------------------------------------

SYS_CONNECT_BY_PATH(column, char) 함수는 계층적 쿼리에서만 유효하며,
 column의 절대 경로를 char로 지정한 문자로 분리하여 반환한다.

【예제】
SQL> select sys_connect_by_path(name, '/') from emp
  2  start with name='jijoe'
  3  connect by prior id=1101;

SYS_CONNECT_BY_PATH(NAME,'/')
-----------------------------
/jijoe

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

SQL>

 

4-20) SYS_CONTEXT 함수
--------------------------------------------------------------------------------

 이 함수는 namespace와 관계되는 parameter의 값을 반환한다.

【형식】
SYS_CONTEXT('namespace','parameter' [,length])

【예제】
SQL> select sys_context('userenv','session_user') from dual;

SYS_CONTEXT('USERENV','SESSION_USER')
-------------------------------------
JIJOE

SQL> select sys_context('userenv','lang') from dual;

SYS_CONTEXT('USERENV','LANG')
-----------------------------
US

SQL>

 userenv에서 사용될 parameter는 다음과 같다.
AUDITED_CURSORID AUTHENTICATION_DATA BG_JOB_ID
CLIENT_IDENTIFIER CLIENT_INFO  CURRENT_SCHEMA
CURRENT_SCHEMAID CURRENT_SQL  CURRENT_USER
CURRENT_USERID  DB_DOMAIN  DB_NAME
ENTRY_ID  EXTERNAL_NAME  FG_JOB_ID
GLOBAL_CONTEXT_MEMORY HOST   INSTANCE
IP_ADDRESS  ISDBA   LANG
LANGUAGE  NETWORK_PROTOCOL NLS_CALENDAR
NLS_CURRENCY  NLS_DATE_FORMAT  NLS_DATE_LANGUAGE
NLS_SORT  NLS_TERRITORY  OS_USER
PROXY_USER  PROXY_USERID  SESSION_USER
SESSION_USERID  SESSIONID  TERMINAL

 


4-21) SYS_DBURIGEN 함수
--------------------------------------------------------------------------------

이 함수는 입력된 argument에 대한 DBURIType의 URL을 반환한다.

【형식】
SYS_DBURIGEN({column|attribute} [rowid],... [,'text()'])

【예제】
SQL> select sys_dburigen(id,name) from emp
  2  where name='jijoe';

SYS_DBURIGEN(ID,NAME)(URL, SPARE)
------------------------------------------------------------------------
DBURITYPE('/PUBLIC/EMP/ROW[ID=''1104'']/NAME', NULL)

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

SQL>

 

4-22) SYS_EXTRACT_UTC 함수
--------------------------------------------------------------------------------

sys_extract_utc(datetime_with_timezone) 함수는
UTC(coordinated universal time: Greenwich mean time) 시각을 반환한다. 

【예제】
SQL> select systimestamp, sys_extract_utc(systimestamp) from dual;

SYSTIMESTAMP
--------------------------------------------------------------------------
SYS_EXTRACT_UTC(SYSTIMESTAMP)
--------------------------------------------------------------------------
06-AUG-04 02.41.39.258976 PM +09:00
06-AUG-04 05.41.39.258976 AM


SQL>

 

4-23) SYS_GUID 함수
--------------------------------------------------------------------------------

sys_guid() 함수는 globally unique identifier를 반환한다.

【예제】
SQL> select sys_guid() from dual;

SYS_GUID()
--------------------------------
E0F6C6D5767C01ADE034080020B588F4

SQL>

 

4-24) SYS_XMLAGG 함수
--------------------------------------------------------------------------------

이 함수는 sys_xmlgen 문에서 만든 XML 문을 기본적으로 ROWSET 태그를 새로 추가 시킨다.

【형식】
SYS_XMLAGG( expr [fmt] )

【예제】
SQL> select sys_xmlagg(sys_xmlgen(name)) from emp
  2  where name like 'j%';

SYS_XMLAGG(SYS_XMLGEN(NAME))
--------------------------------------------------------------------------
<ROWSET>
  <NAME>jijoe</NAME>
</ROWSET>


SQL>

 

4-25) SYS_XMLGEN 함수
--------------------------------------------------------------------------------

이 함수는 지정한 행이나 열을 XML 문으로 만들어 반환한다.

【형식】
SYS_XMLGEN( expr [fmt] )

【예제】
SQL> select sys_xmlgen(name) from emp
  2  where name like 'j%';

SYS_XMLGEN(NAME)
--------------------------------------------------------------------------
<NAME>jijoe</NAME>

SQL>

 

4-26) UID 함수
--------------------------------------------------------------------------------

UID 함수는 사용자의 유일한 ID를 정수로 반환한다.

【예제】
SQL> select uid from dual;

       UID
----------
        93

SQL>

 

4-27) USER 함수
--------------------------------------------------------------------------------

이 함수는 사용자의 이름을 반환한다.

【예제】
SQL> select user,uid from dual;

USER                                  UID
------------------------------ ----------
JIJOE                                  93

SQL>

 


4-28) USERENV 함수
--------------------------------------------------------------------------------

USERENV('parameter') 함수는 사용자의 환경에 관한 정보를 반환한다.

 parameter는 다음과 같은 것이 있다.
CLIENT_INFO ENTRYID  ISDBA  LANG 
LANGUAGE SESSIONID TERMINAL

【예제】
SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.KO16KSC5601

SQL>

 

 

4-29) VSIZE 함수
--------------------------------------------------------------------------------

VSIZE('expr') 함수는 expr이 표시되는 바이트 수를 반환한다.

【예제】
SQL> select name, vsize(name) from emp
  2  where name like 'jijoe';

NAME       VSIZE(NAME)
---------- -----------
jijoe                5

SQL>

$ cat .profile
..........
NLS_LANG=AMERICAN_AMERICA.KO16KSC5601  ☜ 한글 문자셋으로 설정
export NLS_LANG
NLS_LANG=AMERICAN_AMERICA.UTF8  ☜ UNICODE로 설정
export NLS_LANG
$


테이블에서 저장된 데이터가 한글 문자셋인지 유니코드인지 식별하려면 다음과 같이 확인해 볼 수 있다.

    select 한글컬럼명, vsize(한글컬럼명) from 테이블명;

여기서 한글 컬럼에 한글이 3글자라면, vsize 결과가
    9이면 unicode이고,
    6이면 한글 문자셋으로 저장된 것임을 알 수 있다.

【예제】
SQL> select * from test;
 
        ID NAME
---------- ----------------------------------------
      1113 아리랑
      1112 쓰리랑
 
SQL> select name, vsize(name) from test;
 
NAME                                     VSIZE(NAME)
---------------------------------------- -----------
아리랑                                             6
쓰리랑                                             6
 
SQL>

 


4-30) XMLAGG 함수
--------------------------------------------------------------------------------

이 함수는 xmlelement에 의해서 XML 태그를 만든 문장을 모으는 기능이다.

【형식】
XMLAGG( XMLType_instance [order_by_clause])

【예제】
 SQL> select xmlagg(xmlelement("name",e.name)) from emp e;

XMLAGG(XMLELEMENT("NAME",E.NAME))
--------------------------------------------------------------------------
<name>Cho</name>
<name>Joe</name>
<name>kim</name>
<name>jijoe</name>

SQL>

 


4-31) XMLCOLATTVAL 함수
--------------------------------------------------------------------------------

이 함수는 XML fragment를 만드는 기능이다

【형식】
XMLCOLATTVAL( value_expr [AS c_alias],...)

【예제】
SQL> select xmlcolattval(e.name,e.id,e.salary) from emp e;

XMLCOLATTVAL(E.NAME,E.ID,E.SALARY)
--------------------------------------------------------------------------
<column name="NAME">Cho</column>
<column name="ID">1101</column>
<column name="S

<column name="NAME">Joe</column>
<column name="ID">1102</column>
<column name="S

<column name="NAME">kim</column>
<column name="ID">1103</column>
<column name="S

<column name="NAME">jijoe</column>
<column name="ID">1104</column>
<column name=


SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

SQL>

 


4-32) XMLCONCAT 함수
--------------------------------------------------------------------------------

XMLCONCAT( XMLType_instance,...) 함수는 XMLType instance를 series로 넣어 만드는 기능이다.

【예제】
SQL> select xmlconcat(
  2    xmlelement("name",e.name),xmlelement("bonus",e.bonus))
  3  from emp e;

XMLCONCAT(XMLELEMENT("NAME",E.NAME),XMLELEMENT("BONUS",E.BONUS))
--------------------------------------------------------------------------
<name>Cho</name>
<bonus>125</bonus>

<name>Joe</name>
<bonus>100</bonus>

<name>kim</name>
<bonus>100</bonus>

<name>jijoe</name>
<bonus>100</bonus>

SQL>

 

4-33) XMLFOREST 함수
--------------------------------------------------------------------------------

이 함수는 각각의 argument parameter를  XML로 변환한다.

【형식】
XMLFOREST( value_expr [AS c_alias],...)

【예제】
SQL> select xmlelement("emp",
  2  xmlforest(e.id, e.name, e.bonus)) from emp e;

XMLELEMENT("EMP",XMLFOREST(E.ID,E.NAME,E.BONUS))
--------------------------------------------------------------------------
<emp>
  <ID>1101</ID>
  <NAME>Cho</NAME>
  <BONUS>125</BONUS>
</emp>

<emp>
  <ID>1102</ID>
  <NAME>Joe</NAME>
  <BONUS>100</BONUS>
</emp>

<emp>
  <ID>1103</ID>
  <NAME>kim</NAME>
  <BONUS>100</BONUS>
</emp>

<emp>
  <ID>1104</ID>
  <NAME>jijoe</NAME>
  <BONUS>100</BONUS>

</emp>

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

SQL>
 

 

4-34) XMLELEMENT 함수
--------------------------------------------------------------------------------

이 함수는 XML 태그를 붙이는 기능이다

【예제】
SQL> select xmlelement("name",e.name) from emp e
  2  where name like 'j%';

XMLELEMENT("NAME",E.NAME)
--------------------------------------------------------------------------
<name>jijoe</name>

SQL>

 


5-1) AVG* 함수
--------------------------------------------------------------------------------

조건을 만족하는 행(row)의 평균을 값을 반환하며,
 aggregate 함수나
 analytic 함수로 사용된다.

【형식】
AVG( [DISTINCT | ALL] 컬럼명)
   [ [OVER] (analytic 절)]

【예제】aggregate 예
SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

SQL> select avg(salary) from emp;

AVG(SALARY)
-----------
        240

SQL>
【예제】analytic 예
SQL> select avg(distinct salary) over(partition by bonus)
  2  from emp;

AVG(DISTINCTSALARY)OVER(PARTITIONBYBONUS)
-----------------------------------------
                               236.666667
                               236.666667
                               236.666667
                                      250

 
SQL> select avg(salary) over(partition by bonus order by id
    2 rows between 1 preceding and 1 following) as avg  from emp;

       AVG
----------
       245
236.666667
       235
       250

SQL>

 


5-2) CORR* CORR* 함수
--------------------------------------------------------------------------------

집합 쌍의 상관관계 계수를 반환한다.

【형식】
CORR( expr1, expr2 ) [ [OVER] (analytic 절)]

【예제】
SQL> select corr(avg(bonus),max(bonus))
  2  from employees
  3  group by dept_no;

CORR(AVG(BONUS),MAX(BONUS))
---------------------------
                          1

SQL>

 

5-3) COUNT* 함수
--------------------------------------------------------------------------------


 쿼리한 행의 수를 반환한다.
【형식】
COUNT([*|DISTINCT|ALL] 컬럼명) [ [OVER] (analytic 절)]

【예제】
SQL> select count(*) from emp;

  COUNT(*)
----------
         4

SQL> select count (distinct dept_no) from employees;

COUNT(DISTINCTDEPT_NO)
----------------------
                     2

SQL> select count (all dept_no) from employees;

COUNT(ALLDEPT_NO)
-----------------
                4

SQL> select salary,count(*)
  2  over (order by salary)
  3  from emp;

    SALARY COUNT(*)OVER(ORDERBYSALARY)
---------- ---------------------------
       220                           1
       240                           2
       250                           4
       250                           4

SQL>

 


5-4) COVAR_POP 함수
--------------------------------------------------------------------------------

이 함수는 number 쌍의 집합에 대한 population covariance를 반환한다.

【형식】
COVAR_POP(expr1, expr2 [ OVER (analytic 절)] )

【예제】
SQL> select covar_pop(bonus,salary) from emp;

COVAR_POP(BONUS,SALARY)
-----------------------
                   62.5

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

SQL>

 

5-5) COVAR_SAMP 함수
--------------------------------------------------------------------------------

이 함수는 number 쌍의 집합에 대한 sample covariance를 반환한다.

【형식】
COVAR_SAMP(expr1, expr2 [ OVER (analytic 절)] )

【예제】
SQL> select covar_samp(bonus,salary) from emp;

COVAR_SAMP(BONUS,SALARY)
------------------------
              83.3333333

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

SQL>

 

5-6) CUME_DIST 함수
--------------------------------------------------------------------------------

이 함수는 그룹 값 내에서 어떤 값의 cumulative distribution(누적분포)을 계산한다.

【형식】
CUME_DIST(expr,... WITHIN GROUP (ORDER BY
     expr [DESC | ASC] [NULLS {FIRST|LAST}],...)
또는
CUME_DIST() over ([query_partition_clause] order_by_clause)

【예제】
SQL> select cume_dist(230) within group
  2  (order by salary ) from emp;

CUME_DIST(230)WITHINGROUP(ORDERBYSALARY)
----------------------------------------
                                      .4

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

SQL>

 


5-7) DENSE_RANK 함수
--------------------------------------------------------------------------------


그룹 내에서 순위를 반환한다.

【예제】
SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL> select dense_rank(230, .05) within group
  2  (order by salary, bonus) "Dense Rank"
  3  from employees;

Dense Rank
----------
         2

SQL>

 


5-8) FIRST 함수
--------------------------------------------------------------------------------

first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.
【형식】
집합함수 KEEP (
 DENSE_RANK FIRST ORDER BY
    expr [DESC|ASC][NULL{FIRST|LAST}],...)
【예제】
SQL> select
  2  min(salary) keep (dense_rank first order by salary) "Worst",
  3  max(salary) keep (dense_rank last order by salary) "Best"
  4  from employees
  5  order by id;

     Worst       Best
---------- ----------
       220        250

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL>

 


5-9) GROUP_ID 함수
--------------------------------------------------------------------------------

GROUP() 함수는 group by로 분리되어 복제된 번호로 복제 횟수를 구분하도록 출력한다.
번호가 0부터 시작되므로 n번 복제되었으면 n-1의 번호가 출력된다.

【예제】
SQL> select dept_no, group_id() from employees
  2  group by dept_no;

   DEPT_NO GROUP_ID()
---------- ----------
        10          0
        20          0

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL>

 


5-10) Grouping 함수
--------------------------------------------------------------------------------

Grouping 함수는 Rollup이나 cube 연산자랑 함께 사용하여
 grouping 함수에서 기술된 컬럼이 그룹핑시 사용되었는지 보여주는 함수이다.
특별히 연산의 기능은 없으며,
  rollup이나 cube 연산 후 출력된 결과에 대한 사용자의 이해를 높이기 위해 사용된다.
  즉, grouping 함수를 이용할 경우 출력되는 결과값 중 null 값이 있다면,
      이 null 값이 rollup이나 cube 연산의 결과로 생성된 값인지,
      원래 테이블상에 null 값으로 저장된 것인지 확인할 수 있다.

. grouping 함수는 인수로 하나의 값만을 가진다.
. grouping 함수에 사용된 인수는 group by 절에 기술된 값 중에 하나와 반드시 일치해야 한다.
. grouping 함수의 결과값으로 0 또는 1을 반환한다.
    0은 해당인수로 쓰인 값이 rollup이나 cube 연산에 사용되었음을 나타나고,
    1은 사용되지 않았음을 의미한다.
【형식】
SELECT   컬럼명,그룹함수(컬럼명), GROUPING(컬럼명)
FROM  테이블명
WHERE  조건
GROUP BY [ROLLUP | CUBE] 그룹핑하고자하는 컬럼명,...
HAVING  그룹조건
ORDER BY 컬럼명 또는 위치번호

【예제】
SQL> select grade,deptno,sum(salary),GROUPING(deptno)
  2  from aaa
  3  group by rollup(grade,deptno);

     GRADE     DEPTNO SUM(SALARY) GROUPING(DEPTNO)
---------- ---------- ----------- ----------------
         1         10         100                0
         1         20         500                0
         1         30         300                0
         1                    900                1
         2         10         400                0
         2         20         200                0
         2         30         600                0
         2                   1200                1
                             2100                1

9 rows selected.

SQL>

 

5-11) GROUPING_ID 함수
--------------------------------------------------------------------------------

GROUPING_ID(expr,...) 함수는 행과 관련되는 GROUPING 비트 벡터에 대응되는 수치를 반환한다.

【예제】
SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL> select sum(salary), grouping_id(dept_no)
  2  from employees
  3  group by dept_no;

SUM(SALARY) GROUPING_ID(DEPT_NO)
----------- --------------------
        500                    0
        460                    0

SQL>

 

5-12) LAST 함수
--------------------------------------------------------------------------------

first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.
【형식】
집합함수 KEEP (
 DENSE_RANK LAST ORDER BY
    expr [DESC|ASC][NULL{FIRST|LAST}],...)
【예제】
SQL> select
  2  min(salary) keep (dense_rank first order by salary) "Worst",
  3  max(salary) keep (dense_rank last order by salary) "Best"
  4  from employees
  5  order by id;

     Worst       Best
---------- ----------
       220        250

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL>

 


5-13) MAX 함수
--------------------------------------------------------------------------------

이 함수는 최대 값을 반환한다.

【형식】
MAX ([{DISTINCT|ALL}] expr) [OVER(analytic_clause)]

【예제】
SQL> select max(salary) over (partition by dept_no)
  2  from employees;

MAX(SALARY)OVER(PARTITIONBYDEPT_NO)
-----------------------------------
                                250
                                250
                                240
                                240

SQL> select max(salary) from employees;

MAX(SALARY)
-----------
        250

SQL>

 

5-14) MIN 함수
--------------------------------------------------------------------------------

이 함수는 최소 값을 반환한다.

【형식】
MIN ([{DISTINCT|ALL}] expr) [OVER(analytic_clause)]

【예제】
SQL> select min(salary) over (partition by dept_no)
  2  from employees;

MIN(SALARY)OVER(PARTITIONBYDEPT_NO)
-----------------------------------
                                250
                                250
                                220
                                220

SQL> select min(salary) from employees;

MIN(SALARY)
-----------
        220

SQL>

 


5-15) PERCENTILE_CONT 함수
--------------------------------------------------------------------------------

이 함수는 연속 모델에 대한 inverse distribution function이다.

【형식】
PERCENTILE_CONT(expr) WITHIN GROUP (ORDER BY expr [{DESC|ASC}])
  [OVER (query_partition_cluause)]

【예제】
SQL> select dept_no,percentile_cont(0.5) within group
  2  (order by salary DESC)
  3  from employees GROUP BY dept_no;

   DEPT_NO PERCENTILE_CONT(0.5)WITHINGROUP(ORDERBYSALARYDESC)
---------- --------------------------------------------------
        10                                                250
        20                                                230

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL>

 


5-16) PERCENTILE_DISC 함수
--------------------------------------------------------------------------------

이 함수는 불연속 모델에 대한 inverse distribution function이다.

【형식】
PERCENTILE_DISC(expr) WITHIN GROUP (ORDER BY expr [{DESC|ASC}])
  [OVER (query_partition_cluause)]

【예제】
SQL> select dept_no,percentile_disc(0.5) within group
  2  (order by salary DESC)
  3  from employees GROUP BY  dept_no;

   DEPT_NO PERCENTILE_DISC(0.5)WITHINGROUP(ORDERBYSALARYDESC)
---------- --------------------------------------------------
        10                                                250
        20                                                240

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL>

 


5-17) PERCENT_RANK 함수
--------------------------------------------------------------------------------

이 함수는 CUME_DIST 함수와 유사하게 percent_rank 값을 반환한다.

【형식】
PERCENT_RANK(expr,...) WITHIN GROUP (ORDER BY expr [{DESC|ASC}]
  [NULLS {FIRST|LAST}],...)
또는
PERCENT_RANK() OVER( [query_partition_clause] order_by_clause)

【예제】
SQL> select percent_rank(230,0.05) within group
  2  (order by salary,bonus) from employees;

PERCENT_RANK(230,0.05)WITHINGROUP(ORDERBYSALARY,BONUS)
------------------------------------------------------
                                                   .25

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL>

 


5-18) RANK 함수
--------------------------------------------------------------------------------

이 함수는 그룹 내에서 위치를 반환한다.

【형식】
RANK(expr,...) WITHIN GROUP (ORDER BY expr [{DESC|ASC}]
  [NULLS {FIRST|LAST}],...)
또는
RANK() OVER( [query_partition_clause] order_by_clause)

【예제】
SQL> select rank(230,0.05) within group
  2 (order by salary,bonus) from employees;

RANK(230,0.05)WITHINGROUP(ORDERBYSALARY,BONUS)
----------------------------------------------
                                             2
SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL>

 


5-19) REGR_(linear regression) function* 함수
--------------------------------------------------------------------------------

선형 회귀(linear regression) 함수 ordinary-least squares regression line을 찾도록 한다.
 사용되는 회귀함수는 자음 중 하나이다.
 REGR_SLOPE REGR_INTERCEPT REGR_COUNT
 REGR_R2 REGR_AVGX REGR_AVGY 
 REGR_SXX REGR_SYY REGR_SXY

【형식】
REGR { REGR_SLOPE|REGR_INTERCEPT|REGR_COUNT|REGR_R2|REGR_AVGX|
       REGR_AVGY|REGR_SXX|REGR_SYY|REGR_SXY}
    (expr1,expr2) [OVER (analytic_clause)]

【예제】
SQL> select regr_slope(salary,bonus) from employees

REGR_SLOPE(SALARY,BONUS)
------------------------
              .533333333

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL>

 


5-20) STDDEV 함수
--------------------------------------------------------------------------------

이 함수는 standard deviation을 반환한다.

【형식】
STDDEV [{DISTINCT|ALL}] (expr) [OVER (analytic_clause)]

【예제】
SQL> select stddev(salary) from emp;

STDDEV(SALARY)
--------------
    14.1421356

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

SQL>

 


5-21) STDDEV_POP 함수
--------------------------------------------------------------------------------

이 함수는 population standard deviation을 반환한다.

【형식】
STDDEV_POP (expr) [OVER (analytic_clause)]

【예제】
SQL> select stddev_pop(salary) from emp;

STDDEV_POP(SALARY)
------------------
        12.2474487

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

SQL>


5-22) STDDEV_SAMP 함수
--------------------------------------------------------------------------------

이 함수는 cumulative sample standard deviation을 반환한다.

【형식】
STDDEV_SAMP (expr) [OVER (analytic_clause)]

【예제】
SQL> select stddev_samp (salary) from emp;

STDDEV_SAMP(SALARY)
-------------------
         14.1421356

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100


SQL>

 


5-23) SUM 함수
--------------------------------------------------------------------------------

이 함수는 합계를 반환한다.

【형식】
SUM ([{DISTINCT|ALL}] expr) [OVER (analytic_clause)]

【예제】
SQL> select sum(salary) from emp;

SUM(SALARY)
-----------
        960

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

SQL>

 


5-24) VAR_POP 함수
--------------------------------------------------------------------------------

이 함수는 population variance를 반환한다.

【형식】
VAR_POP (expr) [OVER (analytic_clause)]

【예제】
SQL> select var_pop(salary) from emp;

VAR_POP(SALARY)
---------------
            150

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100


SQL>

 


5-25) VAR_SAMP 함수
--------------------------------------------------------------------------------

이 함수는 sample variance를 반환한다.

【형식】
VAR_SAMP (expr) [OVER (analytic_clause)]

【예제】
SQL> select var_samp(salary) from emp;

VAR_SAMP(SALARY)
----------------
             200

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100


SQL>

 


5-26) VARIANCE 함수
--------------------------------------------------------------------------------

이 함수는 variance를 반환한다.

【형식】
VARIANCE ([{DISTINCT|ALL}] expr) [OVER (analytic_clause)]

【예제】
SQL> select variance(salary) from emp;

VARIANCE(SALARY)
----------------
             200

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

SQL>

 


5-27) Grouping sets 함수
--------------------------------------------------------------------------------

Grouping sets 함수는 Group by의 확장된 형태로 하나의 쿼리문에서 원하는 그룹핑 조건을 여러 개 기술할 수 있으며,
 grouping sets 함수 사용이 불가능한 이전 버전에서
 복잡한 union all 연산자를 사용하여 기술하던 것을 간단하게
 한 문장 안에서 해결할 수 있어 검색시 효율성이 증대 된다.
 다시 말해서, grouping sets 함수를 사용하면,
              group by ... union all을 사용한 것보다
              SQL 문이 간단해지고 또한 실행시 성능이 빨라진다.
【형식】
SELECT   컬럼명,그룹함수(컬럼명), GROUPING(컬럼명)
FROM  테이블명
WHERE  조건
GROUP BY [ROLLUP | CUBE] 그룹핑하고자하는 컬럼명, ...
  [GROUPING SETS (컬럼명,컬럼명, ...), ...]
HAVING  그룹조건
ORDER BY 컬럼명 또는 위치번호

【예제】
SQL> select grade,deptno,sum(salary)
  2  from aaa
  3  group by grouping sets(grade,deptno);

     GRADE     DEPTNO SUM(SALARY)
---------- ---------- -----------
         1                    900
         2                   1200
                   10         500
                   20         700
                   30         900

SQL> select grade,deptno,sum(salary)
  2  from aaa
  3  group by grouping sets((grade,name),(deptno,name));

     GRADE     DEPTNO SUM(SALARY)
---------- ---------- -----------
         1                    100
         1                    300
         1                    500
         2                    200
         2                    400
         2                    600
                   10         100
                   20         200
                   30         300
                   10         400
                   20         500
                   30         600

12 rows selected.
SQL>
【예제】Union all을 사용한 경우
SQL> select grade,deptno,sum(salary)
  2  from aaa
  3  group by grade,deptno
  4  union all
  5  select grade,deptno,sum(salary)
  6  from aaa
  7  group by grade,deptno;

     GRADE     DEPTNO SUM(SALARY)
---------- ---------- -----------
         1         10         100
         1         20         500
         1         30         300
         2         10         400
         2         20         200
         2         30         600
         1         10         100
         1         20         500
         1         30         300
         2         10         400
         2         20         200
         2         30         600

12 rows selected.

SQL>

composite columns란 rollup, cube, grouping sets 절과 같은 기능을 사용하면
표현되는 각 컬럼이 하나가 아닌 복수 개의 컬럼으로 정의되는 경우이며
다음 표를 보고 이해하자.
composite column 문의 경우  group by 문의 경우 group by grouping sets(a,b,c)  group by a union allgroup by b union allgroup by c  group by grouping sets(a,b,(b,c))  group by a union allgroup by b union allgroup by b,c  group by grouping sets((a,b,c))  group by a,b,c  group by grouping sets(a,(b),())  group by a union allgroup by b union allgroup by ()  group by grouping sets(a,rollup(b,c))  group by a union allgroup by rollup(b,c)  group by rollup(a,b,c)  group by (a,b,c) union allgroup by (a,b) union allgroup by (a) union allgroup by ()  group by cube(a,b,c)  group by (a,b,c) union allgroup by (a,b) union allgroup by (a,c) union allgroup by (b,c) union allgroup by (a) union allgroup by (b) union allgroup by (c) union allgroup by () 

 


6-1) AVG* 함수
--------------------------------------------------------------------------------

조건을 만족하는 행(row)의 평균을 값을 반환하며,
 aggregate 함수나
 analytic 함수로 사용된다.

【형식】
AVG( [DISTINCT | ALL] 컬럼명)
   [ [OVER] (analytic 절)]

【예제】aggregate 예
SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

SQL> select avg(salary) from emp;

AVG(SALARY)
-----------
        240

SQL>
【예제】analytic 예
SQL> select avg(distinct salary) over(partition by bonus)
  2  from emp;

AVG(DISTINCTSALARY)OVER(PARTITIONBYBONUS)
-----------------------------------------
                               236.666667
                               236.666667
                               236.666667
                                      250

 
SQL> select avg(salary) over(partition by bonus order by id
    2 rows between 1 preceding and 1 following) as avg  from emp;

       AVG
----------
       245
236.666667
       235
       250

SQL>

 


6-2) CORR* CORR* 함수
--------------------------------------------------------------------------------

집합 쌍의 상관관계 계수를 반환한다.

【형식】
CORR( expr1, expr2 ) [ [OVER] (analytic 절)]

【예제】
SQL> select corr(avg(bonus),max(bonus))
  2  from employees
  3  group by dept_no;

CORR(AVG(BONUS),MAX(BONUS))
---------------------------
                          1

SQL>

 

6-3) COUNT* 함수
--------------------------------------------------------------------------------


 쿼리한 행의 수를 반환한다.
【형식】
COUNT([*|DISTINCT|ALL] 컬럼명) [ [OVER] (analytic 절)]

【예제】
SQL> select count(*) from emp;

  COUNT(*)
----------
         4

SQL> select count (distinct dept_no) from employees;

COUNT(DISTINCTDEPT_NO)
----------------------
                     2

SQL> select count (all dept_no) from employees;

COUNT(ALLDEPT_NO)
-----------------
                4

SQL> select salary,count(*)
  2  over (order by salary)
  3  from emp;

    SALARY COUNT(*)OVER(ORDERBYSALARY)
---------- ---------------------------
       220                           1
       240                           2
       250                           4
       250                           4

SQL>

 


6-4) COVAR_SAMP 함수
--------------------------------------------------------------------------------

이 함수는 number 쌍의 집합에 대한 sample covariance를 반환한다.

【형식】
COVAR_SAMP(expr1, expr2 [ OVER (analytic 절)] )

【예제】
SQL> select covar_samp(bonus,salary) from emp;

COVAR_SAMP(BONUS,SALARY)
------------------------
              83.3333333

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

SQL>

 


6-5) CUME_DIST 함수
--------------------------------------------------------------------------------

이 함수는 그룹 값 내에서 어떤 값의 cumulative distribution(누적분포)을 계산한다.

【형식】
CUME_DIST(expr,... WITHIN GROUP (ORDER BY
     expr [DESC | ASC] [NULLS {FIRST|LAST}],...)
또는
CUME_DIST() over ([query_partition_clause] order_by_clause)

【예제】
SQL> select cume_dist(230) within group
  2  (order by salary ) from emp;

CUME_DIST(230)WITHINGROUP(ORDERBYSALARY)
----------------------------------------
                                      .4

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

SQL>

 

6-6) DENSE_RANK 함수
--------------------------------------------------------------------------------


그룹 내에서 순위를 반환한다.

【예제】
SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL> select dense_rank(230, .05) within group
  2  (order by salary, bonus) "Dense Rank"
  3  from employees;

Dense Rank
----------
         2

SQL>

 

6-7) FIRST 함수
--------------------------------------------------------------------------------

first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.
【형식】
집합함수 KEEP (
 DENSE_RANK FIRST ORDER BY
    expr [DESC|ASC][NULL{FIRST|LAST}],...)
【예제】
SQL> select
  2  min(salary) keep (dense_rank first order by salary) "Worst",
  3  max(salary) keep (dense_rank last order by salary) "Best"
  4  from employees
  5  order by id;

     Worst       Best
---------- ----------
       220        250

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL>

 

6-8) FIRST_VALUE 함수
--------------------------------------------------------------------------------

이 함수는 서열화된 값에서 첫 번째를 출력한다.

【형식】
FIRST_VALUE ( expr ) OVER ( analytic_절)

【예제】
SQL> select salary,first_value(name)
  2  over (order by salary asc)
  3  from (select * from employees
  4        where dept_no = 20
  5        order by salary);

    SALARY FIRST_VALU
---------- ----------
       220 jijoe
       240 jijoe

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL>

 

6-9) LAG 함수
--------------------------------------------------------------------------------

이 함수는 analytic 함수로서,
self join하지 않고 하나의 테이블에서 동시에 한 행(row) 이상을 접근할 수 있도록 한다.

【형식】
LAG ( value_expr [,offset] [,default] )
     OVER ([query_partition_clause] order_by_clause )

【예제】
SQL> select name,salary,LAG(salary,1,0)    
  2   OVER (ORDER BY salary) FROM employees;

NAME           SALARY LAG(SALARY,1,0)OVER(ORDERBYSALARY)
---------- ---------- ----------------------------------
jijoe             220                                  0
Joe               240                                220
Cho               250                                240
kim               250                                250

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL>

 


6-10) LAST_VALUE 함수
--------------------------------------------------------------------------------

이 함수는 서열화된 값에서 마지막 번째를 출력한다.

【형식】
LAST_VALUE ( expr ) OVER ( analytic_절)

【예제】
SQL> select salary,last_value(name)
  2  over (order by salary asc)
  3  from (select * from employees
  4        where dept_no = 20
  5        order by salary);

    SALARY LAST_VALUE
---------- ----------
       220 jijoe
       240 Joe

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL>

 


6-11) LEAD 함수
--------------------------------------------------------------------------------

이 함수는 analytic 함수로서, self join하지 않고
하나의 테이블에서 동시에 한 행(row) 이상을 접근할 수 있도록 한다.

【형식】
LEAD ( value_expr [,offset] [,default] )
     OVER ([query_partition_clause] order_by_clause )

【예제】
SQL> select name,salary,LEAD(salary,1,0)   
  2   OVER (ORDER BY salary) FROM  employees;

NAME           SALARY LEAD(SALARY,1,0)OVER(ORDERBYSALARY)
---------- ---------- -----------------------------------
jijoe             220                                 240
Joe               240                                 250
Cho               250                                 250
kim               250                                   0

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL>

 

6-12) NTILE 함수
--------------------------------------------------------------------------------

이 함수는 analytic 함수로서, 데이터를 주어진 bucket 수 expr로 분리한다.

【형식】
NTILE ( expr ) OVER ([query_partition_clause] order_by_clause )

【예제】
SQL> select name,salary,NTILE(3) OVER (ORDER BY salary DESC)
  2  FROM  employees;

NAME           SALARY NTILE(3)OVER(ORDERBYSALARYDESC)
---------- ---------- -------------------------------
Cho               250                               1
kim               250                               1
Joe               240                               2
jijoe             220                               3

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL>

 


6-13) RATIO_TO_REPORT 함수
--------------------------------------------------------------------------------

이 함수는 analytic 함수로서, 데이터 합에 대한 구성비를 계산한다.

【형식】
RATIO_TO_REPORT ( expr ) OVER ([query_partition_clause])

【예제】
SQL> select name,salary,RATIO_TO_REPORT(salary) OVER ()
  2   FROM  employees;

NAME           SALARY RATIO_TO_REPORT(SALARY)OVER()
---------- ---------- -----------------------------
Cho               250                    .260416667
Joe               240                           .25
kim               250                    .260416667
jijoe             220                    .229166667

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL>

 


6-14) ROW_NUMBER 함수
--------------------------------------------------------------------------------

이 함수는 analytic 함수로서, 각 행(row)에 unique 번호를 부여한다.

【형식】
ROW_NUMBER () OVER ([query_partition_clause] order_by_clause )

【예제】
SQL> SELECT ROW_NUMBER() OVER (ORDER BY salary DESC),name
  2    FROM  employees;

ROW_NUMBER()OVER(ORDERBYSALARYDESC) NAME      
----------------------------------- ----------
                                  1 Cho       
                                  2 kim       
                                  3 Joe       
                                  4 jijoe     

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL>

 

 

7-1) REF 타입
--------------------------------------------------------------------------------
테이블의 어떤 컬럼이 독립된 다른 객체 테이블을 참조하는 데이터 타입을 의미한다.
일반적으로 테이블을 만들때 사용하는 외부키(foreign-key)를 이용한 참조관계와 유사하다.
REF 타입의 컬럼 데이터를 읽을 때는 반드시 DEREF 함수를 사용한다.
REF 타입으로 정의된 컬럼에는 실제 데이터가 저장되는 것이 아니고 참조되는 객체가 존재하는 포인트정보만 가지고 있기 때문에 객체가 삭제 된다면, 해당 컬러은 정상적으로 검색되지 못한다.
이러한 현상을 REF의 Dangling 현상이고 한다.
이러한 dangling 현상을 방지하기 위해서는 삭제된 객체 정보를 참조하는 행의 컬럼 정보를 analyze 명령어를 이용하여 null 값으로 변경해 주어야 한다.
【예제】
SQL> connect jijoe/jijoe_password
connected

SQL> create type person_type as object(
  2  first_name         varchar2(10),
  3  last_name          varchar2(10),
  4  phone              varchar(12),
  5  birthday           varchar2(12));
  6  /

Type created.

SQL> create type emp_type as object (
  2  empno      number,
  3  emp        person_type);
  4  /

Type created.

SQL> create table emp2 of emp_type
  2  oidindex emp_oid;

Table created.

SQL> insert into emp2 values(
  2  emp_type(1000,person_type('junik','joe','123-1234','20-jul-04')));

1 row created.

SQL> create table dept(
  2  empno      number(4),
  3  ename      varchar2(15),
  4  mgr        REF emp_type SCOPE IS emp2);

Table created.

SQL> insert into dept
  2  select empno, 'SCOTT', REF(e)
  3  from emp2 e
  4  where empno=1000;

1 row created.


【예제】
SQL> select ename,empno from dept;

ENAME                EMPNO
--------------- ----------
SCOTT                 1000

SQL> select mgr, DEREF(mgr) from dept;

MGR
--------------------------------------------------------------------------------
DEREF(MGR)(EMPNO, EMP(FIRST_NAME, LAST_NAME, PHONE, BIRTHDAY))
--------------------------------------------------------------------------------
0000220208DFA05B27A63701D9E034080020B588F4DFA05B27A63601D9E034080020B588F4
EMP_TYPE(1000, PERSON_TYPE('junik', 'joe', '123-1234', '20-jul-04'))


SQL>

【예제】
SQL> select empno,ename,mgr
  2  from dept
  3  where mgr is dangling;

no rows selected

SQL> analyze table dept validate REF update set dangling to NULL;

Table analyzed.

SQL>

 

8-1) ROWID 컬럼
--------------------------------------------------------------------------------

오라클에서 내부적으로 사용되는 컬럼을 pseudocolumn이라고 하며,
ROWID, ROWNUM등이 있다.
ROWID는 데이터베이스에서 컬럼이 위치한 장소이다.

【예제】
SQL> select rowid from test;

ROWID
------------------
AAAHbHAABAAAMXCAAA

SQL> select rowid from test
  2  where rowidtochar(rowid) like '%AABAA%';

ROWID
------------------
AAAHbHAABAAAMXCAAA

SQL> select lengthb(rowidtonchar(rowid)), rowidtonchar(rowid)
  2  from test;

LENGTHB(ROWIDTONCHAR(ROWID)) ROWIDTONCHAR(ROWID
---------------------------- ------------------
                          36 AAAHbHAABAAAMXCAAA

SQL>


여기서 rowid의 의미는 다음과 같다.
AAAHbH  AAB  AAAMXC  AAA 객체번호  테이블스페이스번호  블록번호  행번호

 


8-2) ROWNUM 컬럼
--------------------------------------------------------------------------------

오라클에서 내부적으로 사용되는 컬럼을 pseudocolumn이라고 하며,
ROWID, ROWNUM등이 있다.
ROWNUM은 테이블에서 select 되어진 일련 번호임
【예제】
SQL> select rownum,ename from emp;
 
    ROWNUM ENAME
---------- ----------
         1 CLARK
         2 MILLER
         3 JONES
         4 ALLEN
         5 MARTIN
         6 CHAN
 
6 rows selected.
 
SQL> delete from emp where ename='JONES';
 
1 row deleted.
 
SQL> select rownum,ename from emp;
 
    ROWNUM ENAME
---------- ----------
         1 CLARK
         2 MILLER
         3 ALLEN
         4 MARTIN
         5 CHAN
 
SQL>



 

반응형
저작자표시 비영리

'ORACLE > SQL' 카테고리의 다른 글

SQL 실행순서  (0) 2009.02.24
다중 행(Multiple-Row) 서브쿼리 IN, NOT IN, ANY, ALL, EXISTS  (0) 2009.02.24
데이터 복사하기 위한 INSERT스크립트 생성  (0) 2009.02.24
TIMESTAMP -> DATE 변환  (0) 2009.02.24
세로값을 가로로 출력  (0) 2009.02.24
Posted by [PineTree]
ORACLE/SQL2009. 2. 24. 10:54

데이터 복사하기 위한 INSERT스크립트 생성

반응형


서로다른 DB사이에 DB링크로 연결된 두개의 테이블을 SRC 테이블에서 TARGET테이블로 COPY하는 INSERT스크립트 생성 SQL

개념은 UNION ALL을 이용해서 명령어를 APPEND하는 방식

앞이나 뒤에 필요한 SQL COMMAND를 추가할 수 있다.

 

나중에 프로시져로 만들어야겠다.

---------------------------------------------------------------------------------------

SELECT 'DELETE ' || TNAME || ';' FROM TAB WHERE TNAME = '&nbsp'
UNION ALL
SELECT 'INSERT /*+ append PARALLEL*/ INTO   '|| TNAME || ' (' NAME FROM TAB WHERE TNAME = '&nbsp'
UNION ALL
SELECT NAME FROM (
 SELECT '          ' ||DECODE(COLUMN_ID,1,'',',')||COLUMN_NAME NAME
 FROM user_tab_columns T1
 WHERE 1=1
 AND TABLE_NAME = '&nbsp'
 ORDER BY T1.COLUMN_ID
)
UNION ALL
SELECT ')' NAME FROM DUAL
UNION ALL
SELECT 'SELECT ' NAME FROM DUAL
UNION ALL
SELECT NAME FROM (
 SELECT '          ' ||DECODE(COLUMN_ID,1,'',',')||COLUMN_NAME NAME
 FROM user_tab_columns T1
 WHERE 1=1
 AND TABLE_NAME = '&nbsp'
 ORDER BY T1.COLUMN_ID
)
UNION ALL
SELECT 'FROM  '|| TNAME || '@DIMS_REAL;' NAME FROM TAB WHERE TNAME = '&nbsp'
UNION ALL
SELECT 'COMMIT;' FROM DUAL

[출처] 데이터 복사하기 위한 INSERT스크립트 생성|작성자 리원아빠


반응형
저작자표시 비영리

'ORACLE > SQL' 카테고리의 다른 글

다중 행(Multiple-Row) 서브쿼리 IN, NOT IN, ANY, ALL, EXISTS  (0) 2009.02.24
Oracle 내부함수  (0) 2009.02.24
TIMESTAMP -> DATE 변환  (0) 2009.02.24
세로값을 가로로 출력  (0) 2009.02.24
Oracle Join Update 시 /*+ bypass_ujvc */ 힌트사용  (0) 2009.02.23
Posted by [PineTree]
ORACLE/SQL2009. 2. 24. 10:46

TIMESTAMP -> DATE 변환

반응형

보통  TO_CHAR -> TO_DATE를 많이 쓰지만..

SELECT TO_DATE(TO_CHAR(SYSTIMESTAMP,'YYYYMMDD HH24:MM:SI'),'YYYYMMDD HH24:MM:SI')

FROM DUAL

 

다음과 같이 CAST 함수를 이용해도 가능하다.

SELECT CAST(SYSTIMESTAMP as DATE ) FROM DUAL

[출처] TIMESTAMP -> DATE 변환|작성자 리원아빠


반응형
저작자표시 비영리

'ORACLE > SQL' 카테고리의 다른 글

Oracle 내부함수  (0) 2009.02.24
데이터 복사하기 위한 INSERT스크립트 생성  (0) 2009.02.24
세로값을 가로로 출력  (0) 2009.02.24
Oracle Join Update 시 /*+ bypass_ujvc */ 힌트사용  (0) 2009.02.23
◎ SQL PLUS상에서 STORED PROCEDURE 소스 확인 방법  (0) 2009.01.22
Posted by [PineTree]
ORACLE/SQL2009. 2. 24. 10:44

세로값을 가로로 출력

반응형

SYS_CONNECT_BY_PATH()함수를 BOM구조에서뿐만 아니라 BOM구조가 아닌경우에도 사용할 수 있다.

 

세로로 되어있는 형태

 


원하는 형태 : 가로로 출력

 
 
성분코드를 그룹코드별로 화면에 가로로 출력하고 싶을때 사용하는 SQL
-- 테이블 생성 스크립트
DROP TABLE ZZ_TAB1;
CREATE TABLE ZZ_TAB1 (
 ITEM_SEQ NUMBER ,
 TOTAL_SEQ NUMBER,
 INGR_SEQ NUMBER,
 INGR_CODE VARCHAR2(6),
 INGR_QTY VARCHAR2(10),
 COSMETIC_MIX_GROUP_SEQ NUMBER,
 SORT_KEY NUMBER
)
;
DROP TABLE DRC_INGR
CREATE TABLE DRC_INGR (
 INGR_CODE VARCHAR2(6),
 INGR_KORNAME VARCHAR2(50)
);
 
-- 테스트 데이터 생성
INSERT INTO ZZ_TAB1 VALUES (2006262803,1,1,'008186','0.291',1,2);
INSERT INTO ZZ_TAB1 VALUES(2006262803,1,2,'008187','0.291',1,1);
INSERT INTO ZZ_TAB1 VALUES(2006262803,1,3,'008188','0.291',1,3);
INSERT INTO ZZ_TAB1 VALUES(2006262803,1,4,'008181','0.291',1,4);
INSERT INTO ZZ_TAB1 VALUES(2006262803,1,5,'008182','0.301',2,1);
INSERT INTO ZZ_TAB1 VALUES(2006262803,1,6,'008183','0.301',2,2);
INSERT INTO ZZ_TAB1 VALUES(2006262803,1,7,'008184','0.301',2,4);
INSERT INTO ZZ_TAB1 VALUES(2006262803,1,8,'008185','0.301',2,5);
INSERT INTO ZZ_TAB1 VALUES(2006262803,1,9,'008189','0.301',2,3);
 
INSERT INTO DRC_INGR VALUES ('008187','동충하초');
INSERT INTO DRC_INGR VALUES ('008186','동청');
INSERT INTO DRC_INGR VALUES ('008188','두시');
INSERT INTO DRC_INGR VALUES ('008181','대청엽');
INSERT INTO DRC_INGR VALUES ('008182','대풍자');
INSERT INTO DRC_INGR VALUES ('008183','동과자');
INSERT INTO DRC_INGR VALUES ('008189','두충엽');
INSERT INTO DRC_INGR VALUES ('008184','동과피');
INSERT INTO DRC_INGR VALUES ('008185','동규자');
COMMIT;

SELECT       ITEM_SEQ
                 ,TOTAL_SEQ
                 ,COSMETIC_MIX_GROUP_SEQ
                 ,SUBSTR(MAX (SYS_CONNECT_BY_PATH (INGR_SEQ, '/')), 2) INGR_SEQ
                 ,SUBSTR(MAX (SYS_CONNECT_BY_PATH (INGR_CODE, '/')), 2) INGR_CODE
                 ,SUBSTR (MAX (SYS_CONNECT_BY_PATH (T1.INGR_KOR_NAME, '/')), 2)  INGR_KOR_NAME
                ,MAX(INGR_QTY) INGR_QTY
FROM   (
    SELECT  ITEM_SEQ,
                 TOTAL_SEQ,
                 INGR_SEQ,
                 T1.INGR_CODE,
                 T2.INGR_KOR_NAME,
                 INGR_QTY,
                 COSMETIC_MIX_GROUP_SEQ,
                 SORT_KEY RNUM  -- 칼럼에 소트키가 있을때
                 --ROW_NUMBER () OVER (PARTITION BY COSMETIC_MIX_GROUP_SEQ ORDER BY ROWNUM) rnum  -- 칼럼에 별도의 소트키가 없을때
   FROM  ZZ_TAB1 T1, DRC_INGR T2
   WHERE  T1.INGR_CODE = T2.INGR_CODE
         ) T1
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR COSMETIC_MIX_GROUP_SEQ = COSMETIC_MIX_GROUP_SEQ
  GROUP BY   ITEM_SEQ
         ,TOTAL_SEQ
         ,COSMETIC_MIX_GROUP_SEQ
;

[출처] 세로값을 가로로 출력|작성자 리원아빠

반응형
저작자표시 비영리

'ORACLE > SQL' 카테고리의 다른 글

데이터 복사하기 위한 INSERT스크립트 생성  (0) 2009.02.24
TIMESTAMP -> DATE 변환  (0) 2009.02.24
Oracle Join Update 시 /*+ bypass_ujvc */ 힌트사용  (0) 2009.02.23
◎ SQL PLUS상에서 STORED PROCEDURE 소스 확인 방법  (0) 2009.01.22
Ampersand ( & )나 특수 문자를 갖는 데이타를 insert하는 방법  (0) 2009.01.22
Posted by [PineTree]
ORACLE/Modelling2009. 2. 24. 10:09

성능 데이터 모델링의 핵심 비법

반응형

성능 데이터 모델링의 핵심 비법

 

이춘식 | 프로젝트의 데이터베이스, 데이터 모델링, 데이터베이스 집필, 강의, 세미나, 기고

 

서울에서 부산까지 2시간 40분, 서울에서 목포까지 2시간 58분. 2004년 3월 KTX 개통으로 인해 서울에서 부산, 서울에서 목포까지 이동하는 시간이다. 시속 300km 이상의 속도를 내는 최첨단 열차인 KTX도 기존 선로를 이용할 경우 선로의 굴곡과 내구성의 특성으로 인해 시속 130km 밖에 속도를 내지 못한다. 대구에서 부산까지는 아직 KTX를 위한 선로가 완성되지 않아 기존 선로에서 운행하다보니 시간이 더 걸리고 있다. 즉, KTX는 속도를 빠르게 하는 기본 환경이 되어있어야만 시속 300km 이상의 빠른 속도를 낼 수 있다. 기존 선로에서 KTX의 성능만 향상 시켜봐야 무용지물일 뿐이다.

 

최근 많은 프로젝트에서는 데이터베이스에 대한 설계는 대충하고 구축 이후 튜닝만 하면 성능이 잘 나올 것이라는 환상을 가지고 있는 경우가 많다. 특히 SQL 구문의 실행 계획을 분석하여 실행 계획을 요리조리 바꾸어 튜닝을 하면 웬만큼 성능이 나오는 특성으로 인해 SQL 문장 튜닝으로 모든 것을 다하려고 한다.

 

데이터/트래픽 양이 많지 않고 업무가 복잡하지 않을 때는 이런 경우가 가능하겠지만 트래픽이 집중되고 데이터 양이 많으며 업무가 복잡할수록 이런 식의 튜닝으로는 성능을 향상시킬 수 없다. 개발자가 적절하게 설계되지 않은 데이터베이스에 아무리 훌륭하게 SQL 구문을 작성한다 해도 성능이 나오지 않는 것이다. 어떤 프로젝트든 개발한 프로그램에서 성능이 저하되어 문제가 발생하면 마치 죄인처럼 개발자를 바라보는 경우를 많이 본다. 그러나 근본적으로 데이터 모델이 잘못되어 개발자가 그렇게 밖에 개발할 수밖에 없어 성능이 저하되는 경우가 비일비재하다. 데이터 모델링은 수행하는 사람은 성능 저하가 나타나지 않도록 최적화된 데이터 모델을 만들어야 할 의무가 있다.

 

흔히 데이터베이스 성능을 이야기하면 SQL 문장에 대한 성능을 이야기하거나 데이터베이스 관리자(DBA)의 경우 데이터베이스 파라미터 정도로 여기는 경우가 많이 있다. 그러나 데이터베이스 설계 단계 즉, 데이터 모델링 단계에서부터 성능을 고려한 모델링을 적용하지 않는다면 구조적인 성능 문제를 가지고 있게 되어 성능개선의 한계에 부딪히는 경우가 많다. 성능 데이터 모델링이란 데이터베이스 성능 향상을 목적으로 설계 단계의 데이터 모델링 때부터 정규화, 반정규화, 테이블 통합, 테이블 분할, 조인구조, PK, FK 등 성능과 관련된 사항이 데이터 모델링에 반영될 수 있도록 하는 것으로 정의할 수 있다.

 

성능 데이터 모델링은 논리적 데이터 모델링이나 물리적 데이터 모델링과 같이 프로젝트 진행 단계에서 어떤 단계별 진행 프로세스에 포함되지는 않는다. 단, 모델링의 전체과정에서 성능과 관련된 주요한 내용이 반영되도록 해야 한다. 이번 컬럼은 필자가 국내 유수의 프로젝트를 수행하거나 진단하면서 성능과 관련된 내용만을 13개 항목으로 정리한 것이다.

 

성능에 영향을 주는 데이터 모델링의 3단락 13항목

 

데이터 모델링과 관련되어 성능에 영향을 미치는 요소는 크게 데이터 모델의 구조에 따라 성능, 대용량 데이터 특성을 고려한 성능, 인덱스 특성을 고려한 성능 향상 등이다. 여기서는 지면 관계상 13개 항목 중 중요한 6개 항목을 선정하여 살펴본다.

 

1. 데이터 모델 구조에 의한 성능 향상
- 정규화를 통한 성능 향상
- 반정규화를 통한 성능 향상
- 복잡한 데이터 모델 단순화를 통한 성능 향상

 

2. 대용량 데이터 특성을 고려한 성능 향상
- 이력 모델의 구분과 기능성 컬럼 이용으로 성능 향상
- 트랜잭션 특성에 의한 슈퍼타입/서브타입 구분에 의한 성능 향상
- 컬럼 수가 많은 테이블을 1:1 분리를 통한 성능 향상
- 대용량의 테이블은 파티셔닝을 적용한 성능 향상

 

3. 인덱스 특성을 고려한 성능 향상
- CHAR 형식에서 개발 오류 제거를 통한 성능 향상
- 일관성 있는 데이터 타입과 길이로 성능 향상
- 분산 환경 구성을 통한 성능 향상
- 효율적인 채번 방법 사용을 통한 성능 향상
- PK 순서 조정을 통한 성능 향상
- FK 인덱스 생성을 통한 성능 향상

 

정규화를 통한 성능 향상

 

일반적으로는 정규화가 잘 되어 있으면 입력, 수정, 삭제의 성능이 향상되고 반정규화를 많이 할수록 조회의 성능이 향상된다. 그러나 데이터 모델링할 때 반정규화만이 조회 성능을 향상시킨다는 고정관념은 탈피되어야 한다. 정규화를 해야만 성능을 향상시키는 경우도 빈번하기 때문이다. 정규화가 된다는 의미는 원칙적으로 데이터 간의 함수 종속성이 높다는 의미이고 데이터를 처리할 때 정규화된 속성의 집합단위로 처리될 확률도 많아진다. 또한 정규화 된 테이블을 조인하면 반드시 성능이 저하되는 것은 아니다.

 

두 개의 엔티티 타입이 통합되어 반정규화되어 성능이 저하된 경우
다음 데이터 모델의 경우를 살펴보자. 매각시간과 매각장소는 매각일자에 종속적인 2차 정규화가 안 된 데이터 모델이다. 경매에 대한 간단한 사례로 매각물건이 있으면 해당 매각물건은 특정한 날짜에 지정된 여러 장소에서 매각을 하고 매각된 내용에 대해 통계 데이터를 관리하는 데이터 모델이다. 이 업무에서는 매각일자에 따라 매각시간과 매각장소가 결정되는 중요한 속성이다. 매각일자는 대략적으로 5000건이 있고 일자별매각물건은 100만 건으로 가정하자.

 

<그림 2>의 모델에서 만약 매각장소가 ‘서울 7호’에서 매각된 총매각금액, 총유찰금액을 산출하는 조회용 SQL 문장을 작성하면 다음과 같이 작성된다.

 

 

<그림 1> 성능 데이터 모델

 

<그림 2> 성능이 저하된 반정규화 사례 - 엔티티 타입 반정규화

 

<그림 3> 성능이 저하된 반정규화 사례 - 정규화를 통한 성능 향상

 

즉 대량으로 존재하는 데이터에서 조인조건이 되는 대상을 찾기 위해 인라인뷰를 사용함으로써 성능이 저하되는 사례이다. 복합식별자 중에서 일반속성이 주식별자 속성 중 일부에만 종속 관계를 가지고 있으므로 2차 정규화의 대상이 된다. 2차 정규화를 적용하면 <그림 3>과 같은 모델이 된다.

 

2차 정규화를 적용하여 매각일자가 PK가 되고 매각시간과 매각장소가 일반속성이 되었다. 정규화를 적용함으로써 매각일자를 PK로 사용하는 매각일자별매각내역과도 관계가 연결될 수 있어 업무흐름에 따른 정확한 데이터 모델링 표기도 가능하게 되었고 드라이빙이 된 테이블이 5000건의 매각기일 테이블이 되므로 성능도 향상되었음을 알 수 있다. <그림 3>의 모델에서 만약 매각장소가 ‘서울 7호’에서 매각된 총매각금액, 총유찰금액을 산출하는 조회용 SQL문장을 작성하면 다음과 같다.

 

매각기일 테이블이 정규화가 되었고 따라서 드라이빙이 되는 대상 테이블의 데이터가 5000건이므로 빠른 조회의 성능이 나온다.

 

<그림 4> 성능이 저하된 반정규화 사례 - 속성이 중복된 경우1

 

<그림 5> 성능이 저하된 반정규화 사례 - 속성이 중복된 경우 2

 

두 개의 속성을 나열하여 반정규화하여 성능이 저하된 경우
계층형 데이터베이스를 많이 사용했던 과거 데이터 모델링의 습관에 따라 관계형 데이터베이스에서도 이와 같이 데이터 모델링을 한 경우가 많이 나타난다. 다음 사례에서 보면 모델이라고 하는 테이블에는 업무적으로 필요한 8개의 인덱스가 이미 생성되어 있는 상태이다. 데이터는 30만 건이고 온라인 환경의 데이터베이스라고 가정하자. 유형기능분류코드에 따라 데이터를 조회하는 경우가 많이 나타나 인덱스를 생성하려고 하니 유형기능분류코드 각각에 대해 인덱스를 생성해야 하므로 무려 9개나 되는 인덱스를 추가 생성해야 한다.

 

한 테이블에 인덱스가 많아지면 조회 성능은 향상되지만 데이터 입력, 수정, 삭제에 대한 성능은 저하된다. 그래서 일반 업무처리(온라인성 업무)에서는 인덱스 수를 가급적 7~8개가 넘지 않도록 하는 것이 좋다고 할 수 있다. 그런데 <그림 4>의 모델은 다른 필요한 인덱스 이외에 유형기능분류코드 속성에 해당하는 인덱스를 9개나 추가로 생성해야 하므로 실전 프로젝트에서는 어쩔 수 없이 인덱스를 생성하지 않거나 A유형기능분류코드1 하나만 인덱스를 생성하는 경우가 발생된다. 이에 따라 A유형기능분류코드1, A유형기능분류코드2, A유형기능분류코드3...을 이용하는 SQL의 성능이 저하되어 나타나는 경우가 많다. 만약 각 유형코드별로 조건을 부여하여 모델코드와 모델명을 조회하는 SQL 문장을 작성한다면 다음과 같이 작성된다.

 

 

각 유형별로 모두 인덱스가 걸려 있어야 인덱스에 의해 데이터를 찾을 수 있다. 앞의 모델은 다음과 같이 정규화를 적용해야 한다. 중복속성에 대한 분리가 1차 정규화의 정의임을 고려하면 모델 테이블은 1차정규화의 대상이 된다. 로우 단위의 대상도 1차 정규화의 대상이 되지만 컬럼 단위로 중복이 되는 경우도 1차 정규화의 대상이 된다. 따라서 모델에 대해 1차 정규화를 적용하면 다음과 같이 분리될 수 있다.

 

하나의 테이블에 9개가 반복적으로 나열되어 있을 때는 인덱스 생성이 어려웠지만 정규화되어 분리한 이후에는 인덱스 추가 생성이 0개가 되었고 또한 분리된 테이블 모델기능분류코드에서 PK인덱스를 생성하여 이용함으로 성능이 향상될 수 있다. 만약 각 유형코드별로 조건을 부여하여 모델코드와 모델명을 조회하는 SQL 문장을 작성한다면 다음과 같다.

 

 

이 SQL 구문은 유형코드+기능분류코드+모델코드에 인덱스가 걸려 있으므로 인덱스를 통해 데이터를 조회하여 성능이 향상된다. 실전 프로젝트에서도 많은 데이터 모델이 컬럼 단위에서 중복된 경우가 발견된다. 아무런 파급효과 계산 없이 무조건 컬럼 단위로 COL1, COL2, COL3... 이런 식으로 데이터 모델링을 하다가는 모델을 이용하여 개발하는 개발자에게 원성을 많이 들을 준비를 해야 한다.

 

그러므로 데이터 모델링을 전개할 때 기본적으로 정규화를 적용하도록 해야 한다. 일단 정규화를 적용한 데이터 모델을 만들고 난 이후에 업무적으로 발생시키는 트랜잭션의 성격, 분산 환경 등의 조건에 따라 반정규화를 적용하도록 해야 한다.

 

반정규화를 통한 성능 향상

 

반정규화의 목적은 당연히 조회 성능 향상이다. 정규화 규칙 1차, 2차, 3차, BCNF, 4차 정규화를 체계적으로 적용했는데 물리적인 데이터 모델을 설계할 때 적절하게 반정규화를 않으면 성능저하 현상이 나타난다. 전혀 반정규화를 적용하지 않으므로 인해 복잡한 SQL 구문이 작성되어 가독성이 떨어지며 SQL 구문의 성능이 저하되는 경우가 많이 나타난다. 따라서 실전 프로젝트에서 반정규화는 반드시 수행되어야 하는 필요한 데이터 모델링의 태스크(task)임에는 분명하나 무분별한 반정규화는 데이터 무결성을 깨뜨리는 요인이 되므로 ‘반드시 성능상 필요한 경우’에만 반정규화를 적용하도록 한다.

 

정규화가 잘 정의된 데이터 모델에서 성능이 저하된 경우 1
<그림 6>은 공급자라고 하는 엔티티 타입이 마스터이고 전화번호와 메일주소 위치가 각각 변경되는 내용이 이력형태로 관리되는 데이터 모델이다. 이 모델에서 공급자정보를 가져오는 경우를 가정해보자.

 

<그림 6> 반정규화를 통한 성능 향상 사례 1

 

<그림 7> 반정규화를 통한 성능 향상 사례 2

 

공급자와 전화번호, 메일주소, 위치는 1:M 관계이므로 한 명의 공급자당 여러 개의 전화번호, 메일주소, 위치가 존재한다. 따라서 가장 최근에 변경된 값을 가져오기 위해서는 조금 복잡한 조인이 발생될 수밖에 없다. 다음 SQL은 이와 같은 조건을 만족하는 SQL 구문이 된다.

 

 

<그림 8> 반정규화를 통한 성능 향상 사례 - 다른 서버 간 1

 

<그림 9> 반정규화를 통한 성능 향상 사례 - 다른 서버 간 2

 

꼼꼼한 독자는 이 SQL 구문을 다 이해하려고 하겠지만 대다수는 SQL 구문이 워낙 길기에 귀찮아서 이해하려 하지 않았을 것이다. 정규화된 모델이 적절하게 반정규화되지 않으면 이와 같은 복잡한 SQL 구문은 쉽게 나올 수 있다. 이른바 A4 용지 5장으로 작성된 SQL이 쉽지 않게 발견될 수 있는 것이다.

 

<그림 6>의 모델을 적절하게 반정규화를 적용하면 즉, 가장 최근에 변경된 값을 마스터에 위치시키면 다음과 같이 아주 간단한 SQL 구문이 작성된다. 앞에서 복잡하게 작성된 SQL 문장이 반정규화를 적용하므로 인해 다음과 같이 간단하게 작성이 되어 가독성도 높아지고 성능도 향상되어 나타났다.

 

결과만 보면 너무 당연하고 쉬운 것 같지만 기억해야 할 사실은 이런 내용들은 모두 실전에서 발견된 내용이라는 데 있다. 앞의 데이터 모델은 최근 값을 나타내는 기능성 컬럼을 추가하여 성능을 향상시킬 수도 있다.

 

정규화가 잘 정의된 데이터 모델에서 성능이 저하된 경우 2
업무의 영역이 커지고 다른 업무와 인터페이스가 많아짐에 따라 데이터베이스 서버가 여러 대인 경우가 있다. <그림 8>은 데이터베이스 서버가 분리되어 분산 데이터베이스가 구성되어 있을 때 반정규화를 통해 성능을 향상시킬 수 있는 경우이다.

 

서버 A에 부서와 접수 테이블이 있고 서버 B에 연계라는 테이블이 있는데 서버 B에서 데이터를 조회할 때 빈번하게 조회되는 부서번호가 서버 A에 존재하기 때문에 연계, 접수, 부서 테이블이 모두 조인이 걸리게 된다. 게다가 분산데이터베이스 환경이기 때문에 다른 서버 간에도 조인이 걸리게 되어 성능이 저하되는 것이다. 이 모델을 통해 서버 B의 연계테이블에서 부서명에 따른 연계상태코드를 가져오는 SQL 구문은 다음과 같이 작성된다.

 

 

오라클의 경우 DB LINK 조인이 발생하여 일반조인보다 성능이 저하될 것이다. 앞의 분산 환경에 따른 데이터 모델을 다음과 같이 서버 A에 있는 부서테이블의 부서명을 서버 B의 연계테이블에 부서명으로 속성 반정규화를 함으로써 조회 성능을 향상시킬 수 있다. <그림 9>의 모델에 대한 SQL 구문은 다음과 같이 작성된다.

 

 

SQL 구문도 간단해지고 분산되어 있는 서버 간에도 DB LINK 조인이 발생하지 않아 성능이 개선되었다. 데이터 모델링에서 반정규화는 속성의 반정규화만 있는 것이 아니라 테이블에 대한 반정규화(통합, 분리), 속성의 반정규화(파생 컬럼 추가, 중복 컬럼 추가, 기능성 컬럼 추가), 관계의 반정규화(중복 관계 생성)가 있다. 3가지 반정규화를 데이터 모델에 반영하여 성능을 향상시킬 수 있는 방법이 있다. 많은 사람들은 중복 속성의 반정규화만을 많이 사용하고 있으나 관계의 반정규화나 테이블의 반정규화 등을 적용하여 실전 프로젝트에서 성능 향상을 시킬 수 있음을 기억해야 한다.

 

컬럼수 많은 테이블에서 1:1 분리를 통한 성능 향상

 

프로젝트에 가보면 때론 하나의 테이블에 300개 이상의 컬럼을 가지고 있는 경우가 있다. 컴퓨터 화면 하나에는 볼 수가 없어서 스크롤을 하면서 하나의 테이블에 있는 컬럼을 구경해야 할 정도이다. 이렇게 많은 컬럼은 로우 체이닝(ROW CHAINING)과 로우 마이그레이션(ROW MIGRATION)이 많아지게 되어 성능이 저하된다.

 

로우 길이가 너무 길어서 데이터 블럭 하나에 데이터가 모두 저장되지 않고 두 개 이상의 블럭에 걸쳐 하나의 로우가 저장되어 있는 형태가 로우 체이닝 현상이다. 또한 로우 마이그레이션은 데이터 블럭에서 수정이 발생하면 수정된 데이터를 해당 데이터 블럭에서 저장하지 못하고 다른 블럭의 빈 공간을 찾아 저장하는 방식이다. 로우 체이닝과 로우 마이그레이션이 발생하여 많은 블럭에 데이터가 저장되면 데이터베이스 메모리에서 디스크와 I/O(입력/출력)가 발생할 때 불필요하게 I/O가 많이 발생하여 성능이 저하된다.

 

컬럼 수가 많음으로 인해 성능 저하
다음 모델은 도서정보라고 하는데 테이블의 컬럼 수가 아주 많이 있다. 생략된 컬럼까지 합하면 대략 200개라고 가정한다. 만약 하나의 로우의 길이가 10KB라고 하고 블럭은 2K 단위로 쪼개어 있다고 가정한다. 또한 블럭에 데이터는 모두 채워진다고 가정하면 대략 하나의 로우는 5블럭에 걸쳐 데이터가 저장될 것이다. 이 때 컬럼의 앞쪽에 위치한 발행기관명, 수량, 중간에 위치한 공고일, 발행일에 대한 정보를 가져오려면 물리적으로 컬럼의 값이 블럭에 넓게 산재되어 있어 디스크 I/O가 많이 일어나게 된다.

 

200개의 컬럼을 동시에 조회하여 화면에 보여주는 경우는 드문 사례가 될 것이다. 만약 200개의 컬럼이 가지고 있는 값을 모두 한 화면에 보여주기 위해서는 화면을 몇 번 스크롤하면서 보여야 한다. 즉 이렇게 많은 컬럼을 가지고 있는 테이블에 대해서는 트랜잭션이 발생될 때 어떤 컬럼에 대해 집중적으로 발생하는지 분석하여 테이블을 쪼개어 주면 디스크 I/O가 감소하게 되어 성능이 개선되게 된다.

 

<그림 10> 컬럼 수가 많은 테이블

 

<그림 11>의 데이터 모델을 살펴보자. 도서정보 테이블에는 전자출판 유형에 대한 트랜잭션이 독립적으로 발생되는 경우가 많이 있고 대체 제품에 대한 유형의 트랜잭션이 독립적으로 발생되는 경우가 많이 있어 1:1 관계로 분리했다. 분리된 테이블은 디스크에 적어진 컬럼이 저장되므로 로우 마이그레이션과 로우 체이닝이 많이 줄어들 수 있다. 따라서 다음과 같이 발행기관명, 수량, 중간에 위치한 공고일, 발행일을 가져오는 동일한 SQL 구문에 대해서도 디스크 I/O가 줄어들어 성능이 개선되게 된다.

 

<그림 11> 컬럼 수가 많은 테이블 1:1로 분리한 경우 1

 

많은 수의 컬럼을 가지는 데이터 모델 형식도 실전 프로젝트에서 흔히 나타나는 현상이다. 트랜잭션을 분석하여 적절하게 1:1 관계로 분리함으로써 성능을 향상할 수 있도록 해야 할 것이다.

 

일관성 있는 데이터 타입과 길이로 성능 향상

 

동일 컬럼에 데이터 타입의 길이가 맞지 않을 경우 컬럼의 형 변환이 발생하여 인덱스를 사용하지 못하는 경우가 발생하므로 반드시 일관성 있는 데이터 타입과 길이를 유지하도록 한다. 일관성 있는 데이터 타입과 길이를 지정하기 위해서는 데이터 모델링 단계에서 반드시 도메인 정의서와 용어사전을 활용하여 데이터 모델링을 전개해야 한다.

 

데이터 타입과 길이가 달라지므로 인한 성능 저하
<그림 13>의 예를 보면 장비에 대한 기본정보를 관리하는 엔티티 타입의 장비번호는 VARCHAR2(10)로 되어 있지만 이 속성을 이용하는 생산정보에서는 장비번호가 NUMBER(10)으로 되어 있다. 이 모델에서 만약 장비도입일자가 2004년 12월 1일인 장비에 대해 라인번호 당 생산건수를 산출하는 SQL구문을 만든다면 다음과 같이 작성된다.

 

 

두 속성의 데이터 타입과 길이가 다르므로 인해 원하는 결과가 나오지 않는다. 따라서 조인이 발생한 A.장비번호 = B.장비번호를 A.장비번호 = RTRIM(B.장비번호)으로 해야 결과를 도출할 수 있고 이에 따라 생산정보의 장비번호 컬럼에 인덱스가 걸려 있다고 해도 인덱스를 이용하지 못하는 현상이 발생되어 FULL TABLE SCAN이 된다.

 

<그림 14>는 SQL 구문에 RTRIM을 이용하여 실행했을 때 실행 계획이다. 인덱스 컬럼이 변형되었기 때문에 생산정보 테이블에 장비번호가 인덱스가 걸려 있어도 FULL TABLE SCAN이 발생되었다. 혹시 독자에 따라서는 ‘이렇게 간단한 오류를 누가 범하겠는가?’하고 의문을 가질 수 있다. 분명한 사실은 이렇게 데이터 타입과 길이에 대한 일관성이 유지되지 않는 경우가 실전 프로젝트에서는 아주 빈번하다는 것이다.

<그림 12> 컬럼 수가 많은 테이블 1:1로 분리한 경우 2

데이터 모델링할 때 그냥 각 속성에 데이터 타입과 길이를 직접 지정하면 앞에서와 같이 컬럼의 일관성이 결여되는 경우가 많으므로 가급적이면 도메인을 정의하여 각 속성에는 도메인을 할당하는 형식으로 데이터 모델링을 진행하는 것이 데이터 모델에 대한 일관성뿐만 아니라 데이터베이스 성능 저하도 예방하는 좋은 방법이 된다.

 

분산 환경 구성을 통한 성능 향상

 

중요 데이터 처리에 부하를 주는 배치처리/통계성업무/인터넷서비스 등은 데이터베이스 분산 환경 구성(데이터베이스 서버)을 통해 메인업무 데이터베이스 서버에 부하를 최소화하도록 배치한다.

 

인터넷 환경에서 분산 환경 구성을 통한 성능 향상
C 프로젝트에서는 네트워크 용량도 아주 우수하고 시스템의 사용도 아주 좋은 사양으로 시스템을 구성하였다. 데이터베이스 서버는 내부에서 처리하는 업무처리를 하기도 하였으며 외부 인터넷에 있는 네티즌이 데이터를 조회할 수 있도록 서비스도 오픈되어 있는 시스템이었다. 개발 프로젝트가 끝나고 한 달 정도는 아주 순조롭게 잘 사용하고 있었는데 어느 날 갑자기 KBS, MBC 등 방송국과 신문에 우리나라에 아주 편리하고 좋은 인터넷 시스템이 있다고 홍보성 기사가 나가게 되었다. 그 다음날 이전까지 아주 서비스를 잘하고 있었던 훌륭한 시스템은 바로 다운되어 버렸다. 시스템이 다운되면서 내부에서 처리하고 있는 중요한 업무까지 마비되어 비상이 된 사례가 있다.

 

<그림 13> 데이터 타입과 길이의 불일치로 인한 성능 저하 1

 

<그림 14> 데이터 타입과 길이의 불일치로 인한 성능 저하 2

 

<그림 15>는 이러한 환경을 보여주는 것이다. 인터넷 환경에서는 불특정 다수의 사람이 어느 시점에 한꺼번에 시스템에 들어와 데이터를 조회할 수 있다. 이러한 이유로 인해 업무처리 중에 자원이 부족하여 성능저하 현상이 나타날 수도 있고 중요한 업무처리 데이터와 외부에서 처리해야 하는 데이터가 공존하다 보면 데이터베이스 서버가 다운될 수 있는 위험이 너무 많이 잠재된다는 것이다.

 

인터넷에서 불특정 다수의 이용자가 서버에 접근하여 처리될 때 데이터의 수를 줄여주고 또한 인터넷 사용자에 의해 데이터베이스 서버가 다운되더라도 업무 서비스는 정상적으로 처리할 수 있도록 하기 위해 <그림 16>과 같이 데이터베이스 서버를 분리하여 구성한다.

 

인터넷 환경에 있는 사용자가 업무처리용 데이터베이스 서버에 들어오지 않으므로 안정적으로 업무처리를 할 수 있을 뿐만 아니라 자원도 모두 이용할 있어 성능도 향상될 수 있다. 이와 같은 분산구조는 계획 단계 때 결정되거나 늦어도 업무분석 단계 결정이 되어야 시스템에 적합한 하드웨어 소프트웨어를 구매하여 적용할 수 있다.

 

<그림 15> 업무 데이텁이스의 불특정 다수 트랜잭션 발생

 

<그림 16> 업무 데이터베이스의 성능 향상

 

LDAP 서버에서 사용자와 권한 관리에 따른 업무 DB에 사용자 관리 정의
LDAP은 시스템의 권한 관리를 빠르고 효율적으로 할 수 있도록 디렉토리 구조 형식으로 데이터를 가지고 있으면서 서비스하는 하나의 패키지 소프트웨어이다. 인터넷 환경에서 한 번에 로그인을 처리하는 SSO(Single Sign On)와 같은 구성을 할 때 많이 사용하는데 다른 업무 시스템과 LDAP이 구성된 시스템 간 데이터 통신이 많이 발생된다.

 

LDAP 서버에서는 보통 사용자 인증 관리인 SSO와 기타 사용자 및 조직에 대한 기본정보를 관리하고 다른 업무 데이터베이스에서 LDAP에 있는 사용자나 조직정보를 이용할 때 FROM 절에 조인이 될 수 없고 바로 건 단위로 조회를 하게 되어 성능 저하 현상이 나타 나는 경우가 많이 있다. 이와 같은 이유로 인해 가급적 사용자 정보는 업무 데이터베이스 영역에 데이터를 복제(REPLICATION)하도록 하고 그에 따라 데이터를 동기화하여 사용해야 한다.

 

<그림 17>과 비슷한 분산구성 방식인 공통코드, 기준정보, 사용자 정보와 같은 내용은 적절하게 배치를 통하거나 실시간으로 복제분산을 적용하여 성능을 향상시켜야 하는 경우에 해당된다. 이 경우도 결과만 보면 아주 당연한 데이터 아키텍처 도출같지만, 실전 프로젝트에서는 왼쪽과 같은 구성으로 성능이 저하되는 경우가 아주 많이 발견되었다.

 

<그림 17> LDAP에서 업무 DB로 사용자 정보 복제

<그림 18> PK 순서에 대한 인덱싱 생성 2

 

PK 순서조정을 통한 성능 향상

 

데이터 모델의 PK 순서가 아무런 의미가 없는 것 같지만 실전 프로젝트에서 의미 없는 PK 순서를 설계하여 성능이 저하되는 경우가 아주 빈번하다. 성능저하 현상의 많은 부분이 PK가 여러 개의 속성으로 구성된 복합식별자일 때 PK 순서에 대해 별로 고려하지 않고 데이터 모델링을 한 경우에 해당된다.

 

특히 물리적인 데이터 모델링 단계에서는 스스로 생성된 PK 순서 이외에 다른 엔티티 타입으로부터 상속받아 발생되는 PK 순서까지 항상 주의하여 표시하도록 해야 한다. PK는 해당 테이블의 데이터를 접근할 때 가장 빈번하게 사용되는 유일한 인덱스(UNIQUE INDEX)를 모두 자동 생성한다. PK 순서를 결정하는 기준은 인덱스 정렬구조를 이해한 상태에서 인덱스를 효율적으로 이용할 수 있도록 PK 순서를 지정해야 한다. 즉 인덱스의 특징은 여러 개의 속성이 하나의 인덱스로 구성되어 있을 때 앞쪽에 위치한 속성의 값이 비교자로 있어야 인덱스가 좋은 효율을 나타낼 수 있다.

 

앞쪽에 위치한 속성 값이 가급적 ‘=’ 아니면 최소한 범위 ‘BET WEEN’, ‘< >’가 들어와야 인덱스를 이용할 수 있는 것이다. 어떤 값이 들어오는지 예상을 해야 하는 특성으로 인해 데이터베이스에 발생시키는 트랜잭션의 성격을 이해하지 못하면 원활한 PK 순서를 지정할 수 없게 된다. 즉, 데이터 모델링에 참여한 사람이 정확한 프로세스의 특징을 이해하지 못한다면 PK 순서를 정확하게 지정할 수 없다는 의미이다.

 

데이터 모델링할 때 결정한 PK 순서와는 다르게 DDL 문장을 날려 PK 순서를 다르게 생성할 수도 있다. 그러나 대부분의 프로젝트에서는 데이터 모델의 PK 순서에 따라 그대로 PK를 생성한다. 만약 다르게 생성한다고 하더라도 데이터 모델과 데이터베이스 테이블의 구조가 다른 것처럼 보여 유지보수에 어려움이 많을 것이다.

 

PK 순서를 잘못 지정하여 성능이 저하된 경우(간단한 오류)
입시마스터라는 테이블의 PK는 수험번호+년도+학기로 구성되어 있고 전형과목실적 테이블은 입시마스터 테이블에서 상속받은 수험번호+년도+학기에 전형과목코드로 PK가 구성되어 있는 복합식별자 구조의 테이블이다. 입시마스터에는 200만 건의 데이터가 있고 학사는 4학기로 구성되어 있고 데이터는 5년 동안 보관되어 있다. 그러므로 한 학기당 평균 2만 건의 데이터가 있다고 가정하자. 이 테이블 구조에서 다음과 같은 SQL 구문이 실행되면 입시마스터 테이블에 있는 인덱스 입시마스터_I01을 이용할 수 있을까?

 

 

입시마스터_I01 인덱스가 수험번호+년도+학기 중 수험번호에 대한 값이 WHERE 절에 들어오지 않으므로 인해 FULL TABLE SCAN이 발생하여 즉 200만 건의 데이터를 모두 읽게 되어 성능이 저하되었다. 입시마스터 테이블에 데이터를 조회할 때 년도와 학기에 대한 내용이 빈번하게 들어오므로 <그림 18>과 같이 PK 순서를 변경함으로써 인덱스를 이용 가능하도록 할 수 있다. 그러면 생성된 인덱스가 정상적으로 이용되어 평균 2만 건의 데이터를 처리함으로써 성능이 개선된다.

 

PK 순서를 잘못 지정하여 성능이 저하된 경우(복잡한 오류)
현금출급기실적의 PK는 거래일자+사무소코드+출급기번호+명세표번호로 되어 있는데 대부분의 SQL 문장에서는 조회할 때 사무소코드가 ‘=’로 들어오고 거래일자에 대해서는 ‘BETWEEN’ 조회를 하고 있다. 이 때 SQL은 정상적으로 인덱스를 이용할 수 있지만 인덱스 효율이 떨어져 성능이 저하되는 경우에 해당된다. 해당 테이블에 발생하는 SQL은 다음과 같다.

 

 

실행 계획을 분석해 보면 인덱스가 정상적으로 이용되었기 때문에 SQL 문장은 잘 튜닝이 된 것으로 착각할 수 있다. 문제는 인덱스를 이용하기는 하는데 얼마나 효율적으로 이용하는지 검증이 필요하다. <그림 19>는 거래일자+사무소코드 순서로 인덱스를 구성한 경우와 사무소코드+거래일자 순서로 인덱스를 구성한 경우 데이터를 처리하는 범위의 차이를 보여주는 것이다. 거래일자+사무소코드로 구성된 그림을 보면 BETWEEN 비교를 한 거래일자 ‘20040701’이 인덱스의 앞에 위치하기 때문에 범위가 넓어졌고 사무소코드+거래일자로 구성된 인덱스의 경우 ‘=’비교를 한 사무소코드 ‘000368’가 인덱스 앞에 위치하여 범위가 좁아졌다.

 

그러므로 이 경우 인덱스 순서를 고려하여 데이터 모델의 PK 순서를 거래일자+사무소코드+출급기번호+명세표번호에서 사무소코드+거래일자+출급기번호+명세표번호로 수정하여 성능을 개선할 수 있다. 물론 테이블의 PK 구조를 그대로 둔 상태에서 인덱스만 하나 더 만들어도 성능은 개선될 수 있다. 이 때 이미 만들어진 PK 인덱스가 전혀 사용되지 않는다면 입력, 수정, 삭제시 불필요한 인덱스로 인해 더 성능이 저하되어 좋지 않다. 최적화된 인덱스 생성을 위해 PK 순서변경을 통한 인덱스 생성이 바람직하다.

 

<그림 19> PK 순서에 대한 인덱싱 생성4

 

PK 순서가 잘못되어 SQL 문장의 성능이 저하되는 경우가 크게 두 가지가 있는데 첫 번째는 위의 첫 번째 단순한 사례와 같이 인덱스를 이용하지 못하고 FULL TABLE SCAN하면서 성능이 저하되는 경우와 두 번째 사례와 같이 인덱스는 이용하는데 범위가 넓어져 성능이 저하되는 경우다. 보통 첫 번째 사례는 SQL의 실행 계획을 보고 쉽게 튜닝을 하는데 비해 두 번째 경우는 UNIQUE 인덱스를 이용하는데 성능이저하되는 이유로 인해 쉽게 튜닝을 하지 못하는 경우가 빈번하다. 인덱스의 정렬(SORT) 구조를 이해한 상태에서 트랜잭션의 특성에 따른 PK 구성을 하여 인덱스 범위를 최소화하는 방향으로 데이터 모델에 반영해야 한다.

 

최적의 성능을 보장하는 데이터 모델 만들기

 

성능 데이터 모델링을 요약하여 정리하면 1단계는 기본적으로 정규화를 적용하고 필요한 경우 반정규화를 테이블, 속성, 관계에 대해 적용하고 불필요한 테이블을 통합하고 전체 데이터 모델에 관계가 누락되지 않도록 해야 한다. 2단계는 대량의 데이터를 처리하는 이력 모델에는 기능성 컬럼을 추가하고, 논리적 데이터 모델의 슈퍼타입/서브타입 모델은 트랜잭션 특성에 의한 물리적 테이블로 설계하고 컬럼 수가 많은 테이블은 트랜잭션이 어떤 속성에 집중적으로 발생되는지 분석하여 1:1 분리하고 대용량의 테이블은 파티셔닝을 적용한다. 3단계는 일관성 있는 데이터 타입과 길이를 사용하며 특히 분산 환경 구성에서 적절한 데이터 분산 전략을 적용하고 일련번호 형식을 사용하는 PK 채번 방법을 LOCK을 최소화하는 구조로 선택하고 PK/FK의 순서를 적절하게 조정하도록 한다. 이 세 가지 단계를 적용하면 데이터 모델이 잘못되어 성능을 저하시킬 수 있는 요인이 거의 없다. 반드시 앞의 사상이 반영된 성능에 관해서도 최적의 데이터 모델을 만들어 기업의 핵심가치를 창출하는 데이터 아키텍트가 되기를 바란다.

 

제공 : DB포탈사이트 DBguide.net


반응형
저작자표시 비영리

'ORACLE > Modelling' 카테고리의 다른 글

Varchar2(8) VS Date 어느 것이 우월한가?  (0) 2009.11.03
Posted by [PineTree]
ORACLE/SQL2009. 2. 23. 20:11

Oracle Join Update 시 /*+ bypass_ujvc */ 힌트사용

반응형
A table 과  b table 를 조인하여 A table의 특정필드에 b table의 특정 필드의 값을
Update 할경우
.

update  /*+ bypass_ujvc */
  (
    SELECT
          a.colnm ra
         ,b.colnm rb   
      FROM a
         , b
     WHERE a.key= b.key
) set ra = rb;

[출처] Oracle Join Update 시 /*+ bypass_ujvc */ 힌트사용|작성자 푸우

반응형
저작자표시 비영리

'ORACLE > SQL' 카테고리의 다른 글

TIMESTAMP -> DATE 변환  (0) 2009.02.24
세로값을 가로로 출력  (0) 2009.02.24
◎ SQL PLUS상에서 STORED PROCEDURE 소스 확인 방법  (0) 2009.01.22
Ampersand ( & )나 특수 문자를 갖는 데이타를 insert하는 방법  (0) 2009.01.22
ROWNUM의 동작 원리와 활용 방법  (0) 2009.01.05
Posted by [PineTree]

티스토리툴바