SELECT TO_CHAR(TO_DATE(SALE_DTE,'YYYYMMDD'),'YYYY-MM-DD') SALE_DTE, CUST_NME, ITEM_NME,
(ITEM_THI_VAL||'*'||ITEM_HEI_VAL||'*'||ITEM_HOR_VAL||'*'||ITEM_VER_VAL) STANDARD,
TO_CHAR(SALE_PRC,'999,999') SALE_PRC, SALE_QTY, TO_CHAR(SALE_AMT_TOT,'999,999,999') SALE_AMT_TOT
FROM MKT0TB015 A, MKT0TB060 B, MST0TB002 C
WHERE A.ITEM_NBR = B.ITEM_NBR
AND A.CUST_NBR = C.CUST_NBR
ORDER BY 1, 2 DESC
ORDER BY 안하면 0.4초 걸리는데 ORDER BY 하면 5초나 걸리네요. 속도좀 빠르게 하는방법 없나요? 인덱스가 SALE_DTE에 있던데 아직 튜닝은 배우지를 않아서.. 모르겠네요.. SALE_DTE 컬럼의 인덱스이름은 SYS_C0012496 입니다..
SELECT a.rn , TO_CHAR(TO_DATE(a.sale_dte, 'yyyymmdd'), 'yyyy-mm-dd') sale_dte , c.cust_nme , b.item_nme , a.standard , a.sale_prc , a.sale_qty , a.sale_amt_tot FROM (SELECT /*+ INDEX_DESC(a sys_c0012496) */ ROW_NUMBER() OVER(ORDER BY sale_dte DESC) rn , sale_dte , item_thi_val||'*'||item_hei_val||'*'||item_hor_val||'*'||item_ver_val standard , TO_CHAR(sale_prc, '999,999') sale_prc , sale_qty , TO_CHAR(sale_amt_tot, '999,999,999') sale_amt_tot , item_nbr , cust_nbr FROM mkt0tb015 a WHERE sale_dte IS NOT NULL ) a , mkt0tb060 b , mst0tb002 c WHERE a.item_nbr = b.item_nbr AND a.cust_nbr = c.cust_nbr AND rn >= (:page_numb - 1) * :page_size + 1 AND rn <= (:page_numb ) * :page_size ORDER BY rn ;