1-(1). 인덱스 튜닝

0. 환경 Setting - 테이블 생성

ALTER SESSION SET WORKAREA_SIZE_POLICY=MANUAL;
ALTER SESSION SET SORT_AREA_SIZE = 1000000000;

-- DROP TABLE SCOTT.S_CUST22;
CREATE TABLE SCOTT.S_CUST22
  (CUST_NO       VARCHAR2(7),
   CUS_NM        VARCHAR2(50),
   CUST_CD       VARCHAR2(3),
   FLAG          VARCHAR2(3),
   DIV          VARCHAR2(2),
   C1            VARCHAR2(30),
   C2            VARCHAR2(30),
   C3            VARCHAR2(30),
   C4            VARCHAR2(30),
   C5            VARCHAR2(30),
   CONSTRAINT PK_S_CUST22 PRIMARY KEY (CUST_NO)
  );

CREATE OR REPLACE PUBLIC SYNONYM S_CUST22 FOR SCOTT.S_CUST22;

INSERT /*+ APPEND */ INTO S_CUST22
SELECT LPAD(TO_CHAR(ROWNUM), 7, '0')                                    CUST_NO
     , RPAD(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1, 65000))), 10, '0')       CUS_NM
     , LPAD(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1, 2000))) || '0', 3, '0')  CUST_CD
     , LPAD(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1, 100))) || '0', 3, '0')   FLAG
     , LPAD(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1, 100)))  || '0', 2, '0')  DIV
     , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'                                     C1
     , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'                                     C2
     , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'                                     C3
     , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'                                     C4
     , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'                                     C5
FROM DUAL
CONNECT BY LEVEL <= 2000000
ORDER BY DBMS_RANDOM.RANDOM()
;

COMMIT;

* T_CUST22 200만건 2,000,000

- CUST_CD 2000개 종류(0001 ~ 2000), 코드당 건수는 약 1만건

- DIV 100개 종류(001 ~ 100), 코드당 건수는 약 2만건

- FLAG 100개 종류, 코드당 건수는 약 20만건

- PRIMARY KEY : CUST_NO

- Distinct Count : CUST_CD > DIV > FLAG

1. 인덱스 설정 - (CUST_CD, FLAG, DIV)

CREATE INDEX SCOTT.IX_S_CUST22_01 ON SCOTT.S_CUST22(CUST_CD, FLAG, DIV);

- 통계 정보 확인 * 오류 발생 - [SQL Error [900] [42000]: ORA-00900: SQL 문이 부적합합니다.]

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'S_CUST22');

(+) EXECUTE 가 실행되지 않아 BEGIN ~ END; 사용 - https://moominie.tistory.com/48

-- 통계 정보 설정 (EXECUTE -> BEGIN ~ END)
BEGIN DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'S_CUST22');
END; 

ALTER SESSION SET WORKAREA_SIZE_POLICY=AUTO;

2. 확인하고자 하는 쿼리

--sh index1
SELECT /*+ gather_plan_statistics */ * 
FROM S_CUST22 
WHERE  CUST_CD BETWEEN '190' AND '200' 
AND   DIV IN ('30', '40', '50', '60', '20')
AND   FLAG = '160';

2-(1). 확인하고자 하는 쿼리 ID 찾기

SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER
FROM   V$SQL 
WHERE  SQL_TEXT LIKE '--sh index1%'
ORDER BY LAST_ACTIVE_TIME DESC;

2-(2). 쿼리 실행계획 확인 - DBMS_XPLAN.DISPLAY_CURSOR

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dutvb1xyrj5n2', NULL, 'ALLSTATS LAST'));

INDEX SKIP SCAN

3. 실행 계획

3-(1) 인덱스 설정 - (DIV, FLAG, CUST_CD)

CREATE INDEX SCOTT.IX_S_CUST22_02 ON SCOTT.S_CUST22(DIV, FLAG, CUST_CD);

--sh index2
SELECT /*+ gather_plan_statistics */ * 
FROM S_CUST22 
WHERE  CUST_CD BETWEEN '190' AND '200' 
AND   DIV IN ('30', '40', '50', '60', '20')
AND   FLAG = '160';

SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER
FROM   V$SQL 
WHERE  SQL_TEXT LIKE '--sh index2%'
ORDER BY LAST_ACTIVE_TIME DESC;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5w8jkmzrpw8bu',NULL, 'ALLSTATS LAST'));

INDEX RANGE SCAN

3- (2) 인덱스 설정 - (FLAG, DIV, CUST_CD)

CREATE INDEX SCOTT.IX_S_CUST22_03 ON SCOTT.S_CUST22(FLAG, DIV, CUST_CD);

--sh index3
SELECT /*+ gather_plan_statistics */ * 
FROM S_CUST22
WHERE  CUST_CD BETWEEN '190' AND '200' 
AND   DIV IN ('30', '40', '50', '60', '20')
AND   FLAG = '160';


SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER
FROM   V$SQL 
WHERE  SQL_TEXT LIKE '--sh index3%'
ORDER BY LAST_ACTIVE_TIME DESC;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1kytkpa4y5jfq',NULL, 'ALLSTATS LAST'));

INDEX RANGE SCAN

3- (3) 인덱스 설정 - (FLAG, CUST_CD, DIV)

CREATE INDEX SCOTT.IX_S_CUST22_04 ON SCOTT.S_CUST22(FLAG, CUST_CD, DIV);

--sh index4
SELECT /*+ gather_plan_statistics */ * 
FROM S_CUST22
WHERE CUST_CD BETWEEN '190' AND '200'
AND DIV IN ('30', '40', '50', '60', '20')
AND FLAG = '160';

SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER
FROM   V$SQL 
WHERE  SQL_TEXT LIKE '--sh index4%'
ORDER BY LAST_ACTIVE_TIME DESC;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('6xbkvxp309qz6',NULL, 'ALLSTATS LAST'));

INDEX RANGE SCAN

4. SQL 튜닝

INDEX ID	IX_1	IX_2	IX_ 3	👍 IX_ 4
인덱스 순서	CUST_CD, FLAG, DIV	DIV, FLAG, CUST_CD	FLAG, DIV, CUST_CD	FLAG, CUST_CD, DIV
조건	BETWEEN, =, IN	IN, =, BETWEEN	=, IN, BETWEEN	=, BETWEEN, IN
인덱스 매칭도	D, C, C [1]	D, D, D [3]	D, D, D [3]	D, D, C [2]
스캔 방식	INDEX SKIP SCAN	INDEX RANGE SCAN - INLIST ITERATOR	INDEX RANGE SCAN - INLIST ITERATOR	INDEX RANGE SCAN
스캔 순서	2-1-0	3-2-1-0	3-2-1-0	2-1-0
Buffers (Logical Read)	133	112	111	105