Re[3]: Optimizer Flaws in 7.23 - Simple example

I don't think SET OPTIMIZATION FIRST_ROWS is available until 7.3. Can someone verify this? I think that's what I heard at the conference. Dianne ______________________________ Reply Separator _________________________________ Subject: Re[2]: Optimizer Flaws in 7.23 - Simple example Author: alan.cowan@autodesk.com (ALAN COWAN) at INTERNET Date: 8/8/97 10:03 AM Thanks Heiko but: After: SET OPTIMIZATION FIRST_ROWS; DBACCESS: QUERY: (RESPONSE TIME OPTIMIZATION) <<<<< This is in the sqexplain.out ------ select DISTINCT a.id from dunsoft a, OUTER dunau b where b.zip = a.zip or b.tel = a.tel Estimated Cost: 36716488 <<<<<<<<<<<<<< No better Estimated # of Rows Returned: 1 <<<< Should be >= nbr rows in dunsoft It's an OUTER. 1) eadmin.a: SEQUENTIAL SCAN 2) eadmin.b: SEQUENTIAL SCAN Filters: (eadmin.b.zip = eadmin.a.zip OR eadmin.b.tel = eadmin.a.tel ) ----------------------------------------------------------------------- And in the 4gl prog: SET OPTIMIZATION FIRST_ROWS |_______________________^ | | A grammatical error has been found on line 15, character 25. | The construct is not understandable in its context. | See error number -4373. |__________________________________^ Does Informix realise this is a Chapter 11 type of bug? It is totally unacceptable for this SQL to take 7 hours. dunau 88,000 recs dunsoft 3,400 Index name Owner Type Cluster Columns i_dunau_zip eadmin dupls No zip i_dunau_tel eadmin dupls No tel i_dunsoft_zip eadmin dupls No zip i_dunsoft_tel eadmin dupls No tel Alan ______________________________ Reply Separator _______________________ Subject: Re: Optimizer Flaws in 7.23 - Simple example Author: Heiko Giesselmann <heikog@informix.com> at INTERNET Date: 8/8/97 1:32 PM Alan, though I am an Informix employee I am definitely no optimizer guru (unfortunately I also don't have time to check your test case). Anyway, you may try to use the following statement before running your sql statement: SET OPTIMIZATION FIRST_ROWS; You can switch back to the default optimization strategy using SET OPTIMIZATION ALL_ROWS; This is undocumented (at least for 7.23) and I am not sure if it will help, but it may be worth a try. Please let me know if this helps. Regards, Heiko ALAN COWAN wrote: } 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 3,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 ---@@N