Optimizer Flaws in 7.23 - Simple example
See previous email "Serious flaws...." The Optimizer does not use indexes even if EVERY join is an indexed field. Informix 5 did not have these problems. NOTE in example there are 10,7534 Blank "tel" columns In QUERY 2: Although a DYNAMIC HASH JOIN is chosen instead of INDEX, cannot argue as cost is still low. QUERY 1: Runs forever. ------ select DISTINCT a.id from dunsoft a, OUTER dunau b where ( b.tel = a.tel or b.zip = a.zip ) <<<<<<< Extra join Estimated Cost: 36716488 <<<<<<<<<<<<<<<<<<<<<<<<<<<<< Estimated # of Rows Returned: 1 <<<<< Should Estimate 1,000 + 1) eadmin.a: SEQUENTIAL SCAN 2) eadmin.b: SEQUENTIAL SCAN <<<<<<< Should use INDEXES Filters: (eadmin.b.tel = eadmin.a.tel OR eadmin.b.fax = eadmin.a.fax ) QUERY 2: ------ select DISTINCT a.id from dunsoft a, OUTER dunau b where ( b.tel = a.tel ) <<<<<<<<<<<<<< Only 1 join Estimated Cost: 9994 Estimated # of Rows Returned: 1 1) eadmin.a: SEQUENTIAL SCAN 2) eadmin.b: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: eadmin.a.tel = eadmin.b.tel ----------------------------------------------------- Distributions: DBSCHEMA Schema Utility INFORMIX-SQL Version 7.23.UC1 Copyright (C) Informix Software, Inc., 1984-1997 Software Serial Number AAB#J989985 Distribution for eadmin.dunau.tel <<<<<<<<<<<<<<<<<<<<<<<<<<<< Constructed on 08/07/1997 High Mode, 0.500000 Resolution --- DISTRIBUTION --- ( ) 1: ( 444, 277, 0234577942 ) 2: ( 444, 291, 0425270631 ) 3: ( 444, 297, 0822441935 ) 4: ( 444, 326, 2014289363 ) 5: ( 444, 338, 2018335525 ) 6: ( 444, 349, 2033198919 ) ..... .... .... 171: ( 444, 324, 9187235423 ) 172: ( 444, 309, 9197340460 ) 173: ( 444, 354, 9414817218 ) 174: ( 444, 374, 9702481999 ) 175: ( 401, 352, 9949716606 ) --- OVERFLOW --- 1: ( 381, NULL) 2: ( 10753, ) <<<<< Blank tel Distribution for eadmin.dunau.zip <<<<<<<<<<<<<<<<<<< Constructed on 08/07/1997 High Mode, 0.500000 Resolution --- DISTRIBUTION --- ( ) 1: ( 444, 179, 01089 ) 2: ( 444, 183, 01740-1034 ) 3: ( 444, 111, 01886 ) 4: ( 444, 131, 02115 ) 5: ( 444, 96, 02173 ) 6: ( 444, 173, 02813-2726 ) ..... ..... ..... 191: ( 444, 341, L4W-4M6 ) 192: ( 444, 354, M4G-3Y2 ) 193: ( 444, 360, N6AX5B7 ) 194: ( 444, 356, S7K-6G5 ) 195: ( 444, 347, V0N 1T0 ) 196: ( 444, 355, V8W 1H8 ) 197: ( 113, 71, Zip code ) --- OVERFLOW --- 1: ( 1220, ) 2: ( 204, 00000 ) 3: ( 139, 92121 ) 4: ( 154, 95054 ) } Distribution for eadmin.dunsoft.tel <<<<<<<<<<<<<<<<<<<< Constructed on 08/07/1997 High Mode, 0.500000 Resolution --- DISTRIBUTION --- ( ) 1: ( 17, 17, 2012563200 ) 2: ( 17, 16, 2015095706 ) 3: ( 17, 14, 2018829797 ) 4: ( 17, 17, 2032959049 ) 5: ( 17, 16, 2038690136 ) 6: ( 17, 17, 2057337600 ) ....... ....... ....... 173: ( 17, 17, 9374555781 ) 174: ( 17, 17, 9414585544 ) 175: ( 17, 17, 9544929191 ) 176: ( 17, 17, 9708797929 ) 177: ( 17, 16, 9729919584 ) 178: ( 1, 1, attention_phone ) --- OVERFLOW --- 1: ( 386, ) <<<<< Blank tel 2: ( 9, 7085756119 ) Distribution for eadmin.dunsoft.zip <<<<<<<<<<<<<<<<<<<<<<<<<<< Constructed on 08/07/1997 High Mode, 0.500000 Resolution --- DISTRIBUTION --- ( ) 1: ( 17, 17, 01432-0368 ) 2: ( 17, 15, 02116 ) 3: ( 17, 12, 02172 ) 4: ( 17, 14, 02766 ) 5: ( 17, 15, 03110 ) 6: ( 17, 15, 03825 ) ....... ....... ....... 183: ( 17, 17, D-70178 ) 184: ( 17, 17, K1H 8P5 ) 185: ( 17, 16, LV-1010 ) 186: ( 17, 17, NSW 2141 ) 187: ( 17, 17, SN9 5NU ) 188: ( 17, 14, V6C 3J8 ) 189: ( 5, 5, ch-8640 ) --- OVERFLOW --- 1: ( 101, ) 2: ( 8, 03242 ) 3: ( 9, 10017 ) 4: ( 6, 12205 ) 5: ( 6, 19107 ) 6: ( 6, 45202 ) 7: ( 10, 60521 ) 8: ( 11, 70160 ) 9: ( 8, 80202 ) 10: ( 6, 87110 ) 11: ( 6, 89102 ) 12: ( 6, 89119 ) 13: ( 6, 92123 ) 14: ( 8, 99503 ) Ominous sign:- no response from Informix yet. Think they have no answer? >>>> Alan Cowan alan.cowan@autodesk.com <<<<