2016년 7월 19일 화요일

결합(복합)인덱스와 오라클힌트[ORACLE HINT강좌]

[평일주간SQL힌트,튜닝)]복합(결합)인덱스와 오라클 힌트예제 실습하시고 잘 이해해 보세요~

복합(결합)인덱스와 오라클 힌트

* INDEX 힌트에 인덱스명이 아닌 칼럼이 출현하면 해당 칼럼을 포함하고 있는 인덱스를
이용하라는 의미이다.

-- 실습 테이블 생성
CREATE TABLE INDEXTEST (
  A1 NUMBER NOT NULL,
  A2 NUMBER NOT NULL,
  A3 VARCHAR2(50) NOT NULL,
  A4 VARCHAR2(100));

-- 100만건 생성 
INSERT INTO INDEXTEST
SELECT
  MOD(ROWNUM-1, 90) * 4 A1,
  ROWNUM - 1 A2,
  TO_CHAR(ROWNUM - 1, 'RN') A3,
  LPAD('A',100,'A') A4
FROM
  DUAL
CONNECT BY
  LEVEL<=1000000;
 
COMMIT; 
 
-- 실습을 위한 인덱스 생성
CREATE INDEX IDX_IT_1_2 ON INDEXTEST(A1,A2);
CREATE INDEX IDX_IT_2_1_3 ON INDEXTEST(A2,A1,A3);
CREATE INDEX IDX_IT_3_1_2 ON INDEXTEST(A3,A1,A2);

-- INDEXTEST 테이블의 통계정보 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'INDEXTEST',CASCADE=>TRUE)

-- 먼저 힌트를 사용하지 않은 쿼리를 보자.
-- Full TableScan을 하지않고 A2, A1, A3 복합 인덱스를 사용한다.
SELECT A1, A2, A3 FROM INDEXTEST
-------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |              |  1000K|    23M|  1336  (1)| 00:00:17 |
|  1 |  INDEX FAST FULL SCAN| IDX_IT_2_1_3 |  1000K|    23M|  1336  (1)| 00:00:17 |
-------------------------------------------------------------------------------------

-- 이번에는 A1, A2 칼럼에 있는 인덱스를 사용하라는 힌트를 줘보자.
-- A1, A2 복합인덱스를 이용한다.
SELECT /*+ index(INDEXTEST (A1, A2)) */ A1, A2, A3 FROM INDEXTEST;

------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |            |  1000K|    23M|  1003K  (1)| 03:20:40 |
|  1 |  TABLE ACCESS BY INDEX ROWID| INDEXTEST  |  1000K|    23M|  1003K  (1)| 03:20:40 |
|  2 |  INDEX FULL SCAN          | IDX_IT_1_2 |  1000K|      |  2755  (1)| 00:00:34 |
------------------------------------------------------------------------------------------

-- 이번엔 A2, A1 칼럼 인덱스를 사용하라는 힌트를 줘보자.
SELECT /*+ index(INDEXTEST (A2, A1)) */ A1, A2, A3 FROM INDEXTEST;

---------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT |              |  1000K|    23M|  4905  (1)| 00:00:59 |
|  1 |  INDEX FULL SCAN | IDX_IT_2_1_3 |  1000K|    23M|  4905  (1)| 00:00:59 |
---------------------------------------------------------------------------------

힌트를 사용하지 않은 쿼리보다 COST가 더 높게 나온다.

-- 이번엔 A3, A1, A2 칼럼 인덱스를 사용하라는 힌트를 줘보자.
-- A3, A1, A2 복합인덱스를 이용한다.
SELECT /*+ index(INDEXTEST (A3, A1, A2)) */ A1, A2, A3 FROM INDEXTEST;

---------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT |              |  1000K|    23M|  5188  (1)| 00:01:03 |
|  1 |  INDEX FULL SCAN | IDX_IT_3_1_2 |  1000K|    23M|  5188  (1)| 00:01:03 |
---------------------------------------------------------------------------------

