oracle 利用绝杀招将 hashjoin 计划转变为 nestloop

最新推荐文章于 2022-08-01 19:51:28 发布
最新推荐文章于 2022-08-01 19:51:28 发布
阅读量1.3k 收藏
点赞数
文章标签： cardinality oracle hint
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/viviliving/article/details/82790640
版权

         以前遇到两个表做关联，每个表都是1亿以上的数据量，执行计划走的hashjoin，直接5个小时没执行完，我看看sql已经优化到没法优化了，但是加hint走nestloop后几分钟就执行完毕了，感觉对于数据量巨大的两个表，加条件的小表作为驱动表nestloop还是很快的

将hashjoin  转为 nested loop

hashjoin原文：
SELECT  KC24.AKB020 AS KC24_AKB020  ,
        KB01.AKB021 AS KB01_AKB021  ,
        KC24.AKC190 AS KC24_AKC190  ,
        KC24.AAE072 AS KC24_AAE072  ,
        KC24.BKA135 AS KC24_BKA135  ,
        KC24.AAE001 AS KC24_AAE001  ,
        KC24.AAC001 AS KC24_AAC001  ,
        KC24.AKA130 AS KC24_AKA130  ,
        KC24.AKC021 AS KC24_AKC021  ,
        KC24.BKC021 AS KC24_BKC021  ,
        KC24.AAB001 AS KC24_AAB001  ,
        KC24.AKE010 AS KC24_AKE010  ,
        KC24.BAA084 AS KC24_BAA084  ,
        KC24.BKC197 AS KC24_BKC197  ,
        KC24.BKE001 AS KC24_BKE001  ,
        KC24.AKC264 AS KC24_AKC264  ,
        KC24.BKC198 AS KC24_BKC198  ,
        KC24.BKC199 AS KC24_BKC199  ,
        KC24.BKC200 AS KC24_BKC200  ,
        KC24.BKC201 AS KC24_BKC201  ,
        KC24.BAC202 AS KC24_BAC202  ,
        KC24.BKC203 AS KC24_BKC203  ,
        KC24.BKC204 AS KC24_BKC204  ,
        KC24.BKC376 AS KC24_BKC376  ,
        KC24.BKC377 AS KC24_BKC377  ,
        KC24.BKC378 AS KC24_BKC378  ,
        KC24.BKC379 AS KC24_BKC379  ,
        KC24.BKC380 AS KC24_BKC380  ,
        KC24.BKC381 AS KC24_BKC381  ,
        KC24.BKC382 AS KC24_BKC382  ,
        KC24.BKC383 AS KC24_BKC383  ,
        KC24.AAE100 AS KC24_AAE100  ,
        KC24.BKA203 AS KC24_BKA203  ,
        KC24.BKC319 AS KC24_BKC319  ,
        KC24.AKC266 AS KC24_AKC266  ,
        KC24.AKC267 AS KC24_AKC267  ,
        KC24.BKB030 AS KC24_BKB030  ,
        KC24.BKC335 AS KC24_BKC335  ,
        KC24.BKC328 AS KC24_BKC328  ,
        KC24.BKC329 AS KC24_BKC329  ,
        KC21.BAZ001 AS KC21_BAZ001  ,
        KC21.BAZ002 AS KC21_BAZ002  ,
        KC21.AKB020 AS KC21_AKB020  ,
        KC21.AKC190 AS KC21_AKC190  ,
        KC21.BKA135 AS KC21_BKA135  ,
        KC21.AAC001 AS KC21_AAC001  ,
        KC21.AAZ500 AS KC21_AAZ500  ,
        KC21.AKA130 AS KC21_AKA130  ,
        KC21.AKC021 AS KC21_AKC021  ,
        KC21.AAB001 AS KC21_AAB001  ,
        KC21.AKA101 AS KC21_AKA101  ,
        KC21.BKC192 AS KC21_BKC192  ,
        KC21.AKC193 AS KC21_AKC193  ,
        KC21.BKC231 AS KC21_BKC231  ,
        KC21.BKC194 AS KC21_BKC194  ,
        KC21.AKC195 AS KC21_AKC195  ,
        KC21.AKC196 AS KC21_AKC196  ,
        KC21.BKC232 AS KC21_BKC232  ,
        KC21.BKC317 AS KC21_BKC317  ,
        KC21.AKA151 AS KC21_AKA151  ,
        KC21.BKC197 AS KC21_BKC197  ,
        KC21.BKC120 AS KC21_BKC120  ,
        KC21.BKC126 AS KC21_BKC126  ,
        KC21.BKC128 AS KC21_BKC128  ,
        KC21.AAE100 AS KC21_AAE100  ,
        KC21.BKC233 AS KC21_BKC233  ,
        KC21.AAA027 AS KC21_AAA027  ,
        KC21.BKC351 AS KC21_BKC351  ,
        KC21.BKC021 AS KC21_BKC021  ,
        KC21.AAE011 AS KC21_AAE011  ,
        KC21.AAE036 AS KC21_AAE036  ,
        KC21.BKC378 AS KC21_BKC378  ,
        KC21.BKC379 AS KC21_BKC379  ,
        KC21.BKC380 AS KC21_BKC380  ,
        KC21.BKC381 AS KC21_BKC381  ,
        KC21.BKC382 AS KC21_BKC382  ,
        KC21.BKC383 AS KC21_BKC383  ,
        KC21.BKC319 AS KC21_BKC319  ,
        KC21.BKC328 AS KC21_BKC328  ,
        KC21.BAE073 AS KC21_BAE073  ,
        KC21.AAE013 AS KC21_AAE013  ,
        KC21.AKF002 AS KC21_AKF002  ,
        KC21.AKE020 AS KC21_AKE020  ,
        KC21.BKF050 AS KC21_BKF050  ,
        KC21.AKC273 AS KC21_AKC273  ,
        KC60.BAZ001 AS KC60_BAZ001  ,
        KC60.BAZ002 AS KC60_BAZ002  ,
        KC60.AKB020 AS KC60_AKB020  ,
        KC60.AKC190 AS KC60_AKC190  ,
        KC60.AAE072 AS KC60_AAE072  ,
        KC60.BKA135 AS KC60_BKA135  ,
        KC60.AKC264 AS KC60_AKC264  ,
        KC60.AKE051 AS KC60_AKE051  ,
        KC60.AKE034 AS KC60_AKE034  ,
        KC60.AKE039 AS KC60_AKE039  ,
        KC60.AKE029 AS KC60_AKE029  ,
        KC60.AKE035 AS KC60_AKE035  ,
        KC60.AKE026 AS KC60_AKE026  ,
        KC60.BKE030 AS KC60_BKE030  ,
        KC60.BKE031 AS KC60_BKE031  ,
        KC60.BKE032 AS KC60_BKE032  ,
        KC60.BKE033 AS KC60_BKE033  ,
        KC60.BKE034 AS KC60_BKE034  ,
        KC60.BKE035 AS KC60_BKE035  ,
        KC60.BKE036 AS KC60_BKE036  ,
        KC60.BKE037 AS KC60_BKE037  ,
        KC60.BKE038 AS KC60_BKE038  ,
        KC60.BKE080 AS KC60_BKE080  ,
        KC60.BKE081 AS KC60_BKE081  ,
        KC60.BKE082 AS KC60_BKE082  ,
        KC60.BKE039 AS KC60_BKE039  ,
        KC60.BKE040 AS KC60_BKE040  ,
        KC60.BKE041 AS KC60_BKE041  ,
        KC60.BKE042 AS KC60_BKE042  ,
        KC60.BKE043 AS KC60_BKE043  ,
        KC60.BKE044 AS KC60_BKE044  ,
        KC60.BKE045 AS KC60_BKE045  ,
        KC60.BKE046 AS KC60_BKE046  ,
        KC60.BKE047 AS KC60_BKE047  ,
        KC60.BKE048 AS KC60_BKE048  ,
        KC60.BKE049 AS KC60_BKE049  ,
        KC60.BKE050 AS KC60_BKE050  ,
        KC60.BKE051 AS KC60_BKE051  ,
        KC60.BKE052 AS KC60_BKE052  ,
        KC60.BKE053 AS KC60_BKE053  ,
        KC60.BKE054 AS KC60_BKE054  ,
        KC60.BKE055 AS KC60_BKE055  ,
        KC60.BKE056 AS KC60_BKE056  ,
        KC60.BKE057 AS KC60_BKE057  ,
        KC60.BKE058 AS KC60_BKE058  ,
        KC60.BKE059 AS KC60_BKE059  ,
        KC60.BKE060 AS KC60_BKE060  ,
        KC60.BKE061 AS KC60_BKE061  ,
        KC60.BKE062 AS KC60_BKE062  ,
        KC60.BKE099 AS KC60_BKE099  ,
        KC60.BKE100 AS KC60_BKE100  ,
        KC60.BKE101 AS KC60_BKE101  ,
        KC60.BKE102 AS KC60_BKE102  ,
        KC60.BKE103 AS KC60_BKE103  ,
        KC60.BKE104 AS KC60_BKE104  ,
        KC60.BKE105 AS KC60_BKE105  ,
        KC60.BKE106 AS KC60_BKE106  ,
        KC60.BKE107 AS KC60_BKE107  ,
        KC60.BKE108 AS KC60_BKE108  ,
        KC60.BKE109 AS KC60_BKE109  ,
        KC60.BKE110 AS KC60_BKE110  ,
        KC60.BKE111 AS KC60_BKE111  ,
        KC60.BKE112 AS KC60_BKE112  ,
        KC60.BKE113 AS KC60_BKE113  ,
        KC60.BKE114 AS KC60_BKE114  ,
        KC60.BKE115 AS KC60_BKE115  ,
        KC60.BKE116 AS KC60_BKE116  ,
        KC60.BKE117 AS KC60_BKE117  ,
        KC60.BKE118 AS KC60_BKE118  ,
        KC60.BKE119 AS KC60_BKE119  ,
        KC60.BKE120 AS KC60_BKE120  ,
        KC60.BKE121 AS KC60_BKE121  ,
        KC60.BKE122 AS KC60_BKE122  ,
        KC60.BKE123 AS KC60_BKE123  ,
        KC60.BKE124 AS KC60_BKE124  ,
        KC60.BKE125 AS KC60_BKE125  ,
        KC60.BKE126 AS KC60_BKE126  ,
        KC60.BKE127 AS KC60_BKE127  ,
        KC60.BKE128 AS KC60_BKE128  ,
        KC60.BKE129 AS KC60_BKE129  ,
        KC60.BKE070 AS KC60_BKE070  ,
        KC60.BKE071 AS KC60_BKE071  ,
        KC60.BKE072 AS KC60_BKE072  ,
        KC60.BKE073 AS KC60_BKE073  ,
        KC60.BKE074 AS KC60_BKE074  ,
        KC60.BKE075 AS KC60_BKE075  ,
        KC60.BKE076 AS KC60_BKE076  ,
        KC60.BKE077 AS KC60_BKE077  ,
        KC60.BKE078 AS KC60_BKE078  ,
        AC01.AAC001 AS AC01_AAC001  ,
        AC01.AAC999 AS AC01_AAC999  ,
        AC01.AAC058 AS AC01_AAC058  ,
        AC01.AAE135 AS AC01_AAE135  ,
        AC01.AAC003 AS AC01_AAC003  ,
        AC01.AAC004 AS AC01_AAC004  ,
        AC01.AAC005 AS AC01_AAC005  ,
        AC01.AAC006 AS AC01_AAC006  ,
        KC21.AAE135 AS KC21_AAE135  ,
        KC21.AAC003 AS KC21_AAC003  ,
        KB01.AKB021 AS KB01_AKB021_1,
        KB01.AKB020 AS KB01_AKB020_1,
        KB01.AKB022 AS KB01_AKB022_1,
        AC01.BAC061 AS AC01_BAC061  ,
        AC01.AAZ500 AS AC01_AAZ500  ,
        AE10.AAB001 AS AE10_AAB001  ,
        AE10.AAB999 AS AE10_AAB999  ,
        AE10.AAE044 AS AE10_AAE044  ,
        AE10.AAE006 AS AE10_AAE006  ,
        KB01.AAZ010 AS KB01_AAZ010  ,
        KB01.AKB020 AS KB01_AKB020  ,
        KB01.AKB022 AS KB01_AKB022  ,
        KC24.BKA627 AS KC24_BKA627  ,
        KB01.AKA101 AS KB01_AKA101  ,
        KB01.BKB010 AS KB01_BKB010  ,
        KB01.BKB011 AS KB01_BKB011  ,
        KB01.BKB012 AS KB01_BKB012  ,
        KB01.AAE139 AS KB01_AAE139  ,
        KC21.BKC023 AS KC21_BKC023  ,
        KC21.AKE013 AS KC21_AKE013
