Help - Search - Members - Calendar
Full Version: Select statement gives different results using bitmap or normal index
Oracle DBA Forums > Oracle > Oracle Forum
holi61
Hi,

we have a strange symptom in a database Oracle 11.2.0.2 EE.

Following question comes from our application developers and I have no idea at the moment what is the reason for this problem ?!

The following SQL statement:

SELECT
v.reporting_month,
sum(v.f_s) "REV_S",
sum(v.f_f) "REV_F",
sum(v.f_c) "REV_C",
sum(v.f_m) "REV_M"
FROM cities cf,
cities ct,
kreditkarten_sets_rs k,
revenues_cy v
WHERE cf.city_code = v.o_city_f
AND ct.city_code = v.o_city_t
AND k.status_ind = 'A'
AND k.nebv_hauv_kons_nr in ('122000200530775', '122000200637687', '122000200912502', '122000201499087', '122000201699090') AND v.kred_nr = k.kred_nr AND v.reporting_month >= k.valid_from AND v.reporting_month <= date_value(k.valid_until) AND v.reporting_month >= '01.04.2011'
AND v.reporting_month <= '30.09.2011'
AND 68861168 = 68861168
group by
v.reporting_month
order by
v.reporting_month;

gives different result when we exchange the index ksr_valid_until_i on table kreditkarten_sets_rs. For some reasons we changed the index from bitmap to normal and are getting different results. Switchting back gives us the same results as before. When we avoid the usage of this index in the statement than we are getting the same results as when we are using the normal index.

Any ideas ?

Regards
Horst
burleson
Hi Horst,

>> For some reasons we changed the index from bitmap to normal and are getting different results.

Definitely a bug! How many distinct values were in the index?

********************************

>> Select statement gives different results using bitmap or normal index

Wow! Great catch!

Inconsistent (incorrect) results are ALWAYS an Oracle bug!

See bug 889611 and 9495697 . . . .

You need tio start by looking on the Oracle bugs database:

http://support.oracle.com

Please let us know the bug number, so we can warn others . . .
holi61
Hello Donald,

thank you for your fast answer.

Will have a look in the Oracle bug database.
If I'll found some informations I'll post it asap.

Thanks.

Kind Regards
Horst
holi61
Hi Donald,

there are several bugs but the one that looks the most likely is 10067750, fixed in 11.2.0.3.

further investigations ongoing ....

With Best Regards
Horst
burleson
H Horst,

>> bug 10067750, fixed in 11.2.0.3.

Thanks for the tip!

Oracke will tell you to upgrade, but if you cannot, you can write-up a "business case" and they may write you a one-off patch for your current release!

Getting "wrong" results is a serious issue, thanks for sharing it with our other readers!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.