About DATABASE

ROWNUM의 동작 원리와 활용 방법

이번 호의 Ask Tom 컬럼은 지금까지와는 조금 다른 내용을 담고 있습니다. 필자는 오라클 데이터베이스에서 Top-N 쿼리와 페이지네이션(pagination) 쿼리를 구현하는 방법에 대해 자주 질문을 받곤 합니다. 하나의 컬럼을 통해 이러한 질문에 한꺼번에 대답하기 위한 방편으로, < Effective Oracle by Design (Oracle Press, 2003)> 의 내용을 인용하기로 했습니다. 컬럼의 포맷에 맞게 책의 내용이 다소 수정되었음을 참고하시기 바랍니다.

Tom Kyte

결과 셋의 제한

ROWNUM은 오라클 데이터베이스가 제공하는 마술과도 같은 컬럼입니다. 이 때문에 많은 사용자들이 문제를 겪기도 합니다. 하지만 그 원리와 활용 방법을 이해한다면 매우 유용하게 사용할 수 있습니다. 필자는 주로 두 가지 목적으로 ROWNUM을 사용합니다.

• Top-N 프로세싱: 이 기능은 다른 일부 데이터베이스가 제공하는 LIMIT 구문과 유사합니다.
• 쿼리 내에서의 페이지네이션(pagination) ? 특히 웹과 같은 "stateless" 환경에서 자주 활용됩니다. 필자는 asktom.oracle.com 웹 사이트에서도 이 테크닉을 사용하고 있습니다.

두 가지 활용 방안을 설명하기 전에, 먼저 ROWNUM의 동작 원리에 대해 살펴 보기로 하겠습니다

ROWNUM의 동작 원리

ROWNUM은 쿼리 내에서 사용 가능한 (실제 컬럼이 아닌) 가상 컬럼(pseudocolumn)입니다. ROWNUM에는 숫자 1, 2, 3, 4, ... N의 값이 할당됩니다. 여기서 N 은 ROWNUM과 함께 사용하는 로우의 수를 의미합니다. ROWNUM의 값은 로우에 영구적으로 할당되지 않습니다(이는 사람들이 많이 오해하는 부분이기도 합니다). 테이블의 로우는 숫자와 연계되어 참조될 수 없습니다. 따라서 테이블에서 "row 5"를 요청할 수 있는 방법은 없습니다. "row 5"라는 것은 존재하지 않기 때문입니다.

또 ROWNUM 값이 실제로 할당되는 방법에 대해서도 많은 사람들이 오해를 하고 있습니다. ROWNUM 값은 쿼리의 조건절이 처리되고 난 이후, 그리고 sort, aggregation이 수행되기 이전에 할당됩니다. 또 ROWNUM 값은 할당된 이후에만 증가(increment) 됩니다. 따라서 아래 쿼리는 로우를 반환하지 않습니다.

select * 

  from t 

 where ROWNUM > 1;

첫 번째 로우에 대해 ROWNUM > 1의 조건이 True가 아니기 때문에, ROWNUM은 2로 증가하지 않습니다. 아래와 같은 쿼리를 생각해 봅시다.

select ..., ROWNUM

  from t

 where 

 group by 

having 

 order by ;

이 쿼리는 다음과 같은 순서로 처리됩니다.

1. FROM/WHERE 절이 먼저 처리됩니다.
2. ROWNUM이 할당되고 FROM/WHERE 절에서 전달되는 각각의 출력 로우에 대해 증가(increment) 됩니다.
3. SELECT가 적용됩니다.
4. GROUP BY 조건이 적용됩니다.
5. HAVING이 적용됩니다.
6. ORDER BY 조건이 적용됩니다.

따라서 아래와 같은 쿼리는 에러가 발생할 수 밖에 없습니다.

select * 

  from emp 

 where ROWNUM <= 5 

 order by sal desc;

이 쿼리는 가장 높은 연봉을 받는 다섯 명의 직원을 조회하기 위한 Top-N 쿼리로 작성되었습니다. 하지만 실제로 쿼리는 5 개의 레코드를 랜덤하게(조회되는 순서대로) 반환하고 salary를 기준으로 정렬합니다. 이 쿼리를 위해서 사용되는 가상코드(pseudocode)가 아래와 같습니다.

ROWNUM = 1

for x in 

( select * from emp )

loop

    exit when NOT(ROWNUM <= 5)

    OUTPUT record to temp

    ROWNUM = ROWNUM+1

end loop

SORT TEMP

위에서 볼 수 있듯 처음의 5 개 레코드를 가져 온후 바로 sorting이 수행됩니다. 쿼리에서 "WHERE ROWNUM = 5" 또는 "WHERE ROWNUM > 5"와 같은 조건은 의미가 없습니다. 이는 ROWNUM 값이 조건자(predicate) 실행 과정에서 로우에 할당되며, 로우가 WHERE 조건에 의해 처리된 이후에만 increment 되기 때문입니다.

올바르게 작성된 쿼리가 아래와 같습니다.

select *

  from  

( select * 

    from emp 

   order by sal desc ) 

 where ROWNUM <= 5;

위 쿼리는 salary를 기준으로 EMP를 내림차순으로 정렬한 후, 상위의 5 개 레코드(Top-5 레코드)를 반환합니다. 아래에서 다시 설명되겠지만, 오라클 데이터베이스가 실제로 전체 결과 셋을 정렬하지 않습니다. (오라클 데이터베이스는 좀 더 지능적인 방식으로 동작합니다.) 하지만 사용자가 얻는 결과는 동일합니다.

ROWNUM을 이용한 Top-N 쿼리 프로세싱

일반적으로 Top-N 쿼리를 실행하는 사용자는 다소 복잡한 쿼리를 실행하고, 그 결과를 정렬한 뒤 상위의 N 개 로우만을 반환하는 방식을 사용합니다. ROWNUM은 Top- N쿼리를 위해 최적화된 기능을 제공합니다. ROWNUM을 사용하면 대량의 결과 셋을 정렬하는 번거로운 과정을 피할 수 있습니다. 먼저 그 개념을 살펴보고 예제를 통해 설명하기로 하겠습니다.

아래와 같은 쿼리가 있다고 가정해 봅시다.

