Re: Really strange bug? (View, Partitioning)

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

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.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."