대용량 페이징 속도가 안나옵니다. 정렬하니 한세월이네요.

갑자기 대용량 데이터를 처리할 상황이 되었습니다.

한 테이블에 3천만건 가량의 데이터가 들어가 있고, 한그룹의 데이터가 2700만건 정도가 됩니다.

데이터는 더 늘어나겠지요.

통계데이터는 배치작업으로 별도 계산한 값을 조회하도록 처리했습니다.

하지만, 원자료를 조회하는 화면에서는 정렬 처리 또는 페이징 처리 과정에서 분단위로 속도가 나오더군요.

 

쉽게 갈 수 없을거라 생각했다.. 아무생각 없이 무지성으로 조회하다가 앗뜨거, 놀라서 검색을 했습니다.

다행히 아래 예제에서 보여주는 내용들이 많은 도움이 되었습니다.

DBMS의 실행계획에 대해서도 공부를 좀 해봐야 할 것 같습니다. 머리에 다 들어가진 않더라도 이렇게 찾으려면 키워드는 머리 속에 좀 넣어둬야 도움이 될 것 같습니다.

 

 

 

 

 

참고 : https://m.blog.naver.com/cchhooo21/220128744819

오라클 페이징 처리

 

오라클은 MySql 에서 지원하는 order by LIMIT 를 지원하지 않아 페이징 처리 속도가 느리다.

알려진 오라클 페이징 기법을 테스트 하여 속도를 비교해 보자

SAMPLE 테이블에 100만건의 데이트를 입력 후 10개씩 보여지는 마지막 페이지를 조회
정렬 조건은 PRIMARY KEY DESC 정렬

1. 페이지 수를 계산하여 셀렉트 

SELECT B.* FROM (
SELECT 

CEIL(ROWNUM/10) PAGE
--, COUNT(*) OVER() AS TOTAL_COUNT
, A.* FROM (

SELECT * FROM OP_SAMPLE 
ORDER BY ID DESC
) A
) B
WHERE PAGE = 100000;

100만 건인 경우 수행시간 : 1.327초, TOTAL_COUNT 까지 조회시 : 3.848초
10만 건인 경우 수행시간 : 0.135초 TOTAL_COUNT 까지 조회시 : 0.255초

2. ROW_NUMBER()를 이용한 페이징

SELECT * FROM (
SELECT A.*, ROW_NUMBER() OVER(ORDER BY ID DESC) AS NUM 
FROM OP_SAMPLE A
)
WHERE NUM BETWEEN 999991 AND 1000000;

100만 건인 경우 수행시간 : 1.136초
10만 건인 경우 수행시간 : 0.253초

3. ROWNUM을 이용한 페이징 

SELECT * FROM ( 
SELECT ROWNUM AS RNUM, Z.* FROM ( 
SELECT * FROM OP_SAMPLE ORDER BY ID DESC
) Z WHERE ROWNUM <= 1000000 
) WHERE RNUM >= 999991;

100만 건인 경우 수행시간 : 0.826초
10만 건인 경우 수행시간 : 0.087초

4. ROWNUM + 인덱스를 이용한 페이징 

SELECT * FROM ( 
SELECT /*+ INDEX_DESC(Z OP_SAMPLE_PK) */ ROWNUM AS RNUM, Z.* FROM ( 
SELECT * FROM OP_SAMPLE
) Z WHERE ROWNUM <= 1000000 
) WHERE RNUM >= 999991;

100만 건인 경우 수행시간 : 0.687초
10만 건인 경우 수행시간 : 0.079초

참고로 MySql 쿼리 조회시 (mysql 5, innoDB) 

 

SELECT * FROM OP_SAMPLE 
ORDER BY ID DESC LIMIT 999991, 10;

100만 건인 경우 수행시간 : 0.033초

 

 

 

오라클 11g 이전의 버전에서는 Top-N 쿼리를 수행하기 위해 인라인 뷰를 사용하여 where 조건절에 rownum을 사용하여 걸러내는 방식으로 쿼리를 작성해왔습니다.

?

SELECT *   FROM (SELECT employee_id, last_name           FROM hr.employees          ORDER BY employee_id)  WHERE rownum <= 5;   EMPLOYEE_ID LAST_NAME ----------- -------------------------         100 King         101 Kochhar         102 De Haan         103 Hunold         104 Ernst

Execution Plan ---------------------------------------------------------- ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 135 | 2 (0)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 5 | 135 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 107 | 1284 | 2 (0)| 00:00:01 | | 4 | INDEX FULL SCAN | EMP_EMP_ID_PK | 5 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=5)

타 DBMS를 사용해왔던 개발자들에게 오라클의 인라인 뷰를 사용한 Top-N 쿼리문은 몹시나 거추장스러운 것으로 불만의 대상이었죠.

(타 DBMS에서의 Top-N 쿼리 처리방식은 오라클 성능 고도화 2권에서도 나오고 있습니다.)

