3) is actually a must :) Your database should be patched to the latest patchset
(which are regression-tested unlike one-offs).
2) could be helpful too. Just add /*+RULE */ hint to your c) query and see if
it will return expected results. Looking at the plan wouldn't hurt either - this
may explain why Oracle throws away rows it shouldn't.
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Marc Blum" <marc_at_marcblum.de> wrote in message news:3cb33f22.4010526_at_news.online.de...
> On Tue, 09 Apr 2002 20:05:33 +0100, Connor McDonald
> <connor_mcdonald_at_yahoo.com> wrote:
>
> >Marc Blum wrote:
> >>
> >> Hi you all,
> >>
> >> Version: 8.1.7.0 EE on NT
> >>
> >> View MY_VIEW joins several tables, one table is rather big (20.000.000
> >> rows) and monthwise partitioned by column MY_TIMESTAMP.
> >>
> >> a)
> >>
> >> SELECT *
> >> FROM my_view
> >> WHERE adress = 'home sweet home'
> >> AND my_timestamp
> >> BETWEEN to_date('01.03.2002','dd.mm.yyyy')
> >> AND to_date('31.03.2002','dd.mm.yyyy')
> >>
> >> returns say 20 rows
> >>
> >> b)
> >>
> >> SELECT *
> >> FROM my_view
> >> WHERE adress = 'home sweet home'
> >> AND my_timestamp
> >> BETWEEN to_date('01.04.2002','dd.mm.yyyy')
> >> AND to_date('30.04.2002','dd.mm.yyyy')
> >>
> >> returns say 30 rows
> >>
> >> c)
> >>
> >> SELECT *
> >> FROM my_view
> >> WHERE adress = 'home sweet home'
> >> AND my_timestamp
> >> BETWEEN to_date('01.03.2002','dd.mm.yyyy')
> >> AND to_date('30.04.2002','dd.mm.yyyy')
> >>
> >> returns NO ROWS!!!!!!!!
> >>
> >> a) UNION ALL b) returns 50 rows as expected
> >>
> >> Our development database with exactly the same os, version, structure
> >> doesn't show this behaviour.
> >>
> >> Does anybody has seen a similar behaviour? Do I have to call Oracle
> >> Support?
> >>
> >> Sorrily, customer hasn't managed to upgrade to 8.1.7.3 until today :-(
> >>
> >> Thanx for any hints.
> >>
> >> regards
> >> Marc Blum
> >> mailto:marc_at_marcblum.de
> >> http://www.marcblum.de
> >
> >I'm assuming the result sets are distinct (otherwise the UNION - which
> >removes duplicates is going exactly what its meant to do)...
>
> yes, they're distinct. Sorrily I didn't make clear enough, that
>
> a)
> UNION ALL
> b)
>
> was a test, because it should return exactly the same result set as c)
>
>
>
> by the way, it's an UNION ALL: no elimination of duplicates ;-)
>
> >
> >But if not, then check the explain-plans for each. It been known in the
> >past for bugs in the CBO to return incorrect rows.
> >
> hm..so I have to
> - spend come time crawling through the seas of metalink
> - test with RBO
> - insist on upgrading to 8.1.7.3
>
> >hth
> >connor
>
> thank you
>
> regards
> Marc Blum
> mailto:marc_at_marcblum.de
> http://www.marcblum.de
Received on Wed Apr 10 2002 - 01:30:54 CDT
