[SQL Tuning]인덱스를 사용하지 못하는 사례 6가지와 해결책

인덱스를 사용하지 못하는 사례 6가지와 해결책

인덱스를 사용하지 못하는 사례 6가지와 해결책

• FULL TABLE SCAN
• NO INDEX
• 인덱스가 있지만 범위가 넓어서 FTS이 더 효율적일때
• 인덱스를 쓰면 더 효율적일듯.. BUT 사용 못하는 경우
• 조건식의 부재 --> 문장 수정
• <>(표준) !=
• IS NULL
• 변형

인덱스 사용 불가능 사례 1. 잘못 사용된 조건식 또는 조건식의 부재

SCOTT@orcl2> select ename, rowid from emp where ename = 'SCOTT';

ENAME      ROWID

---------- ------------------

SCOTT      AAAR3dAAEAAAACXAAH

SCOTT@orcl2> select * from emp where rowid  = 'AAAR3dAAEAAAACXAAH';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

SCOTT@orcl2> @xplan

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  bsfk2xkk604ru, child number 0

-------------------------------------

select * from emp where rowid  = 'AAAR3dAAEAAAACXAAH'

Plan hash value: 1116584662

---------------------------------------------------------------------------------------------

| Id  | Operation                  | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |      |      1 |        |      1 |00:00:00.01 |       1 |

|   1 |  TABLE ACCESS BY USER ROWID| EMP  |      1 |      1 |      1 |00:00:00.01 |       1 |

---------------------------------------------------------------------------------------------

SCOTT@orcl2> SET AUTOTRACE ON EXPLAIN

SCOTT@orcl2> select deptno, sum(sal)

             from emp

             group by deptno

             having deptno in (10,20);  

    DEPTNO   SUM(SAL)

---------- ----------

        20      10875

        10       8750

Elapsed: 00:00:00.00

Execution Plan

----------------------------------------------------------

Plan hash value: 2138686577

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |     1 |     7 |     4  (25)| 00:00:01 |

|*  1 |  FILTER             |      |       |       |            |          |

|   2 |   HASH GROUP BY     |      |     1 |     7 |     4  (25)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("DEPTNO"=10 OR "DEPTNO"=20)

SCOTT@orcl2> select deptno, sum(sal)

             from emp

             where deptno in (10,20)

             group by deptno;

             

    DEPTNO   SUM(SAL)

---------- ----------

        20      10875

        10       8750

Elapsed: 00:00:00.00

Execution Plan

----------------------------------------------------------

Plan hash value: 4067220884

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     2 |    14 |     4  (25)| 00:00:01 |

|   1 |  HASH GROUP BY     |      |     2 |    14 |     4  (25)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| EMP  |     9 |    63 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("DEPTNO"=10 OR "DEPTNO"=20)

SCOTT@orcl2> select deptno, sum(sal)

             from emp

             where deptno in (10,20)

             group by deptno

             order by 1;             

             

    DEPTNO   SUM(SAL)

---------- ----------

        10       8750

        20      10875

Elapsed: 00:00:00.00

Execution Plan

----------------------------------------------------------

Plan hash value: 15469362

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     2 |    14 |     4  (25)| 00:00:01 |

|   1 |  SORT GROUP BY     |      |     2 |    14 |     4  (25)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| EMP  |     9 |    63 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("DEPTNO"=10 OR "DEPTNO"=20)

해결법 : 문장의 수정

SCOTT@orcl2> select deptno, sum(sal)

             from emp

             where deptno in (10,20)

             group by deptno;

• 컬럼의 변형( 표현식의 일부로 사용된 컬럼)
• 해결법 : FUNCTION BASED INDEX(재작성, FBI)

인덱스 사용 불가능 사례 2. 컬럼의 변형( 표현식의 일부로 사용된 컬럼)

SCOTT@orcl2> select *

             from emp

             where substr(ename, 2, 1) = 'C';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

Elapsed: 00:00:00.00

Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(SUBSTR("ENAME",2,1)='C')

해결법 FUNCTION BASED INDEX(재작성, FBI)

SCOTT@orcl2> create INDEX emp_ename_fbi ON emp(substr(ename,2,1));

Index created.

SCOTT@orcl2> select *

             from emp

             where substr(ename, 2, 1) = 'C';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

