SQL性能优化（hint）

[Dota_noney]

select *
from (select rownum as rownum_alias, main_sql.*
from (SELECT /*+ DRIVING_SITE(torihiki,kikan,tantou) */ (SELECT COUNT(1)
FROM T_ORDER T1,
T_ODR_DETAIL T2,
(SELECT TRHKSK_KHN_CD,
TRHKSK_KHN_MI,
SIYU_KIS_NNGPP,
SIYU_TIS_NNGPP
FROM TR_TRHKSK_KHN
WHERE RNR_SKZY_FLG = '0') torihiki,
(SELECT KKCD,
KIKIKKN_RYKSYU,
KIKI_SIYU_KIS_NNGPP,
KIKI_SIYU_TIS_NNGPP
FROM TR_KIKIKKN
WHERE RNR_SKZY_FLG = '0'
AND KIKI_YUT_SYBT = '1') kikan,
(SELECT KTCD,
KKCD,
KIKITNTU_RYKSYU,
KIKI_SIYU_KIS_NNGPP,
KIKI_SIYU_TIS_NNGPP
FROM TR_KIKITNTU
WHERE RNR_SKZY_FLG = '0'
AND KIKI_YUT_SYBT = '1') tantou,
T_STAFF_BOOK_MANAGEMENT T3
WHERE T1.KTYPEDAIKUBUN = '5'
AND torihiki.SIYU_KIS_NNGPP(+) <= RPAD('20120726', 8, ' ')
AND torihiki.SIYU_TIS_NNGPP(+) >= RPAD('20120726', 8, ' ')
AND kikan.KIKI_SIYU_KIS_NNGPP(+) <= RPAD('20120726', 8, ' ')
AND kikan.KIKI_SIYU_TIS_NNGPP(+) >= RPAD('20120726', 8, ' ')
AND tantou.KIKI_SIYU_KIS_NNGPP(+) <= RPAD('20120726', 8, ' ')
AND tantou.KIKI_SIYU_TIS_NNGPP(+) >= RPAD('20120726', 8, ' ')
AND T1.KKEITAICD = '3'
AND T1.CST = '330'
AND T1.RECSTKUBUN IN ('1', '2')
AND LAST_DAY(ADD_MONTHS(TO_DATE(T1.KTO, 'YYYY-MM-DD'), 3)) >=
TO_DATE('20120726', 'YYYY-MM-DD')
AND T1.CNO = T2.CNO
AND T1.CHANSU = T2.CHANSU
AND T2.HKAISHACD = 'Z00000'
AND T2.HSOSHIKICD = tantou.KKCD(+)
AND T2.HBUKACD = tantou.KTCD(+)
AND T2.HSOSHIKICD = kikan.KKCD(+)
AND T1.CNO = T3.CNO(+)
AND T1.CHANSU = T3.CHANSU(+)
AND T1.KDNO = T3.KDNO(+)
AND T1.KHANSU = T3.KHANSU(+)
AND T1.SCD = torihiki.TRHKSK_KHN_CD(+)
AND T2.HSOSHIKICD = '7200'
AND T2.HBUKACD = '001600'
AND (T2.HSEIID = '1KC1H11' OR T2.HFUKUID = '1KC1H11' OR T2.SHIKIID = '1KC1H11' OR
T2.KUJOID = '1KC1H11')) AS TCOUNT,
T1.KDNO,
T1.KHANSU,
T1.CNO,
T1.CHANSU,
T2.TDNO,
T2.CMHANSU,
kikan.KIKIKKN_RYKSYU as KIKIKKN_RYKSYU,
tantou.KIKITNTU_RYKSYU as KIKITNTU_RYKSYU,
T1.KKENMEI as KKENMEI,
T1.KFROM as KFROM,
T1.KTO as KTO,
T1.EXCLUSIVE_COUNTER as EXCLUSIVE_COUNTER,
torihiki.TRHKSK_KHN_MI as TRHKSK_KHN_MI,
T3.HSMEI as HSMEI,
(CASE
WHEN 0 IN (SELECT COUNT(CHOHYOID)
FROM T_REPORT T4
WHERE T1.KDNO = T4.KDNO
AND T1.CNO = T4.CNO
AND T1.CHANSU = T4.CHANSU
AND T2.TDNO = T4.KIHONTDNO
AND T2.CMHANSU = T4.KIHONTDHANSU
AND T4.CHOHYOTYPECD = 'PR08') THEN
0
ELSE
1
END) PDFFLG
FROM T_ORDER T1,
T_ODR_DETAIL T2,
(SELECT TRHKSK_KHN_CD,
TRHKSK_KHN_MI,
SIYU_KIS_NNGPP,
SIYU_TIS_NNGPP
FROM TR_TRHKSK_KHN
WHERE RNR_SKZY_FLG = '0') torihiki,
(SELECT KKCD,
KIKIKKN_RYKSYU,
KIKI_SIYU_KIS_NNGPP,
KIKI_SIYU_TIS_NNGPP
FROM TR_KIKIKKN
WHERE RNR_SKZY_FLG = '0'
AND KIKI_YUT_SYBT = '1') kikan,
(SELECT KTCD,
KKCD,
KIKITNTU_RYKSYU,
KIKI_SIYU_KIS_NNGPP,
KIKI_SIYU_TIS_NNGPP
FROM TR_KIKITNTU
WHERE RNR_SKZY_FLG = '0'
AND KIKI_YUT_SYBT = '1') tantou,
T_STAFF_BOOK_MANAGEMENT T3
WHERE T1.KTYPEDAIKUBUN = '5'
AND torihiki.SIYU_KIS_NNGPP(+) <= RPAD('20120726', 8, ' ')
AND torihiki.SIYU_TIS_NNGPP(+) >= RPAD('20120726', 8, ' ')
AND kikan.KIKI_SIYU_KIS_NNGPP(+) <= RPAD('20120726', 8, ' ')
AND kikan.KIKI_SIYU_TIS_NNGPP(+) >= RPAD('20120726', 8, ' ')
AND tantou.KIKI_SIYU_KIS_NNGPP(+) <= RPAD('20120726', 8, ' ')
AND tantou.KIKI_SIYU_TIS_NNGPP(+) >= RPAD('20120726', 8, ' ')
AND T1.KKEITAICD = '3'
AND T1.CST = '330'
AND T1.RECSTKUBUN IN ('1', '2')
AND LAST_DAY(ADD_MONTHS(TO_DATE(T1.KTO, 'YYYY-MM-DD'), 3)) >=
TO_DATE('20120726', 'YYYY-MM-DD')
AND T1.CNO = T2.CNO
AND T1.CHANSU = T2.CHANSU
AND T2.HKAISHACD = 'Z00000'
AND T2.HSOSHIKICD = tantou.KKCD(+)
AND T2.HBUKACD = tantou.KTCD(+)
AND T2.HSOSHIKICD = kikan.KKCD(+)
AND T1.CNO = T3.CNO(+)
AND T1.CHANSU = T3.CHANSU(+)
AND T1.KDNO = T3.KDNO(+)
AND T1.KHANSU = T3.KHANSU(+)
AND T1.SCD = torihiki.TRHKSK_KHN_CD(+)
AND T2.HSOSHIKICD = '7200'
AND T2.HBUKACD = '001600'
AND (T2.HSEIID = '1KC1H11' OR T2.HFUKUID = '1KC1H11' OR T2.SHIKIID = '1KC1H11' OR
T2.KUJOID = '1KC1H11')
ORDER BY KDNO DESC) main_sql) rownum_sql
where rownum_sql.rownum_alias >= 1
and rownum_sql.rownum_alias < 101
加上hint后感觉跟没加上一样，貌似hint语句没有起效果，求大神帮助啊！搞了2天了。。
（注：数据量比较大）

