SQL & PL/SQL

I am having a problem with the following search query:

SELECT SHIPMENT_NUM
,SHIPPED_DATE
,FROM_ORGANIZATION_ID
,FROM_ORGANIZATION_NAME
,WAYBILL_AIRBILL_NUM
,EXPECTED_RECEIPT_DATE
,RECEIPT_NUM
,EMPLOYEE_ID
,BILL_OF_LADING
,FREIGHT_CARRIER_CODE
,PACKING_SLIP
,SHIP_TO_LOCATION_ID
,SHIP_TO_LOCATION
,NUM_OF_CONTAINERS
,COMMENTS
,SHIPMENT_HEADER_ID
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,USSGL_TRANSACTION_CODE
,GOVERNMENT_CONTEXT
,RECEIPT_SOURCE_CODE
,ASN_TYPE
,VENDOR_SITE
,VENDOR_NAME
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ROW_ID
FROM RCV_MSH_V
WHERE rcv_msh_v.ship_to_org_id = :1
and (rcv_msh_v.shipment_header_id in (select rsl.shipment_header_id
from rcv_shipment_lines rsl
where item_id=:2)) order by shipment_num,expected_receipt_date,shipped_date,shipment_header_id
--------------------------------

I have looked at the view used and have add a few index hints to force the CBO to use index scans vice full table scans. I also think that there is a problem with the UNION statement. The view follows:

CREATE OR REPLACE FORCE VIEW rcv_msh_v (row_id,
shipment_header_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
program_application_id,
program_id,
program_update_date,
request_id,
ussgl_transaction_code,
government_context,
comments,
bill_of_lading,
expected_receipt_date,
freight_carrier_code,
from_organization_name,
num_of_containers,
from_organization_id,
vendor_name,
vendor_site,
packing_slip,
employee_id,
receipt_num,
receipt_source_code,
shipment_num,
shipped_date,
ship_to_location,
ship_to_location_id,
waybill_airbill_num,
asn_type,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
vendor_id,
ship_to_org_id,
vendor_site_id
)
AS
SELECT rsh.ROWID row_id, rsh.shipment_header_id, rsh.last_updated_by,
rsh.last_update_date, rsh.last_update_login, rsh.created_by,
rsh.creation_date, rsh.program_application_id, rsh.program_id,
rsh.program_update_date, rsh.request_id, rsh.ussgl_transaction_code,
rsh.government_context, rsh.comments, rsh.bill_of_lading,
rsh.expected_receipt_date, rsh.freight_carrier_code,
org.organization_name from_organization_name, rsh.num_of_containers,
rsh.organization_id from_organization_id, NULL, NULL,
rsh.packing_slip, rsh.employee_id, rsh.receipt_num,
rsh.receipt_source_code, rsh.shipment_num, rsh.shipped_date,
hr.location_code ship_to_location, rsh.ship_to_location_id,
rsh.waybill_airbill_num, rsh.asn_type, rsh.attribute_category,
rsh.attribute1, rsh.attribute2, rsh.attribute3, rsh.attribute4,
rsh.attribute5, rsh.attribute6, rsh.attribute7, rsh.attribute8,
rsh.attribute9, rsh.attribute10, rsh.attribute11, rsh.attribute12,
rsh.attribute13, rsh.attribute14, rsh.attribute15, rsh.vendor_id,
rsh.ship_to_org_id, TO_NUMBER (NULL)
FROM rcv_shipment_headers rsh,
hr_locations_all_tl hr,
org_organization_definitions org
WHERE receipt_source_code IN ('INVENTORY', 'INTERNAL ORDER')
AND hr.location_id(+) = rsh.ship_to_location_id
AND hr.LANGUAGE(+) = USERENV ('LANG')
AND org.organization_id(+) = rsh.organization_id
UNION ALL
SELECT /*+ index (pov PO_VENDORS_U1) index (rsh RCV_SHIPMENT_HEADERS_N3)*/
rsh.ROWID row_id, rsh.shipment_header_id, rsh.last_updated_by,
rsh.last_update_date, rsh.last_update_login, rsh.created_by,
rsh.creation_date, rsh.program_application_id, rsh.program_id,
rsh.program_update_date, rsh.request_id, rsh.ussgl_transaction_code,
rsh.government_context, rsh.comments, rsh.bill_of_lading,
rsh.expected_receipt_date, rsh.freight_carrier_code, NULL,
rsh.num_of_containers, TO_NUMBER (NULL),
pov.vendor_name vendor_name, povs.vendor_site_code vendor_site,
rsh.packing_slip, rsh.employee_id, rsh.receipt_num,
rsh.receipt_source_code, rsh.shipment_num, rsh.shipped_date,
hr.location_code ship_to_location, rsh.ship_to_location_id,
rsh.waybill_airbill_num, rsh.asn_type, rsh.attribute_category,
rsh.attribute1, rsh.attribute2, rsh.attribute3, rsh.attribute4,
rsh.attribute5, rsh.attribute6, rsh.attribute7, rsh.attribute8,
rsh.attribute9, rsh.attribute10, rsh.attribute11, rsh.attribute12,
rsh.attribute13, rsh.attribute14, rsh.attribute15, pov.vendor_id,
rsh.ship_to_org_id, rsh.vendor_site_id
FROM rcv_shipment_headers rsh,
hr_locations_all_tl hr,
po_vendors pov,
po_vendor_sites povs
WHERE (receipt_source_code = 'VENDOR' AND rsh.asn_type IN ('ASN', 'ASBN')
)
AND hr.location_id(+) = rsh.ship_to_location_id
AND hr.LANGUAGE(+) = USERENV ('LANG')
AND pov.vendor_id = rsh.vendor_id
AND povs.vendor_site_id(+) = rsh.vendor_site_id
--AND (EXISTS (
AND (1 IN (
SELECT 1
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_line_status_code IN
('EXPECTED', 'PARTIALLY RECEIVED', 'FULLY RECEIVED')
AND NOT EXISTS (
SELECT 1
FROM rcv_transactions_interface rti
WHERE rti.shipment_line_id =
rsl.shipment_line_id))
);

--------------------------------------------------------------

Any help/advise would be greatly appreciated.

Greg

Edited by: user3581064 on Aug 4, 2010 9:47 AM