Re: Problem With Exists in 7.3
Hi Again !
Don't you remember my last mail about problem with insert
in 7.3 ? Now I thought we had found the real beast --
OPTIMIZER.
Once again:
-----------
CREATE TEMP TABLE t1 (a integer);
CREATE TEMP TABLE t2
(
_row_id INTEGER NOT NULL ,
PRIMARY KEY (_row_id)
-- ^^^^^^^^^^^
)
WITH NO LOG;
INSERT INTO t1 VALUES(1);
INSERT INTO t2 SELECT ROWID FROM t1;
-- Now we test the JOIN :
SELECT t1.ROWID , t2._row_id
FROM t1, t2
WHERE t1.ROWID = t2._row_id;
-- It returns exactly one record !
-- But when we perform the following:
SELECT
* FROM t1
WHERE EXISTS(
^^^^^^
SELECT _row_id FROM t2 where t2._row_id = t1.rowid
);
-- AND NOTHING RETURNS
-- EVEN MORE:
SELECT
* FROM t1
WHERE NOT EXISTS(
^^^^^^^^^^
SELECT _row_id FROM t2 where t2._row_id = t1.rowid
);
-- AND NOTHING RETURNS AGAIN !
When we had seen the sqlexec plan it was as follows:
Estimated Cost: 3
Estimated # of Rows Returned: 10
1) informix.t2: INDEX PATH
^^^ TABLE IN EXISTS WAS PUTTEN FIRST !
(1) Index Keys: _row_id (Key-Only)
Lower Index Filter:
informix.t2._row_id = informix.t1.ROWID
^^^^^^^^^^^^^^^^^
-- WHERE IT CAN TAKE THIS ?
2) informix.t1: SEQUENTIAL SCAN
NESTED LOOP JOIN
The same things are going on when we used IN instead of EXISTS.
We had solved this problem by taking off primary keys on t1 and
replace it with unique key.
I know that Informix has done a lot with 7.3 optimizer. May be
somebody knows another problems with it ?
Sincerely, Alexander
--
Alexander V.Didytch, Kyiv, Ukraine
"Those who fail to learn lessons early and clearly, and take effective action
to correct bad situations, are doomed to repeat them irretrievably throughout
the project" -Gilb, 1997