PLAN (B_TABLE NATURAL) PLAN (AT INDEX (IDX_A_TABLE)) Does this mean that for every single row selected from table A, it touches every single row from table B?
-----Original Message----- From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Leyne, Sean Sent: Monday, April 02, 2012 5:13 PM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] Not Exists cost > If B_TABLE.VALUE2 never contains 'X' and half of A_TABLE.VALUE1 are 'Y' > what would be the cost of executing this statement? A_TABLE.VALUE1 is > indexed and B_TABLE.VALUE2 does not have an index. > > Would it be half of A_TABLE rows times all of B_TABLE rows? Or would > the Not Exists statement be evaluated for every row in A_TABLE making > it A_TABLE rows times B_TABLE rows? > > SELECT * > FROM A_TABLE at > WHERE > at.VALUE1 = 'Y' AND > NOT EXISTS ( > SELECT 1 > FROM B_TABLE bt > WHERE bt.VALUE2 = 'X'); The query PLAN would tell you for certain, but I would expect the answer to be: half of A_TABLE rows times all of B_TABLE rows. Sean Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.