FROM    KC24,
        KB01,
        KC21,
        KC60,
        AC01,
        AE10
WHERE
        (
                KC21.AKB020     =KC24.AKB020
                AND KC21.AKC190 =KC24.AKC190
                AND KC21.BKA135 =KC24.BKA135
                AND KC24.AKB020 =KC60.AKB020
                AND KC24.AKC190 =KC60.AKC190
                AND KC24.AAE072 =KC60.AAE072
                AND KC24.BKA135 =KC60.BKA135
                AND KC21.AAC001 =AC01.AAC001
                AND KC24.AAE100 = 1
                AND KC24.AKB020 =KB01.AKB020
                AND KC24.AAB001 =AE10.AAB001
        )
        AND
        (
                KC24.BKC197                 ='0'
                AND KC24.BKC381             ='0'
                AND KC24.AKE010>=TO_DATE('2015-08-18 0000','YYYY-MM-DD HH24MI')
                AND KC24.AKE010<=TO_DATE('2015-09-18 2359','YYYY-MM-DD HH24MI')
               
        )
ORDER BY KC24.AAC001 ASC
     

执行计划：
Description    对象所有者    对象名称    耗费    基数    字节
SELECT STATEMENT, GOAL = ALL_ROWS            1,345,842    1,468,254    1,441,825,428
 SORT ORDER BY            1,345,842    1,468,254    1,441,825,428
  HASH JOIN            1,043,008    1,468,254    1,441,825,428
   VIEW    QHSI    AE10    40,927    1,035,204    132,506,112
    SORT UNIQUE            40,927    1,035,204    146,263,834
     UNION-ALL                    
      TABLE ACCESS FULL    QHSI    AB01    243    23,194    3,571,876
      TABLE ACCESS FULL    QHSI    AB56    3    22    1,650
      TABLE ACCESS FULL    QHSI    AB54    8,065    1,011,988    142,690,308
   HASH JOIN            982,611    307,379    262,501,666
    TABLE ACCESS FULL    QHSI    KB01    13    1,607    89,992
    HASH JOIN            982,595    332,243    265,129,914
     HASH JOIN            675,578    332,243    197,684,585
      HASH JOIN            599,990    332,243    170,440,659
       TABLE ACCESS BY INDEX ROWID    QHSI    KC24    107,129    321,067    74,166,477
        INDEX RANGE SCAN    QHSI    IDX_KC24_AKE010    2,469    418,813    
       TABLE ACCESS FULL    QHSI    KC21    222,541    19,154,452    5,401,555,464
      TABLE ACCESS FULL    QHSI    AC01    47,014    4,554,410    373,461,620
     TABLE ACCESS FULL    QHSI    KC60    131,491    16,298,902    3,308,677,106

