ORDER BY의 문제
- 데이터베이스를 이용할 때 웹이나 애플리케이션에 가장 신경 쓰는 부분은
1) 빠르게 처리되는 것
2) 필요한 양만큼만 데이터를 가져오는 것
이다. 예를 들어, 거의 모든 웹페이지에서 페이징을 하는 이유는 최소한의 필요한 데이터만을 가져와서 빠르게 화면에 보여주기 위함이다.
만일 수백 만개의 데이터를 매번 정렬을 해야하는 상황에서 사용자는 정렬된 결과를 볼 때까지 오랜 시간을 기다려야만 하고, 특히 웹에서 동시에 여러 명의 사용자가 정렬이 필요한 데이터를 요청하게 된다면 시스템에는 많은 부하가 걸리게 되고 연결 가능한 Connection의 갯수가 점점 줄어서 서비스가 멈추는 상황을 초래하게 될 것이다.
빠르게 동작하는 SQL을 위해서는 먼저 ORDER BY를 이용하는 작업을 가능하면 하지 말아야하는데, 그 이유는 데이터가 많은 경우에 엄청난 성능의 저하를 가져오기 때문에 데이터가 적은 경우와 정렬을 빠르게 할 수 있는 방법이 있는 경우가 아니라면 주의해야 한다.
실행 계획(execution plan)과 ORDER BY
- 실행 계획은 말 그대로 'SQL을 데이터베이스에서 어떻게 처리할 것인가?'에 대한 계획이다.
1) 파싱 단계 : SQL 구문에 오류가 있는지 SQL을 실행해야 하는 대상 객체(데이블, 제약 조건, 권한 등)가 존재하는지 검사를 한다.
2) 최적화 단계 : SQL이 실행되는데 필요한 비용(cost)을 계산하게 된다. 이 계산된 값을 기초로 해서 어떤 방식으로 실행하는 것이 가장 좋다는 판다하는 '실행 계획(execution paln)'을 세우게 된다.
3) 실행 단계 : 세워진 실행 계획을 통해서 메모리상에서 데이터를 읽거나 물리적인 공간에서 데이터를 로딩하는 등의 작업을 하게 된다. 개발자들은 도구를 이용하거나 SQL Plus 등을 이용해서 특정한 SQL에 대한 실행 계획을 확인할 수 있다.
* 실행 계획 확인하는 방법 : '안쪽에서 바깥쪽으로! 위에서 아래로!'
- [OPTION] - 'FULL'의 의미는 테이블 내의 모든 데이터를 스캔(scan)을 하겠다라는 계획. (테이블을 'FULL'로 접근하고 정렬)
- 실행 계획을 세우는 것은 데이터베이스에서 하는 역할이기 때문에 데이터의 양이나 제약 조건 등의 여러 상황에 따라서 데이터베이스는 실행 계획을 다르게 작성한다.
- 연산 작업을 추가하고 SQL문 처리시간 : 1.658초
- 'FULL'로 스캔했고, 바깥쪽으로 가면서 'SORT'가 일어난 것을 확인할 수 있다.
- 가장 많은 시간을 소모하는 작업은 정렬하는 작업이다.
- 연산 작업을 제거하고 SQL문 처리시간 : 0.005초
- 연산(+1) 작업을 추가한 SQL이 tb1_board 테이블을 Full Scan했지만,
연산없이 bno(PRIMARY KEY)로만 역순 정렬한 SQL의 실행계획은 SORT 과정이 존재하지 않는다.
INDEX
- INDEX는 말 그대로 가장 흔히 접하는 책 맨 뒤쪽에 정리되어있는 '색인'이다. 색인을 이용하면 독자들은 책 전체를 살펴볼 필요 없이 색인을 통해서 자신이 원하는 내용이 책의 어디에 위치해 있는지 빠르게 찾아낼 수 있다.
- 색인은 사람들이 쉽게 찾아볼 수 있게 알파벳 순서 혹은 한글 순서로 정렬한다. 이를 통해서 원하는 내용을 위에서 혹은 반대로 찾아나가는데 이를 '스캔(scan)한다'고 표현한다.
- 데이터베이스를 만들 때 PK를 지정하는 이유는 '식별'이라는 의미가 있지만, 구조상으로는 'INDEX'라는 존재(객체)가 생성되어지는 것을 의미한다.
INDEX를 이용하는 정렬
- INDEX에서 가장 중요한 개념 중 하나는 '정렬이 되어 있다는 점'이다. 정렬이 이미 되어있는 상태이므로 데이터를 찾아내서 이들을 SORT하는 과정을 생략할 수 있다.
- 데이터의 양이 수천, 수만개 정도의 정렬은 그다지 부하가 걸리지 않지만 그 이상의 데이터를 처리해야 하는 상황이라면 정렬을 안할 수 있는 방법에 대해서 고민해야 한다.
1) SORT를 하지 않았음
2) tb1_board를 바로 접근하는 것이 아니라 pk_board를 이용해서 접근
INDEX와 Oracle hint
- hint : 데이터베이스에 '지금 내가 전달한 select문을 이렇게 실행해주면 좋겠어'라고 추가적인 요구를 전달하는 것
- hint는 구문에서 에러가 나도 SQL 실행에 지장을 주지 앟는다. 따라서 hint를 이용한 select문을 작성한 후에는 실행 계획을 통해서 SQL이 실행되는지 확인해야 한다.
- 두 번째 SQL문은 ORDER BY 조건이 없어도 hint(pk_board INDEX를 역순으로 이용해줘~)를 부여했기 때문에 첫 번째와 같은 결과가 출력된다.
hint 사용 문법
- hint 구문은 /*+ 로 시작하고 */ 로 마무리 된다.
- hint 자체는 SQL로 처리 되지 않기 때문에 칼럼명이 나오더라도 별도의 ','로 처리되지 않는다.
∙ FULL hint
- hint 중에는 해당 select문을 실행할 때 테이블 전체를 scan할 것으로 명시하는 FULL hint는 테이블의 모든 데이터를 스캔하기 때문에 데이터가 많을 때는 상당히 느리게 실행된다.
∙ INDEX_ASC, INDEX_DESC hint
- 인덱스를 순서대로 이용할 것인지, 역순으로 이용할 것인지를 지정한다.
- INDEX_ASC, INDEX_DESC hint는 주로 'ORDER BY'를 위해서 사용한다.
- INDEX 자체가 정렬을 해 둔 상태이므로 이를 통해서 'SORT'과정을 생략하기 위한 용도이다.
'DB > SQL' 카테고리의 다른 글
| [SQL] 페이징을 위한 ROWNUM과 인라인뷰(@Oracle) (0) | 2020.02.18 |
|---|