select ... 

  from ... 

 where ... 

 order by columns;

또 이 쿼리가 반환하는 데이터가 수천 개, 수십만 개, 또는 그 이상에 달한다고 가정해 봅시다. 하지만 사용자가 실제로 관심 있는 것은 상위 N개(Top 10, Top 100)의 값입니다. 이 결과를 얻기 위한 방법에는 두 가지가 있습니다.

• 클라이언트 애플리케이션에서 쿼리를 실행하고 상위 N 개의 로우만을 가져오도록 명령
• ? 쿼리를 인라인 뷰(inline view)로 활용하고, ROWNUM을 이용하여 결과 셋을 제한 (예: SELECT * FROM (your_query_here) WHERE ROWNUM <= N)

두 번째 접근법은 첫 번째에 비해 월등한 장점을 제공합니다. 그 이유는 두 가지입니다. 첫 번째로, ROWNUM을 사용하면 클라이언트의 부담이 줄어듭니다. 데이터베이스에서 제한된 결과 값만을 전송하기 때문입니다. 두 번째로, 데이터베이스에서 최적화된 프로세싱 방법을 이용하여 Top N 로우를 산출할 수 있습니다. Top-N 쿼리를 실행함으로써, 사용자는 데이터베이스에 추가적인 정보를 전달하게 됩니다. 그 정보란 바로 "나는N 개의 로우에만 관심이 있고, 나머지에 대해서는 관심이 없다"는 메시지입니다. 이제, 정렬(sorting) 작업이 데이터베이스 서버에서 어떤 원리로 실행되는지 설명을 듣고 나면 그 의미를 이해하실 수 있을 것입니다. 샘플 쿼리에 위에서 설명한 두 가지 접근법을 적용해 보기로 합시다.

select * 

  from t 

 order by unindexed_column;

여기서 T가 1백만 개 이상의 레코드를 저장한 큰 테이블이라고, 그리고 각각의 레코드가 100 바이트 이상으로 구성되어 있다고 가정해 봅시다. 그리고 UNINDEXED_COLUMN은 인덱스가 적용되지 않은 컬럼이라고, 또 사용자는 상위 10 개의 로우에만 관심이 있다고 가정하겠습니다. 오라클 데이터베이스는 아래와 같은 순서로 쿼리를 처리합니다.

1. T에 대해 풀 테이블 스캔을 실행합니다.
2. UNINDEXED_COLUMN을 기준으로 T를 정렬합니다. 이 작업은 "full sort"로 진행됩니다.
3. Sort 영역의 메모리가 부족한 경우 임시 익스텐트를 디스크에 스왑하는 작업이 수행됩니다.
4. 임시 익스텐트를 병합하여 상위 10 개의 레코드를 확인합니다.
5.쿼리가 종료되면 임시 익스텐트에 대한 클린업 작업을 수행합니다. .

결과적으로 매우 많은 I/O 작업이 발생합니다. 오라클 데이터베이스가 상위 10 개의 로우를 얻기 위해 전체 테이블을 TEMP 영역으로 복사했을 가능성이 높습니다.

그럼 다음으로, Top-N 쿼리를 오라클 데이터베이스가 개념적으로 어떻게 처리할 수 있는지 살펴 보기로 합시다.

select *

  from 

(select * 

   from t 

  order by unindexed_column)

 where ROWNUM < :N;

오라클 데이터베이스가 위 쿼리를 처리하는 방법이 아래와 같습니다.

1. 앞에서와 마찬가지로 T에 대해 풀-테이블 스캔을 수행합니다(이 과정은 피할 수 없습니다).
2. :N 엘리먼트의 어레이(이 어레이는 메모리에 저장되어 있을 가능성이 높습니다)에서 :N 로우만을 정렬합니다.

상위N 개의 로우는 이 어레이에 정렬된 순서로 입력됩니다. N +1 로우를 가져온 경우, 이 로우를 어레이의 마지막 로우와 비교합니다. 이 로우가 어레이의 N +1 슬롯에 들어가야 하는 것으로 판명되는 경우, 로우는 버려집니다. 그렇지 않은 경우, 로우를 어레이에 추가하여 정렬한 후 기존 로우 중 하나를 삭제합니다. Sort 영역에는 최대 N 개의 로우만이 저장되며, 따라서 1 백만 개의 로우를 정렬하는 대신N 개의 로우만을 정렬하면 됩니다.

이처럼 간단한 개념(어레이의 활용, N개 로우의 정렬)을 이용하여 성능 및 리소스 활용도 면에서 큰 이익을 볼 수 있습니다. (TEMP 공간을 사용하지 않아도 된다는 것을 차치하더라도) 1 백만 개의 로우를 정렬하는 것보다 10 개의 로우를 정렬하는 것이 메모리를 덜 먹는다는 것은 당연합니다.

아래의 테이블 T를 이용하면, 두 가지 접근법이 모두 동일한 결과를 제공하지만 사용되는 리소스는 극적인 차이를 보임을 확인할 수 있습니다.

create table t

as

select dbms_random.value(1,1000000) 

id, 

       rpad('*',40,'*' ) data

  from dual

connect by level <= 100000;

begin

dbms_stats.gather_table_stats

( user, 'T');

end;

/

Now enable tracing, via

exec 

dbms_monitor.session_trace_enable

(waits=>true);

And then run your top-N query with ROWNUM:

select *

  from

(select *

   from t

  order by id)

where rownum <= 10;

마지막으로 상위 10 개의 레코드만을 반환하는 쿼리를 실행합니다.

declare

cursor c is

select *

  from t

 order by id;

l_rec c%rowtype;

begin

    open c;

    for i in 1 .. 10

    loop

        fetch c into l_rec;

        exit when c%notfound;

    end loop;

    close c;

end;

/

이 쿼리를 실행한 후, TKPROF를 사용해서 트레이스 결과를 확인할 수 있습니다. 먼저 Top-N 쿼리 수행 후 확인한 트레이스 결과가 Listing 1과 같습니다.

Code Listing 1: ROWNUM을 이용한 Top-N 쿼리

select *

  from

(select *

   from t

  order by id)

where rownum <= 10

call         count     cpu elapsed   disk     query      current    rows

--------     --------  -------  -------   -------  --------   --------   ------ 

