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)를 돌려서
번역한 내용들이라 맞지 않거나, 오타가 있을수 있습니다.
댓글로 지적해주시면 감사하겠습니다.
---------------------------------------------
https://use-the-index-luke.com/sql/sorting-grouping/order-by-asc-desc-nulls-last
ㄴ인덱싱 ASC, DESC 및 NULLS FIRST/LAST
데이터베이스는 양방향으로 INDEX을 읽을 수 있습니다. 즉, 스캔한 인덱스 범위가 oder by 절에 지정된 것과 정반대의 순서일 경우 파이프라인 order by도 가능합니다. order by 절의 ASC 및 DESC 수정자는 파이프라인 실행을 방지할 수 있지만 대부분의 데이터베이스는 인덱스 순서에 사용할 수 있도록 하는 간단한 방법을 제공합니다.
----------------------------------------------------------
SELECT sale_date, product_id, quantity
FROM sales
WHERE sale_date >= TRUNC(sysdate) - INTERVAL '1' DAY
ORDER BY sale_date DESC, product_id DESC
----------------------------------------------------------
다음 예제에서는 역순으로 인덱스를 사용합니다.
어제부터 내림차순, 내림차순 PRODUCT_ID 순으로 매출을 전달합니다.
실행 계획은 데이터베이스가 내림차순으로 인덱스를 읽는 것을 보여줍니다.
----------------------------------------------------------
---------------------------------------------------------------
|Id |Operation | Name | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT | | 320 | 300 |
| 1 | TABLE ACCESS BY INDEX ROWID | SALES | 320 | 300 |
|*2 | INDEX RANGE SCAN DESCENDING| SALES_DT_PR | 320 | 4 |
---------------------------------------------------------------
----------------------------------------------------------
이 경우 데이터베이스는 인덱스 트리를 사용하여 마지막으로 일치하는 항목을 찾습니다. 이후 그림 6.2와 같이 리프 노드 체인 "상향"을 따릅니다. 결국, 이것이 데이터베이스가 이중으로 연결된 목록을 사용하여 리프 노드 체인을 구축하는 이유입니다.
Figure 6.2 Reverse Index Scan
----------------------------------------------------------
----------------------------------------------------------
물론 스캔한 인덱스 범위가 조항별 주문에 필요한 것과 완전히 반대 순서인 것이 중요합니다.
----------------------------------------------------------
Important
데이터베이스는 양방향으로 INDEX을 읽을 수 있습니다.
----------------------------------------------------------
다음 예제는 절별 순서대로 ASC 및 DESC 한정자를 혼합하기 때문에 이 전제 조건을 충족하지 않습니다.
----------------------------------------------------------
SELECT sale_date, product_id, quantity
FROM sales
WHERE sale_date >= TRUNC(sysdate) - INTERVAL '1' DAY
ORDER BY sale_date ASC, product_id DESC
----------------------------------------------------------
쿼리는 먼저 PRODUCT_ID 내림차순으로 주문한 어제의 매출을 전달한 다음 PRODUCT_ID 내림차순으로 오늘의 매출을 전달해야 합니다. 그림 6.3은 이프로세스를 보여줍니다. 필요한 순서로 판매를 진행하려면 인덱스 검사 중에 데이터베이스가 "점프" 해야 합니다.
Figure 6.3 Impossible Pipelined order by
----------------------------------------------------------
----------------------------------------------------------
그러나 이 지수는 가장 작은 PRODUCT_ID를 가진 어제의 판매에서 가장 큰 판매까지의 연결고리가 없습니다. 따라서 데이터베이스는 이 INDEX을 사용하여 명시적 정렬 작업을 피할 수 없습니다.
이러한 경우 대부분의 데이터베이스는 order by 절에 맞게 색인 순서를 조정하는 간단한 방법을 제공합니다. 구체적으로, 이는 인덱스 선언에 ASC 및 DESC 수정자를 사용할 수 있음을 의미합니다:
----------------------------------------------------------
DROP INDEX sales_dt_pr
----------------------------------------------------------
----------------------------------------------------------
CREATE INDEX sales_dt_pr
ON sales (sale_date ASC, product_id DESC)
----------------------------------------------------------
----------------------------------------------------------
Warning
version 8.0 이전의 MySQL 데이터베이스는 인덱스 정의의 ASC 및 DESC 수정자를 무시합니다.
MariaDB는 버전 10.8 이후 인덱스에서만 DESC를 준수합니다.
----------------------------------------------------------
이제 인덱스 순서는 절별 순서에 해당하므로 데이터베이스는 정렬 작업을 생략할 수 있습니다:
----------------------------------------------------------
---------------------------------------------------------------
|Id | Operation | Name | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 320 | 301 |
| 1 | TABLE ACCESS BY INDEX ROWID| SALES | 320 | 301 |
|*2 | INDEX RANGE SCAN | SALES_DT_PR | 320 | 4 |
---------------------------------------------------------------
----------------------------------------------------------
그림 6.4는 새로운 인덱스 순서를 보여줍니다. 두 번째 열의 정렬 방향을 변경하면 이전 그림에서 화살표 방향이 바뀝니다.
그러면 첫 번째 화살표가 두 번째 화살표가 시작되는 곳에서 끝나 인덱스가 원하는 순서로 행을 가지게 됩니다.
----------------------------------------------------------
Important
ASC 및 DESC 혼합 한정자를 절별로 사용하는 경우 파이프라인 순서에 사용하려면 인덱스를 동일하게 정의해야 합니다.
이것은 where 절에 대한 인덱스의 유용성에 영향을 주지 않습니다.
----------------------------------------------------------
Figure 6.4 Mixed-Order Index
----------------------------------------------------------
----------------------------------------------------------
ASC/DESC 인덱싱은 개별 열을 반대 방향으로 정렬하는 경우에만 필요합니다. 데이터베이스는 필요한 경우 색인을 내림차순으로 읽을 수 있으므로 모든 열의 순서를 반대로 할 필요는 없습니다. 인덱스 구성 테이블의 보조 색인이 유일한 예외입니다. 보조 인덱스는 정렬 순서를 지정하지 않고 클러스터링 키를 인덱스에 암시적으로 추가합니다. 클러스터링 키를 내림차순으로 정렬해야 하는 경우 다른 모든 열을 내림차순으로 정렬하는 것 외에는 다른 옵션이 없습니다. 그런 다음 데이터베이스는 원하는 순서를 얻기 위해 역방향으로 인덱스를 읽을 수 있습니다.
ASC와 DESC 외에도 SQL 표준은 order by에 대해 잘 알려지지 않은 NULLS FIRST 와 NULLS LAST라는 두 가지 한정자를 정의합니다. NULL 정렬에 대한 명시적 제어는 "최근" SQL:2003의 선택적 확장으로 도입되었습니다. 결과적으로 데이터베이스 지원은 거의 없습니다. 이는 표준이 NULL의 정렬 순서를 정확하게 정의하지 않기 때문에 특히 우려됩니다. 정렬 후 모든 NULL이 함께 나타나야 한다고만 명시되어 있지만, NULL이 다른 항목앞에 나타나야 하는지 뒤에 나타나야 하는지는 명시되어 있지 않습니다.
엄밀히 말하면, 실제로 order by에서 null일 수 있는 모든 열에 대해 NULL 정렬을 지정해야 합니다.
그러나 선택적 확장은 SQL Server 2019 나 MySQL 8.0에 의해 구현되지 않습니다. 반면 Oracle 데이터베이스는 표준에 도입되기 전부터 NULLS 정렬을 지원했지만 릴리스 19c에서 인덱스 정의에서 NULLS 정렬을 허용하지 않습니다. 따라서 NULLS FIRST로 정렬할 때
Oracle 데이터베이스는 파이프라인 order by를 수행할 수 없습니다.
PostgreSQL database(since release 8.3)만 절별 순서 및 인덱스 정의 모두에서 NULLS 한정자를 지원합니다.
다음 개요는 다른 데이터베이스에서 제공하는 기능을 요약합니다.
Figure 6.5 Database/Feature Matrix
----------------------------------------------------------
'use-the-index-luke' 카테고리의 다른 글
| 7장 Partial Results (1) | 2023.10.31 |
|---|---|
| 6.3 Index-Oraganized Table (1) | 2023.10.31 |
| 6.1 Indexed Order By (0) | 2023.10.24 |
| 6장 Sorting and Grouping (1) | 2023.10.20 |
| 5.2 Index-Only Scan (1) | 2023.10.13 |
