Strange query

When we run the query below we get an unexpectedly large number of rows returned. when we put {+ORDERED} in the query the estimated rows reduces from 9322036 to 12 even though the query plan is exactly the same. Any ideas? select tab_j.fld1, tab_b.fld2, " " as lue, 0 as vue, from tab_j, tab_b, outer ( tab_o, tab_s, tab_e ) where tab_j.fld1 >= '2007-06-19 00:00:00' and tab_j.fld1 <= '2007-06-26 00:00:00' and tab_j.fld11 = 1 and tab_b.fld10 matches '%I%' and tab_b.fld9 in ('N', 'N') and tab_j.fld8 = 'BSTK' and tab_j.fld7 = 'TPB' and tab_j.fld6 = tab_b.fld3 and tab_b.fld3 = tab_o.fld3 and tab_o.fld5 = tab_s.fld5 and tab_s.fld4 = tab_e.fld4 Estimated Cost: 12 Estimated # of Rows Returned: 9322036 1) tab_j: INDEX PATH Filters: (tab_j.fld8 = 'BSTK' AND tab_j.fld7 = 'TPB' ) (1) Index Keys: fld11 fld1 (Serial, fragments: ALL) Lower Index Filter: (tab_j.fld1 >= datetime(2007-06-19 00:00:00) year to second AND tab_j.fld11 = 1 ) Upper Index Filter: tab_j.fld1 <= datetime(2007-06-26 00:00:00) year to second 2) tab_b: INDEX PATH Filters: (tab_b.fld10 MATCHES '%I%' AND tab_b.fld9 IN ('N' , 'N' )) (1) Index Keys: fld3 Lower Index Filter: tab_b.fld3 = tab_j.fld6 NESTED LOOP JOIN 3) tab_o: INDEX PATH (1) Index Keys: fld3 fld14 fld15 Lower Index Filter: tab_o.fld3 = tab_b.fld3 NESTED LOOP JOIN 4) tab_s: INDEX PATH (1) Index Keys: fld5 Lower Index Filter: tab_s.fld5 = tab_o.fld5 NESTED LOOP JOIN 5) tab_e: INDEX PATH (1) Index Keys: fld4 Lower Index Filter: tab_e.fld4 = tab_s.fld4 NESTED LOOP JOIN

7.31.UD3 Solaris 9

On Jun 28, 7:15 am, tich...@excite.com wrote: > When we run the query below we get an unexpectedly large number of > rows returned. when we put {+ORDERED} in the query the estimated rows > reduces from 9322036 to 12 even though the query plan is exactly the > same. Any ideas? Do the actual rows returned differ or only the estimate in the sqexplain.out file? Art S. Kagel

On 28 Jun, 16:54, "Art S. Kagel" <art.ka...@gmail.com> wrote: > On Jun 28, 7:15 am, tich...@excite.com wrote: > > > When we run the query below we get an unexpectedly large number of > > rows returned. when we put {+ORDERED} in the query the estimated rows > > reduces from 9322036 to 12 even though the query plan is exactly the > > same. Any ideas? > > Do the actual rows returned differ or only the estimate in the > sqexplain.out file? > > Art S. Kagel Hi Art, the actual rows differ as well

tichman@excite.com wrote: > On 28 Jun, 16:54, "Art S. Kagel" <art.ka...@gmail.com> wrote: >> On Jun 28, 7:15 am, tich...@excite.com wrote: >> >>> When we run the query below we get an unexpectedly large number of >>> rows returned. when we put {+ORDERED} in the query the estimated rows >>> reduces from 9322036 to 12 even though the query plan is exactly the >>> same. Any ideas? >> Do the actual rows returned differ or only the estimate in the >> sqexplain.out file? > > Hi Art, > the actual rows differ as well Hints may affect the speed at which the answer is produced, but may not affect the results. That indicates a bug to me. Sadly, you said IDS 7.31.UD3; that is several years past being current. Your first step will be to upgrade to the most current release, and then see whether the problem is still there. If so, it is IMNSHO a bug. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0226 -- http://dbi.perl.org/