Hi
I have the following query:
SELECT cust_id,
cosmos_cust_id,
prot_id,
customer_name,
protocol_name,
dist_order_no,
cust_ivr_ref_no,
ship_to_country,
ship_date,
shipment_date,
shipped_mntr_cnt,
upload_mntr_cnt,
otg_state,
ship_otg,
ship_status,
ship_to_site_no,
ship_fac_name,
ship_fac_id,
tempez_status,
monitors_alarmed,
excursion_status,
excursion_decision,
ship_source,
username AS locking_user,
courier_name,
courier_id
FROM shipment_landingpage_dtls_v
LEFT JOIN
(SELECT SUBSTR (lock_id,
REGEXP_INSTR (lock_id,
'[^SHIPMENTS_]{1}',
1,
1,
0,
'i'))
AS locking_ship,
l.username AS username
FROM application_lock l
WHERE SUBSTR (UPPER (lock_id), 1, 10) = 'SHIPMENTS_'
AND SYSDATE <
lock_time
+ .0138888888888888888888888888888888888889) a
ON '' || ship_id = a.locking_ship(+)
WHERE 1 = 1
AND ship_id IN
(SELECT s.ship_id
FROM monitor m
INNER JOIN container c
ON c.ctr_id = m.mon_ctr_id
INNER JOIN shipment s
ON s.ship_id = c.ctr_ship_id
WHERE m.deactivated IS NULL
AND c.deactivated IS NULL
AND s.deactivated IS NULL
AND m.deactivated IS NULL
AND LOWER (m.mon_serial_number) =
LOWER ('2703743130'))
When I run the subquery in isolation, it takes millseconds and returns a ship id. If I hardcode the ship id value (instead of using the subquery), the overall query returns in milliseconds. Yet when I run the overall query (including the subquery, it takes 3 minutes).
Am I being naïve when I think that the overall query time should be query + subquery?
Change
SELECT cust_id,
cosmos_cust_id,
prot_id,
customer_name,
protocol_name,
dist_order_no,
...
to this
SELECT /*+ gather_plan_statistics */ cust_id,
cosmos_cust_id,
prot_id,
customer_name,
protocol_name,
dist_order_no,
...
then run your query in SQL Plus and then do:
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'))
then paste that in as a review (in code tags to keep the spacing)
Then we can see the cardinalities and help you further.