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.

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

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.

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'.

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?

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

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.

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 !