看看怎样优化?
这个 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
这个 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
解决方案 »
- SQL大师来,问个查询语句
- (急)Ibatis 中Oracle中查询语句处理问题(在线等待)
- 这个序列怎么用啊?
- 如何删除error_log这个大文件或者缩减其大小?
- varchar(size)会不会真的占用空间?
- Oracle8i在win2003上的启动有问题吗?
- ORACEL805的Schema和Storage Manager到了9i是什么?
- 怎么在sql语句里判断一个字符串是不是date型的
- 怎么用pl/sql修改表名啊? 查了半天没搞定
- oracle如何判断表已经满了
- 高手们,熊猫烧香病毒把ORACLE服务端启动不起来了,怎么才能备份出数据啊?
- 卸载oracle软件的时候会不会把创建的数据库删除
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
SQL*Plus set session autotrac on