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

On Jun 28, 7:15 am, tich... (AT) excite (DOT) com wrote:
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... (AT) gmail (DOT) com> wrote:
Hi Art,
the actual rows differ as well

tichman (AT) excite (DOT) com wrote:
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 (AT) earthlink (DOT) net, jleffler (AT) us (DOT) ibm.com
Guardian of DBD::Informix v2007.0226 -- http://dbi.perl.org/