[오라클/ORACLE] 데이터 잘라서 보는 ROWNUM

ROWNUM

ROWNUM은 오라클 내부적으로 생성되는 가상 컬럼이다. 자료의 일부를 잘라내서 확인하는 용도로 자주 활용된다. SQL 조회 결과의 순번을 기준으로 N번째까지 가져오는 식이다. ROWNUM 함수는 파라미터를 갖지 않는다.

 

우선 예제를 진행하기 위해서 테이블을 만들고 적당히 값을 넣어주도록 한다.

-- customers 테이블 생성
CREATE TABLE customers
( customer_id number(10) NOT NULL,
  last_name varchar2(50) NOT NULL,
  first_name varchar2(50) NOT NULL,
  favorite_website varchar2(50),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id));

-- 테이블에 값 대입
INSERT INTO customers
(customer_id, last_name, first_name, favorite_website)
VALUES
(4000,'Jackson','Joe','www.techonthenet.com');

INSERT INTO customers
(customer_id, last_name, first_name, favorite_website)
VALUES
(5000,'Smith','Jane','www.digminecraft.com');

INSERT INTO customers
(customer_id, last_name, first_name, favorite_website)
VALUES
(6000,'Ferguson','Samantha','www.bigactivities.com');

INSERT INTO customers
(customer_id, last_name, first_name, favorite_website)
VALUES
(7000,'Reynolds','Allen','www.checkyourmath.com');

INSERT INTO customers
(customer_id, last_name, first_name, favorite_website)
VALUES
(8000,'Anderson','Paige',NULL);

INSERT INTO customers
(customer_id, last_name, first_name, favorite_website)
VALUES
(9000,'Johnson','Derek','www.techonthenet.com');

고객 정보가 있는 테이블이 만들어졌다.

 

이제 ROWNUM이 작동하는 원리를 알기 위해 아래와 같이 출력해본다.

SELECT ROWNUM, customers.*
FROM customers
WHERE customer_id > 4500;

 

ROWNUM가 레코드의 정렬에 따라 1부터 순서대로 번호를 매겼다. 이렇게만 보면 사용법이 아주 간단해 보인다. 아쉽게도 그렇지는 않다.

 

이번엔 last_name 컬럼을 기준으로 오름차순 정렬해서 다시 확인해 본다.

SELECT ROWNUM, customers.*
FROM customers
WHERE customer_id > 4500
ORDER BY last_name;

이전과 달리 넘버링 순서가 뒤죽박죽이 된 걸 확인할 수 있다. 이는 오라클의 접근이 테이블에 데이터를 삽입한 순서나 테이블에 인덱스가 있는지 등 여러 변수에 따라 달라지기 때문이다. 이렇게 뒤죽박죽이 되면 원활하게 ROWNUM을 사용할 수 없다.

 

이런 이유로 ROWNUM는 서브쿼리를 통해 결과를 먼저 정렬한 이후 사용할 필요가 있다. 예를 들면 이런 식이다.

SELECT ROWNUM, a.*
FROM (SELECT customers.*
      FROM customers
      WHERE customer_id > 4500
      ORDER BY last_name) a;

위 코드와 동일한 방식으로 셀렉을 한 후 이를 ()안에 넣어 서브쿼리로 사용했다. 여기에 별칭 a를 주고 하나의 테이블처럼 사용했다.

ROWNUM이 번호를 순서대로 인식한다.

활용

이제 ROWNUM을 사용할 준비가 됐으니 활용해보자. 위에서 언급했듯 ROWNUM은 원하는 부분의 데이터를 잘라서 확인할 수 있다. 위 테이블에서 상위 2개의 결과를 뽑는다.

SELECT *
FROM (SELECT customers.*
      FROM customers
      WHERE customer_id > 4500
      ORDER BY last_name DESC)
WHERE ROWNUM < 3; 

이처럼 WHERE 절을 이용해서 ROWNUM의 범위를 정해주면 된다.

만약 3~5번처럼 가운데 있는 값을 꺼내고 싶다면 어떨까. 서브쿼리를 2 개 사용하는 방법이 있다. 다소 복잡해진다.

select * from
(select rownum r,a.*
from(select * from customers 
    WHERE customer_id > 4500
      ORDER BY last_name DESC) a
      where rownum < 6)
      where r > 2;

안에 있는 select 절에서 위와 같은 조건으로 1-5까지의 rownum을 구한 뒤 이를 덮는 select 문을 통해 3부터 시작하는 rownum을 구한다. 참고로 from절에 들어간 셀렉트를 '인라인 뷰'라 부른다. 최종적으로 3-5번째 값만 꺼내올 수 있다.

 

좀더 간략한 코드로도 풀 수 있다.

select * from
(select rownum r,a.*
from(select * from customers 
    WHERE customer_id > 4500
      ORDER BY last_name DESC) a)
      where r between 3 and 5;

---

예제 출처: techonthenet