Re: Oracle NULL vs '' revisited

On Aug 17, 12:56 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Matthew Harrison wrote:
> > Ok, I'm developing another oracle application where the distinction
> > between NULL and '' will be important.
>
> > Given Oracle doesn't adhere to SQL standards for the distinction, is
> > there any best practices for comparing, and storing blank strings in a
> > table where the field is conceptually NOT NULL.
>
> > Thank you.
>
> SQL> create table t (
> 2 col1 varchar2(10),
> 3 col2 varchar2(10));
>
> Table created.
>
> SQL> INSERT INTO t VALUES (NULL, '');
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> SELECT sys_op_map_nonnull(col1), sys_op_map_nonnull(col2)
> 2 FROM t;
>
> SYS_OP_MAP_NONNULL(COL SYS_OP_MAP_NONNULL(COL
> ---------------------- ----------------------
> FF FF
>
> SQL>
>
> Not unless you create a kludge. For example insert 'ZZYZX' where ever
> you intend an empty string.
>
> The better choice would be, no matter the database product, to never
> have NULLs or empty strings. This can be done with proper design.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Proper design should reflect how the application being modeled works in the real world. In the real world information is often missing or unknown so a proper design will have nullable columns.

In Cost-Based Oracle Fundamentals Jonathin Lewis shows some examples of how using default values in place of nulls can mess up the CBO in its determination of cardinality, which is what other optimizer decisions are based on.

IMHO -- Mark D Powell -- Received on Fri Aug 17 2007 - 13:49:51 CDT