-- 이번에는 A1, A2 칼럼 인덱스를 사용하는데 A2 칼럼에 조건을 줘보자.
-- 힌트구안에 A1, A2가 기술되어 있으므로 IDX_IT_1_2 인덱스를 이용하여
-- A1칼럼을 SKIP하면서 A2조건을 확인하는 Index skip Scanning 연산을 한다.
SELECT /*+ index(INDEXTEST (A1, A2)) */ A1, A2, A3 FROM INDEXTEST
WHERE A2 < 10;

------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |            |    10 |  250 |  103  (0)| 00:00:02 |
|  1 |  TABLE ACCESS BY INDEX ROWID| INDEXTEST  |    10 |  250 |  103  (0)| 00:00:02 |
|*  2 |  INDEX SKIP SCAN          | IDX_IT_1_2 |    10 |      |    92  (0)| 00:00:02 |
------------------------------------------------------------------------------------------

-- 이번엔 A1, A2, A3 칼럼의 인덱스를 사용하라는 힌트를 줘보자.
-- IDX_IT_1_2 인덱스에는 A3 칼럼이 없으므로 사용하지 않고 IDX_IT_2_1_3 인덱스를 사용했다.
SELECT /*+ index(INDEXTEST (A1, A2, A3)) */ A1, A2, A3 FROM INDEXTEST
WHERE A2 < 10;

---------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT |              |    10 |  250 |    3  (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_IT_2_1_3 |    10 |  250 |    3  (0)| 00:00:01 |
---------------------------------------------------------------------------------


-- A1, A2, A3순으로 인덱스를 생성하자.
-- A2 칼럼으로 WHERE절에 비교했지만 A3칼럼이 SELECT 리스트에 있고 IDX_IT_1_2_3인덱스가
-- 있으므로 이 인덱스를 이용하여 SKIP SCANNING 한다.
CREATE INDEX IDX_IT_1_2_3 ON INDEXTEST(A1,A2,A3);

SELECT /*+ INDEX(INDEXTEST (A1 A2 A3)) */ A1,A2,A3 FROM INDEXTEST
WHERE A2 < 10;
---------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT |              |    10 |  250 |    92  (0)| 00:00:02 |
|*  1 |  INDEX SKIP SCAN | IDX_IT_1_2_3 |    10 |  250 |    92  (0)| 00:00:02 |
---------------------------------------------------------------------------------

-- 아래의 경우 A1, A2 칼럼의 인덱스를 쓰라는 힌트인데
-- A3 칼럼 때문에 TABLE ACCESS(BY INDEX ROWID)연산을 한다.
SELECT /*+ INDEX(INDEXTEST (A1 A2)) */ A1,A2,A3 FROM INDEXTEST
WHERE A2 < 10;

------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |            |    10 |  250 |  103  (0)| 00:00:02 |
|  1 |  TABLE ACCESS BY INDEX ROWID| INDEXTEST  |    10 |  250 |  103  (0)| 00:00:02 |
|*  2 |  INDEX SKIP SCAN          | IDX_IT_1_2 |    10 |      |    92  (0)| 00:00:02 |
------------------------------------------------------------------------------------------

-- 아래의 경우 A1 칼럼의 인덱스를 쓰라는 힌트인데
-- WHERE절에 A2 칼럼이 출현했으므로 가능하면 A1이 선두칼럼에 있는 인덱스를 선택할 것이고,
-- A3 칼럼이 SELECT 리스트에 출현했으므로 IDX_IT_1_2_3 인덱스를 사용한다.
SELECT /*+ INDEX(INDEXTEST (A1)) */ A1,A2,A3 FROM INDEXTEST
WHERE A2 < 10;

---------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT |              |    10 |  250 |    92  (0)| 00:00:02 |
|*  1 |  INDEX SKIP SCAN | IDX_IT_1_2_3 |    10 |  250 |    92  (0)| 00:00:02 |
--------------------------------------------------------------------------------- 

댓글 없음:

댓글 쓰기