[평일주간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 |
---------------------------------------------------------------------------------
복합(결합)인덱스와 오라클 힌트
* 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 |
---------------------------------------------------------------------------------
댓글 없음:
댓글 쓰기