인덱스가 실행이 안되는 것 같아서 요청 드려 봅니다.

by 취우다 posted Apr 15, 2017


-------------- 아래에 질문 사항을 기입해 주세요. ------------------------------------------------------------------------

인덱스가 실행이 안되는 것 같아서 요청 드려 봅니다. skip order by 가 안되네요 ㅠㅠ


<생성 index>

CREATE INDEX IDX_WORK_BOOK_TB_ORDER ON WORK_BOOK_TB(INST_CD DESC , WORKER_TEAM DESC, TASK_SPECIFIC  DESC );



<select sql>

1번 )

SELECT   *
FROM WORK_BOOK_TB
ORDER BY  INST_CD DESC , WORKER_TEAM DESC, TASK_SPECIFIC  DESC
LIMIT 20


2번 )

SELECT   *
FROM WORK_BOOK_TB USE  INDEX (IDX_WORK_BOOK_TB_ORDER)
ORDER BY  INST_CD DESC , WORKER_TEAM DESC, TASK_SPECIFIC  DESC
LIMIT 20


3번)

SELECT   *
FROM WORK_BOOK_TB
USING INDEX IDX_WORK_BOOK_TB_ORDER
ORDER BY  INST_CD DESC , WORKER_TEAM DESC, TASK_SPECIFIC  DESC
LIMIT 20


==> 뭘해도 소요 시간 2.13초




--------------------------------------------------
SHOW TRACE;
--------------------------------------------------

Query Plan:
  SORT (order by)
    TABLE SCAN (WORK_BOOK_TB)

  rewritten query: select WORK_BOOK_TB.workbook_seq, WORK_BOOK_TB.workbook_ymd, WORK_BOOK_TB.workbook_ord, WORK_BOOK_TB.worker_nm, WORK_BOOK_TB.worker_position, WORK_BOOK_TB.worker_class, WORK_BOOK_TB.worker_team, WORK_BOOK_TB.inst_cd, WORK_BOOK_TB.task_specific, WORK_BOOK_TB.task_common, WORK_BOOK_TB.task_common_specific, WORK_BOOK_TB.office_work_div, WORK_BOOK_TB.work_times, WORK_BOOK_TB.time_required, WORK_BOOK_TB.self_appraisal, WORK_BOOK_TB.add_working, WORK_BOOK_TB.reg_dt, WORK_BOOK_TB.reg_id, WORK_BOOK_TB.updt_dt, WORK_BOOK_TB.del_yn, WORK_BOOK_TB.del_dt from WORK_BOOK_TB WORK_BOOK_TB order by 8 desc , 7 desc , 9 desc  for orderby_num()<= ?:0

Trace Statistics:
  SELECT (time: 2131, fetch: 5705, ioread: 0)
    SCAN (table: work_book_tb), (heap time: 1195, fetch: 5700, ioread: 0, readrows: 313631, rows: 313631)
    ORDERBY (time: 0, topnsort: true)






Articles