Parse        1         0.00     0.00      0          0        0           0

Execute      1         0.00     0.00      0          0        0           0

Fetch        2         0.04     0.04      0        949        0          10

--------     --------  -------  -------   -------  --------   --------   ------ 

total        4         0.04     0.04      0        949        0          10

Rows                         Row          Source Operation

-----------------            ---------------------------------------------------

10                           COUNT STOPKEY (cr=949 pr=0 pw=0 time=46997 us)

10                           VIEW  (cr=949 pr=0 pw=0 time=46979 us)

10                           SORT ORDER BY STOPKEY (cr=949 pr=0 pw=0 time=46961 us)

100000                       TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400066 us)

이 쿼리는 전체 테이블을 읽어 들인 후, SORT ORDER BY STOPKEY 단계를 이용해서 임시 공간에서 사용되는 로우를 10 개로 제한하고 있습니다. 마지막 Row Source Operation 라인을 주목하시기 바랍니다. 쿼리가 949 번의 논리적 I/O를 수행했으며(cr=949), 물리적 읽기/쓰기는 전혀 발생하지 않았고(pr=0, pw=0), 불과 400066 백만 분의 일초 (0.04 초) 밖에 걸리지 않았습니다. 이 결과를 Listing 2의 실행 결과와 비교해 보시기 바랍니다.

Code Listing 2: ROWNUM을 사용하지 않은 쿼리

SELECT * FROM T ORDER BY ID

call         count     cpu elapsed   disk     query      current    rows

--------     --------  -------  -------   -------  --------   --------   ------ 

Parse         1        0.00     0.00        0        0        0           0

Execute       2        0.00     0.00        0        0        0           0

Fetch        10        0.35     0.40      155      949        6          10

--------     --------  -------  -------   -------  --------   --------   ------ 

total        13        0.36     0.40      155      949        6          10

Rows                         Row          Source Operation

-----------------            ---------------------------------------------------

10                           SORT ORDER BY (cr=949 pr=155 pw=891 time=401610 us)

100000                       TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400060 us)

Elapsed times include waiting for the following events:

Event waited on                  Times

------------------------------   ------------

direct path write temp           33

direct path read temp             5

결과가 완전히 다른 것을 확인하실 수 있습니다. "elapsed/CPU time"이 크게 증가했으며, 마지막 Row Source Operation 라인을 보면 그 이유를 이해할 수 있습니다. 정렬 작업은 디스크 상에서 수행되었으며, 물리적 쓰기(physical write) 작업이 "pw=891"회 발생했습니다. 또 다이렉트 경로를 통한 읽기/쓰기 작업이 발생했습니다. (10 개가 아닌) 100,000 개의 레코드가 디스크 상에서 정렬되었으며, 이로 인해 쿼리의 실행 시간과 런타임 리소스가 급증하였습니다.

ROWNUM을 이용한 페이지네이션

필자가 ROWNUM을 가장 즐겨 사용하는 대상이 바로 페이지네이션(pagination)입니다. 필자는 결과 셋의 로우 N 에서 로우 M까지를 가져오기 위해 ROWNUM을 사용합니다. 쿼리의 일반적인 형식이 아래와 같습니다.

select * 

  from ( select /*+ FIRST_ROWS(n) */ 

  a.*, ROWNUM rnum 

      from ( your_query_goes_here, 

      with order by ) a 

      where ROWNUM <= 

      :MAX_ROW_TO_FETCH ) 

where rnum  >= :MIN_ROW_TO_FETCH;

where

여기서,

• FIRST_ROWS(N)는 옵티마이저에게 "나는 앞부분의 로우에만 관심이 있고, 그 중 N 개를 최대한 빨리 가져오기를 원한다"는 메시지를 전달하는 의미를 갖습니다.
• :MAX_ROW_TO_FETCH는 결과 셋에서 가져올 마지막 로우로 설정됩니다. 결과 셋에서 50 번째 ? 60 번째 로우만을 가져오려 한다면 이 값은 60이 됩니다.
• :MIN_ROW_TO_FETCH는 결과 셋에서 가져올 첫 번째 로우로 설정됩니다. 결과 셋에서 50 번째 ? 60 번째 로우만을 가져오려 한다면 이 값은 50이 됩니다.

이 시나리오는 웹 브라우저를 통해 접속한 사용자가 검색을 마치고 그 결과를 기다리고 있는 상황을 가정하고 있습니다. 따라서 첫 번째 결과 페이지(그리고 이어서 두 번째, 세 번째 결과 페이지)를 최대한 빨리 반환해야 할 것입니다. 쿼리를 자세히 살펴 보면, (처음의 :MAX_ROW_TO_FETCH 로우를 반환하는) Top-N 쿼리가 사용되고 있으며, 따라서 위에서 설명한 최적화된 기능을 이용할 수 있음을 알 수 있습니다. 또 네트워크를 통해 클라이언트가 관심을 갖는 로우만을 반환하며, 조회 대상이 아닌 로우는 네트워크로 전송되지 않습니다.

페이지네이션 쿼리를 사용할 때 주의할 점이 하나 있습니다. ORDER BY 구문은 유니크한 컬럼을 대상으로 적용되어야 합니다. 유니크하지 않은 컬럼 값을 대상으로 정렬을 수행해야 한다면 ORDER BY 조건에 별도의 조건을 추가해 주어야 합니다. 예를 들어 SALARY를 기준으로 100 개의 레코드를 정렬하는 상황에서 100 개의 레코드가 모두 동일한 SALARY 값을 갖는다면, 로우의 수를 20-25 개로 제한하는 것은 의미가 없을 것입니다. 여러 개의 중복된 ID 값을 갖는 작은 테이블을 예로 들어 설명해 보겠습니다.

SQL> create table t

  2  as

  3  select mod(level,5) id, 

     trunc(dbms_random.value(1,100)) data 

  4    from dual

  5  connect by level <= 10000;

Table created.

ID 컬럼을 정렬한 후 148-150 번째 로우, 그리고 148?151 번째 로우를 쿼리해 보겠습니다.

SQL> select *

  2    from

  3  (select a.*, rownum rnum

  4     from

  5  (select id, data

  6     from t

  7   order by id) a

  8   where rownum <= 150

  9  )

 10   where rnum >= 148;

 ID           DATA           RNUM