转变：nestloop

SELECT  /*+cardinality(kc24 1)*/KC24.AKB020 AS KC24_AKB020  ,
        KB01.AKB021 AS KB01_AKB021  ,
        KC24.AKC190 AS KC24_AKC190  ,
        KC24.AAE072 AS KC24_AAE072  ,
        KC24.BKA135 AS KC24_BKA135  ,
        KC24.AAE001 AS KC24_AAE001  ,
        KC24.AAC001 AS KC24_AAC001  ,
        KC24.AKA130 AS KC24_AKA130  ,
        KC24.AKC021 AS KC24_AKC021  ,
        KC24.BKC021 AS KC24_BKC021  ,
        KC24.AAB001 AS KC24_AAB001  ,
        KC24.AKE010 AS KC24_AKE010  ,
        KC24.BAA084 AS KC24_BAA084  ,
        KC24.BKC197 AS KC24_BKC197  ,
        KC24.BKE001 AS KC24_BKE001  ,
        KC24.AKC264 AS KC24_AKC264  ,
        KC24.BKC198 AS KC24_BKC198  ,
        KC24.BKC199 AS KC24_BKC199  ,
        KC24.BKC200 AS KC24_BKC200  ,
        KC24.BKC201 AS KC24_BKC201  ,
        KC24.BAC202 AS KC24_BAC202  ,
        KC24.BKC203 AS KC24_BKC203  ,
        KC24.BKC204 AS KC24_BKC204  ,
        KC24.BKC376 AS KC24_BKC376  ,
        KC24.BKC377 AS KC24_BKC377  ,
        KC24.BKC378 AS KC24_BKC378  ,
        KC24.BKC379 AS KC24_BKC379  ,
        KC24.BKC380 AS KC24_BKC380  ,
        KC24.BKC381 AS KC24_BKC381  ,
        KC24.BKC382 AS KC24_BKC382  ,
        KC24.BKC383 AS KC24_BKC383  ,
        KC24.AAE100 AS KC24_AAE100  ,
        KC24.BKA203 AS KC24_BKA203  ,
        KC24.BKC319 AS KC24_BKC319  ,
        KC24.AKC266 AS KC24_AKC266  ,
        KC24.AKC267 AS KC24_AKC267  ,
        KC24.BKB030 AS KC24_BKB030  ,
        KC24.BKC335 AS KC24_BKC335  ,
        KC24.BKC328 AS KC24_BKC328  ,
        KC24.BKC329 AS KC24_BKC329  ,
        KC21.BAZ001 AS KC21_BAZ001  ,
        KC21.BAZ002 AS KC21_BAZ002  ,
        KC21.AKB020 AS KC21_AKB020  ,
        KC21.AKC190 AS KC21_AKC190  ,
        KC21.BKA135 AS KC21_BKA135  ,
        KC21.AAC001 AS KC21_AAC001  ,
        KC21.AAZ500 AS KC21_AAZ500  ,
        KC21.AKA130 AS KC21_AKA130  ,
        KC21.AKC021 AS KC21_AKC021  ,
        KC21.AAB001 AS KC21_AAB001  ,
        KC21.AKA101 AS KC21_AKA101  ,
        KC21.BKC192 AS KC21_BKC192  ,
        KC21.AKC193 AS KC21_AKC193  ,
        KC21.BKC231 AS KC21_BKC231  ,
        KC21.BKC194 AS KC21_BKC194  ,
        KC21.AKC195 AS KC21_AKC195  ,
        KC21.AKC196 AS KC21_AKC196  ,
        KC21.BKC232 AS KC21_BKC232  ,
        KC21.BKC317 AS KC21_BKC317  ,
        KC21.AKA151 AS KC21_AKA151  ,
        KC21.BKC197 AS KC21_BKC197  ,
        KC21.BKC120 AS KC21_BKC120  ,
        KC21.BKC126 AS KC21_BKC126  ,
        KC21.BKC128 AS KC21_BKC128  ,
        KC21.AAE100 AS KC21_AAE100  ,
        KC21.BKC233 AS KC21_BKC233  ,
        KC21.AAA027 AS KC21_AAA027  ,
        KC21.BKC351 AS KC21_BKC351  ,
        KC21.BKC021 AS KC21_BKC021  ,
        KC21.AAE011 AS KC21_AAE011  ,
        KC21.AAE036 AS KC21_AAE036  ,
        KC21.BKC378 AS KC21_BKC378  ,
        KC21.BKC379 AS KC21_BKC379  ,
        KC21.BKC380 AS KC21_BKC380  ,
        KC21.BKC381 AS KC21_BKC381  ,
        KC21.BKC382 AS KC21_BKC382  ,
        KC21.BKC383 AS KC21_BKC383  ,
        KC21.BKC319 AS KC21_BKC319  ,
        KC21.BKC328 AS KC21_BKC328  ,
        KC21.BAE073 AS KC21_BAE073  ,
        KC21.AAE013 AS KC21_AAE013  ,
        KC21.AKF002 AS KC21_AKF002  ,
        KC21.AKE020 AS KC21_AKE020  ,
        KC21.BKF050 AS KC21_BKF050  ,
        KC21.AKC273 AS KC21_AKC273  ,
        KC60.BAZ001 AS KC60_BAZ001  ,
        KC60.BAZ002 AS KC60_BAZ002  ,
        KC60.AKB020 AS KC60_AKB020  ,
        KC60.AKC190 AS KC60_AKC190  ,
        KC60.AAE072 AS KC60_AAE072  ,
        KC60.BKA135 AS KC60_BKA135  ,
        KC60.AKC264 AS KC60_AKC264  ,
        KC60.AKE051 AS KC60_AKE051  ,
        KC60.AKE034 AS KC60_AKE034  ,
        KC60.AKE039 AS KC60_AKE039  ,
        KC60.AKE029 AS KC60_AKE029  ,
        KC60.AKE035 AS KC60_AKE035  ,
        KC60.AKE026 AS KC60_AKE026  ,
        KC60.BKE030 AS KC60_BKE030  ,
        KC60.BKE031 AS KC60_BKE031  ,
        KC60.BKE032 AS KC60_BKE032  ,
        KC60.BKE033 AS KC60_BKE033  ,
        KC60.BKE034 AS KC60_BKE034  ,
        KC60.BKE035 AS KC60_BKE035  ,
        KC60.BKE036 AS KC60_BKE036  ,
        KC60.BKE037 AS KC60_BKE037  ,
        KC60.BKE038 AS KC60_BKE038  ,
        KC60.BKE080 AS KC60_BKE080  ,
        KC60.BKE081 AS KC60_BKE081  ,
        KC60.BKE082 AS KC60_BKE082  ,
        KC60.BKE039 AS KC60_BKE039  ,
        KC60.BKE040 AS KC60_BKE040  ,
        KC60.BKE041 AS KC60_BKE041  ,
        KC60.BKE042 AS KC60_BKE042  ,
        KC60.BKE043 AS KC60_BKE043  ,
        KC60.BKE044 AS KC60_BKE044  ,
        KC60.BKE045 AS KC60_BKE045  ,
        KC60.BKE046 AS KC60_BKE046  ,
        KC60.BKE047 AS KC60_BKE047  ,
        KC60.BKE048 AS KC60_BKE048  ,
        KC60.BKE049 AS KC60_BKE049  ,
        KC60.BKE050 AS KC60_BKE050  ,
        KC60.BKE051 AS KC60_BKE051  ,
        KC60.BKE052 AS KC60_BKE052  ,
        KC60.BKE053 AS KC60_BKE053  ,
        KC60.BKE054 AS KC60_BKE054  ,
        KC60.BKE055 AS KC60_BKE055  ,
        KC60.BKE056 AS KC60_BKE056  ,
        KC60.BKE057 AS KC60_BKE057  ,
        KC60.BKE058 AS KC60_BKE058  ,
        KC60.BKE059 AS KC60_BKE059  ,
        KC60.BKE060 AS KC60_BKE060  ,
        KC60.BKE061 AS KC60_BKE061  ,
        KC60.BKE062 AS KC60_BKE062  ,
        KC60.BKE099 AS KC60_BKE099  ,
        KC60.BKE100 AS KC60_BKE100  ,
        KC60.BKE101 AS KC60_BKE101  ,
        KC60.BKE102 AS KC60_BKE102  ,
        KC60.BKE103 AS KC60_BKE103  ,
        KC60.BKE104 AS KC60_BKE104  ,
        KC60.BKE105 AS KC60_BKE105  ,
        KC60.BKE106 AS KC60_BKE106  ,
        KC60.BKE107 AS KC60_BKE107  ,
        KC60.BKE108 AS KC60_BKE108  ,
        KC60.BKE109 AS KC60_BKE109  ,
        KC60.BKE110 AS KC60_BKE110  ,
        KC60.BKE111 AS KC60_BKE111  ,
        KC60.BKE112 AS KC60_BKE112  ,
        KC60.BKE113 AS KC60_BKE113  ,
        KC60.BKE114 AS KC60_BKE114  ,
        KC60.BKE115 AS KC60_BKE115  ,
        KC60.BKE116 AS KC60_BKE116  ,
        KC60.BKE117 AS KC60_BKE117  ,
        KC60.BKE118 AS KC60_BKE118  ,
        KC60.BKE119 AS KC60_BKE119  ,
        KC60.BKE120 AS KC60_BKE120  ,
        KC60.BKE121 AS KC60_BKE121  ,
        KC60.BKE122 AS KC60_BKE122  ,
        KC60.BKE123 AS KC60_BKE123  ,
        KC60.BKE124 AS KC60_BKE124  ,
        KC60.BKE125 AS KC60_BKE125  ,
        KC60.BKE126 AS KC60_BKE126  ,
        KC60.BKE127 AS KC60_BKE127  ,
        KC60.BKE128 AS KC60_BKE128  ,
        KC60.BKE129 AS KC60_BKE129  ,
        KC60.BKE070 AS KC60_BKE070  ,
        KC60.BKE071 AS KC60_BKE071  ,
        KC60.BKE072 AS KC60_BKE072  ,
        KC60.BKE073 AS KC60_BKE073  ,
        KC60.BKE074 AS KC60_BKE074  ,
        KC60.BKE075 AS KC60_BKE075  ,
        KC60.BKE076 AS KC60_BKE076  ,
        KC60.BKE077 AS KC60_BKE077  ,
        KC60.BKE078 AS KC60_BKE078  ,
        AC01.AAC001 AS AC01_AAC001  ,
        AC01.AAC999 AS AC01_AAC999  ,
        AC01.AAC058 AS AC01_AAC058  ,
        AC01.AAE135 AS AC01_AAE135  ,
        AC01.AAC003 AS AC01_AAC003  ,
        AC01.AAC004 AS AC01_AAC004  ,
        AC01.AAC005 AS AC01_AAC005  ,
        AC01.AAC006 AS AC01_AAC006  ,
        KC21.AAE135 AS KC21_AAE135  ,
        KC21.AAC003 AS KC21_AAC003  ,
        KB01.AKB021 AS KB01_AKB021_1,
        KB01.AKB020 AS KB01_AKB020_1,
        KB01.AKB022 AS KB01_AKB022_1,
        AC01.BAC061 AS AC01_BAC061  ,
        AC01.AAZ500 AS AC01_AAZ500  ,
        AE10.AAB001 AS AE10_AAB001  ,
        AE10.AAB999 AS AE10_AAB999  ,
        AE10.AAE044 AS AE10_AAE044  ,
        AE10.AAE006 AS AE10_AAE006  ,
        KB01.AAZ010 AS KB01_AAZ010  ,
        KB01.AKB020 AS KB01_AKB020  ,
        KB01.AKB022 AS KB01_AKB022  ,
        KC24.BKA627 AS KC24_BKA627  ,
        KB01.AKA101 AS KB01_AKA101  ,
        KB01.BKB010 AS KB01_BKB010  ,
        KB01.BKB011 AS KB01_BKB011  ,
        KB01.BKB012 AS KB01_BKB012  ,
        KB01.AAE139 AS KB01_AAE139  ,
        KC21.BKC023 AS KC21_BKC023  ,
        KC21.AKE013 AS KC21_AKE013
