1. HOME
  2. [종료]구루비 DB 스터디
  3. 2013 상반기 DB멘토링 스터디
  4. 인덱스를 이용한 소트 연산 대체

인덱스를 이용한 소트 연산 대체 (by niceboyous) [2013.01.29]

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

  • 인덱스는 항상 키 컬럼순으로 정렬된 상태를 유지, 소트를 수행하지 않고도 인덱스를 이용해 Unique한 집합을 출력할 수 있다.

h3.1) sort order by 


- test를 위한 table 생성 - 

CREATE TABLE customer
AS
SELECT LEVEL custid
     , CHR(64 + CEIL(LEVEL / 100)) region
     , dbms_random.string('U', 4) name
     , ROUND(dbms_random.value(10, 70)) age
FROM dual
CONNECT BY LEVEL <= 30;



- 단일 컬럼 인덱스 생성 -

CREATE INDEX customer_x01 ON customer (region);

- region 단일 인덱스 이용 select -

set pagesize 9999 line 400
col name for a10
SELECT * FROM customer
WHERE region = 'A'
ORDER BY custid;


Execution Plan
----------------------------------------------------------
Plan hash value: 3806818772

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

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

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

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         12  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
       1425  bytes sent via SQL*Net to client
        530  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         30  rows processed

h3.- sort order by 제거를 위한 결합인덱스 생성 -

  • 결합 인덱스 생성으로 인하여 sort order by operation 제거됨 


- 결합 인덱스 생성 -
CREATE INDEX customer_x02 ON customer(region, custid);

set pagesize 9999 line 400
col name for a10
SELECT * FROM customer
WHERE region = 'A'
ORDER BY custid;


Execution Plan
----------------------------------------------------------
Plan hash value: 2477059019

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |    30 | 60930 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMER     |    30 | 60930 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | CUSTOMER_X02 |    30 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

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

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         11  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
       1495  bytes sent via SQL*Net to client
        530  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         30  rows processed
  • sort 해야할 대상 레코드가 많고, 그중 일부만 읽고 멈출 수 있는 업무에서만 유리
  • 단, 대상 레코드가 소량일 때는 sort order by 가 발생해도 부하가 그리 크지 않다.

h3.2) Sort Group By 대체



create index customer_x01 on customer (region);

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

RE   AVG(AGE)   COUNT(*)
-- ---------- ----------
A  41.0666667         30


Execution Plan
----------------------------------------------------------
Plan hash value: 3656427734

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     5 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |          |     1 |     5 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| CUSTOMER |    30 |   150 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        688  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
  • 실행계획에서 SORT GROUP BY NOSORT가 안되고 HASH GROUP BY를 타게 됨

h6.원인 :
옵티마이저 모드가 all_rows 이면 옵티마이저는 DATA량이 많을수록 인덱스를 이용한 테이블 랜덤 엑세스 비용보다
full table scan하는 쪽을 택할 가능성이 높아진다

옵티마이저 모드를 first_rows 변경

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


RE   AVG(AGE)   COUNT(*)
-- ---------- ----------
A  41.0666667         30


Execution Plan
----------------------------------------------------------
Plan hash value: 3656427734

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     5 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |          |     1 |     5 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| CUSTOMER |    30 |   150 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        688  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

h6.원인 :
not null 제약 조건이 정의 되어있지 않아서....
단일 컬럼 인덱스일 때 컬럼 값이 null이면 인덱스 레코드에 포함되지 않는다. 따라서
인덱스르 이용해 정렬작업을 대체하게 되면 결과에 오류가 발생할 수 있어서 옵티마이저는 SORT GROUP BY NOSORT로 대체하지 않는다.



옵티마이저 모드를 first_rows 변경
is not null 조건 추가


select /*+ first_rows */ region, avg(age), count(*)
from customer
where region is not null
group by region;


RE   AVG(AGE)   COUNT(*)
-- ---------- ----------
A  41.0666667         30


Execution Plan
----------------------------------------------------------
Plan hash value: 1761651907

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |     5 |     2   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT        |              |     1 |     5 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMER     |    30 |   150 |     2   (0)| 00:00:01 |
|*  3 |    INDEX FULL SCAN           | CUSTOMER_X01 |    30 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   3 - filter("REGION" IS NOT NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        688  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

그 외 인덱스가 있는데도 sort를 대체하지 못하는 예

h6.- 정상적인으로 인덱스를 사용했을때


create index emp_deptno_ename_idx on emp(deptno, ename);

select * 
from emp
where deptno=30
order by ename ;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500                    30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3268462453

----------------------------------------------------------------------------------------------------
| 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_DEPTNO_ENAME_IDX |     5 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - access("DEPTNO"=30)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1299  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed
- 결합 인덱스 일 경우 단일 컬럼 인덱스와 다르게 null값을 갖는 레코드를 맨 뒤쪽에 저장하여, null값 부터 출력 하고자 할 때 sort 발생된다.

h6.- null값이 먼저 출력 되도록 nulls first 사용시 sort order by 발생


select * 
from emp
where deptno=30
order by ename nulls first;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500                    30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4293037890

-----------------------------------------------------------------------------------------------------
| 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_DEPTNO_ENAME_IDX |     5 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   3 - access("DEPTNO"=30)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
       1248  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          6  rows processed
  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2013 상반기 DB멘토링 스터디
  4. 인덱스를 이용한 소트 연산 대체