-------       ----------     -----------

0             38             148

0             64             149

0             53             150

SQL>

SQL> select *

  2    from

  3  (select a.*, rownum rnum

  4     from

  5  (select id, data

  6     from t

  7   order by id) a

  8   where rownum <= 151

  9  )

 10   where rnum >= 148;

 ID           DATA           RNUM

-------       ----------     -----------

0             59             148

0             38             149

0             64             150

0             53             151

로우 148의 경우 DATA=38의 결과가 반환되었습니다. 두 번째 쿼리에서는 DATA=59의 결과가 반환되었습니다. 두 가지 쿼리 모두 올바른 결과를 반환하고 있습니다. 쿼리는 데이터를 ID 기준으로 정렬한 후 앞부분의 147 개 로우를 버린 후 그 다음의 3 개 또는 4 개의 로우를 반환합니다. 하지만 ID에 중복값이 너무 많기 때문에, 쿼리는 항상 동일한 결과를 반환함을 보장할 수 없습니다. 이 문제를 해결하려면 ORDER BY 조건에 유니크한 값을 추가해 주어야 합니다. 위의 경우에는 ROWID를 사용하면 됩니다.

SQL> select *

  2    from

  3  (select a.*, rownum rnum

  4     from

  5  (select id, data

  6     from t

  7   order by id, rowid) a

  8   where rownum <= 150

  9  )

 10   where rnum >= 148;

 ID           DATA           RNUM

-------       ----------     -----------

0             45             148

0             99             149

0             41             150

SQL>

SQL> select *

  2    from

  3  (select a.*, rownum rnum

  4     from

  5  (select id, data

  6     from t

  7   order by id, rowid) a

  8   where rownum <= 151

  9  )

 10   where rnum >= 148;

 ID           DATA           RNUM

-------       ----------     -----------

0             45             148

0             99             149

0             41             150

0             45             151

이제 쿼리를 반복 실행해도 동일한 결과를 보장할 수 있게 되었습니다. ROWID는 테이블 내에서 유니크한 값을 가집니다. 따라서 ORDER BY ID 조건과 ORDER BY ROWID 기준을 함께 사용함으로써 사용자가 기대한 순서대로 페이지네이션 쿼리의 결과를 확인할 수 있습니다.

ROWNUM 개념 정리

지금까지 ROWNUM에 관련하여 아래와 같은 개념을 설명하였습니다.

• ROWNUM의 할당 원리와 잘못된 쿼리 작성을 피하는 방법
• ROWNUM이 쿼리 프로세싱에 미치는 영향과 웹 환경의 페이지네이션을 위한 활용 방안
• ROWNUM을 이용하여 Top N쿼리로 인한 TEMP 공간의 사용을 피하고 쿼리 응답 속도를 개선하는 방법

-------------------------------------------------------------------------------------
 단일 행 함수
-------------------------------------------------------------------------------------

* initcap - 첫번째 영문자만 대문자로 바꾸어준다.

select ename, initcap(ename) from emp; 

--> 결과값 : KEVIN --> Kevin

-------------------------------------------------------------------------------------

* concat - 문자열 합치기. ||와 같으나 최대 2개만 가능

select ename || job || deptno, concat(ename,job) from emp;

--> 결과값 : SMITHCLERK20 / SMITHCLERK
 

select ename || job || deptno, concat(ename,job,deptno) from emp;

--> 에러 : concat에는 최대 2개까지만 가능..

-------------------------------------------------------------------------------------

* substr - 글자 수 기준 문자열 추출 , substrb - 바이트기준 문자열 추출

select substr('oracle',1,3), substr('대한민국',1,2) from dual; 

--> 결과값 : ora , 대한 (바이트와 상관없다.)

select substrb('oracle',1,3), substrb('대한민국',1,1) from dual;

--> substrb 는 바이트기준으로 출력한다. 한글은 2바이트,영문 1바이트

--> 결과값 : ora /  (3byte ora / 한글은 2바이트이기때문에 출력불가)

select substr('oracle',2) from dual;

--> 결과값 : racle ( 2번째 문자부터 끝까지 )

-------------------------------------------------------------------------------------

 * length - 글자수,비영어권에서 보편적임 , lengthb - 바이트수

select length('oracle'),length('대한민국'),
          lengthb('oracle'),lengthb('대한민국') from dual;

 --> 결과값 : 6 / 4 (4글자기준) / 6 / 8 (4글자 8byte기준)
       

-------------------------------------------------------------------------------------

* instr - 특정 문자열이 처음 발견 된 위치 값 리턴

select instr('oracle ORACLE oralb','ora',1),
         instr('oracle ORACLE oralb','ora',2),
         instr(lower('oracle ORACLE oralb'),lower('ora'),2)

from dual;

--> 결과값 : 1 / 15 / 8 (대소문자구분없이하기위해 소문자로 형변환)

-------------------------------------------------------------------------------------

        
* lpad, rpad - 특정 크기를 지정하여 공백부분에 특정 문자열로 채워준다.

                      숫자는 바이트라는 것에 절대 유의하자.

select lpad('오라클',20,'?'), lpad('?',20,'?'),rpad('?',20,'?'),rpad('오라클',20,'?') from dual;

--> 결과값 :

??????????????오라클 / ???????????????????? / ???????????????????? / 오라클??????????????

-------------------------------------------------------------------------------------

* ltrim , rtrim - 특정 문자열 혹은 공백 제거

select ltrim('오오징어오라클','오징' ), ltrim('오오징어오라클','클라' ),
       rtrim('오오징어오라클','오징' ), rtrim('오오징어오라클','클라' ),
    rtrim('오징어             ') ,ltrim('             오징어') ,
       rtrim(ltrim ('               오오징어오라클                ') )
from dual;

--> 결과값 :

어오라클 / 오오징어오라클 / 오오징어오라클 / 오오징어오 / 오징어 / 오징어 / 오오징어오라클

rtrim은 오른쪽에서 부터 제거되는 대신에, 지정 문자역시 뒤집어 입력해야한다.

-------------------------------------------------------------------------------------

* translate - 특정 문자열을 사용자가 지정한 매칭값으로 바꿔준다.