FROM    KC24,
        KB01,
        KC21,
        KC60,
        AC01,
        AE10
WHERE
        (
                KC21.AKB020     =KC24.AKB020
                AND KC21.AKC190 =KC24.AKC190
                AND KC21.BKA135 =KC24.BKA135
                AND KC24.AKB020 =KC60.AKB020
                AND KC24.AKC190 =KC60.AKC190
                AND KC24.AAE072 =KC60.AAE072
                AND KC24.BKA135 =KC60.BKA135
                AND KC21.AAC001 =AC01.AAC001
                AND KC24.AAE100 = 1
                AND KC24.AKB020 =KB01.AKB020
                AND KC24.AAB001 =AE10.AAB001
        )
        AND
        (
                KC24.BKC197                 ='0'
                AND KC24.BKC381             ='0'
                AND KC24.AKE010>=TO_DATE('2015-08-18 0000','YYYY-MM-DD HH24MI')
                AND KC24.AKE010<=TO_DATE('2015-09-18 2359','YYYY-MM-DD HH24MI')
               
        )
ORDER BY KC24.AAC001 ASC


Description    对象所有者    对象名称    耗费    基数    字节
SELECT STATEMENT, GOAL = ALL_ROWS            107,149    5    4,910
 SORT ORDER BY            107,149    5    4,910
  NESTED LOOPS            107,148    5    4,910
   NESTED LOOPS            107,139    1    854
    NESTED LOOPS            107,136    1    651
     NESTED LOOPS            107,134    1    569
      NESTED LOOPS            107,131    1    287
       TABLE ACCESS BY INDEX ROWID    QHSI    KC24    107,129    1    231
        INDEX RANGE SCAN    QHSI    IDX_KC24_AKE010    2,469    418,813    
       TABLE ACCESS BY INDEX ROWID    QHSI    KB01    2    1    56
        INDEX RANGE SCAN    QHSI    PK_KB01    1    1    
      TABLE ACCESS BY INDEX ROWID    QHSI    KC21    3    1    282
       INDEX UNIQUE SCAN    QHSI    PK_KC21    2    1    
     TABLE ACCESS BY INDEX ROWID    QHSI    AC01    2    1    82
      INDEX UNIQUE SCAN    QHSI    PK_AC01    1    1    
    TABLE ACCESS BY INDEX ROWID    QHSI    KC60    3    1    203
     INDEX UNIQUE SCAN    QHSI    PK_KC60    2    1    
   VIEW    QHSI    AE10    9    1    128
    SORT UNIQUE            9    3    370
     UNION ALL PUSHED PREDICATE                    
      TABLE ACCESS BY INDEX ROWID    QHSI    AB01    2    1    154
       INDEX UNIQUE SCAN    QHSI    PK_AB01    1    1    
      TABLE ACCESS BY INDEX ROWID    QHSI    AB56    1    1    75
       INDEX UNIQUE SCAN    QHSI    PK_AB56    0    1    
      TABLE ACCESS BY INDEX ROWID    QHSI    AB54    3    1    141
       INDEX UNIQUE SCAN    QHSI    PK_AB54    2    1    

 

 /*+cardinality(kc24 1)*/使用条件，参考本例说明，kc21,kc24作为社保表数据量15年已经突破20亿条，而且这个统计几乎基础表都是全表扫描，但是很明显kc24是有条件的，因此数据集是相对最小的，因此可以最为驱动表，在驱动表数据条数不确定的情况下，指定1，这样效果很明显

