Just trying to help.. Mas Andi.., '/*+ FIRST_ROWS(30) */' itu bukan comment tp itu adalah 'hint', salah satu trik utk tuning query.. first rows (30) itu artinya, dr sekian banyak data yg akan diambil, 30 record pertama diutamakan utk ditampilkan... jd ga usah nunggu semua data di'retrieve' baru ditampilkan.. hmm.. klo mo penjelasan yg lebih lanjut, just read this url : 'http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/hintsref.htm#6283'
cuma mo sharing... buat oracle-ers yg laen.. klo butuh bantuan seputar plsql... bisa dicari disini : 'http://www.oracle.com/pls/db92/db92.homepage' ----- Original Message ---- From: Andi Afandi <[EMAIL PROTECTED]> To: [email protected] Sent: Tuesday, November 27, 2007 8:26:21 AM Subject: [indo-oracle] TOAD Sql Tuner 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] <!-- #ygrp-mkp{ border:1px solid #d8d8d8;font-family:Arial;margin:14px 0px;padding:0px 14px;} #ygrp-mkp hr{ border:1px solid #d8d8d8;} #ygrp-mkp #hd{ color:#628c2a;font-size:85%;font-weight:bold;line-height:122%;margin:10px 0px;} #ygrp-mkp #ads{ margin-bottom:10px;} #ygrp-mkp .ad{ padding:0 0;} #ygrp-mkp .ad a{ color:#0000ff;text-decoration:none;} --> <!-- #ygrp-sponsor #ygrp-lc{ font-family:Arial;} #ygrp-sponsor #ygrp-lc #hd{ margin:10px 0px;font-weight:bold;font-size:78%;line-height:122%;} #ygrp-sponsor #ygrp-lc .ad{ margin-bottom:10px;padding:0 0;} --> <!-- #ygrp-mlmsg {font-size:13px;font-family:arial, helvetica, clean, sans-serif;} #ygrp-mlmsg table {font-size:inherit;font:100%;} #ygrp-mlmsg select, input, textarea {font:99% arial, helvetica, clean, sans-serif;} #ygrp-mlmsg pre, code {font:115% monospace;} #ygrp-mlmsg * {line-height:1.22em;} #ygrp-text{ font-family:Georgia; } #ygrp-text p{ margin:0 0 1em 0;} #ygrp-tpmsgs{ font-family:Arial; clear:both;} #ygrp-vitnav{ padding-top:10px;font-family:Verdana;font-size:77%;margin:0;} #ygrp-vitnav a{ padding:0 1px;} #ygrp-actbar{ clear:both;margin:25px 0;white-space:nowrap;color:#666;text-align:right;} #ygrp-actbar .left{ float:left;white-space:nowrap;} .bld{font-weight:bold;} #ygrp-grft{ font-family:Verdana;font-size:77%;padding:15px 0;} #ygrp-ft{ font-family:verdana;font-size:77%;border-top:1px solid #666; padding:5px 0; } #ygrp-mlmsg #logo{ padding-bottom:10px;} #ygrp-vital{ background-color:#e0ecee;margin-bottom:20px;padding:2px 0 8px 8px;} #ygrp-vital #vithd{ font-size:77%;font-family:Verdana;font-weight:bold;color:#333;text-transform:uppercase;} #ygrp-vital ul{ padding:0;margin:2px 0;} #ygrp-vital ul li{ list-style-type:none;clear:both;border:1px solid #e0ecee; } #ygrp-vital ul li .ct{ font-weight:bold;color:#ff7900;float:right;width:2em;text-align:right;padding-right:.5em;} #ygrp-vital ul li .cat{ font-weight:bold;} #ygrp-vital a{ text-decoration:none;} #ygrp-vital a:hover{ text-decoration:underline;} #ygrp-sponsor #hd{ color:#999;font-size:77%;} #ygrp-sponsor #ov{ padding:6px 13px;background-color:#e0ecee;margin-bottom:20px;} #ygrp-sponsor #ov ul{ padding:0 0 0 8px;margin:0;} #ygrp-sponsor #ov li{ list-style-type:square;padding:6px 0;font-size:77%;} #ygrp-sponsor #ov li a{ text-decoration:none;font-size:130%;} #ygrp-sponsor #nc{ background-color:#eee;margin-bottom:20px;padding:0 8px;} #ygrp-sponsor .ad{ padding:8px 0;} #ygrp-sponsor .ad #hd1{ font-family:Arial;font-weight:bold;color:#628c2a;font-size:100%;line-height:122%;} #ygrp-sponsor .ad a{ text-decoration:none;} #ygrp-sponsor .ad a:hover{ text-decoration:underline;} #ygrp-sponsor .ad p{ margin:0;} o{font-size:0;} .MsoNormal{ margin:0 0 0 0;} #ygrp-text tt{ font-size:120%;} blockquote{margin:0 0 0 4px;} .replbq{margin:4;} --> ____________________________________________________________________________________ Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/ [Non-text portions of this message have been removed]
