use-the-index-luke 저자 Markus Winand
---------------------------------------------
Markus Winand는 SQL에 대한 통찰력을 제공하고 다양한 시스템이 SQL을 지원하는 방법을 modern-sql.com 에서 보여줍니다. 이전에 그는 use-the-index-luke.com 을 만들었는데, 지금도 활발하게 유지되고 있습니다. Markus는 winand.at 를 통해 강사, 연사 및 컨설턴트로 고용될 수 있습니다.
---------------------------------------------
You can upload a Korean translation of use-the-index-luke.com on your blog
Thank you from the bottom of my heart to author Makus Winand for allowing me.
These are translations that I use for studying by using a papago (google translate)
The translations may not be correct or there may be a typo.
I'd appreciate it if you could point it out in the comments.
---------------------------------------------
---------------------------------------------
use-the-index-luke.com 의 한글번역본을 블로그에 업로드 해도 된다고
허락해주신 Makus Winand 저자님께 진심으로 감사합니다.
이 번역본들은 제가 공부용도로 번역기(papago, google transrate)를 돌려서
번역한 내용들이라 맞지 않거나, 오타가 있을수 있습니다.
댓글로 지적해주시면 감사하겠습니다.
---------------------------------------------
Paging Through Results
ㄴhttps://use-the-index-luke.com/sql/partial-results/fetch-next-page
첫 페이지를 효율적으로 검색하기 위해 파이프라인 top-N 쿼리를 구현한 후 다음 페이지를 가져오려면 다른 쿼리가 필요한 경우가 많습니다. 그 결과 이전 페이지의 행을 건너뛸 수 있습니다. 이 문제를 해결하는 데는 두 가지 다른 방법이 있습니다. 첫번째 오프셋 방법은 처음부터 행 번호를 지정하고 이 행 번호의 필터를 사용하여 요청한 페이지 앞에 있는 행을 삭제하는 방법입니다. 두 번째 방법을 검색 방법이라고 하는데, 이전 페이지의 마지막 항목을 검색하고 다음 행만 가져옵니다.
다음 예제에서는 더 널리 사용되는 오프셋 방법을 보여줍니다. 특이 전용 키워드(오프셋)가 있는 데이터베이스에서는 매우 쉽게 처리할 수 있다는 것이 주요 장점입니다.
이 키워드는 fetch first 확장의 일부로 SQL 표준에도 포함되었습니다.
----------------------------------------------------------
Oracle
Oracle 데이터베이스는 릴리스 12c 이후 오프셋을 지원합니다. 이전 릴리스에서는 결과 집합의 행 번호를 자동으로 매기는 유사 열 ROWNUM을 제공합니다. 그러나 이 유사열에는 (>=)보다 크거나 같은 필터를 적용할 수 없습니다.
이 작업을 수행하려면 먼저 열 이름을 별칭으로 변경하여 행 번호를 "구체화"해야 합니다.
SELECT *
FROM ( SELECT tmp.*, rownum rn
FROM ( SELECT *
FROM sales
ORDER BY sale_date DESC
) tmp
WHERE rownum <= 20
)
WHERE rn > 10
하한에는 별칭 RN을 사용하고 상한에는 ROWNUM 유사 열 자체를 사용합니다(Tom Kyte 덕분에).
----------------------------------------------------------
단순성 외에도 이 방법의 또 다른 장점은 임의 페이지를 가져오기 위해 row offset만 있으면 된다는 것입니다. 그러나 데이터베이스는 처음부터 요청된 페이지에 도달할 때까지 모든 행을 계산해야 합니다.
그림 7.2는 더 많은 페이지를 가져올때 스캔한 인덱스 범위가 더 커진다는 것을 보여줍니다.
Figure 7.2 Access Using the Offset Method
----------------------------------------------------------
----------------------------------------------------------
이것은 두 가지 단점을 가지고 있습니다.
(1) 번호 매기기가 항상 처음부터 수행되기 때문에 신규 판매를 삽입할 때 페이지가 이동합니다. (2) 더 뒤로 탐색할 때 응답시간이 증가합니다.
검색 방법은 이전 페이지의 값을 구분 기호로 사용하므로 두 가지 문제를 모두 방지합니다. 즉, 이전 페이지의 마지막 항목뒤에 와야 하는 값을 검색합니다. 이는 단순한 where 절로 표현할 수 있습니다. 반대로 말하면, 검색 방법은 단순히 이미 표시된 값을 선택하지 않습니다.
다음 예에서는 검색방법을 보여 줍니다.
시연을 위해, 우리는 하루에 한 번만 판매한다는 가정하에 시작할 것입니다. 이렇게 하면 SALE_DATE가 고유한 키가 됩니다. 특정 날짜 보다 뒤에 와야 하는 판매를 선택하려면 내립차순 정렬 순서로 인해 보다 작은 조건(<)을 사용해야 합니다. 오름차순의 경우 보다 큰 (>) 조건을 사용해야 합니다. fetch first 절은 결과를 10개 행으로 제한하는 데 사용됩니다.
----------------------------------------------------------
SELECT *
FROM sales
WHERE sale_date < ?
ORDER BY sale_date DESC
FETCH FIRST 10 ROWS ONLY
----------------------------------------------------------
행 번호 대신 이전 페이지의 마지막 값을 사용하여 하한을 지정합니다. 데이터베이스가 Index access에 SALE_DATE < ? 조건을 사용할 수 있기 때문에 성능 측면에서 큰 이점이 있습니다. 즉, 데이터베이스가 이전 페이지의 행을 건너뛸 수 있습니다. 또한 새 행을 삽입하면 안정적인 결과를 얻을 수 있습니다.
그럼에도 불구하고 이 방법은 그림 7.2와 같이 하루에 둘 이상의 판매가 있는 경우에는 작동하지 않습니다.
첫 페이지의 마지막 날짜("어제")를 사용하면 첫 페이지에 이미 표시된 날짜뿐만 아니라 어제의 모든 결과가 건너뛰기 때문입니다.
문제는 order by 순서가 결정론적 행 순서를 설정하지 않는다는 것입니다. 그러나 이는 페이지 구분에 단순 범위 조건을 사용하기 위한 전제 조건입니다.
결정론적 order by 순서가 없으면 데이터베이스는 정의상 결정론적 행 순서를 전달하지 않습니다.
일반적으로 일관된 행 순서가 나타나는 유일한 이유는 데이터베이스가 일반적으로 동일한 방법으로 쿼리를 실행하기 때문입니다.
그럼에도 불구하고 데이터베이스는 실제로 동일한 SALE_DATE를 가진 행을 섞을 수 있으며 여전히 order by 조건을 충족할 수 있습니다.
최근 릴리스에서는 데이터베이스가 결과를 의도적으로 석기 때문이 아니라 데이터베이스가 병렬 쿼리 실행을 사용할 수 있기 때문에 쿼리를 실행할 때마다 결과가 다른 순서로 나타날 수 있습니다.
즉, 실행 스레드가 비결정론적 순서로 끝나기 때문에 동일한 실행 계획이 다른 행 시퀀스를 생성할 수 있습니다.
----------------------------------------------------------
Important
페이징에는 결정론적 정렬 순서가 필요합니다.
----------------------------------------------------------
기능 사양이 "날짜별, 최신 우선" 정렬만 요구하더라도 개발자로서 우리는 order by 모델이 결정론적인 행 시퀀스를 생성하는지 확인해야 합니다. 이를 위해 결정론적 행 시퀀스를 얻기 위해 임의 열로 order by 순서를 확장해야 할 수 있습니다. 파이프라인 order by 에 사용되는 인덱스에 열이 추가되면 파이프라인 order by 에 대해 이 인덱스를 계속 사용할 수 있도록 order by 테이블에 추가하는 것이 좋습니다. 그래도 결정론적 정렬 순서가 나타나지 않으면 고유한 열을 추가하고 그에 따라 인덱스를 확장하십시오.
다음 예제에서는 결정론적 행 시퀀스를 얻기 위해 기본 키 SALE_ID를 사용하여 order by 키와 인덱스를 확장합니다. 또한 원하는 결과를 얻으려면 두 열 모두에 "다음 이후에 온다" 논리를 적용해야 합니다:
----------------------------------------------------------
CREATE INDEX sl_dtid ON sales (sale_date, sale_id)
----------------------------------------------------------
----------------------------------------------------------
SELECT *
FROM sales
WHERE (sale_date, sale_id) < (?, ?)
ORDER BY sale_date DESC, sale_id DESC
FETCH FIRST 10 ROWS ONLY
----------------------------------------------------------
where 절은 잘 알려지지 않은 "Row Values" 구문을 사용합니다("SQL Row Values" 상자 참조).
여러 값을 정규 비교 연산자에 적용할 수 있는 논리 단위로 결합합니다. 스칼라 값과 마찬가지로, 내림차순으로 정렬할 때 보다 작은 조건은 "다음에 옵니다"에 해당합니다. 즉, 쿼리는 지정된 SALE_DATE, SALE_ID 쌍 이후에 발생하는 판매만 고려합니다.
----------------------------------------------------------
----------------------------------------------------------
SQL Row Values
정규 스칼라 값 외에도 SQL 표준은 소위 행 값 생성자를 정의합니다. 행 또는 부분행으로 구성할 순서가 지정된 값 집합 지정
[SQL:92, §7.1: <row value constructor>]
구문적으로 행 값은 대괄호 안에 있는 목록 입니다.
이 구문은 삽입 문에서 사용되는 것으로 가장 잘 알려져 있습니다.
그러나 where 절에서 행 값 생성자를 사용하는 것은 덜 알려져 있지만 여전히 완벽하게 유효합니다. SQL 표준은 실제로 행 값 생성자에 대한 모든 비교 연산자를 정의합니다. less thin 연산의 정의는 예를 들어 다음과 같습니다:
"Rx < Ry"는 모든 i< n 에 대해 RXi=RYi 이고 일부 n에 대해 RXn < RYn인 경우에만 참입니다.
— SQL:92, §8.2.7.2
여기서 I 및 in은 목록의 위치 인덱스를 반영합니다.
즉, RXn 값이 해당 RYn보다 작고 모든 이전 값 쌍이 동일한 경우 행 값 RX가 RY보다 작다는 것을 의미합니다.
(RXi = RYi; for i<n)
이 정의는 식을 RX < RY로 만듭니다
"RX sort before RY"와 동의어로, 이는 정확히 검색 방법에 필요한 논리입니다.
----------------------------------------------------------
행 값 구문은 SQL 표준의 일부이지만 일부 데이터베이스만 이를 지원합니다. SQL Server 2017에서는 행 값을 전혀 지원하지 않습니다. Oracle 데이터베이스는 원칙적으로 행 값을 지원하지만 범위 연산자를 적용할 수 없습니다.(ORA-01796)
MySQL은 행 값 표현식을 올바르게 평가하지만 Index access 중에는 access predicate로 사용할 수 없습니다.
그러나 DB2(LUW만, 10.1 이후)와 PostgreSQL(8.4 이후)은
row value predicate를 적절하게 지원하며 사용가능한 인데긋가 있는 경우 이를 사용하여 인덱스에 access 합니다.
그럼에도 불구하고 Postgre SQL의 행 값만큼 우아하고 효율적이지는 않지만 행 값을 제대로 지원하지 않는 데이터베이스에는 탐색 방법의 대략적인 변형을 사용할 수 있습니다. 이 근사치를 위해 다음 Oracle 예제와 같이 "정규" 비교를 사용하여 필요한 논리를 표현해야 합니다:
----------------------------------------------------------
SELECT *
FROM ( SELECT *
FROM sales
WHERE sale_date <= ?
AND NOT (sale_date = ? AND sale_id >= ?)
ORDER BY sale_date DESC, sale_id DESC
)
WHERE rownum <= 10
----------------------------------------------------------
where 절은 두 부분으로 구성됩니다. 첫번째 파트에서는 SQLE_DATE만 고려하고 (<=)보다 작서나 같은 조건을 사용합니다. 필요에 따라 더 많은 행을 선택합니다.
where 절의 이 부분은 모든 데이터베이스가 Index에 접근하는 데 사용할 수 있을 정도로 간단합니다. where 절의 두 번째 부분은 이전 페잊에 이미 표시된 초과 행을 제거합니다. "Indexxing Equivalent Logic" 상자는 where 절이 이렇게 표현되는 이유를 설명합니다.
----------------------------------------------------------
Indexing Equivalent Logic
논리적 조건은 항상 다른 방식으로 표현될 수 있습니다.
예를 들어 위에 표시된 건너 뛰기 논리를 다음과 같이 구현할 수도 있습니다:
----------------------------------------
WHERE (
(sale_date < ?)
OR
(sale_date = ? AND sale_id < ?)
)
----------------------------------------
이 변형은 조건을 포함하여 사용할 뿐이며 적어도 인간에게는 이해하기가 더 쉬울 것입니다. 데이터베이스는 다른 관점을 가집니다.
이들은 SALE_DATE가 두 분기에 대해 동일한 경우 where 절이 해당 SALE_DATE/SALE_ID 쌍으로 시작하는 모든 행을 선택한다는 것을 인식하지 못합니다. 대신 데이터베이스는 전체 where 절을 firter predicate로 사용합니다. 적어도 옵티마이저가 두 개 또는 여러 개의 데이터베이스 중 "SALE_DATE <= ? 조건을 제거" 할 것으로 예상할 수 있지만, 이서비스를 제공하는 데이터베이스는 없습니다.
그럼에도 불구하고 가독성을 높이지 않더라도 수동으로 이 중복 조건을 추가할 수 있습니다:
----------------------------------------
WHERE sale_date <= ?
AND (
(sale_date < ?)
OR
(sale_date = ? AND sale_id < ?)
)
----------------------------------------
다행히 모든 데이터베이스는 where 절의 이 부분을 access prdicate로 사용할 수 있습니다. 그러나 그 조항은 위에서 보여준 근사 논리로 이해하기가 횔씬 더 어렵습니다. 또한 원래의 논리는 나중에 "불필요한" (중복) 부분이 where 절에서 실수로 제거될 위험을 피합니다.
----------------------------------------------------------
실행 계획은 데이터베이스가 where절의 첫 번째 부분을 access predicate로 사용한다는 것을 보여줍니다.
----------------------------------------------------------
---------------------------------------------------------------
|Id | Operation | Name | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 4 |
|*1 | COUNT STOPKEY | | | |
| 2 | VIEW | | 10 | 4 |
| 3 | TABLE ACCESS BY INDEX ROWID | SALES | 50218 | 4 |
|*4 | INDEX RANGE SCAN DESCENDING| SL_DTIT | 2 | 3 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
4 - access("SALE_DATE"<=:SALE_DATE)
filter("SALE_DATE"<>:SALE_DATE
OR "SALE_ID"<TO_NUMBER(:SALE_ID))
----------------------------------------------------------
SALE_DATE의 access predicate를 사용하면 데이터베이스가 이전 페이지에 완전히 표시된 날짜를 건너뛸 수 있습니다. where절의 두 번째 부분은 필터 서술어입니다.
즉, 데이터베이스는 이전 페이지의 일부 항목을 다시 검사하지만 즉시 삭제합니다.
그림 7.3은 각각의 접근 경로를 보여줍니다.
Figure 7.3 Access Using the Seek Method
----------------------------------------------------------
----------------------------------------------------------
그림 7.4는 오프셋과 탐색 방법의 성능 특성을 비교합니다. 측정의 정확도는 차트의 왼쪽에 있는 차이를 확인하기에는 충분하지 않지만 약 20페이지 이후부터는 차이를 명확하게 볼 수 있습니다.
Figure 7.4 Scalability when Fetching the Next Page
----------------------------------------------------------
----------------------------------------------------------
물론 탐색 방법에도 단점이 있는데, 가장 중요한 것은 그것을 다루는 데 어려움이 있다는 것입니다.
where 절을 매우 주의 깊게 구해야 할 뿐만 아니라 임의 페이지를 가져올 수도 없습니다. 또한 검색 방향을 변경하려면 모든 비교 및 정렬 작업을 되돌려야합니다.
사용자 인터페이스에 무한 스크롤 메커니즘을 사용할 때는 정확히 페이지 건너뛰기와 뒤로 탐색이라는 두가지 기능이 필요하지 않습니다.
Figure 7.5 Database/Feature Matrix
----------------------------------------------------------
----------------------------------------------------------
----------------------------------------------------------
'use-the-index-luke' 카테고리의 다른 글
| 8장 Insert, Delete and Update (0) | 2023.11.10 |
|---|---|
| 7.3 Window-Functions (0) | 2023.11.10 |
| 7.1 Selecting Top-N Rows (0) | 2023.11.03 |
| 7장 Partial Results (1) | 2023.10.31 |
| 6.3 Index-Oraganized Table (1) | 2023.10.31 |
