싱글 인덱스와 결합 인덱스 쿼리 속도차이

SQL> CREATE INDEX I_EMP_DEPTNO ON BIG_EMP(DEPTNO);

인덱스가 생성되었습니다.

 SQL> CREATE INDEX I_EMP_JOB ON BIG_EMP(JOB);

인덱스가 생성되었습니다.

SQL> SELECT * FROM BIG_EMP
  2  WHERE DEPTNO =10 AND JOB='CLERK';

 

싱글 인덱스

SELECT * FROM BIG_EMP
WHERE DEPTNO =10 AND JOB='CLERK'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       78      0.03       0.20         36      10934          0        1154
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       80      0.03       0.22         36      10934          0        1154

Misses in library cache during parse: 1
Optimizer mode: RULE
Parsing user id: 61  (SCOTT)

Rows     Row Source Operation
-------  ---------------------------------------------------
   1154  TABLE ACCESS BY INDEX ROWID BIG_EMP (cr=10934 pr=36 pw=0 time=3268162 us)
   1154   AND-EQUAL  (cr=10728 pr=36 pw=0 time=4305820 us)
   4367    INDEX RANGE SCAN I_EMP_DEPTNO (cr=6501 pr=12 pw=0 time=59344 us)(object id 52636)
   3223    INDEX RANGE SCAN I_EMP_JOB (cr=4227 pr=24 pw=0 time=10544806 us)(object id 52637)

 

결합인덱스

  

SQL> ALTER SESSION SET OPTIMIZER_MODE=RULE;

세션이 변경되었습니다.

SQL> ALTER SESSION SET SQL_TRACE=TRUE;

세션이 변경되었습니다.

SQL> CREATE INDEX I_EMP_DEPTNO_JOB ON BIG_EMP(DEPTNO,JOB);

인덱스가 생성되었습니다.

SQL> SELECT * FROM BIG_EMP
  2  WHERE DEPTNO =10 AND JOB='CLERK';

 

SELECT * FROM BIG_EMP
WHERE DEPTNO =10 AND JOB='CLERK'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       78      0.01       0.03          5        288          0        1154
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       80      0.01       0.04          5        288          0        1154

Misses in library cache during parse: 1
Optimizer mode: RULE
Parsing user id: 61  (SCOTT)

Rows     Row Source Operation
-------  ---------------------------------------------------
   1154  TABLE ACCESS BY INDEX ROWID BIG_EMP (cr=288 pr=5 pw=0 time=35076 us)
   1154   INDEX RANGE SCAN I_EMP_DEPTNO_JOB (cr=82 pr=5 pw=0 time=25813 us)(object id 52639)

모든 경우에서 결합인덱스의 속도가 좋은건 아니니 무조건 적으로 사용은 안됨