Elapsed: 00:00:00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 846145065

---------------------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |     1 |    38 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP           |     1 |    38 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | EMP_ENAME_FBI |     1 |       |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access(SUBSTR("ENAME",2,1)='C')

SCOTT@orcl2> DROP INDEX emp_ename_fbi ;

함수 기반 인덱스는 Oracle Database 8i부터 생성 가능하며, 계산된 결과를(표현식) 저장하고 있다.

때문에 인덱스를 사용하지 못하는 다양한 상황에서 인덱스를 사용할 수 있도록 도움을 줄 수 있다. 

하지만 추가적으로 생성되는 객체이므로 추가적인 저장 공간이 필요하고, 주기적으로 관리를(통계 수집 및 인덱스 재구성) 해야 하므로 최후의 방법으로 사용해야 한다.

• IS NULL, IS NOT NULL 비교
• IS NOT NULL
• NOT NULL 인 데이터가 소량일때, INDEX 사용이 유리하다.
• IS NULL
• NULL이 아닌 값을 저장하도록 변경한다.

인덱스 사용 불가능 사례 3. IS NULL, IS NOT NULL 비교

-- NULL인 DATA는 INDEX할 수 없다.

USER01@orcl2> select *

              from emp

              where comm IS NULL;

USER01@orcl2> @xplan

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------

SQL_ID  bfwgtcn1c44sp, child number 0

-------------------------------------

select *      from emp      where comm IS NULL

Plan hash value: 3956160932

------------------------------------------------------------------------------------

| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |      1 |        |     10 |00:00:00.01 |       4 |

|*  1 |  TABLE ACCESS FULL| EMP  |      1 |     10 |     10 |00:00:00.01 |       4 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("COMM" IS NULL)

USER01@orcl2> select *

              from emp

              where comm IS NOT NULL;

USER01@orcl2> @xplan

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  a9v1scbz9bpv6, child number 0

-------------------------------------

select *               from emp               where comm IS NOT NULL

Plan hash value: 3349444245

----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |      1 |        |      4 |00:00:00.01 |       4 |      1 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP         |      1 |      4 |      4 |00:00:00.01 |       4 |      1 |

|*  2 |   INDEX FULL SCAN           | EMP_COMM_IX |      1 |      4 |      4 |00:00:00.01 |       2 |      1 |

----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("COMM" IS NOT NULL)

• 잘못 사용된 LIKE 조건식
• LIKE 연산을 할 때는 머리부분이 포함될 때만 IRS 가능
• where ename like 'S%'
• INDEX RANGE SCAN
• where ename like '%TT'
• INDEX FULL SCAN
• ORS 하도록 하려면 REVERSE(ENAM) 인덱스를 생성
• where reverse(ename) like 'TT%' 로 적용
• 참고 : REVERSE KEY INDEX
• 112, 123, 200, 201 이런식으로 데이터가 있다면, 211, 321, 002, 102 이런 형식으로 CHAR형식으로 저장하는 것이다.
• create index ord_rev_idx on ord (order_id) reverse
• select * from ord where order_id=2440 ; : IRS 
• select * from ord where order_id>2440;  : TAF(범위검색 불가능) 

인덱스 사용 불가능 사례 4. 잘못 사용된 LIKE조건식

-- 첫 글자가 정해져있어야 INDEX RANGE 사용

SER01@orcl2> select *

             from emp

             where ename like 'S%';

             

             

USER01@orcl2> @xplan

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  4q9vmffkfkfym, child number 0

-------------------------------------

select *        from emp     where ename like 'S%'

Plan hash value: 80071485

----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |              |      1 |        |      2 |00:00:00.01 |       4 |      1 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP          |      1 |      2 |      2 |00:00:00.01 |       4 |      1 |

|*  2 |   INDEX RANGE SCAN          | EMP_ENAME_IX |      1 |      2 |      2 |00:00:00.01 |       2 |      1 |

----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ENAME" LIKE 'S%')

       filter("ENAME" LIKE 'S%')

SER01@orcl2> select *

             from emp

             where ename like '_%S';

USER01@orcl2> @xplan

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  3ywrb1js09rfy, child number 0

-------------------------------------

select *        from emp     where ename like '_%S'

Plan hash value: 1604200367

