Sentencia SQL ============= select /*+ ORDERED INDEX (pick MOT_SO_PICKING_LINES_ALL_IDX3) */ hd.attribute2 attribute2, ln.attribute4 attribute4, hd.order_number order_number, ln.line_number order_line_number, decode(ot.attribute4,'Y', nvl(ln.attribute3,hd.purchase_order_num), hd.purchase_order_num) purchase_order_number, hd.date_ordered order_date, source.name order_source_name, item.segment1 segment1, ln.ordered_quantity - nvl(ln.cancelled_quantity,0) ordered_quantity, sum(nvl(pick.shipped_quantity,0)* nvl(ln.selling_price,0)) line_amount, ln.selling_price selling_price, item.inventory_item_id inventory_item_id, sum(nvl(pick.shipped_quantity,0)) shipped_quantity, sum(pick.cancelled_quantity) cancelled_quantity, sum(pick.invoiced_quantity) invoiced_quantity, item.organization_id organization_id, ln.warehouse_id warehouse_id, item.description description, ln.unit_code unit_code, hd.currency_code currency_code, ln.tax_code tax_code, nvl(hd.conversion_type_code,'Corporate') conversion_type, ot.name name, ra.address_id ship_to, ln.line_id so_line_id, decode(instr(ln.attribute5,'|',1,2), 0,null,ln.attribute5) cpn_config, substr(decode(ln.attribute14,NULL,to_char(ln.line_number), decode(instr(ln.attribute14,'|',1,1),0,ln.attribute14, substr(ln.attribute14,1,INSTR(ln.attribute14,'|',1) -1))),1,30) po_line, decode(&x_India_only,'Y',wdel.name,'0') delivery_number, decode(&x_Thailand_only,'Y',ln.attribute12,null) fcs_product_type, decode(&x_Thailand_only,'Y',ln.list_price,null) list_price from SO_PICKING_LINES_ALL pick, SO_PICKING_HEADERS_ALL phead, WSH_DELIVERIES wdel, SO_LINES_ALL ln, MTL_SYSTEM_ITEMS item, SO_HEADERS_ALL hd, RA_SITE_USES_ALL ra, SO_ORDER_SOURCES source, SO_ORDER_TYPES_ALL ot where pick.attribute14 in ('IR','I') and pick.attribute15 = to_char(&p_event_id) and hd.attribute2 = &p_project_number and ln.attribute4 = &p_task_number and (pick.shipped_quantity is not null and pick.shipped_quantity <> 0) and hd.header_id = ln.header_id and ln.line_id = pick.order_line_id and pick.picking_header_id = phead.picking_header_id and phead.delivery_id = wdel.delivery_id and item.inventory_item_id = ln.inventory_item_id and ln.warehouse_id = item.organization_id and to_number(hd.original_system_source_code) = source.order_source_id(+) and ot.order_type_id = hd.order_type_id and ra.site_use_id = nvl(ln.ship_to_site_use_id,hd.ship_to_site_use_id) group by hd.attribute2, ln.attribute4, hd.order_number, ln.line_number, decode(ot.attribute4,'Y', nvl(ln.attribute3,hd.purchase_order_num), hd.purchase_order_num), hd.date_ordered, source.name, item.segment1, ln.ordered_quantity - nvl(ln.cancelled_quantity,0), ln.selling_price, item.inventory_item_id, item.organization_id, ln.warehouse_id, item.description, ln.unit_code, hd.currency_code, ln.tax_code, nvl(hd.conversion_type_code,'Corporate'), ot.name, ra.address_id, ln.line_id, decode(instr(ln.attribute5,'|',1,2), 0,null,ln.attribute5), substr(decode(ln.attribute14,NULL,to_char(ln.line_number), decode(instr(ln.attribute14,'|',1,1),0,ln.attribute14, substr(ln.attribute14,1,INSTR(ln.attribute14,'|',1) -1))),1,30), decode(&x_India_only,'Y',wdel.name,'0'), decode(&x_Thailand_only,'Y',ln.attribute12,null), decode(&x_Thailand_only,'Y',ln.list_price,null) Plan de ejecución ================= Select Statement RULE Rows: 6 Cost: 130 Sort Group By Rows: 6 Cost: 130 HD.ATTRIBUTE2, LN.ATTRIBUTE4, HD.ORDER_NUMBER, LN.LINE_NUMBER, DECODE(OT.ATTRIBUTE4,'Y', NVL(LN.ATTRIBUTE3,HD.PURCHASE_ORDER_NUM), HD.PURCHASE_ORDER_NUM), HD.DATE_ORDERED, SOURCE.NAME, ITEM.SEGMENT1, LN.ORDERED_QUANTITY - NVL(LN.CANCELLED_QUANTITY,0), LN.SELLING_PRICE, ITEM.INVENTORY_ITEM_ID, ITEM.ORGANIZATION_ID, LN.WAREHOUSE_ID, ITEM.DESCRIPTION, LN.UNIT_CODE, HD.CURRENCY_CODE, LN.TAX_CODE, NVL(HD.CONVERSION_TYPE_CODE,'Corporate'), OT.NAME, RA.ADDRESS_ID, LN.LINE_ID, DECODE(INSTR(LN.ATTRIBUTE5,'|',1,2), 0,NULL,LN.ATTRIBUTE5), SUBSTR(DECODE(LN.ATTRIBUTE14,NULL,TO_CHAR(LN.LINE_NUMBER), DECODE(INSTR(LN.ATTRIBUTE14,'|',1,1),0,LN.ATTRIBUTE14, SUBSTR(LN.ATTRIBUTE14,1,INSTR(LN.ATTRIBUTE14,'|',1) -1))),1,30), DECODE(&X_INDIA_ONLY,'Y',WDEL.NAME,'0'), DECODE(&X_THAILAND_ONLY,'Y',LN.ATTRIBUTE12,NULL), DECODE(&X_THAILAND_ONLY,'Y',LN.LIST_PRICE,NULL) Nested Loops Rows: 6 Cost: 121 Nested Loops Outer Rows: 6 Cost: 115 Nested Loops Rows: 6 Cost: 109 Nested Loops Rows: 6 Cost: 103 Nested Loops Rows: 14 Cost: 89 Nested Loops Rows: 14 Cost: 61 Nested Loops Rows: 14 Cost: 33 Nested Loops Rows: 14 Cost: 19 Table Access By Index Rowid Oe.so_picking_lines_all Rows: 14 Cost: 5 (PICK.ATTRIBUTE14 = 'IR' OR PICK.ATTRIBUTE14 = 'I') AND (PICK.SHIPPED_QUANTITY is not null AND PICK.SHIPPED_QUANTITY <> 0) Non-unique Range Scan Qerp.mot_so_picking_lines_all_idx3 [Analyzed] Keys: 0.000 Rows Per Key: 1.00 Rows: 14 Cost: 3 PICK.ATTRIBUTE15 = TO_CHAR(&P_EVENT_ID) Table Access By Index Rowid Oe.so_picking_headers_all Rows: 309,590 Cost: 1 Unique Unique Scan Oe.so_picking_headers_u1 [Analyzed] Keys: 0.000 Rows Per Key: 1.00 Rows: 309,590 PICK.PICKING_HEADER_ID = PHEAD.PICKING_HEADER_ID Table Access By Index Rowid Oe.wsh_deliveries Rows: 148,646 Cost: 1 Unique Unique Scan Oe.wsh_deliveries_u1 [Analyzed] Keys: 0.000 Rows Per Key: 1.00 Rows: 148,646 PHEAD.DELIVERY_ID = WDEL.DELIVERY_ID Table Access By Index Rowid Oe.so_lines_all Rows: 260 Cost: 2 LN.ATTRIBUTE4 = &P_TASK_NUMBER/* and apps.oe_query.get_entry_status_name(hd.open_flag, hd.cancelled_flag, hd.s1) *//* in ('Booked','Closed') */ Unique Unique Scan Oe.so_lines_u1 [Analyzed] Keys: 0.000 Rows Per Key: 1.00 Rows: 260 Cost: 1 LN.LINE_ID = PICK.ORDER_LINE_ID Table Access By Index Rowid Inv.mtl_system_items Rows: 304,604 Cost: 2 Unique Unique Scan Inv.mtl_system_items_u1 [Analyzed] Keys: 0.000 Rows Per Key: 1.00 Rows: 304,604 Cost: 1 ITEM.INVENTORY_ITEM_ID = LN.INVENTORY_ITEM_ID AND LN.WAREHOUSE_ID = ITEM.ORGANIZATION_ID Table Access By Index Rowid Oe.so_headers_all Rows: 74 Cost: 1 HD.ATTRIBUTE2 = &P_PROJECT_NUMBER Unique Unique Scan Oe.so_headers_u1 [Analyzed] Keys: 0.000 Rows Per Key: 1.00 Rows: 74 HD.HEADER_ID = LN.HEADER_ID Table Access By Index Rowid Ar.ra_site_uses_all Rows: 8,207 Cost: 1 Unique Unique Scan Ar.ra_site_uses_u1 [Analyzed] Keys: 0.000 Rows Per Key: 1.00 Rows: 8,207 RA.SITE_USE_ID = NVL(LN.SHIP_TO_SITE_USE_ID,HD.SHIP_TO_SITE_USE_ID) Table Access By Index Rowid Oe.so_order_sources Rows: 24 Cost: 1 Unique Unique Scan Oe.so_order_sources_u1 [Analyzed] Keys: 0.000 Rows Per Key: 1.00 Rows: 24 TO_NUMBER(HD.ORIGINAL_SYSTEM_SOURCE_CODE) = SOURCE.ORDER_SOURCE_ID(+) Table Access By Index Rowid Oe.so_order_types_all Rows: 266 Cost: 1 Unique Unique Scan Oe.so_order_types_u1 [Analyzed] Keys: 0.000 Rows Per Key: 1.00 Rows: 266 OT.ORDER_TYPE_ID = HD.ORDER_TYPE_ID Plan de ejecución "traducido" (pero en inglés) ============================================== 1. Rows were retrieved using the index MOT_SO_PICKING_LINES_ALL_IDX3. 2. Rows from table SO_PICKING_LINES_ALL were accessed using rowid got from an index. 3. Rows were retrieved using the unique index SO_PICKING_HEADERS_U1. 4. Rows from table SO_PICKING_HEADERS_ALL were accessed using rowid got from an index. 5. For each row retrieved by step 2, the operation in step 4 was performed to find a matching row. 6. Rows were retrieved using the unique index WSH_DELIVERIES_U1. 7. Rows from table WSH_DELIVERIES were accessed using rowid got from an index. 8. For each row retrieved by step 5, the operation in step 7 was performed to find a matching row. 9. Rows were retrieved using the unique index SO_LINES_U1. 10. Rows from table SO_LINES_ALL were accessed using rowid got from an index. 11. For each row retrieved by step 8, the operation in step 10 was performed to find a matching row. 12. Rows were retrieved using the unique index MTL_SYSTEM_ITEMS_U1. 13. Rows from table MTL_SYSTEM_ITEMS were accessed using rowid got from an index. 14. For each row retrieved by step 11, the operation in step 13 was performed to find a matching row. 15. Rows were retrieved using the unique index SO_HEADERS_U1. 16. Rows from table SO_HEADERS_ALL were accessed using rowid got from an index. 17. For each row retrieved by step 14, the operation in step 16 was performed to find a matching row. 18. Rows were retrieved using the unique index RA_SITE_USES_U1. 19. Rows from table RA_SITE_USES_ALL were accessed using rowid got from an index. 20. For each row retrieved by step 17, the operation in step 19 was performed to find a matching row. 21. Rows were retrieved using the unique index SO_ORDER_SOURCES_U1. 22. Rows from table SO_ORDER_SOURCES were accessed using rowid got from an index. 23. For each row returned by step 20 get the matching row from step 22 If there are not matching rows from step 22 return nulls for those columns. 24. Rows were retrieved using the unique index SO_ORDER_TYPES_U1. 25. Rows from table SO_ORDER_TYPES_ALL were accessed using rowid got from an index. 26. For each row retrieved by step 23, the operation in step 25 was performed to find a matching row. 27. The rows were sorted in order to be grouped. 28. Rows were returned by the SELECT statement.