오라클 성능 고도화 원리와 해법 II (2016년)
목록
인덱스를 이용한 소트 연산 대체 0 0 4,032

by 구루비 소트튜닝 Sort Order By Sort Group By [2017.06.26]

05.인덱스를 이용한 소트 연산 대체

  • 인덱스는 항상 키 컬럼 순으로 정렬된 상태를 유지하므로 이를 이용해 소트 오퍼레이션을 생략할 수 있다.

(1)Sort Order By 대체

  • 아래 쿼리를 수행할 때 region + custid 순으로 구성된 인덱스를 사용한다면 sort order by 연산을 대체할 수 있다.

select custid, name, resno, status, tel1
from   customer
where  region = 'A'
order by custid

--------------------------------------------------------------
| Id  | Operation                   | Name          | E-Rows |
--------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMER      |      1 |
|*  2 |   INDEX RANGE SCAN          | CUSTOMER_IDX1 |      1 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("REGION"='12345')

인덱스가 region 단일 컬럼으로 구성됐거나, 결합 인덱스더라도 region 바로 뒤에 custid가 오지 않는다면 region = 'A' 조건을 만족하는 모든 레코드를 인덱스를 경유해 읽어야 한다.
그 과정에서 다량의 랜덤 액세스가 발생할 것이고, 읽은 데이터를 custid순으로 정렬하고 나서야 결과 집합 출력을 시작하므로 OLTP 환경에서 요구되는 빠른 응답속도를 만족하기 어렵게 된다.

  • region 인덱스만 사용한 경우

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | E-Rows |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |        |       |       |          |
|   1 |  SORT ORDER BY               |               |      3 |  2048 |  2048 | 2048  (0)| => Sort 발생
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMER      |      3 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | CUSTOMER_IDX2 |      3 |       |       |          |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("REGION"='12345')

SORT ORDER BY 오퍼레이션이 일어나는 것을 알 수 있다.

(2)Sort Group By 대체

  • region이 선두 컬럼인 결합 인덱스나 단일 컬럼 인덱스를 사용한다면 아래 쿼리에 필요한 sort group by 연산을 대체할 수 있다.
    실행계획에 'sort group by nosort'라고 표시되는 부분이 나타난다.

select region, avg(age), count(*)
from   customer
group by region

---------------------------------------------------------------------------
| Id  | Operation          | Name     | E-Rows |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |        |       |       |          |
|   1 |  HASH GROUP BY     |          |   3843 |   855K|   855K| 1333K (0)|
|   2 |   TABLE ACCESS FULL| CUSTOMER |   3843 |       |       |          |
---------------------------------------------------------------------------

예상과 다르다. 조건을 주지 않으니 HASH GROUP BY 되었다.

무엇이 잘못된 것인지 인덱스를 사용하지 않는다.

범위를 지정해보았다.

select region, avg(age), count(*)
from   customer
where region between  '12345' and '13000'
group by region

---------------------------------------------------------------
| Id  | Operation                    | Name          | E-Rows |
---------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |        |
|   1 |  SORT GROUP BY NOSORT        |               |   1119 |
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMER      |   1119 |
|*  3 |    INDEX RANGE SCAN          | CUSTOMER_IDX1 |   1119 |
---------------------------------------------------------------

인덱스를 사용하였고 SORT GROUP BY NOSORT도 수행하였다.

무엇이 문제일까?

HASH GROUP BY를 사용하지 않으면 되나?

select /*+no_use_hash_aggregation*/region, avg(age), count(*)
from   customer
group by region

---------------------------------------------------------------------------
| Id  | Operation          | Name     | E-Rows |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |        |       |       |          |
|   1 |  SORT GROUP BY     |          |    492K| 90112 | 90112 |79872  (0)|
|   2 |   TABLE ACCESS FULL| CUSTOMER |    492K|       |       |          |
---------------------------------------------------------------------------

SORT GROUP BY 는 되는데 SORT GROUP BY NSORT는 안 되었다.

인덱스를 재생성해봐도 안 된다.

문득 생각해보니.. region 컬럼이 null 허용 컬럼이었다.

index 스캔 후 소팅을 하지 않으려면 null 값에 대한 처리가 있어야 하는데

index는 null 컬럼을 관리하지 않으므로 어쩔 수 없이 소트를 하게 된 것 같다.

추측이 맞는지 확인해보자.

먼저 null 허용 컬럼 테스트

CREATE TABLE TEST20170621 (c1 VARCHAR2(30));

CREATE INDEX TEST20170621_IDX1 ON TEST20170621 (c1);

INSERT INTO TEST20170621
SELECT REGION FROM CUSTOMER

SELECT /*+ INDEX (TEST20170621 TEST20170621_IDX1) */ c1
  FROM TEST20170621
 GROUP BY c1;

