优化SQL文
看看怎样优化？
这个 T_P2HP0040 表 数据有几万条 ，检索很慢
SELECT
ROWNUM AS No
,YD.KOJI_NO AS KOJI_NO
,YD.CONTAINER_NO AS CONTAINER_NO
,TP.CONTAINER_NAME AS CONTAINER_MEISHOO
,TP.GENCHAKU_DATE AS GENCHAKU_YMD
,YD.YOKYU_MESAI_NO AS YOKYU_MESAI_NO
,YD.KANBAN AS KANBAN
,YD.ZUMEN_NO AS ZUMEN_NO
,CM.KETTE_NOKI AS KETTE_NOKI
,CM.HACCHUSAKI_CODE AS MADOGUCHI_CODE
,CM.HACCHUSAKI_KAISHA_NAME AS HACCHUSAKI_KAISHA_NAME
,YD.TAN_I AS TAN_I
,YD.SEKKEI_SUURYOO AS SEKKEI_SUURYOO
,KT.JURYO_DATE AS JURYO_DATE
,KT.ISUGKN_SHINSE_DATE AS ISUGKN_SHINSE_DATE
,KT.KENSHU_END_DATE AS KENSHU_END_DATE
,YM.KOJI_NAME AS KOJI_NAME
FROM
DEN_YOKYU_MESAI_DETAIL_TBL
YD
,
(
SELECT
DISTINCT
KOOBAN
,CONTAINER_NO
,CONTAINER_NAME
,GENCHAKU_DATE
FROM
T_P2HP0040
WHERE
1 = 1
AND KOOBAN LIKE ' 0000001% '
AND GENCHAKU_DATE > = '20061001 '
AND GENCHAKU_DATE <= '20061231 '
)
TP
,V_DEN_CHUMON_MESAI_TBL CM
,V_DEN_KENSHU_TBL KT
,V_DEN_YOKYU_MESAI YM
WHERE
1 = 1
AND YD.KOJI_NO LIKE '0000001% '
AND YD.IHI_KIGYO_CODE = CM.IHI_KIGYO_CODE
AND YD.SYSTEM_KUBUN = CM.SYSTEM_KUBUN
AND YD.YOKYU_MESAI_NO = CM.YOKYU_MESAI_NO
AND YD.COUNTER = CM.COUNTER
AND YD.YOKYU_HENKO_KAISU = CM.YOKYU_HENKO_KAISU
AND YD.YOKYU_HENKO_KAISU
=
(
SELECT
MAX( YOKYU_HENKO_KAISU )
FROM
DEN_YOKYU_MESAI_DETAIL_TBL
WHERE
IHI_KIGYO_CODE = YD.IHI_KIGYO_CODE
AND SYSTEM_KUBUN = YD.SYSTEM_KUBUN
AND YOKYU_MESAI_NO = YD.YOKYU_MESAI_NO
AND COUNTER = YD.COUNTER
)
AND CM.CHUMON_KAISU
=
(
SELECT
MAX( CHUMON_KAISU )
FROM
V_DEN_CHUMON_MESAI_TBL
WHERE
IHI_KIGYO_CODE = CM.IHI_KIGYO_CODE
AND SYSTEM_KUBUN = CM.SYSTEM_KUBUN
AND YOKYU_MESAI_NO = CM.YOKYU_MESAI_NO
AND COUNTER = CM.COUNTER
AND YOKYU_HENKO_KAISU = CM.YOKYU_HENKO_KAISU
AND CHUMON_NO = CM.CHUMON_NO
)
AND KT.IHI_KIGYO_CODE = CM.IHI_KIGYO_CODE
AND KT.CHUMON_NO = CM.CHUMON_NO
AND KT.CHUMON_KAISU = CM.CHUMON_KAISU
AND KT.SYSTEM_KUBUN = CM.SYSTEM_KUBUN
AND KT.YOKYU_MESAI_NO = CM.YOKYU_MESAI_NO
AND KT.COUNTER = CM.COUNTER
AND KT.JURYO_KAISU
=
(
SELECT
MAX( JURYO_KAISU )
FROM
V_DEN_KENSHU_TBL
WHERE
IHI_KIGYO_CODE = KT.IHI_KIGYO_CODE
AND SYSTEM_KUBUN = KT.SYSTEM_KUBUN
AND YOKYU_MESAI_NO = KT.YOKYU_MESAI_NO
AND COUNTER = KT.COUNTER
AND YOKYU_HENKO_KAISU = KT.YOKYU_HENKO_KAISU
AND CHUMON_NO = KT.CHUMON_NO
AND CHUMON_KAISU = KT.CHUMON_KAISU
)
AND YM.CANCEL = '0 '
AND YM.IHI_KIGYO_CODE = YD.IHI_KIGYO_CODE
AND YM.SYSTEM_KUBUN = YD.SYSTEM_KUBUN
AND YM.YOKYU_MESAI_NO = YD.YOKYU_MESAI_NO
AND YM.COUNTER = YD.COUNTER
AND YM.YOKYU_HENKO_KAISU = YD.YOKYU_HENKO_KAISU
AND YM.UKEWATASHI_JOKEN_CODE = '01 '
AND TRIM( TP.KOOBAN ) = YD.KOJI_NO
AND TP.CONTAINER_NO = TRIM( YD.CONTAINER_NO )
AND TP.GENCHAKU_DATE
=
(
SELECT
MIN( GENCHAKU_DATE )
FROM
T_P2HP0040
WHERE
KOOBAN = TP.KOOBAN
AND CONTAINER_NO = TP.CONTAINER_NO
AND GENCHAKU_DATE > = '20061001 '
AND GENCHAKU_DATE <= '20061231 '
)
ORDER BY
KOJI_NO ASC
,GENCHAKU_YMD ASC
,CONTAINER_NO ASC
,YOKYU_MESAI_NO ASC
,KANBAN ASC
,ZUMEN_NO ASC
[解决办法]
SELECT
DISTINCT
KOOBAN
,CONTAINER_NO
,CONTAINER_NAME
,GENCHAKU_DATE
FROM
T_P2HP0040
WHERE
1 = 1
AND KOOBAN LIKE ' 0000001% '
AND GENCHAKU_DATE > = '20061001 '
AND GENCHAKU_DATE <= '20061231 '
这个可以进一步优化
用exists
[解决办法]
光看這個是不行的，要知道每個表的關鍵字段，才可以進一步的優化
[解决办法]
看執行計劃和io信息！
SQL*Plus set session autotrac on