几个比较难的oracle sql优化

[billlyh]

1. 如何查找 “某信息科技”供应商在“某周” PO 信息（包括ph,pla,pll,pda）有过更新的 PO信息；表之间的关联关系如下
select *
from po_vendors pv,
po_headers_all ph,
po_lines_all pla,
po_line_locations_all pll,
po_distributions_all pda
where pv.vendor_name like '%信息%科技%'
and ph.vendor_id = pv.vendor_id
and ph.po_header_id = pla.po_header_id
and pla.po_line_id = pll.po_line_id
and pll.line_location_id = pda.line_location_id
2.请将 SQL 优化成以表 PO_ASL_DOCUMENTS pad1为驱动表，且 PO_ASL_DOCUMENTS pad1 与 po_headers_all pha11 为嵌套连接

SELECT
PHA.PO_HEADER_ID,
PHA.SEGMENT1,
PHA.VENDOR_ID,
HE.FULL_NAME,
PV.VENDOR_NAME,
PVS.VENDOR_SITE_CODE
FROM PO_HEADERS_ALL PHA,
HW_EMPLOYEES_PROD_V HE,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVS,
(select
distinct pad1.document_header_id
from PO_ASL_DOCUMENTS pad1,
po_headers_all pha11
where pad1.document_header_id = pha11.po_header_id
) PAD --Added By Sie_zhangwenfeng at 2010-06-12 性能优化--
WHERE PHA.SEGMENT1 >=
NVL(:L_FROM_BLANKETPO_NUM, '00000000')
AND
PHA.SEGMENT1 <= NVL(:L_TO_BLANKETPO_NUM, 'zzzzzzzzz')
AND PHA.TYPE_LOOKUP_CODE IN ('BLANKET', 'CONTRACT')
--Rimed By Sie_zhangwenfeng at 2010-06-12 Begin--
--性能优化--
/*AND
PHA.PO_HEADER_ID IN
(SELECT DOCUMENT_HEADER_ID FROM PO_ASL_DOCUMENTS) */
AND PHA.PO_HEADER_ID = PAD.DOCUMENT_HEADER_ID
--Rimed By Sie_zhangwenfeng at 2010-06-12 End--
AND PHA.AUTHORIZATION_STATUS = 'APPROVED'
AND PHA.CREATION_DATE >=
NVL(:L_B_FROM_CREATION_DATE, PHA.CREATION_DATE)
AND PHA.CREATION_DATE <
NVL(:L_B_TO_CREATION_DATE, SYSDATE + 1)
AND PHA.APPROVED_DATE >=
NVL(:L_B_FROM_APP_DATE, PHA.APPROVED_DATE)
AND PHA.APPROVED_DATE <
NVL(:L_B_TO_APP_DATE, SYSDATE + 1)
AND PHA.ORG_ID = :L_SOURCE_ORG_ID
AND PHA.AGENT_ID = HE.PERSON_ID
AND PHA.VENDOR_ID = PV.VENDOR_ID
AND PHA.VENDOR_ID = PVS.VENDOR_ID
AND PHA.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND HE.EMPLOYEE_NUMBER IS NOT NULL
--begin 增加参数处理,liaoningfeng,2008-12-05
AND EXISTS (SELECT 1 FROM PO_APPROVED_SUPPLIER_LIST asl
,po_lines_all pl
WHERE asl.vendor_id = PVS.VENDOR_ID
AND asl.vendor_site_id = pvs.vendor_site_id
AND pl.po_header_id = pha.po_header_id
AND pl.item_id = asl.item_id
AND ((NVL(:P_VMI_ITEM_FLAG, 'N') = 'Y' AND
hw_vmi_cbb_new.Is_Vmi_Item(asl.item_id
,:l_organization_id
,asl.vendor_id
,asl.vendor_site_id)='Y')
OR NVL(:P_VMI_ITEM_FLAG, 'N') = 'N')
AND ((NVL(:P_MM_ITEM_FLAG, 'N') = 'Y' AND
hw_vmi_cbb_new.Is_MM_Item(asl.item_id
,:l_organization_id
,asl.vendor_id
,asl.vendor_site_id)='Y')
OR NVL(:P_MM_ITEM_FLAG, 'N') = 'N')
)