Re: LTX makes no sense

} From: jvl@dbserver.dwr.co.gov (Jean Van Loan) } Subject: LTX makes no sense } To: informix-list@rmy.emory.edu } Date: Wed, 7 Dec 1994 11:21:23 -0700 (MDT) } } Can anyone answer this puzzle, which tech support } agrees is "bazaar" (yes, the products were } installed correctly, tools, then ONLINE 5.02 UC6 then } ISTAR): } } Why the long transaction, error -458? } } When: } the database does not have logging } no updates (ie: changes to data) are involved } set explain mentions no temp tables/files. } } The select that causes the -458 is: } } select c1,c2 from a } where not exists } (select * from b where a.c1 = b.c1) } } What is being logged and why? } } Thanks for any insight. } ------------------------------------------------------ } Jean Van Loan Colo. Div. Water Resources } jvl@dbserver.dwr.co.gov 1313 Sherman St. Rm. 821 } Denver, CO 80203 } Ph. 303-866-3585 ext.258 Fax: 303-866-3589 The NOT EXISTS construct (like the NOT IN construct) is a real resource hog, and it should be avoided. This query as stated will scan the entire b table for each row of the a table, subject to some optimization by indexes. So much for theory. Now, why did this hogging of resources trigger the long transaction detection mechanism? That I can't say, but recasting the query is definitely in order. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\