Re: One DELETE with OR clause behaves differently from two DELETEs ?

Martin T. wrote:

> Hello all.
>
> (Oracle 9.2.0.1.0, Windows XP)
>
> (snipped)

Nobody any idea on this?

I have now set up a little test script ... maybe someone can comment on the optimizers behaviour: (see the script below) This script runs fine. When I do an explain plan on the 3 queries below, the optimizer will guess a resul of 5k rows for the 1st and a result of 1 (one) row for the second query. So far so good.
When I then explain the 3rd query, where I just ORed the conditions of the other two queries, the optimizer will then guess at a result of 975 rows when the result set is in fact the combined set of the 1st two queries.
Now I know that the optimizer does not always have to guess right, but this seems a little far off for the 3rd query, considering the other two queries.

Any Ideas??

thanks!

• Martin -

Test script:

---
DROP TABLE ID_RANGE;
DROP TABLE MY_DATA;

CREATE TABLE MY_DATA
(
  ID NUMBER NOT NULL,
  TIME_STAMP DATE NOT NULL,
  TIME_DATA VARCHAR2(4000)


);


CREATE UNIQUE INDEX PK_MY_DATA ON MY_DATA
(
  ID


);


ALTER TABLE MY_DATA ADD (
  CONSTRAINT PK_MY_DATA PRIMARY KEY (ID)
  USING INDEX


);


CREATE TABLE ID_RANGE
(
  START_ID NUMBER NOT NULL,
  STOP_ID NUMBER NOT NULL,
  RANGE_DATA VARCHAR2(4000)


);


CREATE INDEX PK_RANGE_1 ON ID_RANGE
(
  START_ID


);


CREATE INDEX PK_RANGE_2 ON ID_RANGE
(
  STOP_ID


);


ALTER TABLE ID_RANGE ADD (
  CONSTRAINT FK1_RANGE FOREIGN KEY (START_ID)
    REFERENCES MY_DATA (ID));

ALTER TABLE ID_RANGE ADD (
  CONSTRAINT FK2_RANGE FOREIGN KEY (STOP_ID)
    REFERENCES MY_DATA (ID));

INSERT /*+append*/ INTO MY_DATA
SELECT OBJECT_ID, CREATED, OBJECT_NAME FROM ALL_OBJECTS;

INSERT /*+append*/ INTO ID_RANGE
SELECT O1.OBJECT_ID O1_ID, O2.OBJECT_ID O2_ID,
O1.OBJECT_NAME||O2.OBJECT_NAME
FROM ALL_OBJECTS O1, ALL_OBJECTS O2
WHERE O1.OBJECT_ID <> O2.OBJECT_ID
  AND ROWNUM < 10000;

COMMIT;

BEGIN
  dbms_stats.gather_table_stats('FIP_TRAPPEL', 'MY_DATA');
  dbms_stats.gather_table_stats('FIP_TRAPPEL', 'ID_RANGE');
END;
/

COMMIT;

--
-- OPTIMIZER GUESSES 5000 Rows
--
SELECT * FROM ID_RANGE
WHERE START_ID IN (
  SELECT ID FROM MY_DATA WHERE ID > 1000 and id < 10000


);


--
-- OPTIMZER guesses 1 row
--
SELECT * FROM ID_RANGE
WHERE STOP_ID IN (
  SELECT ID FROM MY_DATA WHERE ID > 1000 and ID < 10000


);


--
-- OPTIMIZER guesses 975 rows ... ??!
--
SELECT * FROM ID_RANGE
WHERE START_ID IN (
  SELECT ID FROM MY_DATA WHERE ID > 1000 and id < 10000
)
OR STOP_ID IN (
  SELECT ID FROM MY_DATA WHERE ID > 1000 and ID < 10000


);