Really strange bug? (View, Partitioning)

(Crosspost)

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

1. 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 Received on Tue Apr 09 2002 - 10:25:18 CDT