-------------- 아래에 질문 사항을 기입해 주세요. ------------------------------------------------------------------------
인덱스가 실행이 안되는 것 같아서 요청 드려 봅니다. 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)
안녕하세요.
27만건 테이블로 동일 유형으로 테스트를 진행했습니다.
SELECT /*+ RECOMPILE */
*
FROM
tn_mber_back
USE INDEX (idx_tn_mber_back_01)
ORDER BY
unique_id DESC, mber_id desc,mber_nm desc
LIMIT 20;
Query Plan:
INDEX SCAN (tn_mber_back.idx_tn_mber_back_01) ()
skip order by: true
rewritten query: select tn_mber_back.unique_id, tn_mber_back.mber_id, tn_mber_back.[password], tn_mber_back.mber_nm, tn_mber_back.virtl_idntfc_code, tn_mber_back.dplct_cnfirm_code, tn_mber_back.brthdy, tn_mber_back.sexdstn, tn_mber_back.telno, tn_mber_back.email, tn_mber_back.adres, tn_mber_back.pbsvnt_at, tn_mber_back.child_at, tn_mber_back.frgnr_at, tn_mber_back.email_acnt_reqst_at, tn_mber_back.drmncy_at, tn_mber_back.regist_ip, tn_mber_back.updt_ip, tn_mber_back.sbscrb_dt, tn_mber_back.updt_dt, tn_mber_back.password_updt_dt, tn_mber_back.password_extn_at, tn_mber_back.mntr_staff_group, tn_mber_back.water_at, tn_mber_back.accr, tn_mber_back.login_fail_cnt from tn_mber_back tn_mber_back using index tn_mber_back.idx_tn_mber_back_01 order by 1 desc , 2 desc , 4 desc for orderby_num()<= ?:0
Trace Statistics:
SELECT (time: 0, fetch: 24, ioread: 0)
SCAN (index: tn_mber_back.idx_tn_mber_back_01), (btree time: 0, fetch: 23, ioread: 0, readkeys: 20, filteredkeys: 20, rows: 20) (lookup time: 0, rows: 20)
인덱스 생성 후 통계정보를 갱신해 보시고 /*+ RECOMPILE */ 옵션을 추가 후 수행해 보시길 바랍니다.
UPDATE STATISTICS ON WORK_BOOK_TB WITH FULLSCAN;
그리고, 해당 테이블의 구조(DDL 또는 컬럼 Layout 정보) 정보를 보내주시길 바랍니다.
UPDATE STATISTICS ON WORK_BOOK_TB WITH FULLSCAN;