select translate('oracle',
                      'abcdefghijklmnopqrstuvwxyz',
                     'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
from dual;

--> 결과값 'ORACLE' - 대소문자 변환이 되었다.

select translate('공공칠빵',
                      '공일이삼사오육칠팔구빵',
                      '01234567890')
from dual;

--> 결과값 : 0070 (바뀌어질 것과 바뀌는 것의 타입은 전혀 상관없다.)

-------------------------------------------------------------------------------------

* replace - 문자열 바꾸기, 대소문자 구별..

select replace('oracle oracle9i ORACLEDBA', 'ora', '오라') from dual; 

--> 결과값 : 오라cle 오라cle9i ORACLEDBA
 

select replace(upper('oracle oracle9i ORACLEDBA'),upper('ora'), '오라') from dual; 

--> 결과값 : 오라CLE 오라CLE9I 오라CLEDBA

--> 대소문자 구별을 없애기 위해, 모든값을 대문자로 강제 형변환하였다.

-------------------------------------------------------------------------------------

* reverse - 문자열 순서 뒤집기

select reverse('oracle oracle9i ORACLEDBA') from dual; 

--> 결과값 : ABDELCARO i9elcaro elcaro

-------------------------------------------------------------------------------------

* round - 반올림, 옵션 숫자가 음수면 정수자리, 양수면 소수자리 반올림

select 91.459, round(91.459,-3),round(91.459,-2) , round(91.459,-1) ,round(91.459,0) ,
       round(91.459,1) ,round(91.459,2), round(91.459,3)
from dual;

--> 결과값 : 91.459 / 0 / 100 / 90 / 91 / 91.5 / 91.46 / 91.459

-------------------------------------------------------------------------------------

* trunc - 숫자 절삭, 옵션 숫자가 음수면 정수자리, 양수면 소수자리

select 91.459, trunc(91.459,-3),trunc(91.459,-2) , trunc(91.459,-1) ,trunc(91.459,0) ,
       trunc(91.459,1) ,trunc(91.459,2), trunc(91.459,3)
from dual;

--> 결과값 : 91.459 | 0 | 0 | 90 | 91 | 91.4 | 91.45 | 91.459

-------------------------------------------------------------------------------------

* mod(나머지), power(제곱), sqrt(루트)

select mod(5,2), power(5,2), sqrt(2) from dual;
--> 결과값 : 1 / 25 / 1.4142135623731

* sign

select sign(5-2), sign(5-5), sign(2-5), sign(-3) from dual;
--> 결과값 : 1 / 0 / -1 / -1

* chr - ascii 코드에 해당하는 문자 리턴
select chr(65), chr(97), ascii('A'),ascii('a') from dual;
--> 결과값 : A / a / 65 / 97

-------------------------------------------------------------------------------------

-- 1.5.2 날짜 연산 (p.113)
-------------------------------------------------------------------------------------

* sysdate

select sysdate+1 , sysdate-1,sysdate-1/24,sysdate+1/24 from dual; 
--> 1 일 증감, 1시간 전,  1시간 후

select sysdate - to_date('2006-10-13', 'yyyy-mm-dd') from dual;
--> 1.67614583333333 (현재 날짜시간에서 해당날짜를 뺀 값, 대략 1.6일정도) 

-------------------------------------------------------------------------------------

* to_date

select to_date('2006-10-13', 'yyyy-mm-dd') from dual;
--> 2006-10-13 00:00:00

select to_date('20061013','yyyymmdd') from dual;
--> 2006-10-13 00:00:00

select to_date('20060931','yyyymmdd') from dual;
--> 에러.. 달력에9월 31일은 존재하지 않는다.

-------------------------------------------------------------------------------------

* months_between - 달 차이 구하기. (9월과 5월은 4달...)

select months_between('20061014','20060914') from dual;
--> 1 (1달 뒤)

select months_between('20060914','20061014') from dual;
--> -1 (1달 전)

select months_between(sysdate,'20060901') from dual;
--> 1.44128136200717 (대략 1달 보름)

-------------------------------------------------------------------------------------

* add_months -달 계산

select add_months(sysdate,2) from dual;
--> 2006-12-14 16:19:44 (현재 시각에 2달을 더하기.)

select add_months(sysdate,-2) from dual;
--> 2006-08-14 16:20:02 (현재 시각에 2달을 뺐다.)

-------------------------------------------------------------------------------------

* next_day - 가장 최근에 돌아오는 특정요일의 날짜 검색

select next_day(sysdate,'FRI') from dual;
select next_day(sysdate,'FRIDAY') from dual;

--> 돌아오는 금요일의 날짜와 현재와 같은 시각 리턴

select next_day(sysdate,'금') from dual;
select next_day(sysdate,'금요일') from dual; 
--> 한글은 sqlgate에서 실행불가, sqlplus에서 실행하자.

-------------------------------------------------------------------------------------

* last_day - 특정 월의 마지막 날짜 

select last_day(sysdate) from dual;

----------------------------------------------------------------

* 현재시각을 기준으로 특정값만 추출 --> 숫자 출력

select sysdate,
    to_char(sysdate,'yyyy') 년,to_char(sysdate,'mm') 월,
    to_char(sysdate,'dd') 일 , to_char(sysdate,'hh') 시,
    to_char(sysdate,'mm') 분,to_char(sysdate,'ss') 초
from dual;
--> 2006 10 15 03 10 42

-----------------------------------------------------------------

* 현재시각을 기준으로 특정값만 추출 #2  --> 영문출력

select sysdate,  -- mon <-> month 같다 dy <-> day
    to_char(sysdate,'year') 년,to_char(sysdate,'month') 월,
    to_char(sysdate,'day') 일 ,to_char(sysdate,'hh') 시,
    to_char(sysdate,'mm') 분,to_char(sysdate,'ss') 초 ,
    to_char(sysdate,'dy')
from dual;
-->two thousand sixoctober  sunday   03 10 02

-----------------------------------------------------------------

* 분기 (quarter) , 몇째 주  , 그 해의 주차, 요일에 해당하는 숫자

select to_char(sysdate,'q'), to_char(sysdate,'w'),
       to_char(sysdate,'ww'),to_char(sysdate,'d'),
    to_char(sysdate,'dd'),to_char(sysdate,'ddd')
from dual;
--> 4(4분기), 3(10월 3째주), 42(2006년 42주차),
--- 2 (월요일), 16(16일), 289(2006년 289일째) d / dd / ddd

-----------------------------------------------------------------

* 요일값 구하기

--> to_char(sysdate,'d') 
--> 리턴값은 다음과 같은 숫자 - 일요일(1) 월(2) 화(3)수(4)목(5)금(6)토(7)

리눅스기반 오라클에서 리턴값 한글로 강제 변환 방법

1) 방법 1  (case구문은 oracle 9i 부터 사용)

