Re: NULL Values

From: Frank <fvanbortel_at_netscape.net>
Date: Wed, 29 Jan 2003 20:05:58 +0100
Message-ID: <3E382616.8070603_at_netscape.net>

beutler wrote:
> I've problems retrieving a table where the key fields may contain NULL
> VALUES. The first problem is, that Oracle does not seem to distinguish
> between NULL VALUES, Empty Strings or even blank strings (which are
> trimmed to NULL). Isn't there any way to change this behaviour ?
> I want to retrieve the data in logical order, that means for me empty
> fields first. Because Oracle trims empty strings or strings of blanks
> to NULL and by default, returns NULLs last, I tried to use the NULLS
> FIRST clause in my query. But this is very slow, it seams Oracle will
> then no longer use the index if the query constraints via the index
> fields...
> Isn't there any way to retrieve data with empty values first while
> reading using an index ?

Oracle does not support (primary) keys with NULLS... Are you referring to an index, possibly set up to satify Foreign Key lookups?
And... varchar2 columns are not right trimmed; a 'string of spaces' will effectively be spaces, but an empty string is treated as NULL, and stored as such:

SQL> create table strings (col1 varchar2(10)); Table created.

SQL> insert into strings values (' ');
1 row created.

SQL> select dump(col1) from strings;
DUMP(COL1)

---

Typ=1 Len=1: 32

SQL> insert into strings values (' '); 1 row created.

SQL> select dump(col1) from strings;
DUMP(COL1)

---

Typ=1 Len=1: 32
Typ=1 Len=5: 32,32,32,32,32

SQL> insert into strings values ('');
1 row created.

SQL> select dump(col1) from strings;

DUMP(COL1)

---

Typ=1 Len=1: 32
Typ=1 Len=5: 32,32,32,32,32
NULL Which leaves the question how to distinguish between all these "empty" columns...

SQL> insert into strings values('a');
1 row created.

SQL> insert into strings values('Z');
1 row created.

SQL> select col1, dump(col1) from strings order by col1 asc; COL1 DUMP(COL1)

---------- ------------------------------
            Typ=1 Len=1: 32
            Typ=1 Len=5: 32,32,32,32,32
Z          Typ=1 Len=1: 90
a          Typ=1 Len=1: 97
            NULL

1 space, 5 spaces, Z, a, NULL - looks like binary sort to me

SQL> select col1, dump(col1) from strings order by col1 desc; COL1 DUMP(COL1)

---------- ------------------------------
            NULL
a          Typ=1 Len=1: 97
Z          Typ=1 Len=1: 90
            Typ=1 Len=5: 32,32,32,32,32
            Typ=1 Len=1: 32

Yup, still binary.

SQL> alter session set nls_sort=DUTCH;
Session altered.

SQL> select col1, dump(col1) from strings order by col1 desc;

COL1 DUMP(COL1)

---------- ------------------------------
            NULL
Z          Typ=1 Len=1: 90
a          Typ=1 Len=1: 97
            Typ=1 Len=5: 32,32,32,32,32
            Typ=1 Len=1: 32

Hmmm. NULL, Z, a, 5 spaces, 1 space. Used to be NULL, a, Z, 5 spaces, 1 space.

So, define "natural order"...

Frank Received on Wed Jan 29 2003 - 20:05:58 CET