If you have a simple SELECT query with an IN predicate with a list of say 930 items in the list, the query will compile (on one of the sqws*** machines) in approximately 13.5 seconds. If you change the 'IN' to 'NOT IN', the compilation time goes to over 50 seconds.
Furthermore, once the changes get made to allow IN lists and NOT IN lists that are say 3000 items long, if you then try the same SELECT query with a IN list of 3000 items (unique items), the compilation time is about 18-20 seconds. However, if you then change the query to specify NOT IN, the compilation time goes to 15-20 *minutes*. NOTE: Currently, IN lists are limited to about 2230 unique items and NOT IN lists are limited to about 930 unique items (beyond those lengths you get stack overflow.) Once a fix to LP 1323826 gets checked into the source base, you should be able to have IN lists or NOT IN lists with as many as 3000 unique items.
Example SELECT query (with 930 unique items) that exhibits this behavior is as follows:
--create table mytable97 (partnum decimal(4,0), partname char(18));
prepare s1 from select * from mytable97 where partname in (
'A10','B10','C10','D10','E10','F10','G10','H10','I10','J10'
,'K10','L10','M10','N10','O10','P10','Q10','R10','S10','T10'
,'AA10','AB10','AC10','AD10','AE10','AF10','AG10','AH10','AI10','AJ10'
,'BA10','BB10','BC10','BD10','BE10','BF10','BG10','BH10','BI10','BJ10'
,'CA10','CB10','CC10','CD10','CE10','CF10','CG10','CH10','CI10','CJ10'
,'DA10','DB10','DC10','DD10','DE10','DF10','DG10','DH10','DI10','DJ10'
,'EA10','EB10','EC10','ED10','EE10','EF10','EG10','EH10','EI10','EJ10'
,'FA10','FB10','FC10','FD10','FE10','FF10','FG10','FH10','FI10','FJ10'
,'GA10','GB10','GC10','GD10','GE10','GF10','GG10','GH10','GI10','GJ10'
,'HA10','HB10','HC10','HD10','HE10','HF10','HG10','HH10','HI10','HJ10'
,'A11','B11','C11','D11','E11','F11','G11','H11','I11','J11'
,'K11','L11','M11','N11','O11','P11','Q11','R11','S11','T11'
,'AA11','AB11','AC11','AD11','AE11','AF11','AG11','AH11','AI11','AJ11'
,'BA11','BB11','BC11','BD11','BE11','BF11','BG11','BH11','BI11','BJ11'
,'CA11','CB11','CC11','CD11','CE11','CF11','CG11','CH11','CI11','CJ11'
,'DA11','DB11','DC11','DD11','DE11','DF11','DG11','DH11','DI11','DJ11'
,'EA11','EB11','EC11','ED11','EE11','EF11','EG11','EH11','EI11','EJ11'
,'FA11','FB11','FC11','FD11','FE11','FF11','FG11','FH11','FI11','FJ11'
,'GA11','GB11','GC11','GD11','GE11','GF11','GG11','GH11','GI11','GJ11'
,'HA11','HB11','HC11','HD11','HE11','HF11','HG11','HH11','HI11','HJ11'
,'A12','B12','C12','D12','E12','F12','G12','H12','I12','J12'
,'K12','L12','M12','N12','O12','P12','Q12','R12','S12','T12'
,'AA12','AB12','AC12','AD12','AE12','AF12','AG12','AH12','AI12','AJ12'
,'BA12','BB12','BC12','BD12','BE12','BF12','BG12','BH12','BI12','BJ12'
,'CA12','CB12','CC12','CD12','CE12','CF12','CG12','CH12','CI12','CJ12'
,'DA12','DB12','DC12','DD12','DE12','DF12','DG12','DH12','DI12','DJ12'
,'EA12','EB12','EC12','ED12','EE12','EF12','EG12','EH12','EI12','EJ12'
,'FA12','FB12','FC12','FD12','FE12','FF12','FG12','FH12','FI12','FJ12'
,'GA12','GB12','GC12','GD12','GE12','GF12','GG12','GH12','GI12','GJ12'
,'HA12','HB12','HC12','HD12','HE12','HF12','HG12','HH12','HI12','HJ12'
,'A13','B13','C13','D13','E13','F13','G13','H13','I13','J13'
,'K13','L13','M13','N13','O13','P13','Q13','R13','S13','T13'
,'AA13','AB13','AC13','AD13','AE13','AF13','AG13','AH13','AI13','AJ13'
,'BA13','BB13','BC13','BD13','BE13','BF13','BG13','BH13','BI13','BJ13'
,'CA13','CB13','CC13','CD13','CE13','CF13','CG13','CH13','CI13','CJ13'
,'DA13','DB13','DC13','DD13','DE13','DF13','DG13','DH13','DI13','DJ13'
,'EA13','EB13','EC13','ED13','EE13','EF13','EG13','EH13','EI13','EJ13'
,'FA13','FB13','FC13','FD13','FE13','FF13','FG13','FH13','FI13','FJ13'
,'GA13','GB13','GC13','GD13','GE13','GF13','GG13','GH13','GI13','GJ13'
,'HA13','HB13','HC13','HD13','HE13','HF13','HG13','HH13','HI13','HJ13'
,'A14','B14','C14','D14','E14','F14','G14','H14','I14','J14'
,'K14','L14','M14','N14','O14','P14','Q14','R14','S14','T14'
,'AA14','AB14','AC14','AD14','AE14','AF14','AG14','AH14','AI14','AJ14'
,'BA14','BB14','BC14','BD14','BE14','BF14','BG14','BH14','BI14','BJ14'
,'CA14','CB14','CC14','CD14','CE14','CF14','CG14','CH14','CI14','CJ14'
,'DA14','DB14','DC14','DD14','DE14','DF14','DG14','DH14','DI14','DJ14'
,'EA14','EB14','EC14','ED14','EE14','EF14','EG14','EH14','EI14','EJ14'
,'FA14','FB14','FC14','FD14','FE14','FF14','FG14','FH14','FI14','FJ14'
,'GA14','GB14','GC14','GD14','GE14','GF14','GG14','GH14','GI14','GJ14'
,'HA14','HB14','HC14','HD14','HE14','HF14','HG14','HH14','HI14','HJ14'
,'A15','B15','C15','D15','E15','F15','G15','H15','I15','J15'
,'K15','L15','M15','N15','O15','P15','Q15','R15','S15','T15'
,'AA15','AB15','AC15','AD15','AE15','AF15','AG15','AH15','AI15','AJ15'
,'BA15','BB15','BC15','BD15','BE15','BF15','BG15','BH15','BI15','BJ15'
,'CA15','CB15','CC15','CD15','CE15','CF15','CG15','CH15','CI15','CJ15'
,'DA15','DB15','DC15','DD15','DE15','DF15','DG15','DH15','DI15','DJ15'
,'EA15','EB15','EC15','ED15','EE15','EF15','EG15','EH15','EI15','EJ15'
,'FA15','FB15','FC15','FD15','FE15','FF15','FG15','FH15','FI15','FJ15'
,'GA15','GB15','GC15','GD15','GE15','GF15','GG15','GH15','GI15','GJ15'
,'HA15','HB15','HC15','HD15','HE15','HF15','HG15','HH15','HI15','HJ15'
,'A16','B16','C16','D16','E16','F16','G16','H16','I16','J16'
,'K16','L16','M16','N16','O16','P16','Q16','R16','S16','T16'
,'AA16','AB16','AC16','AD16','AE16','AF16','AG16','AH16','AI16','AJ16'
,'BA16','BB16','BC16','BD16','BE16','BF16','BG16','BH16','BI16','BJ16'
,'CA16','CB16','CC16','CD16','CE16','CF16','CG16','CH16','CI16','CJ16'
,'DA16','DB16','DC16','DD16','DE16','DF16','DG16','DH16','DI16','DJ16'
,'EA16','EB16','EC16','ED16','EE16','EF16','EG16','EH16','EI16','EJ16'
,'FA16','FB16','FC16','FD16','FE16','FF16','FG16','FH16','FI16','FJ16'
,'GA16','GB16','GC16','GD16','GE16','GF16','GG16','GH16','GI16','GJ16'
,'HA16','HB16','HC16','HD16','HE16','HF16','HG16','HH16','HI16','HJ16'
,'A17','B17','C17','D17','E17','F17','G17','H17','I17','J17'
,'K17','L17','M17','N17','O17','P17','Q17','R17','S17','T17'
,'AA17','AB17','AC17','AD17','AE17','AF17','AG17','AH17','AI17','AJ17'
,'BA17','BB17','BC17','BD17','BE17','BF17','BG17','BH17','BI17','BJ17'
,'CA17','CB17','CC17','CD17','CE17','CF17','CG17','CH17','CI17','CJ17'
,'DA17','DB17','DC17','DD17','DE17','DF17','DG17','DH17','DI17','DJ17'
,'EA17','EB17','EC17','ED17','EE17','EF17','EG17','EH17','EI17','EJ17'
,'FA17','FB17','FC17','FD17','FE17','FF17','FG17','FH17','FI17','FJ17'
,'GA17','GB17','GC17','GD17','GE17','GF17','GG17','GH17','GI17','GJ17'
,'HA17','HB17','HC17','HD17','HE17','HF17','HG17','HH17','HI17','HJ17'
,'A18','B18','C18','D18','E18','F18','G18','H18','I18','J18'
,'K18','L18','M18','N18','O18','P18','Q18','R18','S18','T18'
,'AA18','AB18','AC18','AD18','AE18','AF18','AG18','AH18','AI18','AJ18'
,'BA18','BB18','BC18','BD18','BE18','BF18','BG18','BH18','BI18','BJ18'
,'CA18','CB18','CC18','CD18','CE18','CF18','CG18','CH18','CI18','CJ18'
,'DA18','DB18','DC18','DD18','DE18','DF18','DG18','DH18','DI18','DJ18'
,'EA18','EB18','EC18','ED18','EE18','EF18','EG18','EH18','EI18','EJ18'
,'FA18','FB18','FC18','FD18','FE18','FF18','FG18','FH18','FI18','FJ18'
,'GA18','GB18','GC18','GD18','GE18','GF18','GG18','GH18','GI18','GJ18'
,'HA18','HB18','HC18','HD18','HE18','HF18','HG18','HH18','HI18','HJ18'
,'A19','B19','C19','D19','E19','F19','G19','H19','I19','J19'
,'K19','L19','M19','N19','O19','P19','Q19','R19','S19','T19'
,'AA19','AB19','AC19','AD19','AE19','AF19','AG19','AH19','AI19','AJ19'
) ;
