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

• 인덱스는 항상 키 컬럼 순으로 정렬된 상태 유지 : sort 연산 생략 가능

PK 컬럼에 distinct 쿼리 수행

1. 특징 : sort unique 명령 생략 = sort unique nosort
   1. sort 수행 없이 인덱스를 이용해 unique한 집합 출력

SQL> EXPLAIN PLAN FOR
  2  SELECT DISTINCT empno FROM emp ;

Explained.

SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 4053311859

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |    14 |    42 |     2  (50)| 00:00:01 |
|   1 |  SORT UNIQUE NOSORT|        |    14 |    42 |     2  (50)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_EMP |    14 |    42 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

1. sort order by 대체
   1. 특징 : 정렬해야 할 대상 레코드는 무수히 많고, 그 중 일부만 읽고 멈출 수 있는 업무에서만 유리
      인덱스를 스캔하면서 결과집합을 끝까지 fetch한다면 오히려 I/O 및 리소스 사용 측면에서 손해.
      대상 레코드가 소량일 때는 정렬이 발생하더라도 부하가 크지 않아 개선 효과도 미미함
   2. 인덱스 없음 : sort 수행

SQL> conn oe/loveora
Connected.
SQL> EXPLAIN PLAN FOR
SELECT  CUSTOMER_ID, CUST_LAST_NAME, CREDIT_LIMIT,
        MARITAL_STATUS, INCOME_LEVEL
FROM    CUSTOMERS A
WHERE   NLS_TERRITORY = 'AMERICA'
ORDER   BY CUSTOMER_ID;   

Explained.

SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2792773903

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    40 |  2080 |     6  (17)| 00:00:01 |
|   1 |  SORT ORDER BY     |           |    40 |  2080 |     6  (17)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |    40 |  2080 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------------------------------------
   2 - filter("NLS_TERRITORY"='AMERICA')

1. 1. 인덱스 순서가 CUSTOMER_ID -> NLS_TERRITORY의 경우, 힌트를 사용하지 않으면 위의 쿼리의 실행계획과 동일함

CREATE INDEX OE.CUSTOMERS_X01
ON OE.CUSTOMERS
(
	CUSTOMER_ID,
	NLS_TERRITORY
)
TABLESPACE EXAMPLE
NOLOGGING
NOCOMPRESS
NOPARALLEL ;

EXPLAIN PLAN FOR
SELECT  /*+ index(A CUSTOMERS_X01) */ CUSTOMER_ID, CUST_LAST_NAME, CREDIT_LIMIT,
        MARITAL_STATUS, INCOME_LEVEL
FROM    CUSTOMERS A
WHERE   NLS_TERRITORY = 'AMERICA'
ORDER   BY CUSTOMER_ID;

@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3142583221

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |    40 |  2080 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS     |    40 |  2080 |     4   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | CUSTOMERS_X01 |    40 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------------------------------------
   2 - access("NLS_TERRITORY"='AMERICA')
       filter("NLS_TERRITORY"='AMERICA')

1. 1. 인덱스 순서가 "NLS_TERRITORY -> CUSTOMER_ID"의 경우, 힌트를 사용하지 않아도 인덱스를 타므로 sort 수행하지 않음

CREATE INDEX OE.CUSTOMERS_X02
ON OE.CUSTOMERS
(
	NLS_TERRITORY,
	CUSTOMER_ID
)
TABLESPACE EXAMPLE
NOLOGGING
NOCOMPRESS
NOPARALLEL ;

EXPLAIN PLAN FOR
SELECT  CUSTOMER_ID, CUST_LAST_NAME, CREDIT_LIMIT,
        MARITAL_STATUS, INCOME_LEVEL
FROM    CUSTOMERS A
WHERE   NLS_TERRITORY = 'AMERICA'
ORDER   BY CUSTOMER_ID; 

Explained.

SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 2848789920

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |    40 |  2080 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS     |    40 |  2080 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | CUSTOMERS_X02 |    40 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------------------------------------
   2 - access("NLS_TERRITORY"='AMERICA')

1. sort group by 대체
   1. 특징 : sort group by nosort
      인덱스를 이용한 nosort 방식으로 수행될 때는 group by 명령에도 불구하고 부분범위 처리가 가능해져 성능 개선 가능
   2. NLS_TERRITORY가 선두 컬럼인 인덱스가 존재를 해도 힌트를 사용하지 않으면 "HASH GROUP BY"로 풀림

SQL> EXPLAIN PLAN FOR
SELECT NLS_TERRITORY, AVG(CREDIT_LIMIT), COUNT(*)
FROM   CUSTOMERS
GROUP  BY NLS_TERRITORY;

Explained.

SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1577413243

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     8 |    88 |     6  (17)| 00:00:01 |
|   1 |  HASH GROUP BY     |           |     8 |    88 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS |   319 |  3509 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------

SQL> EXPLAIN PLAN FOR
SELECT /*+ index(A CUSTOMERS_X02) */ NLS_TERRITORY, AVG(CREDIT_LIMIT), COUNT(*)
FROM   CUSTOMERS A
GROUP  BY NLS_TERRITORY; 

