One DELETE with OR clause behaves differently from two DELETEs ?

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 1 Sep 2006 03:00:19 -0700
Message-ID: <1157104819.123504.28340@i3g2000cwc.googlegroups.com>

Hello all.

(Oracle 9.2.0.1.0, Windows XP)

Following problem:
What's the difference btw this:
DELETE FROM MACHINE_DOWN_TIMES
WHERE STOP_MEASURE_ID IN (
 SELECT ID FROM MEASURES WHERE ORDER_ID = :p_order_id AND TIME_STAMP >
:p_order_end_date

);
DELETE FROM MACHINE_DOWN_TIMES
WHERE STOP_END_MEASURE_ID IN (
 SELECT ID FROM MEASURES WHERE ORDER_ID = :p_order_id AND TIME_STAMP >
:p_order_end_date

);

and this stmt, which I though would do the equivalent, but it is much slower:

DELETE FROM MACHINE_DOWN_TIMES
WHERE STOP_MEASURE_ID IN (
 SELECT ID FROM MEASURES WHERE ORDER_ID = :p_order_id AND TIME_STAMP >
:p_order_end_date

)
OR STOP_END_MEASURE_ID IN (
 SELECT ID FROM MEASURES WHERE ORDER_ID = :p_order_id AND TIME_STAMP >
:p_order_end_date

);

I think this should be doable in one single delete, but with the single version, the index just wont be used (see plan table below -- hope format is ok).

Anyone enlighten me if I could rewrite the single delete or tune it up with some hints? (Or if I'm being stupid and the single delete is sematically different from the two single ones anyway :) )

thanks!
best, Martin

PLANS:
Plan of the ORed delete:

---

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

DELETE STATEMENT Optimizer Mode=CHOOSE 13 K 98

  DELETE FIP_TRAPPEL.MACHINE_DOWN_TIMES
    FILTER
      TABLE ACCESS FULL FIP_TRAPPEL.MACHINE_DOWN_TIMES 13 K 178 K 98       TABLE ACCESS BY INDEX ROWID FIP_TRAPPEL.MEASURES 1 16 3         INDEX UNIQUE SCAN FIP_TRAPPEL.PK_MEASURES 1 M 2       TABLE ACCESS BY INDEX ROWID FIP_TRAPPEL.MEASURES 1 16 3         INDEX UNIQUE SCAN FIP_TRAPPEL.PK_MEASURES 1 M 2

---

Plan of the delete w/o the OR. (both the same):

---

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

DELETE STATEMENT Optimizer Mode=CHOOSE 15 33

  DELETE	FIP_TRAPPEL.MACHINE_DOWN_TIMES
    TABLE ACCESS BY INDEX ROWID	FIP_TRAPPEL.MACHINE_DOWN_TIMES	1  	14
	2
      NESTED LOOPS		15  	450  	33
        TABLE ACCESS BY INDEX ROWID	FIP_TRAPPEL.MEASURES	15  	240  	3

          INDEX RANGE SCAN	FIP_TRAPPEL.MEASURES_UC1	291  	 	2

        INDEX RANGE SCAN	FIP_TRAPPEL.IX_MDT_1	1  	 	1

Received on Fri Sep 01 2006 - 05:00:19 CDT