| |
 |
|
Oracle 10g bug 4243687
Wrong values from count(*)
By Burleson Consulting
|
Found an interesting bug in
Oracle 10g Release 1 yesterday. While working with a MERGE
command we got the following results:
SQL> select count(*) from
indv_d;
COUNT(*)
----------
62579
SQL> merge into dw1.indv_d f
2 using
3 (select a.cwin, a.dob, nvl(a.ethn_cd,'NA') ethn_cd,
4 nvl(b.ethic_desc,' Not Available') ethic_desc, a.sex_cd,
c.sex_desc, a.prm_lang_cd,
5 nvl(d.lang_name,' Not Available') lang_name
6 from hra1.indv a
7 left outer join cis0.rt_ethic b
8 on a.ethn_cd = b.ethic_cd
9 left outer join cis0.rt_sex c
10 on a.sex_cd = c.sex_cd
11 left outer join cis0.rt_lang d
12 on a.prm_lang_cd = d.lang_cd
13 order by a.cwin
14 ) e
15 on
16 (
17
get_sign(e.cwinto_char(trunc(e.dob))e.ethn_cde.ethic_desce.sex_cd
18 e.sex_desce.prm_lang_cde.lang_name)
=
get_sign(f.cwinto_char(trunc(f.dob))f.ethn_cdf.ethic_descf.sex_cd
f.sex_descf.prm_lang_cdf.lang_name)
19 20 21 22 )
23 when not matched
24 then insert
25 (cwin, dob, ethn_cd,
26 ethic_desc, sex_cd, sex_desc, prm_lang_cd,
27 lang_name, indv_d_as_of)
28 values(e.cwin, e.dob, e.ethn_cd,
29 e.ethic_desc, e.sex_cd, e.sex_desc, e.prm_lang_cd,
30 e.lang_name, to_date('31-may-2005'))
31 ;
3179 rows merged.
SQL> select count(*) from indv_d;
COUNT(*)
----------
62579
SQL> select distinct indv_d_as_of, count(*)
2 from indv_d
3 group by indv_d_as_of;
INDV_D_AS COUNT(*)
--------- ----------
30-APR-05 62579
SQL> commit;
Commit complete.
Hmm…not quite what we expected….let’s do that again…
SQL> merge into dw1.indv_d f
2 using
3 (select a.cwin, a.dob, nvl(a.ethn_cd,'NA') ethn_cd,
4 nvl(b.ethic_desc,' Not Available') ethic_desc, a.sex_cd,
c.sex_desc, a.prm_lang_cd,
5 nvl(d.lang_name,' Not Available') lang_name
6 from hra1.indv a
7 left outer join cis0.rt_ethic b
8 on a.ethn_cd = b.ethic_cd
9 left outer join cis0.rt_sex c
10 on a.sex_cd = c.sex_cd
11 left outer join cis0.rt_lang d
12 on a.prm_lang_cd = d.lang_cd
13 order by a.cwin
14 ) e
15 on
16 (
17
get_sign(e.cwinto_char(trunc(e.dob))e.ethn_cde.ethic_desce.sex_cd
18 e.sex_desce.prm_lang_cde.lang_name)
19 =
20
get_sign(f.cwinto_char(trunc(f.dob))f.ethn_cdf.ethic_descf.sex_cd
21 f.sex_descf.prm_lang_cdf.lang_name)
22 )
23 when not matched
24 then insert
25 (cwin, dob, ethn_cd,
26 ethic_desc, sex_cd, sex_desc, prm_lang_cd,
27 lang_name, indv_d_as_of)
28 values(e.cwin, e.dob, e.ethn_cd,
29 e.ethic_desc, e.sex_cd, e.sex_desc, e.prm_lang_cd,
30 e.lang_name, to_date('31-may-2005'))
31 ;
0 rows merged.
SQL> select count(*) from indv_d;
COUNT(*)
----------
62579
So, something happened to make
it so that the source and target of the merge are no longer out
of sync, but the good old standby “SELECT COUNT(*)” doesn’t seem
to be working…let’s look at some additional queries and see what
happens…
SQL> select count(*)
2 from hra1.indv a
3 left outer join cis0.rt_ethic b
4 on a.ethn_cd = b.ethic_cd
5 left outer join cis0.rt_sex c
6 on a.sex_cd = c.sex_cd
7 left outer join cis0.rt_lang d
8 on a.prm_lang_cd = d.lang_cd
9 order by a.cwin;
COUNT(*)
----------
64570
SQL> select (64570-62579) from dual;
(64570-62579)
-------------
1991
Hmm…seems we have a slight
discrepancy here…let’ do some further investigation.
SQL> select distinct
scn_to_timestamp(ora_rowscn), count(*)
2 from indv_d
3* group by scn_to_timestamp(ora_rowscn)
SQL> /
SCN_TO_TIMESTAMP(ORA_ROWSCN) COUNT(*)
------------------------------------------- --------
27-JUN-05 01.28.57.000000000 PM 2354
27-JUN-05 01.29.00.000000000 PM 12518
27-JUN-05 01.29.03.000000000 PM 7117
27-JUN-05 01.29.06.000000000 PM 8062
27-JUN-05 01.29.09.000000000 PM 7329
27-JUN-05 01.29.12.000000000 PM 7119
27-JUN-05 01.29.15.000000000 PM 7471
27-JUN-05 01.29.18.000000000 PM 7522
27-JUN-05 01.29.21.000000000 PM 2996
27-JUN-05 02.54.22.000000000 PM 3270
10 rows selected.
The total of the SCN counts
above is 65758, so obviously something is hooky with the
COUNT(*) process.
A check of the explain plan for the COUNT(*) (sorry, I didn’t
get a capture of that, it was done in PL/SQL Developer) shows
the COUNT(*) is using one of our bitmap indexes to resolve the
count, which makes sense as the COUNT(*) has been optimized to
use the lowest cost path and since bitmaps also index NULL
values, it should be quickest. However, a quick metalink check
(armed with the proper search criteria now (bitmap count(*))
brings up the following:
BUG: 4243687
Problem statement: COUNT(*) WRONG RESULTS WITH MERGE INSERT AND
BITMAP INDEXES
Which goes on to explain:
While inserting a record with the merge insert, maintenance of
bitmap indexes is not done.
Just a bit of an annoyance.
So be careful out there in 10g release 1 when using bitmaps and
merge based inserts as this is not scheduled to be fixed until
Release 2. Always drop the bitmap indexes before a merge insert
and rebuild the bitmap indexes after your merge completes.
When we dropped the bitmap indexes we got the proper results and
we also got the proper counts after rebuilding them.
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
