6.1 Indexed Order By

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/indexed-order-by

 

order by 절이 있는 SQL 쿼리는 관련 인덱스가 이미 필요한 순서로 행을 전달하는 경우 결과를 명시적으로 정렬할 필요가 없습니다.

즉, where 절에 사용되는 동일한 인덱스가 order by 절도 포함해야 합니다.

 

예를 들어, 판매 날짜 및 제품 ID별로 어제의 판매 주문을 선택하는 다음 쿼리를 생각해 보십시오:

----------------------------------------------------------

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY sale_date, product_id

----------------------------------------------------------

 

sale_DATE에 where 절에 사용할 수 있는 인덱스가 이미 있습니다. 그러나 데이터베이스는 order by 절을 만족시키기 위해 명시적인 정렬 작업을 수행해야합니다:

----------------------------------------------------------

---------------------------------------------------------------
|Id | Operation                    | Name       | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT             |            |  320 |   18 |
| 1 |  SORT ORDER BY               |            |  320 |   18 |
| 2 |   TABLE ACCESS BY INDEX ROWID| SALES      |  320 |   17 |
|*3 |    INDEX RANGE SCAN          | SALES_DATE |  320 |    3 |
---------------------------------------------------------------

----------------------------------------------------------

 

INDEX RANGE SCAN은 결과를 인덱스 순서로 전송합니다. 이 사실을 활용하려면 인덱스 정의를 확장하여 order by 절에 해당 하도록 하면됩니다:

----------------------------------------------------------

DROP INDEX sales_date

----------------------------------------------------------

----------------------------------------------------------

CREATE INDEX sales_dt_pr ON sales (sale_date, product_id)

----------------------------------------------------------

----------------------------------------------------------

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY sale_date, 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          | SALES_DT_PR |  320 |    4 |
---------------------------------------------------------------

----------------------------------------------------------

 

쿼리에 order by 절이 있음에도 불구하고 정렬 작업 SORT OREDER BY가 실행 계획에서 사라졌습니다.

데이터베이스는 인덱스 순서를 이용하여 명시적 정렬 작업을 건너뜁니다.

----------------------------------------------------------

Important

INDEX 순서가 order by 절에 해당하는 경우 데이터베이스는 명시적 정렬 작업을 생략 할 수 있습니다.

----------------------------------------------------------

새 실행 계획의 작업 수가 적더라도 새 인덱스의 클러스터링 요소가 더 나쁘기 때문에 비용 가치가 상당히 증가했습니다. ("자동 최적화된 클러스터링 요소" 참조). 이 시점에서 비용 가치가 항상 실행 노력의 좋은 지표는 아니라는 점에 유의해야 합니다.

----------------------------------------------------------

Automatically Optimized Clustering Factor

자동 최적화된 클러스터링 요소

 

오라클 데이터베이스는 인덱스 순서에 대한 ROWID를 고려하여 클러스터링 팩터를 최소로 유지합니다. 두 인덱스 항목이 동일한 키 값을 가질 때마다 ROWID는 최종 순서를 결정합니다. 따라서 인덱스도 테이블 순서에 따라 정렬되므로 ROWID가 테이블 행의 물리적 주소를 나타내기 때문에 가능한 클러스터링 팩터가 가장 작습니다.

 

인덱스에 다른 열을 추가하면 ROWID 앞에 새 정렬 기준을 삽입할 수 있습니다. 데이터베이스는 테이블 순서에 따라 인덱스 항목을 정렬할 수 있는 자유도가 낮으므로 인덱스 클러스터링 팩터가 더 나빠질 수 있습니다.

 

그럼에도 불구하고 인덱스 순서는 테이블 순서와 대략 일치할 수 있습니다. 하루 매출은 더 이상 순서가 완전히 같지는 않지만 여전히 테이블과 인덱스에서 함께 군집화되어 있을 수 있습니다. 데이터베이스는 SALE_DT_PR 인덱스를 사용할 때 테이블 블록을 여러 번 읽어야 하지만, 테이블 블록은 이전과 동일합니다. 자주 액세스하는 데이터의 캐슁으로 인해 성능에 미치는 영향은 비용 값으로 표시된 것보다 상당히 낮을 수 있습니다.

