Re: Really strange bug? (View, Partitioning)

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 Tue Apr 09 2002 - 14:27:37 CDT