Explained.

SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 595338122

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |     8 |    88 |    24   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT        |               |     8 |    88 |    24   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMERS     |   319 |  3509 |    24   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | CUSTOMERS_X02 |   319 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

1. 인덱스가 소트연산을 대체하지 못하는 경우
   1. 개요
      1. 옵티마이저 옵션 = all_rows : 옵티마이저가 인덱스를 이용하지 않는 편이 더 낫다고 판단하는 경우
         1. 데이터량이 많을 수록 인덱스를 이용한 테이블 랜덤 액세스 비용이 높아져 옵티마이저는 차라리 full Table Scan 선택 가능성 높음
      2. 옵티마이저 옵션 = first_rows : 일부만 fetch하고 멈출 것이므로 옵티마이저는 인덱스를 이용해 정렬 작업 대체
         1. if) 옵티마이저가 계속 sort 명령 사용 하는경우는 "해당 컬럼에 not null" 제약조건 없음
            • 단일 인덱스 : null은 인덱스에 포함 안됨. 인덱스를 이용해 정렬 작업 대체시 결과에 오류 발생 가능성 있으므로 옵티마이저는 사용자의 뜻을 따를 수 없음
            • group by에서 단일 컬럼인덱스일 때, 해달 컬럼에 not null 제약 설정돼 있어야 제대로 작동함
            • nulls first(null값이 먼저 출력되게 하는 경우) : sort order by 로 수행됨 == 인덱스가 소트 대체 못하는 경우임
            • 결합 인덱스 : null값을 가진 레코드를 맨 뒤쪽에 저장. 이 경우 null값부터 출력할 경우, 인덱스를 이용하더라도 소트는 불가피(즉 소트 수행됨)
            • nulls last : 인덱스를 뒤쪽부터 읽기 위해서 index_desc 힌트를 사용하면, null값들이 먼저 출력 되는데, null값들을 나중에 출력하기 위해서 nulls last 구문을 사용하면 소트 발생
   2. 인덱스 생성 전

create table emp as select * from scott.emp;
begin
dbms_stats.gather_table_stats(user, 'emp', method_opt => 'for all columns size 1');
end ;
/
EXPLAIN PLAN FOR
select * from emp order by sal;
@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 150391907

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   518 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    14 |   518 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

1. 1. 인덱스 생성 후

CREATE INDEX emp_sal_idx ON emp(sal) ;

SQL> EXPLAIN PLAN FOR
select * from emp order by sal; 

Explained.

SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 150391907

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   518 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    14 |   518 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

1. 1. optimizer_mode 변경 : first_rows

SQL> ALTER SESSION SET optimizer_mode = first_rows ;

Session altered.

SQL> EXPLAIN PLAN FOR
select * from emp order by sal;    

Explained.

SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 150391907

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   518 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    14 |   518 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

1. 1. column 속성 변경 : NOT NULL

SQL> ALTER TABLE emp MODIFY sal NOT NULL ;

Table altered.

SQL> ALTER SESSION SET optimizer_mode = first_rows ;

Session altered.

SQL> EXPLAIN PLAN FOR
select * from emp order by sal;  2  

Explained.

SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3576537612
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |    14 |   518 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP         |    14 |   518 |     2   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | EMP_SAL_IDX |    14 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

1. 인데스가 있어도 sort를 대체하지 못하는 경우
   1. sort 수행

SQL> CREATE INDEX EMP_DEPT_ENAME_IDX ON EMP(DEPTNO, ENAME );

Index created.

SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT /*+ INDEX(E EMP_DEPT_ENAME_IDX) */
       *
FROM   EMP E
WHERE  DEPTNO = 30
ORDER  BY ENAME; 

Execution Plan
--------------------------------------------------------------------------------------------------
Plan hash value: 3593519358

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     5 |   185 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP                |     5 |   185 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_DEPT_ENAME_IDX |     5 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("DEPTNO"=30)
       filter("DEPTNO"=30)

1. 1. null 값이 먼저 출력되도록 하려고 아래처럼 nulls first 구문을 사용하는 순간 실행계획에 sort order by가 다시 나타난다.
      단일 컬럼 인덱스일 때는 null 값을 저장하지 않지만 결합 인덱스일 때는 null 값을 가진 레코드를 맨 뒤쪽에 저장한다.
      따라서 null 값부터 출력하려고 할 때는 인덱스를 이용하더라도 sort가 불가피하다.

SQL> SELECT /*+ INDEX(E EMP_DEPT_ENAME_IDX) */
       *
FROM   EMP E
WHERE  DEPTNO = 30
ORDER  BY ENAME NULLS FIRST;  

Execution Plan
---------------------------------------------------------------------------------------------------
Plan hash value: 669367854

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |     5 |   185 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY               |                    |     5 |   185 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP                |     5 |   185 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_DEPT_ENAME_IDX |     5 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   3 - access("DEPTNO"=30)