----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |              |      1 |        |      3 |00:00:00.01 |       4 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP          |      1 |      1 |      3 |00:00:00.01 |       4 |

|*  2 |   INDEX FULL SCAN           | EMP_ENAME_IX |      1 |      1 |      3 |00:00:00.01 |       2 |

----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(("ENAME" LIKE '_%S' AND "ENAME" IS NOT NULL))

해결법

USER01@orcl2> select order_id, reverse(to_char(order_id)), rowid

              from ord;

ORDER_ID REVERSE(TO_CHAR(ORDER_ID))               ROWID

---------- ---------------------------------------- ------------------

      2458 8542                                     AAASQ6AAJAAAG3TAAA

      2397 7932                                     AAASQ6AAJAAAG3TAAB

        ......

  

      2456 6542                                     AAASQ6AAJAAAG3TABn

      2457 7542                                     AAASQ6AAJAAAG3TABo

USER01@orcl2> create index ord_rev_idx on ord(order_id) reverse;

Index created.

USER01@orcl2> select * from ord where order_id = 2440;

USER01@orcl2> @xplan

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  4tx5umkzwqtp2, child number 0

-------------------------------------

select * from ord where order_id = 2440

Plan hash value: 415761361

----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |      1 |        |      1 |00:00:00.01 |       3 |

|   1 |  TABLE ACCESS BY INDEX ROWID| ORD         |      1 |      1 |      1 |00:00:00.01 |       3 |

|*  2 |   INDEX RANGE SCAN          | ORD_REV_IDX |      1 |      1 |      1 |00:00:00.01 |       2 |

----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ORDER_ID"=2440)

Note

-----

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

USER01@orcl2> select * from ord where order_id > 2440;

USER01@orcl2> @xplan

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  7nwgzs814cxgb, child number 0

-------------------------------------

select * from ord where order_id > 2440

Plan hash value: 624314519

------------------------------------------------------------------------------------

| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |      1 |        |     18 |00:00:00.01 |       4 |

|*  1 |  TABLE ACCESS FULL| ORD  |      1 |     18 |     18 |00:00:00.01 |       4 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("ORDER_ID">2440)

Note

-----

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

• 부정형 비교
• where deptno <>20 : 부정형 비교는 TAF, IFS
• where deptno in(10,30) : 인덱스 가능한 문장으로 재작성 한다. 단, 데이터에 의존

인덱스 사용 불가능 사례 5. 부정형 비교

USER01@orcl2> select * from emp where deptno <>20;

USER01@orcl2> @xplan

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  211t5hpx7anvh, child number 0

-------------------------------------

select * from emp where deptno <>20

Plan hash value: 3956160932

------------------------------------------------------------------------------------

| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |      1 |        |      9 |00:00:00.01 |       4 |

|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      9 |      9 |00:00:00.01 |       4 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("DEPTNO"<>20)

해결법

USER01@orcl2> select * from emp where deptno in (10,30);

USER01@orcl2> SELECT /*+ index(emp(deptno)) */ *

              FROM emp

              WHERE deptno != 20 ;

• 암시적 형변환

인덱스 사용 불가능 사례 6. 암시적 형변환

USER01@orcl2> select * from emp where empno like '77%';

USER01@orcl2> @xplan

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  2rd6yr185fjv2, child number 0

-------------------------------------

select * from emp where empno like '77%'

Plan hash value: 3956160932

------------------------------------------------------------------------------------

| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |      1 |        |      2 |00:00:00.01 |       4 |

|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      1 |      2 |00:00:00.01 |       4 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(TO_CHAR("EMPNO") LIKE '77%')

해결법

USER01@orcl2> select * from emp where empno between 7700 and 7799;

USER01@orcl2> @xplan

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  fa0anu9p8392u, child number 0

-------------------------------------

select * from emp where empno between 7700 and 7799

Plan hash value: 860389208

----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |              |      1 |        |      2 |00:00:00.01 |       4 |      1 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP          |      1 |      4 |      2 |00:00:00.01 |       4 |      1 |

|*  2 |   INDEX RANGE SCAN          | EMP_EMPNO_IX |      1 |      4 |      2 |00:00:00.01 |       2 |      1 |

----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO">=7700 AND "EMPNO"<=7799)