[我本是朱]

为什么要一堆表放在一起呢？建几个中间表吧

[NLP爱好者]

看到头痛，那个人写的代码，能不能分开写，两个两个表关联，用临时表。

这个看的晕啊！！！！！！！！！！！

[Dave]

SQL 的问题，首先看的是执行计划。

还有hint 是不建议开发人员使用的。 Oracle 现在CBO 还是比较智能的，如果强制使用hint，改变执行计划，可能会导致其他性能的问题。

[xiaozhuangfeng]

具体的自己去做吧(这sql也太长了)，在这里说几点sql优化的经验：
1.选择最有效率的表名顺序：记录数最少的表作为其表，放在最后；
2.WHERE子句中的连接顺序：那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾；
3.减少访问数据库的次数：少使用子表查询，多使用表连接；
4.使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表；
5.用Where子句替换HAVING子句避免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序、统计等操作；
6.用NOT EXISTS替代NOT IN:NOT IN子句将执行一个内部的排序和合并,无论在哪种情况下，NOT IN都是最低效的，因为它对子查询中的表执行了一个全表遍历;
7.用表连接替换EXISTS:通常来说，采用表连接的方式比EXISTS更有效率 ;
8.用索引提高效率;
9.避免在索引列上使用IS NULL和IS NOT NULL ;
10.使用UNION-ALL代替UNION ;

[jdsnhan]

此句只应天上有，人间难得几回闻。

啥信息都没有，想帮忙都帮不上

[dawugui]

建议你提供详细的资料：
例如表的结构，表之间的关系，测试数据，相关算法及需要的结果。
这样有助于我们理解你的意思，更主要的是能尽快让你获得答案或解决问题的方法。