11.5.9使用API进行PICK RELEASE.doc

Order SQL调优实例 Author: Octavius.He Creation Date: July 13, 2009 Last Updated: September 7, 2009 Document Ref: Document Reference Number Version: DRAFT 1A Approvals: Approver 1 Approver 2 Copy Number _____ Document Control Change Record 3 Date Author Version Change Reference 21-May-08 Octavius.He Draft 1a No Previous Document Reviewers Name Position Distribution Copy No. Name Location Library Master Project Library Project Manager Note To Holders: If you receive an electronic copy of this document and print it out, please write your name on the equivalent of the cover page, for document control purposes. If you receive a hard copy of this document, please write your name on the front cover, for document control purposes. Contents Document Control ii 1. Oracle SQL优化 2 1.1. 业务背景 2 1.2. 调优过程 4 1.3. 进一步探索 7 1.4. 结论 9 2. 其他相关知识 10 2.1. 清除BUFFER 10 3. Open and Closed Issues for this Deliverable 11 Open Issues 11 Closed Issues 11  Oracle SQL优化 业务背景 业务环境简介 ORACLE OM模块的挑库发放，是一个比较基础的功能，最常用的，就是往。 在实际开发过程中，曾经遇到过一个很简单但是很值得回味的SQL优化，特此记录下来，用以总结，并希望对其他开发人员有所帮助。 需求 SQL的内容很简单，取出未结订单，与一张客户化物料编码Mapping表关联，订单的OU和发运组织有所限制，具体SQL如下： SELECT ooh.order_number, ool.line_number||.||ool.shipment_number, ooh.header_id, ool.line_id, ool.inventory_item_id FROM oe_order_lines_all ool, oe_order_headers_all ooh, cux_hb_item_mapping him, mtl_parameters mp WHERE ool.flow_status_code NOT IN(CLOSED,CANCELLED) AND ooh.flow_status_code NOT IN(CLOSED,CANCELLED) AND him.item_code LIKE 1% AND ool.header_id = ooh.header_id AND ool.inventory_item_id = him.item_id AND ool.ship_from_org_id = mp.organization_id AND mp.organization_code IN(32A, 32B, 32C, 32D, 33A, 33B, 34A, 34B, 35A, 38A,40A, 41A, G1A, G1B, G1C, G2A, G2B, G2C, G6A, 41B, 40B, G6B, G6C, A00, L1A, L1B, 38B) AND ooh.org_id