select case to_char(sysdate,'d')
    when '1' then '일요일'
       when '2' then '월요일'
       when '3' then '화요일'
       when '4' then '수요일'
       when '5' then '목요일'
       when '6' then '금요일'
       when '7' then '토요일'
       end "오늘의 요일명"   --> alias , 쌍따옴표 주의!!
from dual;

2) 방법 2

select case
       when to_char(sysdate,'d')='1' then '일요일'
       when to_char(sysdate,'d')='2' then '월요일'
       when to_char(sysdate,'d')='3' then '화요일'
       when to_char(sysdate,'d')='4' then '수요일'
       when to_char(sysdate,'d')='5' then '목요일'
       when to_char(sysdate,'d')='6' then '금요일'
       when to_char(sysdate,'d')='7' then '토요일'
       end "오늘의 요일명"
from dual;

3) 방법 3 (오라클 8i 이전에 주로 사용..)

select decode(to_char(sysdate,'d'),'1','일요일'
                                   ,'2','월요일'
                                   ,'3','화요일'
                                   ,'4','수요일'
                                   ,'5','목요일'
                                   ,'6','금요일'
                                   ,'7','토요일')
"오늘의 요일명"
from dual;                              

-----------------------------------------------------------------

* 현재 시각 (표준시각 current_date)

select sysdate, current_date from dual;

--> 2006-10-19 20:20:14(시스템시간) / 2006-10-19 11:20:15(세계표준시)

-----------------------------------------------------------------

* 날짜 타입 서수형으로의 변환

select to_char(sysdate, 'yyspth'),to_char(sysdate, 'mmspth'),
       to_char(sysdate, 'ddspth')
from dual;
--> sixth tenth sixteenth (200 '6'년 '10'월 '16'일)

-----------------------------------------------------------------

* 현재 날짜 원하는 형식으로 변환

select to_char(sysdate,'yyyy"년" mm"월" dd"일"')
from dual;
--> 2006년 10월 16일

-----------------------------------------------------------------

* 'fmyyyy-mm-dd' 날짜중 0을 제거/삽입 

select ename, to_char(hiredate,'yyyy-mm-dd') hiredate,
       to_char(hiredate,'fmyyyy-mm-dd') hiredate, --> 제거 
      to_char(hiredate,'fmyyyy-mmfm-dd') hiredate --> /yyyy제거 mm삽입 
from emp;
--> 1981-04-02 -> 1981-4-2 (0을 삭제하자.)

-----------------------------------------------------------------

* 통화기호와 자리표시

select ename,sal,
       to_char(sal,'09999'),  --> 00800   --> 01600
       to_char(sal,'$9,999'), -->  $800   --> $1,600
       to_char(sal,'L9,999')  -->  $800   --> $1,600
from emp;

-----------------------------------------------------------------

* 현재 설정 (언어, 통화, 달력등 보기)

select * from v$nls_parameters;

-----------------------------------------------------------------

* 날짜 계산

select '20061016'-'20061010'
from dual;
--> 6 (문자열 숫자열로 오라클 서버가 자동 형변환)

select sysdate-'20061010'
from dual;
--> 에러

select to_char(sysdate,'yyyymmdd')-'20061010'
from dual;
--> 6 (강제형변환을 해주어야한다)

select sysdate - to_date('20061010','yyyymmdd')
from dual;
--> 6.43899305555555

-----------------------------------------------------------------

* to_yminterval

select sysdate, add_months(sysdate,14),
       sysdate + to_yminterval('01-02') -- only Oracle 9i upper!
from dual; 
--> 2006-10-16 10:33:15 / 2007-12-16 10:33:15 / 2007-12-16 10:33:15
--- 14개월 / 1년 2개월 후 

-----------------------------------------------------------------

* to_dsinterval

select sysdate + to_dsinterval('001 02:03:04')
from dual;
--> 1일 2시간 3분 4초 후... / 2006-10-17 12:37:41

-----------------------------------------------------------------

* to_yminterval + to_dsinterval

select sysdate
     + to_yminterval('01-02')
     + to_dsinterval('001 02:03:04')
from dual;
--> 1년 2월 1일 2시간 3분 4초 후.. / 2007-12-17 12:39:39

-----------------------------------------------------------------

* extract - Oracle 9i이상에서만 동작한다.

    --> 날짜데이터에서 특정값을 숫자형으로 추출..(우측정렬)
    --> to_char와 결과물은 같지만, to_char는 문자열이다.(좌측)

select sysdate,
       extract(year from sysdate),
       to_char(sysdate,'yyyy'),   
       extract(month from sysdate),
       to_char(sysdate,'mm'),
       extract(day from sysdate)+1,  --> 원래 숫자형이므로 형변환 X
       to_char(sysdate,'dd')+1  --> 1을 더하면서 강제형변환이 일어났다.
from dual;

-->

2006-10-19 20:28:53 / 2006(우) / 2006(좌) / 10(우) / 10(좌) / 20(우) / 20(우)

-----------------------------------------------------------------

* 실수로 반복 입력한 데이터의 삭제.. --> rowid와 rownum을 이용..

select rownum, rowid, name,jubun
from member;

--> 잘못입력된 데이터의 데이터 입력시 자동생성되는 rowid와 rownum을 검색하자

delete member
where rowid like 'AAAHZuAAJAAAAAP%';

--> rowid를 검색하여, 그 행을 조건절을 이용하여 삭제.

-----------------------------------------------------------------

* 클라이언트 정보 검색

select userenv('language') "language",
       userenv('terminal') "terminal",
       userenv('sessionid') "sessionid"
from dual;