据说这个hin是Oracle SQL tunning的终极杀手锏

回到未来望过去
关注
Oracle执行计划
tttzzzqqq2018的博客
08-21 7179
执行计划：一条查询语句在ORACLE中的执行过程或访问路径的描述。即就是对一个查询任务，做出一份怎样去完成任务的详细方案。如果要分析某条SQL的性能问题，通常我们要先看SQL的执行计划，看看SQL的每一步执行是否存在问题。看懂执行计划也就成了SQL优化的先决条件。通过执行计划定位性能问题，定位后就通过建立索引、修改sql等解决问题。
oracle利用绝杀招将hashjoin计划转变为nestloop
viviliving的专栏
08-09 528
以前遇到两个表做关联，每个表都是1亿以上的数据量，执行计划走的hashjoin，直接5个小时没执行完，我看看sql已经优化到没法优化了，但是加hint走nestloop后几分钟就执行完毕了，感觉对于数据量巨大的两个表，加条件的小表作为驱动表nestloop还是很快的 将hashjoin 转为 nested loop hashjoin原文： SELECT KC24.AKB020 AS KC24_AKB020 , KB01.AKB021 AS KB01_AKB021 ...
一种特殊场景下的HASH JOIN的优化为NEST LOOP.
weixin_33700350的博客
02-16 338
应用场景： 有如下的SQL: select t.*, t1.ownerfrom t, t1where t.id=t1.id; 表t ,t1的数据量比较大，比如200W行.但是两张表能关联的行数却很少，比如不到50条. T1表的行比较宽,且在id列上有单列索引. 这里限制t1的索引为单列索引是为了让访问t1表数据的时候要通过rowid回表.因为在实际应用中,我们通常会需要t1表的几个列，但是...
[摘]将“相关值”导致的Nested Loop优化成Hash Join
weixin_34087307的博客
04-11 185
      原文地址：http://www.itpub.net/thread-1376537-1-2.html       “相关值”是我剽窃的一个词，与之相反的是“不相关值”，我借用来描述以下这种情况：在SQL中有两个表A和B要join，join条件是A.name=B.first_name，判定符“=”左右两边的值是“不相关”的，而substr(A.name,1,length(B.first_n...
