Re: Really strange bug? (View, Partitioning)

Thanx so far. I will post my results here, although it will take some days.

On Wed, 10 Apr 2002 10:30:54 +0400, "Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote:

>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
>

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Wed Apr 10 2002 - 14:42:32 CDT