-------------- 아래에 질문 사항을 기입해 주세요. ------------------------------------------------------------------------
인덱스가 실행이 안되는 것 같아서 요청 드려 봅니다. 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)