그런데 오라클 12c에서는 이런 개발자들의 불만을 해결할만한 새로운 방식의 쿼리 작성법이 추가되었습니다.

바로 로우 제한 절(row limiting clause)의 추가입니다. 먼저 상위 5개의 결과를 추출하는 쿼리는 아래와 같습니다.

?

SELECT employee_id, last_name   FROM hr.employees  ORDER BY employee_id  FETCH FIRST 5 ROWS ONLY;   EMPLOYEE_ID LAST_NAME ----------- -------------------------         100 King         101 Kochhar         102 De Haan         103 Hunold         104 Ernst

Execution Plan ---------------------------------------------------------- --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 107 | 5671 | 2 (0)| 00:00:01 | |* 1 | VIEW | | 107 | 5671 | 2 (0)| 00:00:01 | |* 2 | WINDOW SORT PUSHED RANK| | 107 | 1284 | 2 (0)| 00:00:01 | | 3 | VIEW | index$_join$_001 | 107 | 1284 | 2 (0)| 00:00:01 | |* 4 | HASH JOIN | | | | | | | 5 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK | 107 | 1284 | 1 (0)| 00:00:01 | | 6 | INDEX FAST FULL SCAN| EMP_NAME_IX | 107 | 1284 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=5) 2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMPLOYEE_ID")<=5) 4 - access(ROWID=ROWID)

ORDER BY 절을 사용하여 정렬을 한 상태에서 FETCH FIRST 5 ROWS ONLY 절로 상위 5개의 결과값만 추출하도록 제한을 걸어두었지요.

만약 1~5위의 결과는 생략하고 6~10위의 결과만 보기 원한다면 어떻게 하면 될까요?

?

SELECT employee_id, last_name   FROM hr.employees  ORDER BY employee_id OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;   EMPLOYEE_ID LAST_NAME ----------- -------------------------         105 Austin         106 Pataballa         107 Lorentz         108 Greenberg         109 Faviet

Execution Plan ---------------------------------------------------------- --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 107 | 5671 | 2 (0)| 00:00:01 | |* 1 | VIEW | | 107 | 5671 | 2 (0)| 00:00:01 | |* 2 | WINDOW SORT PUSHED RANK| | 107 | 1284 | 2 (0)| 00:00:01 | | 3 | VIEW | index$_join$_001 | 107 | 1284 | 2 (0)| 00:00:01 | |* 4 | HASH JOIN | | | | | | | 5 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK | 107 | 1284 | 1 (0)| 00:00:01 | | 6 | INDEX FAST FULL SCAN| EMP_NAME_IX | 107 | 1284 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=CASE WHEN (5>=0) THEN 5 ELSE 0 END +5 AND "from$_subquery$_003"."rowlimit_$$_rownumber">5) 2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMPLOYEE_ID")<=CASE WHEN (5>=0) THEN 5 ELSE 0 END +5) 4 - access(ROWID=ROWID)

바로 OFFSET 5 ROWS를 추가하여 5개의 결과값을 건너뛰고서 그 이후의 결과값을 추출하라고 DB에게 요구할 수 있습니다.

당연한 내용이겠지만 OFFSET에 음수 값을 줄 경우, 오라클은 이를 0으로 간주하여 맨 처음값부터 출력을 하게되고, 마찬가지로 NULL 값을 입력하거나 row의 개수보다 더 큰 값을 주었을 때에도 0으로 간주합니다. (참 똑똑하지요?)

뿐만 아니라 백분률을 토대로 Top-N 쿼리를 수행해내기도 하는데요.

?

SELECT employee_id, last_name, salary   FROM hr.employees  ORDER BY salary  FETCH FIRST 5 PERCENT ROWS ONLY;   EMPLOYEE_ID LAST_NAME                     SALARY ----------- ------------------------- ----------         132 Olson                           2100         136 Philtanker                      2200         128 Markle                          2200         127 Landry                          2400         135 Gee                             2400         191 Perkins                         2500   6 rows selected.

Execution Plan ---------------------------------------------------------- --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 107 | 8453 | 3 (0)| 00:00:01 | |* 1 | VIEW | | 107 | 8453 | 3 (0)| 00:00:01 | | 2 | WINDOW SORT | | 107 | 1712 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1712 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=CEIL("from$_ subquery$_003"."rowlimit_$$_total"*5/100))

위의 예에서 보시다시피 salary로 정렬된 값에서 상위 5% 이내의 결과만 출력하도록 PERCENT 키워드를 넣어서 쿼리문을 작성할 수도 있습니다.

그런데 위의 쿼리를 보면 뭔가 석연찮은 점을 발견할 수 있습니다. 바로 중복값의 처리입니다.

원래 전체 결과값은 아래와 같습니다. (결과 값이 너무 길어서 접어두었으니, 열어보세요.)

 Expand source

expand source?