-------------------------------------------------------------------------------
| Id  | Operation          | Name         | E-Rows |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |        |       |       |          |
|   1 |  HASH GROUP BY     |              |   5004 |  1349K|  1349K| 1360K (0)|
|   2 |   TABLE ACCESS FULL| TEST20170621 |   5004 |       |       |          |
-------------------------------------------------------------------------------

역시나 HASH GROUP BY 수행.

다음으로 NOT NULL 컬럼 테스트

DROP TABLE TEST20170621

CREATE TABLE TEST20170621 (c1 VARCHAR2(30) NOT NULL);

CREATE INDEX TEST20170621_IDX1 ON TEST20170621 (c1);

INSERT INTO TEST20170621
SELECT REGION FROM CUSTOMER

SELECT /*+ INDEX (TEST20170621 TEST20170621_IDX1) */ c1
  FROM TEST20170621
 GROUP BY c1;

-----------------------------------------------------------
| Id  | Operation            | Name              | E-Rows |
-----------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |        |
|   1 |  SORT GROUP BY NOSORT|                   |   5004 |
|   2 |   INDEX FULL SCAN    | TEST20170621_IDX1 |   5004 |
-----------------------------------------------------------

SORT GROUP BY NOSORT 수행이 정상적으로 된다.
  • NOSORT 방식으로 수행될 때는 GROUP BY 오퍼레이션에도 불구하고 부분범위처리가 가능해져 OLTP 환경에서 매우 극적인 성능 개선 효과를 얻을 수 있다.

(3)인덱스가 소트 연산을 대체하지 못하는 경우

  • 옵티마이저가 이런 결정을 하는 가장 흔한 원인은, 인덱스를 이용하지 않는 편이 더 낫다고 판단하는 경우이다.

select * from emp order by sal;

-----------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |       |          |
|   1 |  SORT ORDER BY     |      |    114 | 13312 | 13312 |12288  (0)|
|   2 |   TABLE ACCESS FULL| EMP  |    114 |       |       |          |
-----------------------------------------------------------------------
  • 옵티마이저 모드를 first_rows로 바꾸었는데도 불구하고 계속해서 소트 오퍼레이션을 고집한다면 sal 컬럼에 not null 제약이 없는 경우.
    => 위에서 테스트한 내용이 바로 뒤에 나왔다;
  • nulls first 구문을 사용하는 경우

create index emp_deptno_ename_idx on emp(deptno, ename);

select /*+index(e emp_deptno_ename_idx)*/ *
from   emp e
where  deptno = 30
order by ename

---------------------------------------------------------------------
| Id  | Operation                   | Name                 | E-Rows |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP                  |      1 |
|*  2 |   INDEX RANGE SCAN          | EMP_DEPTNO_ENAME_IDX |      1 |
---------------------------------------------------------------------

정상적으로 수행된다.

select /*+index(e emp_deptno_ename_idx)*/ *
from   emp e
where  deptno = 30
order by ename nulls first

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | E-Rows |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |        |       |       |          |
|   1 |  SORT ORDER BY               |                      |      1 |  2048 |  2048 | 2048  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP                  |      1 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | EMP_DEPTNO_ENAME_IDX |      1 |       |       |          |
-------------------------------------------------------------------------------------------------

nulls last를 사용하면
sort가 일어난다.

단일 컬럼 인덱스일 때는 null 값을 저장하지 않지만 결합인덱스일 때는 null 값을 가진 레코드를 맨 뒤쪽에 저장한다.
따라서 null 값을 제일 앞으로 가지고 오려면 sort가 필요하게 된다.

nulls을 마지막으로 가지고 오게 하면

select /*+index(e emp_deptno_ename_idx)*/ *
from   emp e
where  deptno = 30
order by ename nulls last

---------------------------------------------------------------------
| Id  | Operation                   | Name                 | E-Rows |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP                  |      1 |
|*  2 |   INDEX RANGE SCAN          | EMP_DEPTNO_ENAME_IDX |      1 |
---------------------------------------------------------------------

역시 sort가 일어나지 않는다.

역순으로 조회하고 nulls last를 하면?

select /*+index_desc(e emp_deptno_ename_idx)*/ *
from   emp e
where  deptno = 30
order by ename nulls last

--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | E-Rows |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                      |        |       |       |          |
|   1 |  SORT ORDER BY                |                      |      1 |  2048 |  2048 | 2048  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID | EMP                  |      1 |       |       |          |
|*  3 |    INDEX RANGE SCAN DESCENDING| EMP_DEPTNO_ENAME_IDX |      1 |       |       |          |
--------------------------------------------------------------------------------------------------

역시 sort가 일어난다.
"구루비 주주클럽 스터디모임" 에서 2016년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3374

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입