5. 인덱스를 이용한 소트 연산 대체
2011.06.13 20:27
5. 인덱스를 이용한 소트 연산 대체
▶SORT UNIQUE NOSORT 오퍼레이션
소트를 수행하지 않고도 인덱스를 이용해 UNIQUE한 집합을 출력
(1)SORT ORDER BY 오퍼레이션 대체
(2)SORT GROUP BY 오퍼레이션 대체
SELECT /*+ */ DISTINCT EMPNO
FROM EMP;
(9i) - 교재에서 설명하는 내용
-------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 9 |
| 1 | SORT UNIQUE NOSORT| | 14 | 56 | 9 |
| 2 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 |
-------------------------------------------------------------
(10g 이후..) - 옵티마이져가 자동으로 NOSORT 처리함
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
1)SORT ORDER BY 대체
▶테스트환경 구축
DROP TABLE YHN_CUSTOMER;
CREATE TABLE YHN_CUSTOMER
AS
SELECT E.EMPLOYEE_ID AS CUSTID
, E.LAST_NAME AS NAME
, E.EMAIL AS RESNO
, 'U' AS STATUS
, E.PHONE_NUMBER AS TELL
, E.HIRE_DATE - 265*40 AS AGE
, SUBSTR(D.DEPARTMENT_NAME, 1, 1) AS REGION
FROM HR.EMPLOYEES E, HR.DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;
CREATE UNIQUE INDEX PK_CUSTOMER ON YHN_CUSTOMER(CUSTID);
CREATE INDEX CUSTOMER_X01 ON YHN_CUSTOMER(REGION);
CREATE INDEX CUSTOMER_X02 ON YHN_CUSTOMER(REGION, CUSTID);
▶인덱스가 REGION 단일 컬럼으로 구성되었거나, 결합인덱스라도 REGION 바로 뒤에
CUSTID 오지 않는 경우
SELECT /*+ INDEX(C, CUSTOMER_X01) */CUSTID, NAME, RESNO, STATUS, TELL
FROM YHN_CUSTOMER C
WHERE REGION = 'A'
ORDER BY CUSTID;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 177 | 3 (34)| 00:00:01 |
| 1 | SORT ORDER BY | | 3 | 177 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| YHN_CUSTOMER | 3 | 177 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | CUSTOMER_X01 | 3 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("REGION"='A')
▶결합인덱스 구성이 조건절에 이어 ORDER BY의 컬럼으로 구성되어 있는 경우
SELECT /*+ INDEX(C, CUSTOMER_X02) */CUSTID, NAME, RESNO, STATUS, TELL
FROM YHN_CUSTOMER C
WHERE REGION = 'A'
ORDER BY CUSTID;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 177 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| YHN_CUSTOMER | 3 | 177 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CUSTOMER_X02 | 3 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("REGION"='A')
2)SORT GROUP BY 대체
▶GROUP BY의 사용컬럼이 결합 인덱스나 단일 컬럼 인덱스의 선두컬럼으로 사용된다면
SORT GROUP BY 연산을 대체할 수 있다. 실행계획에 SORT GROUP BY NOSORT 부분
SELECT REGION, AVG(SYSDATE-AGE), COUNT(*) FROM YHN_CUSTOMER
GROUP BY REGION;
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 80 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 8 | 80 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| YHN_CUSTOMER | 106 | 1060 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
▶책내용과 다름 - DATA량이 얼마되지 않아서 옵티마이져가 FULL SCAN으로 유도함
SELECT /*+ */
REGION, AVG(SYSDATE-AGE), COUNT(*)
FROM YHN_CUSTOMER C
WHERE REGION > ' ' -- 교재와 유사한 형태의 PLAN을 유도하기 위해 WHERE절에 조건 추가
GROUP BY REGION;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 80 | 2 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 8 | 80 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| YHN_CUSTOMER | 106 | 1060 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | CUSTOMER_X02 | 106 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("REGION">' ' AND "REGION" IS NOT NULL)
3)인덱스가 소트 연산을 대체하지 못하는 경우
▶아래는 SAL 컬럼을 선두로 갖는 인덱스가 있는데도 정렬을 수행하는 경우임
SELECT /*+ INDEX(E, YHN_EMP_IDX) */
*
FROM YHN_EMP E
ORDER BY SAL;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14000 | 533K| | 204 (2)| 00:00:02 |
| 1 | SORT ORDER BY | | 14000 | 533K| 896K| 204 (2)| 00:00:02 |
| 2 | TABLE ACCESS FULL| YHN_EMP | 14000 | 533K| | 41 (3)| 00:00:01 |
--------------------------------------------------------------------------------------
Outline Data
-------------
/*+
(생략...)
ALL_ROWS
(생략...)
*/
●위의 SQL처럼 옵티마이저가 이런 결정을 하는 가장 큰 이유는 인덱스를 이용하지 않는 편이 더 낫다고
판단하는 경우임.
●또란 옵티마이저 모드가 ALL_ROWS임,
이 때 옵티마이저는 전체 로우를 FETCH 하는 것을 기준으로 쿼리 수행 비용을 산정후 데이터량이 많을수록
인덱스를 이용한 테이블 랜덤 액세스 비용이 높아져 옵티마이저는 차라리 FULL TABLE SCAN하는 쪽을 택할
가능성이 높아짐
●옵티마이저 모드를 FIRST_ROWS로 바꾸면 사용자가 일부만 FETCH하고 멈출 것임을 시사하므로 옵티마이저는
인덱스를 이용해 정렬 작업을 대체함.
-- 옵티마이저 모드를 FIRST_ROWS로 변경한 PLAN
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
SELECT /*+ INDEX(E, YHN_EMP_IDX) */
*
FROM YHN_EMP E
ORDER BY SAL;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14000 | 533K| | 204 (2)| 00:00:02 |
| 1 | SORT ORDER BY | | 14000 | 533K| 896K| 204 (2)| 00:00:02 |
| 2 | TABLE ACCESS FULL| YHN_EMP | 14000 | 533K| | 41 (3)| 00:00:01 |
--------------------------------------------------------------------------------------
Outline Data
-------------
/*+
(생략...)
FIRST_ROWS
(생략...)
*/
●여전히 SORT 발생
●이러한 현상은 해당 인덱스 컬럼의 선언이 NOT NULL 제약조건이 없을 경우임
단일 컬럼 인덱스일 때 값이 NULL이면 인덱스 레코드에 포함되지 않음.
따라서 인덱스를 이용해 정렬 작업을 대체한다면 결과에 오류가 생길 수 있어 옵티마이저는
사용자의 뜻을 따를 수 없는 것이다.
●GROUP BY도 마찬가지임.
GROUP BY NOSORT를 위해 사용하려는 인덱스가 단일 컬럼 인덱스일 때는 해당 컬럼에
NOT NULL 제약이 설정되어 있어야 제대로 동작함
-- 해당 인덱스 컬럼을 NOT NULL로 변경 후 PLAN
ALTER TABLE YHN_EMP MODIFY SAL NOT NULL;
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
SELECT /*+ INDEX(E, YHN_EMP_IDX) */
*
FROM YHN_EMP E
ORDER BY SAL;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1124 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 14000 | 533K| 1124 (1)| 00:00:08 |
| 2 | INDEX FULL SCAN | YHN_EMP_IDX | 14000 | | 30 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
(생략...)
FIRST_ROWS
(생략...)
*/
▶ORDER BY 사용시 NULL 값이 먼저 출력되도록 하려고 NULLS FIRST 구문을 사용하는 순간 실행계획에
SORT ORDER BY가 나타남
CREATE INDEX EMP_DEPT_ENAME_IDX ON YHN_EMP(DEPTNO , ENAME);
SELECT /*+ INDEX(E, EMP_DEPT_ENAME_IDX) */
*
FROM YHN_EMP E
WHERE DEPTNO = 30
ORDER BY ENAME ;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 1 | 39 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPT_ENAME_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=30)
-- ORDER BY에 NULLS FIRST를 추가할 경우
SELECT /*+ INDEX(E, EMP_DEPT_ENAME_IDX) */
*
FROM YHN_EMP E
WHERE DEPTNO = 30
ORDER BY ENAME NULLS FIRST;
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (50)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 39 | 2 (50)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| YHN_EMP | 1 | 39 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPT_ENAME_IDX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=30)
●단일 컬럼 인덱스일 때는 NULL 값을 저장하지 않음
●결합 인덱스일 때는 NULL 값을 가진 레코드를 맨 뒤쪽에 저장한다.
따라서 NULL 값부터 출력하려고 할 때는 인덱스를 이용하더라도 소트가 불가피함
(교재 P28, 196참조)
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
» | 5. 인덱스를 이용한 소트 연산 대체 | 멋진넘 | 2011.06.13 | 3356 |
19 | 6. IOT, 클러스터 테이블 활용 | 휘휘 | 2011.02.26 | 2752 |
18 | 6. 히스토그램 | 실천하자 | 2011.04.25 | 10921 |
17 | 6. 조인 제거 | 멋진넘 | 2011.05.30 | 4596 |
16 |
6. Sort Area를 적게 사용하도록 SQL 작성
![]() | 실천하자 | 2011.06.14 | 8861 |
15 | 7. 인덱스 스캔 효율 [1] | 휘휘 | 2011.03.09 | 16902 |
14 | 7. 비용 | 휘휘 | 2011.05.03 | 6173 |
13 | 7. OR-Expansion | 멋진넘 | 2011.05.31 | 8720 |
12 | 7. Sort Area 크기 조정 | 실천하자 | 2011.06.14 | 15079 |
11 | 8. 인덱스 설계 | 멋진넘 | 2011.03.07 | 8345 |
10 | 7. 조인을 내포한 DML 튜닝 | 실천하자 | 2011.04.04 | 7176 |
9 | 8. 통계정보 Ⅱ [1] | 멋진넘 | 2011.04.30 | 31096 |
8 | 8. 공통 표현식 제거 | darkbeom | 2011.06.07 | 5223 |
7 |
9. 비트맵 인덱스
![]() | 실천하자 | 2011.03.06 | 12354 |
6 |
8. 고급 조인 테크닉-1
[1] ![]() | darkbeom | 2011.04.04 | 13269 |
5 | 9. Outer 조인을 Inner 조인으로 변환 | darkbeom | 2011.06.07 | 7834 |
4 |
8. 고급 조인 테크닉-2
![]() | suspace | 2011.04.05 | 7018 |
3 | 10. 실체화 뷰 쿼리로 재작성 | suspace | 2011.06.07 | 5531 |
2 | 11. 집합 연산을 조인으로 변환 | suspace | 2011.06.08 | 5994 |
1 | 12. 기타 쿼리 변환 [3] | 실천하자 | 2011.06.03 | 6646 |