Help with a view

Hi,
I'm not sure if this is the correct forum but here goes...
Im trying to create a shipment label, Im getting the data from an oracle 10g database via a view which i'm writing ( see code below).

Im designing the label in a program called bartender.

I have got the information I need, but its taking over 2 minutes to bring back the data, Can anyone give me some hints as to how I can speed up the query ( I am relatively new to sql, so please be gentle with me ;))

select distinct
sh.shipment_id,
sh.shipper_address1,
sh.shipper_address2,
sh.shipper_zip_code,
sh.shipper_city,
sh.shipper_state,
sh.deliver_to_customer_no,
sh.contract,
sh.shipper_name,
ifsapp.cust_ord_customer_address_api.Get_Company_Name2(sh.deliver_to_customer_no, sh.ship_addr_no) Customer_Name,
ifsapp.customer_order_line_api.Get_Part_No(spu.order_no, spu.line_no, spu.rel_no, spu.line_item_no) Part_no,
ifsapp.sales_part_api.Get_Catalog_Desc(sh.contract, ifsapp.customer_order_line_api.Get_Part_No(spu.order_no, spu.line_no, spu.rel_no, spu.line_item_no)) Part_Desc,
shu.customer_part_no,
sh.ship_date,
spu.handling_unit_id,
spu.lot_batch_no,
tst.qty,
tst.net_weight,
tst.net_weight + tst.HU_weight gross_weight,
tst.pallet_qty
from
shipment sh,
shipment_package_unit spu,
shipment_handling_utility shu,
(select distinct handling_unit_id, sum(qty_in_package_unit) qty, sum(net_weight) net_weight, sum(handling_unit_weight) HU_Weight, count(qty_in_package_unit) pallet_qty from shipment_handling_utility

group by handling_unit_id) tst

where
sh.shipment_id = spu.shipment_id and
spu.shipment_id = shu.shipment_id and
spu.handling_unit_id = shu.handling_unit_id and
tst.handling_unit_id = spu.handling_unit_id and
shu.handling_unit_type = 'Homogenous'

If you have any idears I would appreciate it.

Regards,
Mick.
Start Free Trial