视图中使用ROWNUM要注意
昨天晚上帮一位兄弟优化一个ebs的sql。sql有好几百行。
SQL的样子是select .... from 视图 where ....过滤条件
视图的代码贴出来给大家看一下，比较长
CREATE OR REPLACE VIEW CUX_RCV_TRANSACTION_F AS
SELECT
rownum order_number,'' RCV_REMARKS
,'N' selected_flag
,rcv_enter_receipts_v.po_number
,rcv_enter_receipts_v.po_line_number
,cux_po_rcv_common_webutil.get_mtl_system_item(rcv_enter_receipts_v.item_id,
rcv_enter_receipts_v.to_organization_id) item_name
,rcv_enter_receipts_v.item_description
,cux_po_rcv_common_webutil.get_mtl_category(rcv_enter_receipts_v.item_category_id) item_category
,cux_po_rcv_common_webutil.get_rcv_receipts_query_info(cux_po_rcv_common_webutil.get_gp_transaction_uom_tl,
rcv_enter_receipts_v.po_line_location_id,
rcv_enter_receipts_v.rcv_shipment_line_id,
rcv_enter_receipts_v.receipt_source_code,
rcv_enter_receipts_v.to_organization_id,
rcv_enter_receipts_v.item_id,
rcv_enter_receipts_v.primary_uom_class,
rcv_enter_receipts_v.ship_to_location_id,
rcv_enter_receipts_v.vendor_id,
rcv_enter_receipts_v.customer_id,
rcv_enter_receipts_v.item_rev_control_flag_to,
rcv_enter_receipts_v.asn_type,
rcv_enter_receipts_v.oe_order_header_id,
rcv_enter_receipts_v.oe_order_line_id,
rcv_enter_receipts_v.from_organization_id) transaction_uom_tl
,nvl(cux_po_rcv_common_webutil.get_po_line_info(cux_po_rcv_common_webutil.get_gp_po_l_attribute3,
rcv_enter_receipts_v.po_line_id),
0) AS cux_receipt_location_id
,SYSDATE cux_receipt_date
,cux_po_rcv_common_webutil.get_requisition_info(cux_po_rcv_common_webutil.get_gp_pr_deliver_to_loc_dsp,
rcv_enter_receipts_v.po_header_id,
rcv_enter_receipts_v.po_line_id,
rcv_enter_receipts_v.po_line_location_id) deliver_to_location_dsp
,rcv_enter_receipts_v.ordered_qty
,rcv_enter_receipts_v.expected_receipt_date
,cux_po_rcv_common_webutil.get_po_line_info(cux_po_rcv_common_webutil.get_gp_po_l_attribute8,
rcv_enter_receipts_v.po_line_id) po_l_attribute8
,cux_po_rcv_common_webutil.get_requisition_info2(cux_po_rcv_common_webutil.get_gp_pr_requestor,
rcv_enter_receipts_v.po_header_id,
rcv_enter_receipts_v.po_line_id,
rcv_enter_receipts_v.po_line_location_id) requestor
,cux_po_rcv_common_webutil.get_vendor_name(rcv_enter_receipts_v.vendor_id) vendor_name
,cux_po_rcv_common_webutil.get_rcv_receipts_query_info(cux_po_rcv_common_webutil.get_gp_destination_type_dsp,
rcv_enter_receipts_v.po_line_location_id,
rcv_enter_receipts_v.rcv_shipment_line_id,
rcv_enter_receipts_v.receipt_source_code,
rcv_enter_receipts_v.to_organization_id,
rcv_enter_receipts_v.item_id,
rcv_enter_receipts_v.primary_uom_class,
rcv_enter_receipts_v.ship_to_location_id,
rcv_enter_receipts_v.vendor_id,
rcv_enter_receipts_v.customer_id,
rcv_enter_receipts_v.item_rev_control_flag_to,
rcv_enter_receipts_v.asn_type,
rcv_enter_receipts_v.oe_order_header_id,
rcv_enter_receipts_v.oe_order_line_id,
rcv_enter_receipts_v.from_organization_id) destination_type_dsp
,rcv_enter_receipts_v.unit_price
,rcv_enter_receipts_v.line_chkbox
,rcv_enter_receipts_v.primary_uom
,rcv_enter_receipts_v.item_revision
,rcv_enter_receipts_v.asn_type
,rcv_enter_receipts_v.country_of_origin_code
,rcv_enter_receipts_v.vendor_lot_num
,rcv_enter_receipts_v.receipt_required_flag
,rcv_enter_receipts_v.hazard_class
,rcv_enter_receipts_v.un_number
,rcv_enter_receipts_v.ussgl_transaction_code
,rcv_enter_receipts_v.currency_code
,rcv_enter_receipts_v.currency_conversion_type
,rcv_enter_receipts_v.rate_type_display
,rcv_enter_receipts_v.currency_conversion_date
,rcv_enter_receipts_v.currency_conversion_rate
,rcv_enter_receipts_v.order_type
,rcv_enter_receipts_v.po_release_number
,rcv_enter_receipts_v.po_shipment_number
,rcv_enter_receipts_v.source
,rcv_enter_receipts_v.ordered_uom
,rcv_enter_receipts_v.secondary_ordered_qty
,rcv_enter_receipts_v.secondary_ordered_uom
,rcv_enter_receipts_v.vendor_item_number
,rcv_enter_receipts_v.customer_item_num
,rcv_enter_receipts_v.freight_carrier_code
,rcv_enter_receipts_v.container_num
,rcv_enter_receipts_v.truck_num
,rcv_enter_receipts_v.bill_of_lading
,rcv_enter_receipts_v.bar_code_label
,rcv_enter_receipts_v.waybill_airbill_num
,rcv_enter_receipts_v.freight_bill_num
,rcv_enter_receipts_v.po_header_id
,rcv_enter_receipts_v.source_type_code
,rcv_enter_receipts_v.receipt_source_code
,rcv_enter_receipts_v.order_type_code
,rcv_enter_receipts_v.po_line_id
,rcv_enter_receipts_v.po_release_id
,rcv_enter_receipts_v.po_line_location_id
,rcv_enter_receipts_v.ship_to_location_id
,rcv_enter_receipts_v.ship_to_location
,rcv_enter_receipts_v.need_by_date
,rcv_enter_receipts_v.government_context
,rcv_enter_receipts_v.inspection_required_flag
,rcv_enter_receipts_v.enforce_ship_to_location_code
,rcv_enter_receipts_v.rcv_shipment_header_id
,rcv_enter_receipts_v.rcv_shipment_number
,rcv_enter_receipts_v.rcv_shipment_line_id
,rcv_enter_receipts_v.rcv_line_number
,rcv_enter_receipts_v.from_organization_id
,rcv_enter_receipts_v.to_organization_id
,rcv_enter_receipts_v.vendor_id
,rcv_enter_receipts_v.customer_id
,rcv_enter_receipts_v.vendor_site_id
,rcv_enter_receipts_v.customer_site_id
,rcv_enter_receipts_v.item_category_id
,rcv_enter_receipts_v.req_number
,rcv_enter_receipts_v.req_header_id
,rcv_enter_receipts_v.req_line_id
,rcv_enter_receipts_v.req_line
,rcv_enter_receipts_v.req_distribution_id
,rcv_enter_receipts_v.outside_operation_flag
,rcv_enter_receipts_v.item_id
,rcv_enter_receipts_v.primary_uom_class
,rcv_enter_receipts_v.item_allowed_units_lookup_code
,rcv_enter_receipts_v.item_locator_control
,rcv_enter_receipts_v.restrict_locators_code
,rcv_enter_receipts_v.restrict_subinventories_code
,rcv_enter_receipts_v.shelf_life_code
,rcv_enter_receipts_v.shelf_life_days
,rcv_enter_receipts_v.lot_control_code
,rcv_enter_receipts_v.serial_number_control_code
,rcv_enter_receipts_v.item_rev_control_flag_to
,rcv_enter_receipts_v.item_rev_control_flag_from
,rcv_enter_receipts_v.routing_id
,rcv_enter_receipts_v.routing_name
,rcv_enter_receipts_v.note_to_receiver
,rcv_enter_receipts_v.pll_note_to_receiver
,rcv_enter_receipts_v.closed_code
,rcv_enter_receipts_v.attribute_category
,rcv_enter_receipts_v.attribute1
,rcv_enter_receipts_v.attribute2
,rcv_enter_receipts_v.attribute3
,rcv_enter_receipts_v.attribute4
,rcv_enter_receipts_v.attribute5
,rcv_enter_receipts_v.attribute6
,rcv_enter_receipts_v.attribute7
,rcv_enter_receipts_v.attribute8
,rcv_enter_receipts_v.attribute9
,rcv_enter_receipts_v.attribute10
,rcv_enter_receipts_v.attribute11
,rcv_enter_receipts_v.attribute12
,rcv_enter_receipts_v.attribute13
,rcv_enter_receipts_v.attribute14
,rcv_enter_receipts_v.attribute15
,rcv_enter_receipts_v.shipped_date
,rcv_enter_receipts_v.match_option
,rcv_enter_receipts_v.oe_order_num
,rcv_enter_receipts_v.oe_order_line_num
,rcv_enter_receipts_v.oe_order_header_id
,rcv_enter_receipts_v.oe_order_line_id
,rcv_enter_receipts_v.lpn_id
,cux_po_rcv_common_webutil.get_po_header_info(cux_po_rcv_common_webutil.get_gp_po_h_agent_name,
rcv_enter_receipts_v.po_header_id) agent_name
,cux_po_rcv_common_webutil.get_po_header_info(cux_po_rcv_common_webutil.get_gp_po_h_agent_id,
rcv_enter_receipts_v.po_header_id) agent_id
,cux_po_rcv_common_webutil.get_requisition_info2(cux_po_rcv_common_webutil.get_gp_pr_requestor_id,
rcv_enter_receipts_v.po_header_id,
rcv_enter_receipts_v.po_line_id,
rcv_enter_receipts_v.po_line_location_id) requestor_id
,
cux_po_rcv_common_webutil.get_rcv_receipts_query_info(cux_po_rcv_common_webutil.get_gp_uom_code,
rcv_enter_receipts_v.po_line_location_id,
rcv_enter_receipts_v.rcv_shipment_line_id,
rcv_enter_receipts_v.receipt_source_code,
rcv_enter_receipts_v.to_organization_id,
rcv_enter_receipts_v.item_id,
rcv_enter_receipts_v.primary_uom_class,
rcv_enter_receipts_v.ship_to_location_id,
rcv_enter_receipts_v.vendor_id,
rcv_enter_receipts_v.customer_id,
rcv_enter_receipts_v.item_rev_control_flag_to,
rcv_enter_receipts_v.asn_type,
rcv_enter_receipts_v.oe_order_header_id,
rcv_enter_receipts_v.oe_order_line_id,
rcv_enter_receipts_v.from_organization_id) uom_code
,cux_po_rcv_common_webutil.get_po_unit_price(rcv_enter_receipts_v.po_release_id,
rcv_enter_receipts_v.po_line_location_id) po_unit_price
,cux_po_rcv_common_webutil.get_rcv_receipts_query_info(cux_po_rcv_common_webutil.get_gp_po_distribution_id,
rcv_enter_receipts_v.po_line_location_id,
rcv_enter_receipts_v.rcv_shipment_line_id,
rcv_enter_receipts_v.receipt_source_code,
rcv_enter_receipts_v.to_organization_id,
rcv_enter_receipts_v.item_id,
rcv_enter_receipts_v.primary_uom_class,
rcv_enter_receipts_v.ship_to_location_id,
rcv_enter_receipts_v.vendor_id,
rcv_enter_receipts_v.customer_id,
rcv_enter_receipts_v.item_rev_control_flag_to,
rcv_enter_receipts_v.asn_type,
rcv_enter_receipts_v.oe_order_header_id,
rcv_enter_receipts_v.oe_order_line_id,
rcv_enter_receipts_v.from_organization_id) po_distribution_id
,cux_po_rcv_common_webutil.get_rcv_receipts_query_info(cux_po_rcv_common_webutil.get_gp_deliver_to_person_id,
rcv_enter_receipts_v.po_line_location_id,
rcv_enter_receipts_v.rcv_shipment_line_id,
rcv_enter_receipts_v.receipt_source_code,
rcv_enter_receipts_v.to_organization_id,
rcv_enter_receipts_v.item_id,
rcv_enter_receipts_v.primary_uom_class,
rcv_enter_receipts_v.ship_to_location_id,
rcv_enter_receipts_v.vendor_id,
rcv_enter_receipts_v.customer_id,
rcv_enter_receipts_v.item_rev_control_flag_to,
rcv_enter_receipts_v.asn_type,
rcv_enter_receipts_v.oe_order_header_id,
rcv_enter_receipts_v.oe_order_line_id,
rcv_enter_receipts_v.from_organization_id) deliver_to_person_id
,cux_po_rcv_common_webutil.get_rcv_receipts_query_info(cux_po_rcv_common_webutil.get_gp_deliver_to_location_id,
rcv_enter_receipts_v.po_line_location_id,
rcv_enter_receipts_v.rcv_shipment_line_id,
rcv_enter_receipts_v.receipt_source_code,
rcv_enter_receipts_v.to_organization_id,
rcv_enter_receipts_v.item_id,
rcv_enter_receipts_v.primary_uom_class,
rcv_enter_receipts_v.ship_to_location_id,
rcv_enter_receipts_v.vendor_id,
rcv_enter_receipts_v.customer_id,
rcv_enter_receipts_v.item_rev_control_flag_to,
rcv_enter_receipts_v.asn_type,
rcv_enter_receipts_v.oe_order_header_id,
rcv_enter_receipts_v.oe_order_line_id,
rcv_enter_receipts_v.from_organization_id) deliver_to_location_id
,cux_po_rcv_common_webutil.get_rcv_receipts_query_info(cux_po_rcv_common_webutil.get_gp_deliver_to_locator_id,
rcv_enter_receipts_v.po_line_location_id,
rcv_enter_receipts_v.rcv_shipment_line_id,
rcv_enter_receipts_v.receipt_source_code,
rcv_enter_receipts_v.to_organization_id,
rcv_enter_receipts_v.item_id,
rcv_enter_receipts_v.primary_uom_class,
rcv_enter_receipts_v.ship_to_location_id,
rcv_enter_receipts_v.vendor_id,
rcv_enter_receipts_v.customer_id,
rcv_enter_receipts_v.item_rev_control_flag_to,
rcv_enter_receipts_v.asn_type,
rcv_enter_receipts_v.oe_order_header_id,
rcv_enter_receipts_v.oe_order_line_id,
rcv_enter_receipts_v.from_organization_id) deliver_to_locator_id
,to_number(cux_po_rcv_common_webutil.get_cux_interface_quantity(rcv_enter_receipts_v.po_line_location_id,
rcv_enter_receipts_v.receipt_source_code)) cux_interface_quantity
,
to_number(cux_po_rcv_common_webutil.get_rcv_receipts_query_info(cux_po_rcv_common_webutil.get_gp_available_qty,
rcv_enter_receipts_v.po_line_location_id,
rcv_enter_receipts_v.rcv_shipment_line_id,
rcv_enter_receipts_v.receipt_source_code,
rcv_enter_receipts_v.to_organization_id,
rcv_enter_receipts_v.item_id,
rcv_enter_receipts_v.primary_uom_class,
rcv_enter_receipts_v.ship_to_location_id,
rcv_enter_receipts_v.vendor_id,
rcv_enter_receipts_v.customer_id,
rcv_enter_receipts_v.item_rev_control_flag_to,
rcv_enter_receipts_v.asn_type,
rcv_enter_receipts_v.oe_order_header_id,
rcv_enter_receipts_v.oe_order_line_id,
rcv_enter_receipts_v.from_organization_id)) cux_transaction_quantity
,
to_number(cux_po_rcv_common_webutil.get_rcv_receipts_query_info(cux_po_rcv_common_webutil.get_gp_primary_qty,
rcv_enter_receipts_v.po_line_location_id,
rcv_enter_receipts_v.rcv_shipment_line_id,
rcv_enter_receipts_v.receipt_source_code,
rcv_enter_receipts_v.to_organization_id,
rcv_enter_receipts_v.item_id,
rcv_enter_receipts_v.primary_uom_class,
rcv_enter_receipts_v.ship_to_location_id,
rcv_enter_receipts_v.vendor_id,
rcv_enter_receipts_v.customer_id,
rcv_enter_receipts_v.item_rev_control_flag_to,
rcv_enter_receipts_v.asn_type,
rcv_enter_receipts_v.oe_order_header_id,
rcv_enter_receipts_v.oe_order_line_id,
rcv_enter_receipts_v.from_organization_id)) primary_qty
,
cux_po_rcv_common_webutil.get_rcv_receipts_query_info(cux_po_rcv_common_webutil.get_gp_destination_type_code,
rcv_enter_receipts_v.po_line_location_id,
rcv_enter_receipts_v.rcv_shipment_line_id,
rcv_enter_receipts_v.receipt_source_code,
rcv_enter_receipts_v.to_organization_id,
rcv_enter_receipts_v.item_id,
rcv_enter_receipts_v.primary_uom_class,
rcv_enter_receipts_v.ship_to_location_id,
rcv_enter_receipts_v.vendor_id,
rcv_enter_receipts_v.customer_id,
rcv_enter_receipts_v.item_rev_control_flag_to,
rcv_enter_receipts_v.asn_type,
rcv_enter_receipts_v.oe_order_header_id,
rcv_enter_receipts_v.oe_order_line_id,
rcv_enter_receipts_v.from_organization_id) destination_type_code
,
--接收类型
cux_po_rcv_common_webutil.get_po_header_info(cux_po_rcv_common_webutil.get_gp_po_h_attribute5,
rcv_enter_receipts_v.po_header_id) po_h_attribute5
,cux_po_rcv_common_webutil.get_po_l_location_info(cux_po_rcv_common_webutil.get_gp_po_l_location_prm_date,
rcv_enter_receipts_v.po_line_location_id) promised_date
,rcv_enter_receipts_v.req_submitter_id
FROM (SELECT /*+ LEADING(POH) */
'N' line_chkbox
,'VENDOR' source_type_code
,'VENDOR' receipt_source_code
,'PO' order_type_code
,poh.type_lookup_code order_type
,poll.po_header_id
,poh.segment1 po_number
,poll.po_line_id
,pol.line_num po_line_number
,poll.line_location_id po_line_location_id
,poll.shipment_num po_shipment_number
,poll.po_release_id
,por.release_num po_release_number
,to_number(NULL) req_header_id
,NULL req_number
,to_number(NULL) req_line_id
,to_number(NULL) req_line
,to_number(NULL) req_distribution_id
,to_number(NULL) rcv_shipment_header_id
,NULL rcv_shipment_number
,to_number(NULL) rcv_shipment_line_id
,to_number(NULL) rcv_line_number
,poh.po_header_id from_organization_id
,poll.ship_to_organization_id to_organization_id
,poh.vendor_id vendor_id
,pov.vendor_name SOURCE
,poh.vendor_site_id
,nvl(polt.outside_operation_flag, 'N') outside_operation_flag
,pol.item_id
,pol.unit_meas_lookup_code primary_uom
,mum.uom_class primary_uom_class
,
nvl(msi.allowed_units_lookup_code, 2) item_allowed_units_lookup_code
,nvl(msi.location_control_code, 1) item_locator_control
,decode(msi.restrict_locators_code, 1, 'Y', 'N') restrict_locators_code
,decode(msi.restrict_subinventories_code, 1, 'Y', 'N') restrict_subinventories_code
,nvl(msi.shelf_life_code, 1) shelf_life_code
,nvl(msi.shelf_life_days, 0) shelf_life_days
,msi.serial_number_control_code
,msi.lot_control_code
,decode(msi.revision_qty_control_code, 1, 'N', 2, 'Y', 'N') item_rev_control_flag_to
,NULL item_rev_control_flag_from
,NULL item_number
,pol.item_revision
,pol.item_description
,pol.category_id item_category_id
,pohc.hazard_class
,poun.un_number
,pol.vendor_product_num vendor_item_number
,poll.ship_to_location_id
,hl.location_code ship_to_location
,NULL packing_slip
,poll.receiving_routing_id routing_id
,rcvrh.routing_name
,poll.need_by_date
,nvl(poll.promised_date, poll.need_by_date) expected_receipt_date
,poll.quantity ordered_qty
,pol.unit_meas_lookup_code ordered_uom
,NULL ussgl_transaction_code
,poll.government_context
,poll.inspection_required_flag
,poll.receipt_required_flag
,poll.enforce_ship_to_location_code
,nvl(poll.price_override, pol.unit_price) unit_price
,poh.currency_code
,poh.rate_type currency_conversion_type
,poh.rate_date currency_conversion_date
,poh.rate currency_conversion_rate
,
poh.note_to_receiver
,NULL destination_type_code
,to_number(NULL) deliver_to_person_id
,to_number(NULL) deliver_to_location_id
,NULL destination_subinventory
,poll.attribute_category
,poll.attribute1
,poll.attribute2
,poll.attribute3
,poll.attribute4
,poll.attribute5
,poll.attribute6
,poll.attribute7
,poll.attribute8
,poll.attribute9
,poll.attribute10
,poll.attribute11
,poll.attribute12
,poll.attribute13
,poll.attribute14
,poll.attribute15
,poll.closed_code
,NULL asn_type
,NULL bill_of_lading
,to_date(NULL) shipped_date
,NULL freight_carrier_code
,NULL waybill_airbill_num
,NULL freight_bill_num
,NULL vendor_lot_num
,NULL container_num
,NULL truck_num
,NULL bar_code_label
,dct.user_conversion_type rate_type_display
,poll.match_option
,poll.country_of_origin_code
,to_number(NULL) oe_order_header_id
,to_number(NULL) oe_order_num
,to_number(NULL) oe_order_line_id
,to_number(NULL) oe_order_line_num
,to_number(NULL) customer_id
,to_number(NULL) customer_site_id
,NULL customer_item_num
,poll.note_to_receiver pll_note_to_receiver
,poll.secondary_quantity secondary_ordered_qty
,poll.secondary_unit_of_measure secondary_ordered_uom
,poll.preferred_grade qc_grade
,to_number(NULL) lpn_id
,pol.attribute9 req_submitter_id --ADD BY CUIYANWEI 20101210
FROM po_headers_all poh
,po_line_locations_all poll-------------------
,po_lines_all pol
,po_releases_all por
,po_vendors pov
,po_hazard_classes_tl pohc
,po_un_numbers_tl poun
,rcv_routing_headers rcvrh
,hr_locations_all_tl hl
,mtl_system_items msi
,mtl_units_of_measure mum
,po_line_types_b polt
,gl_daily_conversion_types dct
WHERE nvl(poll.approved_flag, 'N') = 'Y'--
AND nvl(poll.cancel_flag, 'N') = 'N'--
AND nvl(poll.closed_code, 'OPEN') != 'FINALLY CLOSED'--
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')---
AND poh.po_header_id = poll.po_header_id----
AND pol.po_line_id = poll.po_line_id---
AND pol.hazard_class_id = pohc.hazard_class_id(+)
AND pohc.language(+) = userenv('LANG')
AND pol.un_number_id = poun.un_number_id(+)
AND poun.language(+) = userenv('LANG')
AND poll.po_release_id = por.po_release_id(+)---
AND poll.ship_to_location_id = hl.location_id(+)
AND hl.language(+) = userenv('LANG')
AND poh.vendor_id = pov.vendor_id(+)
AND pol.line_type_id = polt.line_type_id(+)
AND poll.receiving_routing_id = rcvrh.routing_header_id(+)
AND mum.unit_of_measure(+) = pol.unit_meas_lookup_code
AND nvl(msi.organization_id, poll.ship_to_organization_id) =
poll.ship_to_organization_id-------------
AND msi.inventory_item_id(+) = pol.item_id
AND dct.conversion_type(+) = poh.rate_type
AND nvl(poh.consigned_consumption_flag, 'N') = 'N'
AND nvl(por.consigned_consumption_flag, 'N') = 'N'
AND nvl(pol.matching_basis, 'QUANTITY') != 'AMOUNT'
UNION ALL
SELECT /*+ LEADING(POH) */ 'N' line_chkbox
,'ASN' source_type_code
,'VENDOR' receipt_source_code
,'PO' order_type_code
,poh.type_lookup_code order_type
,poll.po_header_id
,poh.segment1 po_number
,poll.po_line_id
,pol.line_num po_line_number
,poll.line_location_id po_line_location_id
,poll.shipment_num po_shipment_number
,poll.po_release_id
,por.release_num po_release_number
,to_number(NULL) req_header_id
,NULL req_number
,to_number(NULL) req_line_id
,to_number(NULL) req_line
,to_number(NULL) req_distribution_id
,
rsh.shipment_header_id rcv_shipment_header_id
,rsh.shipment_num rcv_shipment_number
,rsl.shipment_line_id rcv_shipment_line_id
,rsl.line_num rcv_line_number
,nvl(rsl.from_organization_id, poh.po_header_id) from_organization_id
,rsl.to_organization_id
,rsh.vendor_id
,pov.vendor_name SOURCE
,rsh.vendor_site_id
,nvl(polt.outside_operation_flag, 'N') outside_operation_flag
,rsl.item_id
,rsl.unit_of_measure primary_uom
,mum.uom_class primary_uom_class
,nvl(msi.allowed_units_lookup_code, 2) item_allowed_units_lookup_code
,nvl(msi.location_control_code, 1) item_locator_control
,decode(msi.restrict_locators_code, 1, 'Y', 'N') restrict_locators_code
,decode(msi.restrict_subinventories_code, 1, 'Y', 'N') restrict_subinventories_code
,nvl(msi.shelf_life_code, 1) shelf_life_code
,nvl(msi.shelf_life_days, 0) shelf_life_days
,msi.serial_number_control_code
,msi.lot_control_code
,decode(msi.revision_qty_control_code, 1, 'N', 2, 'Y', 'N') item_rev_control_flag_to
,NULL item_rev_control_flag_from
,NULL item_number
,rsl.item_revision
,rsl.item_description
,rsl.category_id item_category_id
,pohc.hazard_class
,poun.un_number
,rsl.vendor_item_num
,rsl.ship_to_location_id
,hl.location_code ship_to_location
,rsl.packing_slip
,rsl.routing_header_id routing_id
,rcvrh.routing_name
,poll.need_by_date
,rsh.expected_receipt_date
,poll.quantity ordered_qty
,pol.unit_meas_lookup_code ordered_uom
,rsl.ussgl_transaction_code
,rsl.government_context
,
poll.inspection_required_flag
,poll.receipt_required_flag
,poll.enforce_ship_to_location_code
,nvl(poll.price_override, pol.unit_price) unit_price
,poh.currency_code
,poh.rate_type currency_conversion_type
,poh.rate_date currency_conversion_date
,poh.rate currency_conversion_rate
,poh.note_to_receiver
,rsl.destination_type_code
,rsl.deliver_to_person_id
,rsl.deliver_to_location_id
,rsl.to_subinventory destination_subinventory
,rsl.attribute_category
,rsl.attribute1
,rsl.attribute2
,rsl.attribute3
,rsl.attribute4
,rsl.attribute5
,rsl.attribute6
,rsl.attribute7
,rsl.attribute8
,rsl.attribute9
,rsl.attribute10
,rsl.attribute11
,rsl.attribute12
,rsl.attribute13
,rsl.attribute14
,rsl.attribute15
,poll.closed_code
,rsh.asn_type
,rsh.bill_of_lading
,rsh.shipped_date
,rsh.freight_carrier_code
,rsh.waybill_airbill_num
,rsh.freight_bill_number
,rsl.vendor_lot_num
,rsl.container_num
,rsl.truck_num
,rsl.bar_code_label
,dct.user_conversion_type rate_type_display
,
poll.match_option
,rsl.country_of_origin_code
,to_number(NULL) oe_order_header_id
,to_number(NULL) oe_order_num
,to_number(NULL) oe_order_line_id
,to_number(NULL) oe_order_line_num
,to_number(NULL) customer_id
,to_number(NULL) customer_site_id
,NULL customer_item_num
,poll.note_to_receiver pll_note_to_receiver
,poll.secondary_quantity secondary_ordered_qty
,poll.secondary_unit_of_measure secondary_ordered_uom
,poll.preferred_grade qc_grade
,rsl.asn_lpn_id
,pol.attribute9 req_submitter_id
FROM rcv_shipment_lines rsl
,rcv_shipment_headers rsh-----------------
,po_headers_all poh
,po_line_locations_all poll------------
,po_lines_all pol
,po_releases_all por
,po_vendors pov
,po_hazard_classes_tl pohc
,po_un_numbers_tl poun
,rcv_routing_headers rcvrh
,hr_locations_all_tl hl
,mtl_system_items msi
,mtl_units_of_measure mum
,po_line_types_b polt
,gl_daily_conversion_types dct
WHERE nvl(poll.approved_flag, 'N') = 'Y'
AND nvl(poll.cancel_flag, 'N') = 'N'
AND nvl(poll.closed_code, 'OPEN') != 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND poh.po_header_id = poll.po_header_id
AND pol.po_line_id = poll.po_line_id
AND pol.hazard_class_id = pohc.hazard_class_id(+)
AND pohc.language(+) = userenv('LANG')
AND pol.un_number_id = poun.un_number_id(+)
AND poun.language(+) = userenv('LANG')
AND poll.po_release_id = por.po_release_id(+)
AND rsl.ship_to_location_id = hl.location_id(+)
AND hl.language(+) = userenv('LANG')
AND rsh.vendor_id = pov.vendor_id(+)
AND pol.line_type_id = polt.line_type_id(+)
AND rsl.routing_header_id = rcvrh.routing_header_id(+)
AND mum.unit_of_measure(+) = rsl.unit_of_measure
AND nvl(msi.organization_id, rsl.to_organization_id) =
rsl.to_organization_id
AND msi.inventory_item_id(+) = rsl.item_id
AND poll.line_location_id = rsl.po_line_location_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsh.asn_type IN ('ASN', 'ASBN')
AND rsl.shipment_line_status_code != 'CANCELLED'
AND dct.conversion_type(+) = poh.rate_type
AND nvl(poh.consigned_consumption_flag, 'N') = 'N'
AND nvl(por.consigned_consumption_flag, 'N') = 'N'
AND NOT EXISTS
(SELECT 1
FROM rcv_transactions_interface rt
WHERE rt.shipment_line_id = rsl.shipment_line_id
AND nvl(rt.transaction_type, 'SHIP') = 'CANCEL'
AND nvl(rt.transaction_status_code, 'PENDING') = 'PENDING'
AND rt.po_line_location_id = rsl.po_line_location_id)
AND nvl(pol.matching_basis, 'QUANTITY') != 'AMOUNT') rcv_enter_receipts_v
WHERE ((nvl(rcv_enter_receipts_v.closed_code, 'OPEN') NOT IN
('CLOSED', 'CLOSED FOR RECEIVING')))
AND (to_number(cux_po_rcv_common_webutil.get_rcv_receipts_query_info(cux_po_rcv_common_webutil.get_gp_available_qty,
rcv_enter_receipts_v.po_line_location_id,
rcv_enter_receipts_v.rcv_shipment_line_id,
rcv_enter_receipts_v.receipt_source_code,
rcv_enter_receipts_v.to_organization_id,
rcv_enter_receipts_v.item_id,
rcv_enter_receipts_v.primary_uom_class,
rcv_enter_receipts_v.ship_to_location_id,
rcv_enter_receipts_v.vendor_id,
rcv_enter_receipts_v.customer_id,
rcv_enter_receipts_v.item_rev_control_flag_to,
rcv_enter_receipts_v.asn_type,
rcv_enter_receipts_v.oe_order_header_id,
rcv_enter_receipts_v.oe_order_line_id,
rcv_enter_receipts_v.from_organization_id)) -
to_number(cux_po_rcv_common_webutil.get_cux_interface_quantity(rcv_enter_receipts_v.po_line_location_id,
rcv_enter_receipts_v.receipt_source_code))) > 0;
那个sql要跑几十秒。然后那位兄弟把视图代码弄出来，再加过滤条件，0.2秒出结果。 他搞了半天没搞出来。
那位兄弟看执行计划用 plsql 工具 F5 查看。 ----记住这句话，谁用F5看执行计划谁就是菜鸟。之后通过查看 explain plan for ...这种执行计划解决问题
该问题类似：
sqlplus / as sysdba
grant dba to scott;
sqlplus scott/tiger
create table test as select * from dba_objects;
SQL> create table test as select * from dba_objects;
表已创建。
create or replace view push_test as select rownum as id,
a.* from test a;
SQL> create or replace view push_test as select rownum as id,
2 a.* from test a;
视图已创建。
create index idx_object_id on test(object_id);
SQL> set lines 200 pages 200
SQL> set autot trace
SQL> select * from push_test where object_id=2;
执行计划
----------------------------------------------------------
Plan hash value: 677040414
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 69219 | 14M| 291 (1)| 00:00:04 |
|* 1 | VIEW | PUSH_TEST | 69219 | 14M| 291 (1)| 00:00:04 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| TEST | 69219 | 13M| 291 (1)| 00:00:04 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=2)
Note
-----
- dynamic sampling used for this statement (level=6)
统计信息
----------------------------------------------------------
338 recursive calls
0 db block gets
1323 consistent gets
1033 physical reads
0 redo size
1459 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
create or replace view push2_test as select
a.* from test a;
SQL> create or replace view push2_test as select
2 a.* from test a;
视图已创建。
SQL> select * from push2_test where object_id=2;
执行计划
----------------------------------------------------------
Plan hash value: 985375477
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 207 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_ID"=2)
Note
-----
- dynamic sampling used for this statement (level=6)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1403 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
意思就是说 在视图 创建的时候 select rownum as ..... 多了这个ROWNUM 导致过滤条件推入不进视图，只能在视图外面过滤。 怎么看在外面过滤呢？
SQL> select * from push_test where object_id=2;
执行计划
----------------------------------------------------------
Plan hash value: 677040414
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 69219 | 14M| 291 (1)| 00:00:04 |
|* 1 | VIEW | PUSH_TEST | 69219 | 14M| 291 (1)| 00:00:04 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| TEST | 69219 | 13M| 291 (1)| 00:00:04 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=2)
Note
-----
- dynamic sampling used for this statement (level=6)
统计信息
----------------------------------------------------------
338 recursive calls
0 db block gets
1323 consistent gets
1033 physical reads
0 redo size
1459 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
看ID=1 这里 view 前面有* ,*就表示过滤， * 在view前面，说明过滤是在view上面过滤，而不是在里面过滤的。这个就导致跑得慢了。因为视图里面有rownum，CBO 必须做一个count操作，这个时候无法进行谓词推入，因为推入了，原始sql语句意义变化。
最终征求那位哥们意见，能否去掉 视图的 rownum ，如果能去掉，就能优化，如果不能去掉，那sql无法优化。 最终肯定是 干掉了。
记住了， 视图的select后面最好不要包含rownum，否则无法谓词推入。
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。