NEST LOOP改为HASH JOIN 性能提高6倍
ctest011501的博客
08-31 337
NEST LOOP改为HASH JOIN 性能提高6倍Kevin Zou2011-8-31在看系统的AWR report时，发现有个SQL语句出现频率很高，而且都是在消耗资源的top 5内。决定把这个SQL拉出来看看。523,...
oracle的表连接hash join、nested loop join
cupid1102的专栏
05-22 4890
今天打算对hash join和nested loop join的原理及使用做下记录，每次总结完，过一段时间就有写模糊了：   一、hash join原理：        hash join只能在等值连接下使用，且只有在CBO优化器才能使用；        其思想是将小的表（内表）存在于hash area内存中做成hash table，之后大表（外表）进行全扫描，每条记录都在连接的值上做ha
解读 Oracle12c 执行计划——HASH JOIN
梅森上校的博客 业精于勤荒于嬉，形成于思毁于随。
09-24 3214
解读 Oracle12c 执行计划——HASH JOIN   Oracle官方文档中的解释如下： Hash Joins - Hash joins are used for joining large data sets. The optimizer uses the smaller of the two tables or data sources to build a hash table...
oracle嵌套三层循环语句,Oracle表连接操作——Nest Loop Join（嵌套循环）
weixin_30062621的博客
04-02 1448
关系型数据库并不是最早出现的数据库表现形式，之前还存在层次、网状数据库结构。随着关系型数据库的出现，以数据表的方式进行信息留存的方案迅速发展起来。关系型数据库的重要元素包括数据表和表连接，借助各种类型的表连接，可以将平铺直叙的信息加以组装拼接。在Oracle数据库中，应对不同的连接情况和数据特征，数据库CBO会生成不同的执行计划和连接操作。本篇介绍各种连接中的一种——Nest Loop Join嵌...
oracle 表连接 - hash join 哈希连接
dataminer 的博客
12-18 3562
一. hash 连接(哈希连接)原理 指的是两个表连接时, 先利用两表中记录较少的表在内存中建立 hash 表, 然后扫描记录较多的表并探测 hash 表, 找出与 hash 表相匹配的行来得到结果集的表连接方法. 哈希连接只能用于等值连接条件(=)。 假设下面的 sql 语句中表 T1 和 T2 的连接方式是哈希连接, T1 是驱动表 select * from T1, T2 wher
深入理解Oracle表(5)：三大表连接方式详解之Hash Join的定义，原理，算法，成本，模式和位图
热门推荐
记录创业路上的一些想法
01-30 1万+
Hash Join只能用于相等连接，且只能在CBO优化器模式下。相对于nested loop join，hash join更适合处理大型结果集       Hash Join的执行计划第1个是hash表(build table)，第2个探查表(probe table)，一般不叫内外表，nested loop才有内外表       Hash表也就是所谓的内表，探查表所谓的外表       两者的执行
表的连接方式:NESTED LOOP、HASH JOIN、SORT MERGE JOIN（修改）
Seas_小庙的专栏
02-05 4948
表连接方式及使用场合 NESTED LOOP 嵌套循环连接        由两个for循环组成。不管什么连接，本算法都可以使用。连接的两个关系，分别称之为外层关系和内层关系，把数据块数大的关系作为外层关系，小的关系作内层关系。分为块嵌套循环连接（简单点说，就是把已经放在内存的两个关系块全部连接完成后再进行下一数据库块的比较，减少内存中数据块的IO）和索引嵌套循环连接（如果内层关系有索引，使用
gp的优化记录hash join和nested loop
Ciao's blog
08-01 897
greenplus优化记录
Mysql 优化器内部JOIN算法hash join Nestloopjoin及classic hash join CHJ过程详解
数据科学汇集
09-26 1157
Mysql hash join之classic hash join CHJ过程详解 hash join的历史 优化器里的hash join算法在SQL Server、Oracle、postgress等数据库早已实现，而Mysql在8.0.18之后才支持。在8.0.18之前mysql只支持嵌套循环关联(nested loop join),这其中最简单就是简易嵌套循环关联simple nestloop join，随后mysql做了改进进而支持block nestloop join, index nestlo
将“相关值”导致的Nested Loop优化成Hash Join
coucyxpwr716751的博客
07-13 91
非常精妙的优化链接:http://www.cnblogs.com/killkill/archive/2011/04/11/2013044.html ...
多表连接的三种方式详解 HASH JOIN、MERGE JOIN、NESTED LOOP
微信搜：import_bigdata，大数据领域硬核原创作者
10-04 3865
点击上方蓝色字体，选择“设为星标”回复”资源“获取更多资源大数据技术与架构点击右侧关注，大数据开发领域最强公众号！大数据真好玩点击右侧关注，大数据真好玩！多表之间的连接有三种方式：Nes...
解读nestloop和hash join
05-13 946
SQL> set lines 200 SQL> set pages 200 SQL> alter session set statistics_level = all; NESTLOOP：嵌套循环 SQL> select e.ename,e.job,d.dname from emp e, dept d where   2  e.deptno=d.deptno and e.sal EN
浅谈SQL Server中的三种物理连接操作(Nested Loop Join、Merge Join、Hash Join)
hzp666的博客
08-06 1676
简介 在SQL Server中，我们所常见的表与表之间的Inner Join,Outer Join都会被执行引擎根据所选的列，数据上是否有索引，所选数据的选择性转化为Loop Join,Merge Join,Hash Join这三种物理连接中的一种。理解这三种物理连接是理解在表连接时解决性能问题的基础，下面我来对这三种连接的原理，适用场景进行描述。 嵌套循环连接(Nested Loop Join) 循环嵌套连接是最基本的连接,正如其名所示那样，需要进行循环嵌套，这种连接方式的过程...
循序渐进讲解Oracle数据库的Hash join
卧龙小山
11-09 186
循序渐进讲解Oracle数据库的Hash join 2008-05-01 00:00作者：Peter出处：天极网责任编辑：McLaren &lt;!--正文开始--&gt; &lt;!-- Error --&gt; 　　在开发过程中，很多人经常会使用到Hash Map或者Hash Set这种数据结构，这种数据结构的特点就是插入和访问速度快。当向集合中加入一个对象时，会调用has...
SQL 执行计划的固定手段 --- Hint 方式（Nested Loop Join）
weixin_50513167的博客
04-18 1624
Nested Loop Join 指定时用到的 Hint 和 Hash Join 相对应的，通常，利用索引时一般会用到 Nested Loop Join。 下面我们来继续看看如何控制 Nested Loop Join 的使用，以及 Nested Loop Join 的顺序。 LEADING Hint （指定 Nested Loop Join 顺序） USE_NL (指定使用 Nested Loop Join) 依然通过例子来进行说明。 准备： drop table t1 purge; drop tabl
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值