SQL性能优化（hint）

Question

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天了。。
（注：数据量比较大）

Answer 1

具体的自己去做吧(这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 ;

Answer 2

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