limit, rownum, top

limit, rownum, top 출처 : 까오기네

가정 : id(아이디),password(비밀번호),idx(고유번호) 세개의 field를 갖는 test라는 테이블이 있고 이 안에는 72개의 데이터가 insert 되어 있다고 가정한다.

limit

mysql에서 지원하는 limit은 두개의 인자를 갖는다.

select * from test order by idx desc limit 5,10

위에서 보면 앞에 인자는 위치정보이면 뒤에 인자는 select에 의해 불러오는 데이터의 수량이다. 따라서 위의 쿼리문을 해석하면 test 테이블을 idx로 내림차순 정렬을 한 것을 6번째부터 15번째까지 10개의 데이터만을 불러오라는 내용이다.

다음의 쿼리문을 보자

select * from test order by idx desc limit 10

위의 내용은 다음과 같은 결과값을 갖는다.

select * from test order by idx desc limit 0, 10

즉 앞의 인자가 0일때는 생략이 가능하다는 것이다.

rownum

oracle에서 전체 테이블에서 특정 부분만을 가져올때는 rownum을 사용할 수 있다. rownum은 가져오는 데이터의 수량으로 인식하면 된다.

select * from test where rownum<=5 order by idx desc

위의 쿼리문에 대한 해석은 test 테이블을 idx로 내림차순 정렬하여 첫번째부터 5번째까지 5개의 데이터만 불러오라는 내용이다.
여기서는 rownum이 단순히 수량만을 나타내는 것처럼 보이지만 실제는 위치정보도 포함할 수 있다.

다음을 보도록 하자.

select id,password, rownum from test where rownum<=5 order by idx desc

id	password	idx
aaaa	0000	1
bbbb	1111	2
cccc	2222	3
dddd	3333	4
eeee	4444	5

위에서 처럼 rownum은 데이터 필드안에 삽입이 되서 보여 줄 수 있으며 이것을 통해 정렬도 가능하다.

select id, password, rownum as rnum from test where rownum<=5 
order by rnum desc
select * from (select id, password, rownum as rnum from test where rownum<=5) 
order by rnum desc
		

id	password	idx
eeee	4444	5
dddd	3333	4
cccc	2222	3
bbbb	1111	2
aaaa	0000	1

위의 두개의 쿼리문은 동일한 결과값을 갖는다. 하지만 둘의 차이점은 첫번째 쿼리문은 order by를 rnum에게 할당함으로써 정렬하는 방법을 빼앗겼지만 두번째 쿼리문은 서브쿼리내에 정렬을 할 수가 있어 원하는 데이터를 가져오는데 훨씬 수월하다. 그렇다면 첫번째 방법으로는 완전히 불가능 한걸까?
아니다.
오라클에서 지원하는 힌트를 사용하면 가능하다.

먼저 인덱스를 생성한다.

create index test_indx on test(idx);

그리고 힌트를 사용하여 쿼리문을 만든다.

select /*+ index_desc(test test_indx) */ id, password, rownum as rnum from test
where rownum<=5 order by rnum desc 
		

이렇게 하면 order by 부분은 hint에서 처리할 수 있다.
결론적으로 oracle의 rownum은 불러오는 데이터의 수량을 결정할 수 있으며 데이터의 안에서 글번호와 같은 위치에 대한 정보를 담아서 처리하는 기능을 지원해준다는 것이다.

top

mssql에서 지원하는 top은 위의 두개와 비교했을 때 정말 원초적인 기능이다. 단순히 불러오는 데이터의 수량만을 결정하는 것이기 때문이다.
top은 위치정보로 사용할 수도 없으며 이를 보완하고 도와줄 다른 어떤 기능도 없다. 계층형 게시판에서 원하는 데이터를 축출한다는 것은 아마도 가장 험난한 길을 인도하는 기능이다.

select top 5 * from test order by idx desc

top을 통해 원하는 부분만 가져올려면 여러번의 subquery를 작성해서 가져 와야 한다.

인덱스의 활용 출처 : 까오기네

만약 우리가 집을 짓는데 제일 먼저 해야 할 일이 뭘까? 그것은 아마도 설계일 것이다. 기초가 되는 기본 설계를 잘 해야만이 그 후 작업이 원활하고 최적화가 되는 것이다.
프로그래머는 java, servlet, jsp뿐 아니라 최소한의 데이터베이스에 대해서도 알아야 한다. 게시판, 카운터, 일정관리 등을 만드는데 DB 설계를 다른 사람이 해줄 수도 있겠지만 대부분이 개발자가 설계하고 만들기 때문이다. 여기서는 DB에 대해 심도 깊은 공부를 할 수는 없지만 최소한의 지식에 대해 배우기로 한다.

인덱스란 무엇인가?

SQL서버에서 테이블을 만들고 데이터를 추가, 수정, 삭제 할 때 데이터의 레코드는 내부적으로 아무런 순서 없이 저장된다. 이때 데이터 저장영역을 Heap이라고 한다. Heap에서는 인덱스가 없는 테이블의 데이터를 찾을 때 무조건 전체 데이터 페이지의 처음 레코드부터 끝 페이지의 마지막 레코드까지 다 읽어서 검색조건과 비교하게 된다. 이런 식의 데이터 검색방법을 테이블 스캔(table scan) 또는 풀 스캔(full scan)이라고 한다. 이럴 경우 양이 많은 테이블에서 일부분의 데이터만 불러 올 때 풀 스캔을 하면 처리 성능이 떨어진다. 즉 인덱스는 데이터를 select 할 때 빨리 찾기 위해 사용된다.

인덱스 생성 시 고려할 점

인덱스를 생성 시에는 where 절과 join, order by 등과 관련된 칼럼중 사용 빈도가 높고 키 값의 선별도가 좋은 칼럼에 사용해야 한다. 반대로 사용 빈도가 낮고 칼럼의 선별도가 나쁜, 예를 들어 한 칼럼의 값이 true/false, 성별(M/F) 등에는 인덱스를 사용하지 않는 것이 좋다 또 테이블이 작거나 자주 갱신 될 때도 사용하지 않는 것이 좋다.

인덱스의 생성

인덱스에는 크게 clustered와 nonclustered 인덱스로 나눌 수 있다. clustered 인덱스는 물리적 정렬로 DB에 데이터를 입력 시 이것을 기준으로 입력이 된다. 따라서 한 테이블에 오직 하나만 존재 할 수 있으며 table을 열었을 때 order by를 사용하지 않아도 데이터가 clustered 인덱스에 따라 정렬이 되어 있는 것을 확인 할 수 있다. 물리적으로 정렬이 되어 있는 만큼 가장 빠른 처리를 한다. nonclustered 인덱스는 clustered 인덱스와는 달리 중복된 값을 가지면 한 테이블에 여러 개를 생성 할 수 있다. 이 외에 unique에 대해 알아보겠다.
unique는 말 그대로 중복을 허용하지 않는 값을 보호 할 때 사용한다. 예를 들어 회원 관리 프로그램에서 아이디가 중복되는 것을 막고자 한다면 이 옵션을 사용하면 된다. 우리가 자주 사용하는 primary key의 경우에는 clustered와 unique 특성을 갖게 하는 제약키이다.