--> 현재 설정된 언어 값 / 접속컴퓨터터미널이름 / 세션ID

AMERICAN_AMERICA.KO16MSWIN949 / MVP386 /167

select uid, user from dual;

--> 59 (USER ID) / SCOTT(접속계정)

------------------------------------------------------------------------------

* 순위 매기기

방법 1)

select ename "사원명",deptno "부서번호",sal "급여",
       rank() over(order by sal desc) "전체등수",   --공동랭크 포함 - 공동2등 다음 4등
       rank() over(partition by deptno order by sal desc) "부서별등수",
       dense_rank() over(order by sal desc) 전체서열, --공동랭크 불포함 - 공동2등 다음 4등
       dense_rank() over(partition by deptno order by sal desc) 부서별서열
from EMP;

방법 2) 인라인 쿼리문 - 사실상 이 문제에는 필요없다.

select *
from
(
select ename "사원명",deptno "부서번호",sal "급여",
       rank() over(order by sal desc) "전체등수",   --공동랭크 포함 - 공동2등 다음 4등
       rank() over(partition by deptno order by sal desc) "부서별등수",
       dense_rank() over(order by sal desc) 전체서열, --공동랭크 불포함 - 공동2등 다음 4등
       dense_rank() over(partition by deptno order by sal desc) 부서별서열
from EMP
) T
where "전체등수" <=10
order by 2,3 desc;

-------------------------------------------------------------------------------------

-- Quiz )

-------------------------------------------------------------------------------------

1. member 테이블에서 여자만 출력하시오.

select *

from member

where substr(jubun,7,1) in (2,4)

select *

from member

where substr(jubun,7,1) =  '2' or substr(jubun,7,1) =  '4'

--> 위의 in 구문보다 아래의 OR 구문이 대용량DB에서 속도면에서 유리하다.

--> 2와 4에 홑따옴표(')를 붙여주지 않아도 동작은 되지만, 무결성을 위해 붙여주자

-------------------------------------------------------------------------------------

2. 아래와 같은 테이블이 있다.

create table filetab
(fileno number,
filename varchar2(200)
) tablespace users;

insert into filetab values(1,'c:\aaa\bbb\ccc\sales.xls');
insert into filetab values(2,'d:\aaa\salesinfo.doc');
insert into filetab values(3,'c:\research.xxls');
insert into filetab values(4,'d:\aaa\bbb\marketing.hwp');

1) 확장자가 xls인 파일만 출력하시오.

select *

from filetab

where filename like '%.xls';

--> 데이터중에 .xls와 .xxls가 있다. 점(.)을 꼭 넣어 구분해주자.

2) 아래와 같이 출력하시오.

--------------------------------
 fileno      filename
--------------------------------
1            sales.xls
2            salesinfo.doc
3            research.xxls
4            marketing.hwp

-->

select fileno,

         reverse(substr(reverse(filename),1,instr(reverse(filename),'\',1)-1)) filename
from filetab;

-------------------------------------------------------------------------------------

Quiz ) - 2006.10.14.16:36:00

-------------------------------------------------------------------------------------

1. 오늘 입대하면 언제 제대할까? (군 기간은 2년)
select add_months(sysdate,24) from dual;
--> 2008-10-14 16:36:34

2. 오늘 입대하면 몇끼를 먹어야 제대할까? (단, 하루3끼)
select (add_months(sysdate,24)-sysdate)*3 from dual;
--> 2193

----------------------------------------------------------------------------------

Quiz ) 아래와 같이 출력하시오.

-----------------------------

 이름  주민번호 계통 성별 나이

-----------------------------

----------------------------------------------------------------------------------

insert into member values('귀화남','7510165234567');
insert into member values('귀화녀','7611126234567');
commit;

select * from MEMBER;

방법 1) 복잡하고 잘못된 코딩

select name 이름,jubun 주민번호,

    case when substr(jubun,7,1) in ('1','2','3','4')
       then '한국계'
      else '외국계'
    end "원래국적" ,
    decode(substr(jubun,7,1),'1','남'
                                        ,'3','남'
                                        ,'5','남'
                                       ,'여') "성별" , --> 계통

    case when substr(jubun,7,1) in ('1','2') then to_char(sysdate,'yyyy') - ('19' || substr(jubun,1,2))
         when substr(jubun,7,1) in ('3','4') then to_char(sysdate,'yyyy') - ('20' || substr(jubun,1,2))
         when substr(jubun,7,1) in ('5','6') then to_char(sysdate,'yyyy') - ('19' || substr(jubun,1,2))        
           when substr(jubun,7,1) in ('7','8') then to_char(sysdate,'yyyy') - ('20' || substr(jubun,1,2)) 
        end "현재나이"       --> 나이계산
from member;

방법 2) 간단한 코딩 

select T.*,
add_months(to_char(sysdate,'yyyy')||'-02-20',12*(60-현재나이)) "정년일",
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') - 입사일 "근무일수",
add_months(to_char(sysdate,'yyyy')||'-02-20',12*(60-현재나이)) -
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') "남은일수"
from
(
select name 성명, jubun 주민번호, hiredate 입사일,
  case when substr(jubun,7,1) in ('1','2','3','4') then '한국계'
       else '외국계' end 혈통,
  case when substr(jubun,7,1) in ('1','3','5') then '남'
       else '여' end 성별,
  case when substr(jubun,7,1) in ('1','2','5','6') 
       then extract(year from sysdate) - (to_number(substr(jubun,1,2))+1899)
       else extract(year from sysdate) - (to_number(substr(jubun,1,2))+1999)
       end 현재나이     
from MEMBER
) T;

-->
귀화인 7510165234567 외국계 남 31
귀화여 7611126234567 외국계 여 30
이순신 7001031234567 한국계 남 36
김하늘 8012252234567 한국계 여 26
남자애 0005023234567 한국계 남 6
여자애 0103014234567 한국계 여 5

----------------------------------------------------------------------------------

-- 컬럼 추가
----------------------------------------------------------------------------------

alter table member
add hiredate date;

----------------------------------------------------------------------------------

Quiz ) member 테이블에서 아래와 같이 추출. 단 정년은 60세 되는해의 2월 20일이다.

--------------------------------------------------------------

성명 주민번호 입사일 계통 성별 현재나이 근무일수 정년일 남은일수