----------------------------------------------------------

이 최적화를 위해 스캔한 인덱스 범위가 절별 순서에 따라 정렬되면 충분합니다. 따라서 PRODUCT_ID로만 정렬할 경우 다음과 같은 특정 예제에서도 최적화가 작동합니다:

 

----------------------------------------------------------

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY product_id

----------------------------------------------------------

그림 6.1에서 PRODUCT_ID가 스캔한 인덱스 범위에서 유일한 관련 정렬 기준임을 알 수 있습니다. 따라서 색인 순서는 데이터베이스가 정렬 작업을 생략할 수 있도록 이 색인 범위의 order by 순서에 해당합니다.

 

Figure 6.1 Sort Order in the Relevant Index Range

----------------------------------------------------------

----------------------------------------------------------

이 최적화는 검색된 인덱스 범위를 확장할때 예기치 않은 동작을 일으킬 수 있습니다.

----------------------------------------------------------

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date >= TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY product_id

----------------------------------------------------------

이 쿼리는 어제의 매출을 검색하지 않고 어제 이후의 모든 매출을 검색합니다. 즉, 며칠에 걸쳐 PRODUCT_ID로만 정렬 되지 않은 인덱스 범위를 검색합니다. 그림6.1을 다시 보고 스캔한 인덱스 범위를 하단까지 확장하면 다시 더 작은 PRODUCT_ID 값이 있음을 알 수 있습니다. 따라서 데이터베이스는 명시적 정렬 작업을 사용하여 order by 절을 충족해야 합니다.

----------------------------------------------------------

---------------------------------------------------------------
|Id |Operation                    | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT             |             |  320 |  301 |
| 1 | SORT ORDER BY               |             |  320 |  301 |
| 2 |  TABLE ACCESS BY INDEX ROWID| SALES       |  320 |  300 |
|*3 |   INDEX RANGE SCAN          | SALES_DT_PR |  320 |    4 |
---------------------------------------------------------------

----------------------------------------------------------

 

파이프라인 실행을 예상했는데도 데이터베이스가 정렬 작업을 사용하는 경우 두가지 이유가 있을 수 있습니다. (1) 명시적 정렬 작업을 사용하는 실행 계획의 비용값이 더 낫습니다. (2) 스캔한 인덱스 범위의 인덱스 순서가 order by 절과 일치하지 않습니다.

 

두 경우를 구분하는 간단한 방법은 order by 절 순서대로 전체 인덱스 정의를 사용하는 것입니다. 즉, 두 번째 원인을 제거하기 위해 쿼리를 인덱스에 맞게 조정하는 것입니다. 데이터베이스가 여전히 명시적 정렬 작업을 사용하는 경우 최적화 도구는 비용 값으로 인해 이 계획을 선호합니다. 그렇지 않으면 데이터베이스는 원래 order by 절에 대한 인덱스를 사용할 수 없습니다.

----------------------------------------------------------

Tip

명시적 정렬 작업의 이유를 찾으려면 order by 절로 전체 인덱스 정의를 사용합니다.

----------------------------------------------------------

두 경우 모두 실행을 통해 파이프라인 order by 에 도달할 수 있는지 여부와 방법에 대해 의문을 가질 수 있습니다. 이를 위해 order by 절에서 전체 인덱스 정의로 쿼리를 실행하고 결과를 검사할 수 있습니다. 인덱스에 대한 잘못된 인식이 있으며 인덱스 순서가 절별 원래 순서에서 요구하는 것과 같지 않으므로 데이터베이스가 인덱스를 사용하여 정렬 작업을 피할 수 없다는 것을 알게 될 것입니다.

 

Optimizer가 비용 값에 대해 명시적 정렬 작업을 선호하는 경우, 일반적으로 Optimizer가 쿼리의 전체 실행을 위한 최상의 실행 계획을 취하기 때문입니다. 즉,Optimizer는 마지막 레코그를 가장 빨리 얻을 수 있는 실행 계획을 선택합니다. 응용프로그램이 처음 몇 개의 행만 가져오는 것을 탐지하면 데이터베이스는 인덱스된 순서를 선호할 수 있습니다. 7장 "부분 결과"에서는 해당 최적화 방법을 설명합니다.