Null values in not null columns on compressed partitioned table

(Doc ID 1201263.1)

Last updated on SEPTEMBER 09, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 to 10.2.0.4 - Release: 10.2 to 10.2
Information in this document applies to any platform.

Symptoms

On a partitioned database in a partitioned compressed table wrong values are seen:

SQL> select rowid, user_group_float10
2 from securitydbo.security_master_detail_hist
3 where user_group_float10 =
4 (
5 select min(user_group_float10)
6 from securitydbo.security_master_detail_hist
7* )

ROWID USER_GROUP_FLOAT10
------------------ ------------------
AABBQHABKAAAOKtAAB -8.95E-116

SQL> select nvl(user_group_float10,-999)
2 from securitydbo.security_master_detail_hist
3 where rowid='AABBQHABKAAAOKtAAB';

NVL(USER_GROUP_FLOAT10,-999)
----------------------------
-999

or null is seen in not null column

It was detected that moving the data "fixes" the issue.
For example moving the partition or creating a table based on the partition.

 created a table t1 as select * from the partition.


SQL> select effective_date, user_group_float9
2 from securitydbo.security_master_detail_hist h
3 where security_alias = 110766
4 and src_intfc_inst in (40)
5 order by effective_date
6 /

EFFECTIVE USER_GROUP_FLOAT9
--------- -----------------
12-JAN-09         ------------>here is null

SQL>
SQL> select effective_date, user_group_float9
2 from t1 h
3 where security_alias = 110766
4 and src_intfc_inst in (40)
5 order by effective_date
6 /

EFFECTIVE USER_GROUP_FLOAT9
--------- -----------------
12-JAN-09 100

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms