View Only
  • 1.  optimization question on EXISTS subquery

    Posted Tue June 21, 2022 06:09 PM
    I was looking for sites to help explain the EXIST condition to someone, and found a page that explains it from a SQL Server perspective.  The page mentioned something that makes perfect sense, but I've never seen it documented in the Informix world:
    The EXISTS operator returns TRUE if the subquery returns one or more rows.

    I was wondering whether anyone knows if Informix acts the same way?  From a query optimization standpoint, it seems like it should act this way, but I've never seen it stated.  I'd like to think that the engine doesn't waste resources trying to find all rows in the subquery's result set when it simply needs to know that there is at least one row that satisfies the condition(s) of the subquery. 

    On reflection, though, I suppose the NOT EXIST would require going through the full subquery to confirm that it has an empty result set.

    Mark Collins


  • 2.  RE: optimization question on EXISTS subquery

    IBM Champion
    Posted Tue June 21, 2022 06:26 PM
    Hi Mark,

    Yes, that's the way that Informix works.  As soon as the subquery in the EXISTS finds a single match, then the condition is satisfied.  The explain plan shows "First Row" in such a case:

    For example:

    2) informix.orders: INDEX PATH (First Row)

    As you said, a NOT EXISTS would require that the entire subquery is evaluated to confirm that there are no records found, although I expect it too can be ended prematurely if a match is found and then move on to the next record.

    When using EXISTS for a subquery, take care as using an IN can sometimes be more efficient by eliminating a correlated subquery (when a condition in the subquery references a column outside the subquery), but it does depend on the data set.

    Mike Walker
    xDB Systems, Inc

  • 3.  RE: optimization question on EXISTS subquery

    Posted Wed June 22, 2022 03:54 PM

    Thanks for confirming that.  I tried a SET EXPLAIN on my query prior to posting, but it does not show the "(First Row)" that yours does.  And even if it did, I would still have wondered if that meant that it would stop once it returned the first row, or if it was like the optimization directive ALL ROWS vs FIRST ROWS.  Maybe I would've spotted that one says "Row" and the other says "ROWS", but I probably would have thought that it was internally setting the directive.  

    Mark Collins