Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Location
    Massachusetts
    Posts
    27

    Question Unanswered: Which predicate is better?

    Which one of these is better, u feel ? I feel the first one, but I am not able to properly justify it.

    <> 'X'

    or

    NOT IN ('X')

    Administration guide shows both of these to be non-indexable, but evaluated in Stage 1. In that case, both becomes equivalent? Does this means even if I say NOT IN('X'), Optimizer might change it to <> 'X' ?

    TIA.

    Platform: DB2 V7.1 on OS/390.
    "It is Monday morning 3:02 AM. What is your SQL response time ?"

  2. #2
    Join Date
    May 2003
    Posts
    113

    Re: Which predicate is better?

    Same feel as you, <> 'X' should be better, because the best optimization result of NOT IN ('X') is changed it to <>'X'.
    I don't think 390's optimizer change it, however, the runtime struction should be make both equivalent, though 'NOT IN' will take a little bit longer bind time


    Originally posted by gsreejith
    Which one of these is better, u feel ? I feel the first one, but I am not able to properly justify it.

    <> 'X'

    or

    NOT IN ('X')

    Administration guide shows both of these to be non-indexable, but evaluated in Stage 1. In that case, both becomes equivalent? Does this means even if I say NOT IN('X'), Optimizer might change it to <> 'X' ?

    TIA.

    Platform: DB2 V7.1 on OS/390.

  3. #3
    Join Date
    Sep 2003
    Location
    Massachusetts
    Posts
    27
    Thanks for replying, but I am not sure I understood u completely.

    Are you saying that a bind using NOT IN ('X') will take longer than a bind using <> 'X'?

    Also What is 390's optimizer? The only optimizer I know is the DB2 Optimizer which kicks in at bind-time and I honestly don't feel a bind can take longer because the SQL is bad, even if it does it should be very negligible.
    "It is Monday morning 3:02 AM. What is your SQL response time ?"

  4. #4
    Join Date
    May 2003
    Posts
    113
    The difference on Bind for these predicates are very very small as you mentioned. Thus, you are right(almost at least).

    For exmaple
    SELECT 1 FROM (
    SELECT * FROM T1
    UNION
    SELECT * FROM T2) X(C1,C2,C3)
    WHERE C1 op 'X'


    For <>'X', usually, it can be considered as a simple predicate, which can be pushdown/up inside a subquery. above example can be rewritten to:
    SELECT 1 FROM (
    SELECT * FROM T1 WHERE C1 <>'X'
    UNION
    SELECT * FROM T2 WHERE C1 <>'X') X(C1,C2,C3)
    WHERE C1 op 'X';
    which usually lead to a better runtime performance

    For NOT IN 'X', usually, it be considered as a more complex predicate, when optimizer trys rewrite a query, they have to think 'hard', and work 'hard'.


    Originally posted by gsreejith
    Thanks for replying, but I am not sure I understood u completely.

    Are you saying that a bind using NOT IN ('X') will take longer than a bind using <> 'X'?

    Also What is 390's optimizer? The only optimizer I know is the DB2 Optimizer which kicks in at bind-time and I honestly don't feel a bind can take longer because the SQL is bad, even if it does it should be very negligible.

  5. #5
    Join Date
    Sep 2003
    Location
    Massachusetts
    Posts
    27
    Can I ask how can you say that a NOT IN can be 'considered' more complex ? Just because it looks complex may not necessarily mean that it is so.

    I am asking this because it seems to me that the manuals treat both as the same. Also an Explain of a query with <> or NOT IN gives me the same elapsed time.

    The SQL which u showed can be written using NOT IN too, right?
    "It is Monday morning 3:02 AM. What is your SQL response time ?"

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Make Explain and see the "rewriten" SQL for bouth examples. If "rewrite" is the same, then there is no difference in performance.

    DB2 optimizer rewrites every SQL so there is very posible there will be the same rewrite for bouth SQLs.

    I think, nidm meant with "390's optimizer" term the difference with DB2 UDB optimizer for Linux, Unix and Windows and DB2 for OS/390 optimizer. But this is the term from nidm. There is no such term in DB2 documentation. Nidm should write: "DB2 optimizer on DB2 for OS/390", but this is too long to write at forums...

    Hope this helps,
    Grofaty
    Last edited by grofaty; 04-08-04 at 02:33.

  7. #7
    Join Date
    May 2003
    Posts
    113
    thank you very much. :-)
    I apologize that I didn't make my point clearly.
    Both gsreejith and grofaty did mentioned the point, and I agree with that: "the two predicate are same for this case".

    When I talked about 'NOT IN' is complex, majorly because it can take more than one items on RHS. In some extremely case, it could be more than 100. I, personally, saw one case has more than 1000 lit constants on RHS. In such case, mostlikely optimizer won't do any rewrite because it takes too much bind time CPU.

    Originally posted by grofaty
    Hi,

    Make Explain and see the "rewriten" SQL for bouth examples. If "rewrite" is the same, then there is no difference in performance.

    DB2 optimizer rewrites every SQL so there is very posible there will be the same rewrite for bouth SQLs.

    I think, nidm meant with "390's optimizer" term the difference with DB2 UDB optimizer for Linux, Unix and Windows and DB2 for OS/390 optimizer. But this is the term from nidm. There is no such term in DB2 documentation. Nidm should write: "DB2 optimizer on DB2 for OS/390", but this is too long to write at forums...

    Hope this helps,
    Grofaty

  8. #8
    Join Date
    Sep 2003
    Location
    Massachusetts
    Posts
    27
    Thanks nidm and grofaty.

    Ofcourse if there are even more than one item in the NOT IN list, then it is not efficient at all..but I guess when there is only 1 item ('X'), then both the predicates are same since Optimizer rewrites a NOT IN as <> and hence I got the same elapsed time for an Explain in both cases.

    Thanks again for ur inputs !
    "It is Monday morning 3:02 AM. What is your SQL response time ?"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •