| If it were in our environment, we would be making the changes below to the query. We have around 439,511 rows in our shipment table and 319,023 in the particular domain I tested. Because of that, we also have a index on shipment.domain_name (Even as few domains as their are) and add the -- AND s.domain_name = 'TMS' -- to the end of the query. Due to almost all of your rows being in the domain you are running the report for, I dont know that this will help you much although it may in the future if you continue to expand other domains so you may want to try it with and without that line to make sure your situation doesn't slow it down. We also add the hint to the top even if the index shows in the explain plan because it seems to make the query run more efficiently. Finally, instead of the view we use the subquery below (if you need sell shipments instead of buy, just adjust). If you take the script out of the view run an explain plan, you will see the cost is high. One of the reasons is the joins they use to get the data.
I dont know that this will make a whole lot of difference on the results you get in 1 minute in the UI. With the vpd profiles and a date range, you are rarely going to get anything above that once you reach 100k+ rows.
Anyway - check it out and hopefully it will help a little. Every customer/system/db is different and being that you might use the system in a totally different way that we do,
I can only hope that it will help. Well good luck and let me know if you have any additional questions.
SELECT /*+ INDEX(SHIPMENT PK_SHIPMENT)*/
glog_util.remove_domain(ORLS.ORDER_RELEASE_GID) ORDER_RELEASE,
glog_util.remove_domain(orls.source_location_gid) OR_ORIGIN,
oo.location_name OR_O_NAME,
oo.country_code3_gid OR_O_CTRY,
oo.postal_code OR_O_PC,
oo.city OR_O_CITY,
glog_util.remove_domain(orp.STATUS_VALUE_GID) OR_PLANNING,
orls.late_pickup_date OR_LATEST_PICKUP,
orls.total_weight OR_GROSS_WEIGHT,
glog_util.remove_domain(shiplist.shipment_gid) SHIPMENT_ID,
glog_util.remove_domain(sr.shipment_refnum_value) LOAD_PLAN,
glog_util.remove_domain(sgd.ship_group_gid) SHIPMENT_GROUP_ID,
glog_util.remove_domain(s.servprov_gid) CARRIER,
s.start_time S_START_TIME,
glog_util.remove_domain(s.source_location_gid) SHIP_ORIGIN,
so.location_name SHIP_O_NAME,
so.country_code3_gid SHIP_O_CTRY,
so.postal_code SHIP_O_PC,
so.city SHIP_O_CITY,
s.total_weight S_WEIGHT
FROM
ORDER_RELEASE orls,
ORDER_RELEASE_STATUS ors,
ORDER_RELEASE_STATUS orp,
SHIPMENT_REFNUM sr,
SHIPMENT s,
LOCATION so,
LOCATION oo,
SHIP_GROUP_D sgd,
(SELECT DISTINCT shp.shipment_gid, ssul.order_release_gid
FROM SHIPMENT shp,
SHIPMENT_STOP_D d,
S_SHIP_UNIT_LINE ssul
WHERE shp.shipment_gid = d.shipment_gid
AND d.s_ship_unit_gid = ssul.s_ship_unit_gid
AND ssul.order_release_gid IS NOT NULL
AND d.STOP_NUM = 1
AND shp.PERSPECTIVE = 'B') shiplist
WHERE orls.order_release_gid = ors.order_release_gid
AND ors.status_type_gid ='TMS.LOAD_PLAN_OR'
AND ors.status_value_gid = 'TMS.NOT CONFIRMED'
AND orls.order_release_gid = orp.order_release_gid
AND orp.status_type_gid='TMS.PLANNING'
AND orls.source_location_gid = oo.location_gid
AND orls.order_release_gid = shiplist.order_release_gid
AND shiplist.shipment_gid = sr.shipment_gid
AND sr.shipment_refnum_qual_gid = 'TMS.LOAD_PLAN'
AND shiplist.shipment_gid = s.shipment_gid
AND s.shipment_gid = sgd.shipment_gid (+)
AND s.source_location_gid = so.location_gid
AND orls.late_pickup_date BETWEEN TO_DATE('19/05/2005','DD/MM/YYYY')
AND TO_DATE('20/05/2005','DD/MM/YYYY')
AND s.domain_name = 'TMS'  |