Re[5]: Serious Flaws in 7.23's Optimizer - Not using indexes

I'm the dba in Alan's shop. I tried it set to 0 in the onconfig file. It made no difference. Thanks, Dianne ______________________________ Reply Separator _________________________________ Subject: Re[4]: Serious Flaws in 7.23's Optimizer - Not using indexes Author: ALAN COWAN at hq901po1 Date: 8/6/97 1:20 PM Tried 0 And 1 in my environment (not in onconfig but should do the same job). No difference Thanks anyway, Alan ______________________________ Reply Separator _________________________________ Subject: RE: Serious Flaws in 7.23's Optimizer - Not using indexes Author: Paul Mosser <pmosser@minisoftinc.com> at INTERNET Date: 8/6/97 12:40 PM Try setting OPTCOMPIND (in the onconfig file) to 0, instead of the default 2. ============================== pmosser@minisoftinc.com Paul A. Mosser, Developer/DBA MiniSoft, Inc. Phoenix, AZ U.S.A. ______________________________ Reply Separator _________________________________ Subject: RE: Serious Flaws in 7.23's Optimizer - Not using indexes Alan, My test didn't make any difference to the optimizer. But here's what I did: update statistics medium for table dunau distributions only;dunsoft dunsoldto update statistics high for table dunau (name);id zip dun tel fax update statistics high for table dunsoft (all index columns like above); update statistics high for table dunsoldto (id); This is the recommended approach and what we currently use. From looking at your sql more closely, I believe we have basically hit the same problem that Stuart had with the OR's. I posted to informix-list asking if anyone knew if it had been turned in as a bug (since they said it used to work) or if we had to live with it, but got no response. So I will get the details together and call Informix myself. Dianne _____________________________ Forward Header __________________________________ Subject: Serious Flaws in 7.23's Optimizer - Not using indexes Author: alan.cowan@autodesk.com (ALAN COWAN) at INTERNET Date: 8/6/97 9:38 AM In a nutshell: The Optimizer does not usee indexes even if EVERY join is an indexed field. Lots of problems in other examples including every time I use EXISTS it does a SEQUENTIAL search where an index is available. Informix 5 did not have these problems. In the following SQL: The Optimizer does a SEQUENTIAL SCAN on the largest table, dunau, when it should use the index on dun and the index on id " " " " tel " " " " fax " " " " name " " " " zip select DISTINCT a.id, max(b.id) from dunsoft a, OUTER ( dunau b, dunsoldto c ) where ( ( a.dun != '000000000' and b.dun != '000000000' and b.dun = a.dun ) or ( b.name[1,15] = a.name[1,15] and b.zip = a.zip ) or b.tel = a.tel or b.fax = a.fax ) and c.id = b.id group by a.id" GIVES: Estimated Cost: 36838924 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Estimated # of Rows Returned: 1 1) eadmin.a: INDEX PATH (1) Index Keys: id 2) eadmin.b: SEQUENTIAL SCAN Filters: (((((eadmin.a.dun != '000000000' AND eadmin.b.dun != '000000000' ) AND eadmin.b.dun = eadmin.a.dun ) OR (eadmin.b.name[1,15] = eadmin.a.name[1,15] AND eadmin.b.zip = eadmin.a.zip ) ) OR eadmin.b.tel = eadmin.a.tel ) OR eadmin.b .fax = eadmin.a.fax ) 3) eadmin.c: INDEX PATH (1) Index Keys: id (Key-Only) Lower Index Filter: eadmin.c.id = eadmin.b.id ---------------- FILE SIZES: dunsoft 3,400 dunau 88,000 dunsoldto 67,000 INDEXES: All columns in the joins, each its own index UPDATE STATISTICS:Ran HIGH on all columns update statistics HIGH for table dunau ; update statisticsHIGH for table dunsoft ; update statistics HIGH for table dunsoldto ; update statistics HIGH for table dunsoft (id); update statistics HIGH for table dunsoft (dun) update statistics HIGH for table dunau (id) ; updatestatistics HIGH for table dunau (dun) ; update statistics HIGH for table dunsoldto (id); update statistics HIGH for table dunau (name) ; updatestatistics HIGH for table dunau (zip) ; update statistics HIGH for table dunau (tel) ; update statistics HIGH for table dunau (fax) ; update statistics HIGH for table dunsoft (name); updatestatistics HIGH for table dunsoft (zip); update statistics HIGH for table dunsoft (tel); update statistics HIGH for table dunsoft (fax); Tried MEDIUM on all columns and got a minimal reduction: Estimated Cost: 36780192 (weird) Tried LOW on all columns and same as MEDIUM Even: update statistics LOWfor table dunau DROP DISTRIBUTIONS; update statistics LOWfor table dunsoft DROP DISTRIBUTIONS; update statistics LOWfor table dunsoldto DROP DISTRIBUTIONS; no change --------------------------------- Only got it to work by Totally Converting to UNIONS (not Teamsters): QUERY: --------------- select DISTINCT a.id, max(b.id) from dunsoft a, OUTER ( dunau b, dunsoldto c ) where a.dun !='000000000' and b.dun != '000000000' and b.dun = a.dun and c.id = b.id group by a.id --------- UNION select DISTINCT a.id, max(b.id) from dunsoft a, OUTER ( dunau b, dunsoldto c ) where ( a.dun ='000000000' or b.dun = '000000000' or b.dun != a.dun ) and b.tel = a.tel and c.id = b.id group by a.id --------- UNION select DISTINCT a.id, max(b.id) from dunsoft a, OUTER ( dunau b, dunsoldto c ) where ( ( a.dun= '000000000' or b.dun = '000000000' or b.dun != a.dun ) and b.tel != a.tel ) and ( b.name[1,15] = a.name[1,15] and b.zip = a.zip ) and c.id = b.id group by a.id Estimated Cost: 58853 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Estimated # of Rows Returned: 3 1) eadmin.a: INDEX PATH Filters: eadmin.a.dun != '000000000' (1) Index Keys: id 2) eadmin.b: INDEX PATH Filters: eadmin.b.dun != '000000000' (1) Index Keys: dun Lower Index Filter: eadmin.b.dun = eadmin.a.dun 3) eadmin.c: INDEX PATH (1) Index Keys: id (Key-Only) Lower Index Filter: eadmin.c.id = eadmin.b.id Union Query: ------------ 1) eadmin.a: INDEX PATH (1) Index Keys: id 2) eadmin.b: INDEX PATH Filters: ((eadmin.a.dun = '000000000' OR eadmin.b.dun = '000000000' ) OR ead min.b.dun != eadmin.a.dun ) (1) Index Keys: tel Lower Index Filter: eadmin.b.tel = eadmin.a.tel 3) eadmin.c: INDEX PATH (1) Index Keys: id (Key-Only) Lower Index Filter: eadmin.c.id = eadmin.b.id