SELECT employee_id, last_name, salary   FROM hr.employees  ORDER BY salary;   EMPLOYEE_ID LAST_NAME                     SALARY ----------- ------------------------- ----------         132 Olson                           2100         136 Philtanker                      2200         128 Markle                          2200         127 Landry                          2400         135 Gee                             2400         191 Perkins                         2500         119 Colmenares                      2500         140 Patel                           2500         144 Vargas                          2500         182 Sullivan                        2500         131 Marlow                          2500         198 OConnell                        2600         199 Grant                           2600         118 Himuro                          2600         143 Matos                           2600         126 Mikkilineni                     2700         139 Seo                             2700         117 Tobias                          2800         183 Geoni                           2800         130 Atkinson                        2800         195 Jones                           2800         116 Baida                           2900         134 Rogers                          2900         190 Gates                           2900         197 Feeney                          3000         187 Cabrio                          3000         181 Fleaur                          3100         196 Walsh                           3100         115 Khoo                            3100         142 Davies                          3100         194 McCain                          3200         125 Nayer                           3200         138 Stiles                          3200         180 Taylor                          3200         133 Mallin                          3300         129 Bissot                          3300         186 Dellinger                       3400         141 Rajs                            3500         189 Dilly                           3600         137 Ladwig                          3600         188 Chung                           3800         193 Everett                         3900         192 Bell                            4000         185 Bull                            4100         107 Lorentz                         4200         184 Sarchand                        4200         200 Whalen                          4400         105 Austin                          4800         106 Pataballa                       4800         124 Mourgos                         5800         202 Fay                             6000         104 Ernst                           6000         173 Kumar                           6100         167 Banda                           6200         179 Johnson                         6200         166 Ande                            6400         123 Vollman                         6500         203 Mavris                          6500         165 Lee                             6800         113 Popp                            6900         155 Tuvault                         7000         178 Grant                           7000         161 Sewall                          7000         164 Marvins                         7200         172 Bates                           7300         171 Smith                           7400         154 Cambrault                       7500         160 Doran                           7500         111 Sciarra                         7700         112 Urman                           7800         122 Kaufling                        7900         159 Smith                           8000         153 Olsen                           8000         120 Weiss                           8000         121 Fripp                           8200         110 Chen                            8200         206 Gietz                           8300         177 Livingston                      8400         176 Taylor                          8600         175 Hutton                          8800         109 Faviet                          9000         103 Hunold                          9000         152 Hall                            9000         158 McEwen                          9000         157 Sully                           9500         151 Bernstein                       9500         163 Greene                          9500         170 Fox                             9600         204 Baer                           10000         169 Bloom                          10000         156 King                           10000         150 Tucker                         10000         162 Vishney                        10500         149 Zlotkey                        10500         148 Cambrault                      11000         174 Abel                           11000         114 Raphaely                       11000         168 Ozer                           11500         147 Errazuriz                      12000         108 Greenberg                      12008         205 Higgins                        12008         201 Hartstein                      13000         146 Partners                       13500         145 Russell                        14000         102 De Haan                        17000         101 Kochhar                        17000         100 King                           24000   107 rows selected.

보다시피 Perkins와 같이 salary가 2500인 사람이 더 있는데, 단지 정렬 순위에서 맨 위에 있다는 이유로 나머지 사람들은 잘려버리고 Perkins만 상위 5%로 살아남은 것이지요. (저 사람, 운이 좋은건가요?)

나머지 salary가 2500인 사람들도 억울하지 않게 쿼리를 수정해야겠지요? 아래의 쿼리문처럼 WITH TIES 옵션을 주어서 말입니다.

?

SELECT employee_id, last_name, salary   FROM hr.employees  ORDER BY salary  FETCH FIRST 5 PERCENT ROWS WITH TIES;   EMPLOYEE_ID LAST_NAME                     SALARY ----------- ------------------------- ----------         132 Olson                           2100         136 Philtanker                      2200         128 Markle                          2200         127 Landry                          2400         135 Gee                             2400         191 Perkins                         2500         119 Colmenares                      2500         140 Patel                           2500         144 Vargas                          2500         182 Sullivan                        2500         131 Marlow                          2500   11 rows selected.

Execution Plan ---------------------------------------------------------- --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 107 | 8453 | 3 (0)| 00:00:01 | |* 1 | VIEW | | 107 | 8453 | 3 (0)| 00:00:01 | | 2 | WINDOW SORT | | 107 | 1712 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1712 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("from$_subquery$_003"."rowlimit_$$_rank"<=CEIL("from$_subqu ery$_003"."rowlimit_$$_total"*5/100))

이렇게 편리한 구문의 추가로 인해 12c부터는 인라인 뷰를 만들기 위한 괄호와의 숨바꼭질은 어느 정도 사라질 것 같군요!

결론 : 오라클 12c의 SQL은 SQL:2008을 따라서 Top-N 쿼리문이 DB2, PostgreSQL, Sybase의 쿼리문과 같아졌습니다!