asked on

Need help in tuning query. merge cartesian join is ok?

Please find the below query.

SELECT *
FROM sale_order_dtls sod
WHERE status = 'S'
AND 0 = (SELECT NVL(MAX(bord.total_containers),0) - NVL(count(crgl.con_det_no),0)
FROM contr_req_ground_dtl crgl,
contr_req_ground_hdr crgh,
BOOKING_ORD_REQ_DTL bord
WHERE crgl.container_ref_no = crgh.contianer_ref_no
and crgh.sale_order_no = sod.sale_order_no
AND crgh.lot_no = sod.lot_no
AND crgh.sale_order_no = bord.sale_order_no
AND crgh.lot_no = bord.lot_no
AND NVL(crgl.rejection_flag,'N') = 'N'
AND crgl.stuff_confirmed = 'Y'
AND crgh.is_valid = 1
AND crgl.is_valid = 1
AND bord.is_valid = 1
);

and the explain plan for the same is attached.

Query runs faster, but in explain plan I am getting Merge join cartesian.

IS this a problem? Will the same query be slow, if more no. of records are there in table?
explain-plan.jpg

Guy Hengel [angelIII / a3]

I would make the query a NOT EXISTS() ... may be faster than 0 = count(*) ...

SELECT *
  FROM sale_order_dtls sod
 WHERE status = 'S'
   AND NOT EXISTS (SELECT  NULL
              FROM contr_req_ground_dtl crgl,
                   contr_req_ground_hdr crgh,
                   BOOKING_ORD_REQ_DTL  bord
             WHERE crgl.container_ref_no = crgh.contianer_ref_no
               and crgh.sale_order_no = sod.sale_order_no
               AND crgh.lot_no = sod.lot_no
               AND crgh.sale_order_no = bord.sale_order_no
               AND crgh.lot_no = bord.lot_no
               AND NVL(crgl.rejection_flag,'N') = 'N'
               AND crgl.stuff_confirmed = 'Y'
               AND crgh.is_valid = 1
               AND crgl.is_valid = 1
               AND bord.is_valid = 1
);

Open in new window

ASKER CERTIFIED SOLUTION

Franck Pachot

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer