Q. What can cause the error Size of optimizer block exceeded?
A. This error occurs when the WHERE clause of an UPDATE or SELECT has greater than 256 ANDs or ORs.
Example:
create table foo( f1 int,f2 int,f3 int,f4 int,f5 int,f6 int,f7 int,f8 int,f9 int,f10 int,f11 int,f12 int,f13 int,f14 int,f15 int,f16 int,f17 int,f18 int,f19 int,f20 int,f21 int,f22 int,f23 int,f24 int,f25 int,f26 int,f27 int,f28 int,f29 int,f30 int,f31 int,f32 int,f33 int,f34 int,f35 int,f36 int,f37 int,f38 int,f39 int,f40 int,f41 int,f42 int,f43 int,f44 int,f45 int,f46 int,f47 int,f48 int,f49 int,f50 int,f51 int,f52 int,f53 int,f54 int,f55 int,f56 int,f57 int,f58 int,f59 int,f60 int,f61 int,f62 int,f63 int,f64 int,f65 int,f66 int,f67 int,f68 int,f69 int,f70 int,f71 int,f72 int,f73 int,f74 int,f75 int,f76 int,f77 int,f78 int,f79 int,f80 int,f81 int,f82 int,f83 int,f84 int,f85 int,f86 int,f87 int,f88 int,f89 int,f90 int,f91 int,f92 int,f93 int,f94 int,f95 int,f96 int,f97 int,f98 int,f99 int,f100 int,f101 int,f102 int,f103 int,f104 int,f105 int,f106 int,f107 int,f108 int,f109 int,f110 int,f111 int,f112 int,f113 int,f114 int,f115 int,f116 int,f117 int,f118 int,f119 int,f120 int,f121 int,f122 int,f123 int,f124 int,f125 int,f126 int,f127 int,f128 int,f129 int,f130 int,f131 int,f132 int,f133 int,f134 int,f135 int,f136 int,f137 int,f138 int,f139 int,f140 int,f141 int,f142 int,f143 int,f144 int,f145 int,f146 int,f147 int,f148 int,f149 int,f150 int,f151 int,f152 int,f153 int,f154 int,f155 int,f156 int,f157 int,f158 int,f159 int,f160 int,f161 int,f162 int,f163 int,f164 int,f165 int,f166 int,f167 int,f168 int,f169 int,f170 int,f171 int,f172 int,f173 int,f174 int,f175 int,f176 int,f177 int,f178 int,f179 int,f180 int,f181 int,f182 int,f183 int,f184 int,f185 int,f186 int,f187 int,f188 int,f189 int,f190 int,f191 int,f192 int,f193 int,f194 int,f195 int,f196 int,f197 int,f198 int,f199 int,f200 int,f201 int,f202 int,f203 int,f204 int,f205 int,f206 int,f207 int,f208 int,f209 int,f210 int,f211 int,f212 int,f213 int,f214 int,f215 int,f216 int,f217 int,f218 int,f219 int,f220 int,f221 int,f222 int,f223 int,f224 int,f225 int,f226 int,f227 int,f228 int,f229 int,f230 int,f231 int,f232 int,f233 int,f234 int,f235 int,f236 int,f237 int,f238 int,f239 int,f240 int,f241 int,f242 int,f243 int,f244 int,f245 int,f246 int,f247 int,f248 int,f249 int,f250 int,f251 int,f252 int,f253 int,f254 int,f255 int,f256 int,f257 int);
update foo set f1=1 where f1=1 and f2=2 and f3=3 and f4=4 and f5=5 and f6=6 and f7=7 and f8=8 and f9=9 and f10=10 and f11=11 and f12=12 and f13=13 and f14=14 and f15=15 and f16=16 and f17=17 and f18=18 and f19=19 and f20=20 and f21=21 and f22=22 and f23=23 and f24=24 and f25=25 and f26=26 and f27=27 and f28=28 and f29=29 and f30=30 and f31=31 and f32=32 and f33=33 and f34=34 and f35=35 and f36=36 and f37=37 and f38=38 and f39=39 and f40=40 and f41=41 and f42=42 and f43=43 and f44=44 and f45=45 and f46=46 and f47=47 and f48=48 and f49=49 and f50=50 and f51=51 and f52=52 and f53=53 and f54=54 and f55=55 and f56=56 and f57=57 and f58=58 and f59=59 and f60=60 and f61=61 and f62=62 and f63=63 and f64=64 and f65=65 and f66=66 and f67=67 and f68=68 and f69=69 and f70=70 and f71=71 and f72=72 and f73=73 and f74=74 and f75=75 and f76=76 and f77=77 and f78=78 and f79=79 and f80=80 and f81=81 and f82=82 and f83=83 and f84=84 and f85=85 and f86=86 and f87=87 and f88=88 and f89=89 and f90=90 and f91=91 and f92=92 and f93=93 and f94=94 and f95=95 and f96=96 and f97=97 and f98=98 and f99=99 and f100=100 and f101=101 and f102=102 and f103=103 and f104=104 and f105=105 and f106=106 and f107=107 and f108=108 and f109=109 and f110=110 and f111=111 and f112=112 and f113=113 and f114=114 and f115=115 and f116=116 and f117=117 and f118=118 and f119=119 and f120=120 and f121=121 and f122=122 and f123=123 and f124=124 and f125=125 and f126=126 and f127=127 and f128=128 and f129=129 and f130=130 and f131=131 and f132=132 and f133=133 and f134=134 and f135=135 and f136=136 and f137=137 and f138=138 and f139=139 and f140=140 and f141=141 and f142=142 and f143=143 and f144=144 and f145=145 and f146=146 and f147=147 and f148=148 and f149=149 and f150=150 and f151=151 and f152=152 and f153=153 and f154=154 and f155=155 and f156=156 and f157=157 and f158=158 and f159=159 and f160=160 and f161=161 and f162=162 and f163=163 and f164=164 and f165=165 and f166=166 and f167=167 and f168=168 and f169=169 and f170=170 and f171=171 and f172=172 and f173=173 and f174=174 and f175=175 and f176=176 and f177=177 and f178=178 and f179=179 and f180=180 and f181=181 and f182=182 and f183=183 and f184=184 and f185=185 and f186=186 and f187=187 and f188=188 and f189=189 and f190=190 and f191=191 and f192=192 and f193=193 and f194=194 and f195=195 and f196=196 and f197=197 and f198=198 and f199=199 and f200=200 and f201=201 and f202=202 and f203=203 and f204=204 and f205=205 and f206=206 and f207=207 and f208=208 and f209=209 and f210=210 and f211=211 and f212=212 and f213=213 and f214=214 and f215=215 and f216=216 and f217=217 and f218=218 and f219=219 and f220=220 and f221=221 and f222=222 and f223=223 and f224=224 and f225=225 and f226=226 and f227=227 and f228=228 and f229=229 and f230=230 and f231=231 and f232=232 and f233=233 and f234=234 and f235=235 and f236=236 and f237=237 and f238=238 and f239=239 and f240=240 and f241=241 and f242=242 and f243=243 and f244=244 and f245=245 and f246=246 and f247=247 and f248=248 and f249=249 and f250=250 and f251=251 and f252=252 and f253=253 and f254=254 and f255=255 and f256=256 and f257=257;
Generates the error:
Statement failed, SQLCODE = -904
size of optimizer block exceeded
This error has been reported to occur in conjunction with the error unknown username or password. This error is not related to the problem.
A common cause for having an UPDATE statement that includes every field in a table occurs when using a TTable or TQuery with Delphi or C++ Builder and the UpdateMode property is set to UpWhereAll. Changing the UpdateMode to upWhereChanged or upWhereKeyOnly solves the problem for most applications. If you need further customizations, a TUpdateSQL component can be used in conjunction with your TQuery or TTable to further control which fields are used in the WHERE clause of your UPDATE statement.
Last Modified: 17-APR-01