--------------------------------------------------------------

-----------------------------------------------------------------

select *
from member;

update member set hiredate=to_date('1998-01-04','yyyy-mm-dd')
where name='이순신'; 
update member set hiredate=to_date('1999-12-14','yyyy-mm-dd')
where name='김하늘'; 
update member set hiredate=to_date('2002-09-10','yyyy-mm-dd')
where name='남자애'; 
update member set hiredate=to_date('2003-03-20','yyyy-mm-dd')
where name='여자애'; 
update member set hiredate=to_date('2000-01-30','yyyy-mm-dd')
where name='귀화인'; 
update member set hiredate=to_date('2000-01-30','yyyy-mm-dd')
where name='귀화여'; 

commit;

정답 )

select T.*,

add_months(to_char(sysdate,'yyyy')||'-02-20',12*(60-현재나이)) "정년일",

to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') - 입사일 "근무일수",

add_months(to_char(sysdate,'yyyy')||'-02-20',12*(60-현재나이)) -
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') "남은일수"

from
(
select name 성명, jubun 주민번호, hiredate 입사일,

  case when substr(jubun,7,1) in ('1','2','3','4') then '한국계'
       else '외국계' end 혈통,

  case when substr(jubun,7,1) in ('1','3','5') then '남'
       else '여' end 성별,

  case when substr(jubun,7,1) in ('1','2','5','6') 
       then extract(year from sysdate) - (to_number(substr(jubun,1,2))+1899)
       else extract(year from sysdate) - (to_number(substr(jubun,1,2))+1999)
       end 현재나이     

from MEMBER
) T;

----------------------------------------------------------------------------------

Quiz ) emp테이블의 사원중 1년간 총연봉(급여+보너스)가 30000이상인 사람 추출

----------------------------------------------------------------------------------

방법1)

select ename, coalesce(sal*12+comm, comm, sal*12, 0)
from emp
where coalesce(sal*12+comm, comm, sal*12, 0) >= 30000;

방법2) 인라인쿼리

select *
from
(
select ename 사원명, coalesce(sal*12+comm,comm,sal*12,0) 연봉
from emp
) T
where T.연봉 >= 30000; 

----------------------------------------------------------------------------------

Quiz ) 급여가 아닌 연봉으로 순위 출력

----------------------------------------------------------------------------------

select *
from
(
select ename "사원명",deptno "부서번호",to_char(coalesce(sal*12+comm,comm,sal*12,0),'$999,999') "연봉",
       rank() over(order by coalesce(sal*12+comm,comm,sal*12,0) desc) "전체등수",   --공동랭크 포함 - 공동2등 다음 4등
       rank() over(partition by deptno order by coalesce(sal*12+comm,comm,sal*12,0) desc) "부서별등수",
       dense_rank() over(order by coalesce(sal*12+comm,comm,sal*12,0) desc) 전체서열, --공동랭크 불포함 - 공동2등 다음 4등
       dense_rank() over(partition by deptno order by coalesce(sal*12+comm,comm,sal*12,0) desc) 부서별서열
from EMP
) T
where "전체등수" <=10
order by 2,3 desc;       coalesce(sal*12+comm,comm,sal*12,0)

----------------------------------------------------------------------------------

 7장 연습문제

1. 현재 날짜 출력하고 컬럼명은 'Current Date'로 출력하시오

select to_char(sysdate,'yyyy-mm-dd') "Current Date"
from dual;

2. EMP 테이블에서 현재 급여에서 15%증가된 급여를 사원번호, 이름,업무,급여,

  증가된 급여(New Salary), 증가액(Increase)를 출력

select empno,ename,job,sal,round(nvl(sal+sal*0.15,0),0) "인상된 급여",round(nvl(sal*0.15,0),0) "증가액"
from emp;

3. EMP테이블에 이름,입사일,입사일로부터 6개월 후 처음 돌아오는 월요일의 날짜 출력

select ename,hiredate,next_day(add_months(hiredate,6),'monday')
from emp;

4. EMP테이블에서 이름,입사일, 입사일로부터 현재까지의 월수, 총급여, 현재급여 출력

select ename,hiredate,round(months_between(sysdate,hiredate),0) 근무개월수,
       round(months_between(sysdate,hiredate),0)*sal 총월급,
       round(months_between(sysdate,hiredate),0)*(nvl(sal,0)+nvl(comm,0)) 총급여
from emp
order by 5 desc;

5. 다음과 같이 출력하시오.

Dream Salary

--------------------------------------------------

KING earns   $5,000 monthly but wants   $15,000
SCOTT earns   $3,000 monthly but wants    $9,000
FORD earns   $3,000 monthly but wants    $9,000
JONES earns   $2,975 monthly but wants    $8,925

--------------------------------------------------

select ename || ' earns' || to_char(sal,'$999,999') || ' monthly but wants ' || to_char(sal*3,'$999,999')
from emp
order by sal desc;

6. EMP테이블에서 모든 사원의 이름과 급여를 출력.

  단, 이름은 15자리로 지정하고, 공백부분은 *로 채워라.

select ename,lpad(to_char(sal,'$9,999'),15,'*')
from emp
order by sal

7. EMP테이블에서 모든 사원의 이름,업무,입사일,입사요일 출력
select ename,job,hiredate,
       case
         when to_char(hiredate,'d') =1 then '일요일'
            when to_char(hiredate,'d')='2' then '월요일'
            when to_char(hiredate,'d')='3' then '화요일'
            when to_char(hiredate,'d')='4' then '수요일'
            when to_char(hiredate,'d')='5' then '목요일'
            when to_char(hiredate,'d')='6' then '금요일'
            when to_char(hiredate,'d')='7' then '토요일'
       end "입사요일"      
from emp;

8. EMP테이블에서 이름이 6글자 이상인 사원의 이름,이름글자수,업무 출력
select ename,length(ename) 이름길이,job
from emp
where length(ename) >= 6
order by ename desc;

9. EMP테이블에서 모든 사원의 정보를 이름,업무,급여,보너스,급여+보너스 출력
select ename,job,sal,nvl(comm,0) 보너스,nvl2(sal+comm,comm,0) "급여+보너스"
from emp
order by 5 desc;