性能优化案例讲解

--一个表使用到两个索引时

SELECT * FROM PO_HEADERS_ALL PH
WHERE PH.SEGMENT1 = NVL(:SEGMENT1,PH.SEGMENT1)
AND PH.CREATION_DATE > SYSDATE - 10
;

SELECT *
FROM PO_HEADERS_ALL PH,
po_lines_all pla
WHERE 1 = 1
-- and PH.SEGMENT1 = NVL(:SEGMENT1,PH.SEGMENT1)
and ph.segment1 like :segment1 || '%'
--AND PH.CREATION_DATE > SYSDATE - 10
--and ph.attribute1 = 'sdfsdfdsfsdfsdf'
and ph.po_header_id = pla.po_header_id
;

--使用视图索引
CREATE OR REPLACE VIEW HW_PO_DFA_DFA_GR AS
SELECT /*+ use_nl(mmt,rt,poh) index(mmt MTL_MATERIAL_TRANSACTIONS_N5)*/
mmt.transaction_quantity gr_qty,
mmt.transaction_uom gr_uom,
mmt.transaction_date gr_date,
rt.transaction_type,
rt.po_header_id,
rt.po_line_location_id,
poh.segment1 po_number,
poh.interface_source_code
FROM po_headers_all poh,
rcv_transactions rt,
mtl_material_transactions mmt
WHERE mmt.transaction_source_type_id = 1
AND rt.transaction_id = mmt.rcv_transaction_id
And poh.po_header_id = rt.po_header_id;
;

--指定视图索引
select /*+ use_nl(t.mmt,t.rt,t.poh) index(T.mmt MTL_MATERIAL_TRANSACTIONS_N5) */ * from HW_PO_DFA_DFA_GR t
where t.gr_date > sysdate - 10

;

--指定子视图执行顺序
SELECT /*+ LEADING(PAD) use_nl(PAD,pha) */ --Added By Sie_zhangwenfeng at 2010-06-12 性能优化--
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 /*+ use_nl(pad1,pha11)*/
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 https://www.360docs.net/doc/1b1490373.html,_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')
)

;
--Ordered 排序
SELECT /*+ORDERED index(prl PO_REQUISITION_LINES_N3)*/
COUNT(*)
FROM po_requisition_lines_all prl,
po_requisition_headers_all prh,
po_req_distributions_all prd,
gl_code_combinations glcc,
po_vendors pov,
gl_sets_of_books gsb,
/* gl_daily_rates gdr,*/
financials_system_par

ams_all fsp,
po_system_parameters_all pspa,
mtl_system_items_b msib /*,
(SELECT msra.inventory_item_id inventory_item_id,
https://www.360docs.net/doc/1b1490373.html,anization_id organization_id,
source_org.vendor_id,
source_org.allocation_percent,
source_org.rank,
row_number() over(PARTITION BY msra.inventory_item_id, https://www.360docs.net/doc/1b1490373.html,anization_id ORDER BY source_org.allocation_percent DESC, nvl(source_org.rank, 9999) ASC) row_num
FROM mrp_sr_assignments msra,
mtl_system_items items,
mrp_sourcing_rules msr,
mrp_sr_receipt_org receipt_org,
mrp_sr_source_org source_org
WHERE source_org.sr_receipt_id = receipt_org.sr_receipt_id
AND receipt_org.sourcing_rule_id = msr.sourcing_rule_id
AND SYSDATE BETWEEN receipt_org.effective_date AND nvl(receipt_org.disable_date, to_date(2634525, 'J'))
AND https://www.360docs.net/doc/1b1490373.html,anization_id = nvl(receipt_org.receipt_organization_id, https://www.360docs.net/doc/1b1490373.html,anization_id)
AND msr.status = 1
AND msr.sourcing_rule_id = msra.sourcing_rule_id
AND msra.assignment_type = 3
AND source_org.source_type = 3
AND items.inventory_item_id = msra.inventory_item_id
AND msra.assignment_set_id = 3) sr*/
WHERE prl.requisition_header_id = prh.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
AND glcc.code_combination_id = prd.code_combination_id
AND prl.line_location_id IS NULL
AND https://www.360docs.net/doc/1b1490373.html,anization_id = 1
AND msib.inventory_item_id = prl.item_id
AND prl.vendor_id = pov.vendor_id(+)
AND prh.authorization_status = 'APPROVED'
AND nvl(prh.cancel_flag, 'N') = 'N'
AND nvl(prl.cancel_flag, 'N') = 'N'
AND https://www.360docs.net/doc/1b1490373.html,_id = https://www.360docs.net/doc/1b1490373.html,_id
AND https://www.360docs.net/doc/1b1490373.html,_id = https://www.360docs.net/doc/1b1490373.html,_id
AND fsp.set_of_books_id = gsb.set_of_books_id
/* AND gdr.from_currency(+) = prl.currency_code
AND gdr.to_currency(+) = 'USD'
AND trunc(gdr.conversion_date(+)) = trunc(SYSDATE)
AND nvl(gdr.conversion_type, pspa.default_rate_type) = pspa.default_rate_type*/
AND https://www.360docs.net/doc/1b1490373.html,_id = https://www.360docs.net/doc/1b1490373.html,_id
AND https://www.360docs.net/doc/1b1490373.html,_id IN (218, 18294, 284, 289, 337, 317, 359, 287, 424, 8057, 10219)
and prl.item_id is not null
and prl.need_by_date >= trunc(sysdate,'mm')
and prl.need_by_date < add_months(trunc(sysdate,'mm'),1)

;
Where 条件的执行顺序：
TThe ORDERED_PREDICATES hint forces the optimizer to preserve the order of predicate evaluation, except for predicates used as index keys. Use this hint in the WHERE clause of SELECT statements.

If you do not use the ORDERED_PREDICATES hint, Oracle evaluates all predicates in the order specified by the following rules:

Predicates without user-defined functions, type methods, or subqueries are evaluated first, in the order specified in the WHERE clause.
?
Predicates with user

-defined functions and type methods that have user-computed costs are evaluated next, in increasing order of their cost.
?
Predicates with user-defined functions and type methods without user-computed costs are evaluated next, in the order specified in the WHERE clause.
?
Predicates not specified in the WHERE clause (for example, predicates transitively generated by the optimizer) are evaluated next.
?
Predicates with subqueries are evaluated last in the order specified in the WHERE clause.