Dear all friends,
Saya menjalankan SQL Tuning Advisor dengan menggunakan TOAD dan hasilnya
ada rekomendasi seperti di bawah berikut perbandingan dgn yang original.
Pertanyaan saya;
Apa bedanya kedua Query tersebut..?
Saya hanya melihat beda pada '/*+ FIRST_ROWS(30) */' di rekomendasinya.
Bukankah itu hanyalah sebuah comment....?
Dilihat dari costnya, yang rekomendasi cuma 7, ini jauh sekali
dibandingkan dgn yang original yang costnya di atas 200.
Apakah dengan mengganti query yang original dgn yang rekomendasi akan benar2
save cost performance.....?
Original query:
---------------
SELECT opl_function.OPL_RoundUp(nvl(dl.fk_sys_curr_deal,
dlDuo.Fk_Sys_Curr_Deal), sumSlv.TOTAL_PAY_AMT) TOTAL_PAY_AMT_RND
FROM
(
SELECT
NVL(FK_DEAL, 0) FK_DEAL,
NVL(FK_DEAL_ITEM, 0) FK_DEAL_ITEM,
PRD,
NTH_TERM,
SUM(PAY_AMT) TOTAL_PAY_AMT
FROM
TB_OP_DEAL_SLV
GROUP BY
FK_DEAL_ITEM,
FK_DEAL,
PRD,
NTH_TERM
) sumSlv
left join TB_OP_DEAL_ITEM itm
on sumSlv.fk_deal_item = itm.pk_deal_item
left join TB_OP_DEAL dl
on itm.fk_deal = dl.pk_deal
left join TB_OP_DEAL dlDuo
on sumSlv.fk_deal = dlDuo.Pk_Deal
Recommended query:
------------------
SELECT /*+ FIRST_ROWS(30) */
opl_function.OPL_RoundUp(nvl(dl.fk_sys_curr_deal, dlDuo.Fk_Sys_Curr_Deal),
sumSlv.TOTAL_PAY_AMT) TOTAL_PAY_AMT_RND
FROM
(
SELECT
NVL(FK_DEAL, 0) FK_DEAL,
NVL(FK_DEAL_ITEM, 0) FK_DEAL_ITEM,
PRD,
NTH_TERM,
SUM(PAY_AMT) TOTAL_PAY_AMT
FROM TB_OP_DEAL_SLV
GROUP BY
FK_DEAL_ITEM,
FK_DEAL,
PRD,
NTH_TERM
) sumSlv
left join TB_OP_DEAL_ITEM itm
on sumSlv.fk_deal_item = itm.pk_deal_item
left join TB_OP_DEAL dl
on itm.fk_deal = dl.pk_deal
left join TB_OP_DEAL dlDuo
on sumSlv.fk_deal = dlDuo.Pk_Deal
Please do not refer to any URL....
Thank you guys...
Afandi
---------------------------------
Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now.
[Non-text portions of this message have been removed]
